How to setup optimistic locking for overlapping datetime?

Question

I am trying to make a booking system, where users can book a booking at a location for a certain time period. The problem I am having is, 2 entries that happen at the same time and overlap in the time period.

If the time periods are the same I solved this with adding a unique constraint (I'm using liquibase):

 <addUniqueConstraint tableName="booking"
 constraintName="unique_location_booking"
 columnNames="location_id, start_time, end_time"/>

But if they happen exactly at the same time and are overlapping, it will store both of them in the db.

For example:

{
 "userId": 7,
 "startTime": "2024年07月04日T12:00:00",
 "endTime": "2024年07月04日T14:00:00",
 "location": {
 "id": 2
 }
},
{
 "userId": 8,
 "startTime": "2024年07月04日T12:00:00",
 "endTime": "2024年07月04日T14:00:00",
 "location": {
 "id": 2
 }
},
{
 "userId": 9,
 "startTime": "2024年07月04日T13:00:00",
 "endTime": "2024年07月04日T15:00:00",
 "location": {
 "id": 2
 }
}

Before I added the constraint all of them would be added to the db, but after the constraint only 2 of them are added since there is an exact duplicate.

{
 "userId": 7,
 "startTime": "2024年07月04日T12:00:00",
 "endTime": "2024年07月04日T14:00:00",
 "location": {
 "id": 2
 }
},
{
 "userId": 8,
 "startTime": "2024年07月04日T12:00:00",
 "endTime": "2024年07月04日T14:00:00",
 "location": {
 "id": 2
 }
}

But,

{
 "userId": 8,
 "startTime": "2024年07月04日T12:00:00",
 "endTime": "2024年07月04日T14:00:00",
 "location": {
 "id": 2
 }
},
{
 "userId": 9,
 "startTime": "2024年07月04日T13:00:00",
 "endTime": "2024年07月04日T15:00:00",
 "location": {
 "id": 2
 }
}

are still added to the db since even if they are not the same exactly time-wise, they still overlap each other.

The way I am checking before adding is this:

@Transactional
public BookingDTO createBooking(Booking booking) {
 Location location = locationRepository.findById(booking.getLocation().getId())
 .orElseThrow(() -> new IllegalArgumentException("Location not found"));
 boolean bookingEndsBeforeItStarts = booking.getEndTime().isBefore(booking.getStartTime());
 if (bookingEndsBeforeItStarts) throw new IllegalArgumentException("Booking can't end before it starts.");
 if (bookingRepository.existsOverlappingBooking(location.getId(), booking.getStartTime())) {
 throw new IllegalArgumentException("Overlapping booking exists for the given location and time.");
 }
 try {
 booking.setLocation(location);
 Booking savedBooking = bookingRepository.save(booking);
 return BookingMapper.toDTO(savedBooking);
 } catch (OptimisticLockException ex) {
 throw new IllegalStateException("Optimistic locking failure occurred", ex);
 }
}

The problem is since they are created at the same time the overlapping check passes, since none of them are yet in the db.

I have tried to do something like this in the repository class:

@Lock(LockModeType.OPTIMISTIC_FORCE_INCREMENT)
Booking save(Booking booking);

But I suspect since the userId and bookingId are different, is why this does not work. What I can add is that in the db the version is always 0.

Entity
@Table(name = "booking")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Booking {
 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 private Long id;
 @Column(name = "user_id", nullable = false)
 private Long userId;
 @ManyToOne
 @JoinColumn(name = "location_id", nullable = false)
 private Location location;
 @Column(name = "start_time", nullable = false)
 private LocalDateTime startTime;
 @Column(name = "end_time", nullable = false)
 private LocalDateTime endTime;
 @Version
 @Column(nullable = false)
 private Long version;
}

Like-wise just adding version like some tutorials claim does not work still.

My question is how can I set this up so it works with optimistic locking.

My repository: https://github.com/VMM-MMV/BookingService

• rzwitserloot: You can't do the checks on 'your' side (java side) and have the optimistic locking occur DB side, which is what e.g. @LockModeType.OPTIMISTIC is all about. So, what you want, is not possible. Instead, move both acts (the check and the write) to a single place. Moving it java side is nuts (you'd have to reinvent the whole db system), which means: Move the check to the DB. Some DB engines, such as postgres, have 'range' types as native db concepts. It's trivial there. But if your DB doesn't, oof. stored procedures and all that. You may want to update the question and indicate the platform.
• user26020733: All I need is to check if 2 intervals overlap, while adding,
• rzwitserloot: You can't, because the thing you need to check against isn't there. And can't be relied upon to be there, because the optimistic locking isn't done by you.