Adding reservations without time conflicts

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')
        ->select('rsvn.id')
        ->where('rsvn.room = :roomId')
        ->andWhere(':beginTime < rsvn.end_time')
        ->andWhere(':endTime > rsvn.begin_time')
        ->setMaxResults($limit)
        ->setParameters([
            'roomId' => $rsvn->getRoom()->getId(),
            'beginTime' => $rsvn->getBeginTime(),
            'endTime' => $rsvn->getEndTime(),
        ])
        ->getQuery()->getResult();
}
// ReservationController.php
$em = $this->getDoctrine()->getManager();
$repo = $this->getDoctrine()->getRepository(Reservation::class);
$em->beginTransaction();
try {
    if (count($repo->getConflictIds($rsvn)) > 0) {
        throw new Exception();
    }
    $em->persist($rsvn);
    $em->flush();
    $em->commit();
    // redirect
} catch (Exception $e) {
    $em->rollback();
}

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;
  3. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  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

Source: Symfony Questions

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *