Your Location is: Home > Symfony

Adding reservations without time conflicts

From: Tanzania View: 3802 Mateusz Piechnat 


I develop a reservation system (using PHP, MariaDB, Symfony and Doctrine), in which each user can book a room at any desired time, but cannot add a reservation, which overlaps another one in the same room. Therefore, before each INSERT, I perform a SELECT to check if the reservation time is available.

// ReservationRepository.php
public function getConflictIds(Reservation $rsvn, int $limit = 1): array
    return $this->createQueryBuilder('rsvn')
        ->where(' = :roomId')
        ->andWhere(':beginTime < rsvn.end_time')
        ->andWhere(':endTime > rsvn.begin_time')
            'roomId' => $rsvn->getRoom()->getId(),
            'beginTime' => $rsvn->getBeginTime(),
            'endTime' => $rsvn->getEndTime(),
// ReservationController.php
$em = $this->getDoctrine()->getManager();
$repo = $this->getDoctrine()->getRepository(Reservation::class);
try {
    if (count($repo->getConflictIds($rsvn)) > 0) {
        throw new Exception();
    // redirect
} catch (\Exception $e) {

I am afraid that the first user will check if the reservation is available (SELECT), while the second user will also check this before the first user performs an INSERT, so both of them will add bookings that may overlap.

I think it would be best to block other users from checking for availability (SELECT) before the first one ends transaction, but I am open to other solutions. I am considering four options, but I don't know which one will be the best in this case:

  1. LOCK TABLE reservations WRITE;
  2. SELECT * FROM rooms WHERE id = ? FOR UPDATE;
  4. Perform SELECT after INSERT and do ROLLBACK if, apart from the added row, there is another one that causes a conflict.

Any suggestions Thanks

Best answer