Dodawanie rezerwacji sali bez konfliktu

Dzień dobry,
chciałbym dodać jedną lub wiele (maks. kilkadziesiąt) rezerwacji do sali, tak, żeby nie wywoływały one konfliktu czasowego z już istniejącymi.

Każda rezerwacja posiada m.in: room_id (foreign key), begin_time, end_time

Początkowo wydawało mi się, że wystarczy sprawdzić, czy istnieje zachodzący czasowo wiersz, a jeśli nie, to dodać rezerwację. Uzmysłowiłem sobie jednak, że przy dużym obciążeniu może się zdarzyć, że klient1 sprawdzi brak konfliktu, a następnie klient2 dostanie brak konfliktu, jeżeli klient1 nie zdąży jeszcze wykonać INSERT i ostatecznie obie zachodzące na siebie rezerwacje trafią do tabeli. Z tego, co wygooglowałem, można się przed tym zabezpieczyć na dwa sposoby wykorzystujące transakcje:

A. Wykonać SELECT … FOR UPDATE na wierszu sali, co jak rozumiem zablokuje innych klientów, na czas operacji, a następnie przed dodaniem każdej rezerwacji (INSERT) wykonywać zapytanie sprawdzające (SELECT), czy nie ma konfliktu. W przypadku konfliktu nie dodawać.

B. Dodawać rezerwacje po kolei (INSERT) i sprawdzać czy poza dodanym wierszem, jest jeszcze jakiś inny, który wywołuje konflikt (SELECT), jeśli tak, to ROLLBACK.

Mam dwa pytania:

1. Które podejście jest lepsze?

2. Czy w takiej sytuacji potrzebna jest jakaś optymalizacja, jeśli chodzi o ilość zapytań do bazy? Np. Multiple INSERT oraz SELECT z przerośniętą klauzulą WHERE z warunkami czasowymi dla wszystkich dodawanych wierszy?

Kto ma robić te rezerwacje? Mam wrażenie, że próbujesz rozwiać problem, który nie istnieje. Jak rezerwacje spływają z jakiejś aplikacji frontowej i jest stosunkowo niedużo tego, to zablokowanie tego innym jest dobrym rozwiązaniem. Inną opcją jest też zrobienie tego asynchronicznie. Użytkownik dodaje zapotrzebowanie na rezerwacje. System obsługuje kolejkę zapotrzebowań i rezerwuje zasoby jak się da, jak nie to wysyła do użytkownika, że niestety musi poszukać innego terminu i wysłać mu listę dostępnych terminów.

UglyMan napisał(a):

Kto ma robić te rezerwacje? Mam wrażenie, że próbujesz rozwiać problem, który nie istnieje.

Hej, dziękuję za odpowiedź.
Użytkowników może być ok. 1000, sal ok. 300, ilość rezerwacji szacuje na milion rocznie.
Niektórzy użytkownicy będą mieli ograniczenia, które będą znoszone w czasie, w związku z tym może się zdarzyć, że w tym samym momencie kilka osób będzie próbowało zarezerwować lepszą salą na podobną godzinę na zasadzie „kto pierwszy ten lepszy”, aczkolwiek w tym przypadku to będą pojedyncze rezerwacje.

Jak myślisz, czy taki stan rzeczy można zaliczyć do kategorii „stosunkowo niedużo”?

Jak na razie frontend i backend tworzę w PHP/Symfony/Doctrine. Opcja asynchroniczna w tych technologiach wydaje mi się nieco skomplikowana. Byłoby najlepiej, gdyby informacja o pomyślnej rezerwacji pojawiała się natychmiast po przeładowaniu strony.

Ja bym na razie został przy blokowaniu innym dostępu. Możesz też zrobić tak, że, jak robisz zapis otwierasz transakcje i blokujesz zapis innym. Sprawdzasz, czy termin jest dostępny jak tak robisz rezerwacje. Jak nie wywalasz błędem. Taki select for update na oknie może spowodować, że ktoś otworzy okno pójdzie sobie gdzieś i nikt nie będzie mógł nic zrobić.

