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-04T12:00:00",
    "endTime": "2024-07-04T14:00:00",
    "location": {
        "id": 2
    }
},
{
    "userId": 8,
    "startTime": "2024-07-04T12:00:00",
    "endTime": "2024-07-04T14:00:00",
    "location": {
        "id": 2
    }
},
{
    "userId": 9,
    "startTime": "2024-07-04T13:00:00",
    "endTime": "2024-07-04T15: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-04T12:00:00",
    "endTime": "2024-07-04T14:00:00",
    "location": {
        "id": 2
    }
},
{
    "userId": 8,
    "startTime": "2024-07-04T12:00:00",
    "endTime": "2024-07-04T14:00:00",
    "location": {
        "id": 2
    }
}

But,

{
    "userId": 8,
    "startTime": "2024-07-04T12:00:00",
    "endTime": "2024-07-04T14:00:00",
    "location": {
        "id": 2
    }
},
{
    "userId": 9,
    "startTime": "2024-07-04T13:00:00",
    "endTime": "2024-07-04T15: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