To co opisałeś to klasyczny przykład write skew. Jakiej bazy danych używasz? Jeśli wspiera poziom izolacji serializable to możesz go użyć i będziesz miał gwarancję konsystencji (kosztem performance).

Alternatywą jest podejście w stylu materializing conflicts:

Z książki Designing Data-Intensive Applications

UglyMan napisał(a):

Ja bym na razie został przy blokowaniu innym dostępu. Możesz też zrobić tak, że, jak robisz zapis otwierasz transakcje i blokujesz zapis innym. Sprawdzasz, czy termin jest dostępny jak tak robisz rezerwacje. Jak nie wywalasz błędem.

Masz tu na myśli blokadę całej tabeli LOCK TABLE reservations WRITE ? Początkowo wydawało mi się, że select for update na jednym wierszu z tabeli rooms, spowoduje mniejsze opóźnienia systemu, niż blokada całej tabeli reservations.

Taki select for update na oknie może spowodować, że ktoś otworzy okno pójdzie sobie gdzieś i nikt nie będzie mógł nic zrobić.

Nie do końca rozumiem jak coś takiego mogłoby zaistnieć. Może wstawię przykładowy kod dodawania pojedynczej rezerwacji:

/** @var \Doctrine\ORM\EntityManagerInterface */
$em = $this->getDoctrine()->getManager();
/** @var ReservationRepository */
$repo = $this->getDoctrine()->getRepository(Reservation::class);

Opcja A

$em->beginTransaction();
try {
    $em->find(Room::class, $rsvn->getRoom()->getId(), LockMode::PESSIMISTIC_WRITE);
    // doctrine tworzy zapytanie: SELECT ... FROM rooms WHERE id = ? FOR UPDATE
    $ids = $repo->getConflictIds($rsvn, 1); // (Reservation $rsvn, int $limit)
    if (count($ids) > 0) {
        throw new Exception();
    }
    $em->persist($rsvn);
    $em->flush();
    $em->commit();
    // redirect
} catch (\Exception $e) {
    $em->rollback();
}

Opcja B

$em->beginTransaction();
try {
    $em->persist($rsvn);
    $em->flush();
    $ids = $repo->getConflictIds($rsvn, 2); // (Reservation $rsvn, int $limit)
    if (count($ids) > 1) {
        throw new Exception();
    }
    $em->commit();
    // redirect
} catch (\Exception $e) {
    $em->rollback();
}

damianem napisał(a):

To co opisałeś to klasyczny przykład write skew. Jakiej bazy danych używasz? Jeśli wspiera poziom izolacji serializable to możesz go użyć i będziesz miał gwarancję konsystencji (kosztem performance).

Baza to MySQL lub MariaDB, z tego, co czytałem, obsługuje serializable. W doctrine jest nawet dedykowane wywołanie:
$em->getConnection()->setTransactionIsolation(TransactionIsolationLevel::SERIALIZABLE);

Nie do końca rozumiem dokumentacje. Z jednej strony jest napisane, że można mieć pewność, że to działa tak jakby transakcje wykonywały się po kolei. Z drugiej strony nie mogę znaleźć informacji, że to zablokuje innemu klientowi wykonanie SELECTa sprawdzającego, czy przedział czasowy jest dostępny :(

Alternatywą jest podejście w stylu materializing conflicts:

Fajne, ale trochę skomplikowane :)

Nie znam się na PHP to tu nie pomogę. Ale musisz zablokować tabele z rezerwacjami - tak żeby nikt inny nie mógł tam nic wstawić w tym czasie. Nie wiem jakiej bazy używasz, ale na niektórych bazach jak są indeksy to możliwe jest tylko blokada na całej tabeli, a nie pojedynczym wierszu. Ja by to zrobił tak: złożył locka na tabeli z rezerwacjami, sprawdził, czy termin jest wolny, jak tak zakładam rezerwacje, zdejmuje loka. Tylko trzeba dobrze obsłużyć wyjątki i wszelkie możliwe wypierduszki, żeby ci lock nie wisiał.