Blokowanie w Postgres dla kombinacji UPDATE / INSERT

Mam dwa stoliki. Jednym z nich jest tabela dziennika; inny zawiera zasadniczo kody kuponów, których można użyć tylko raz.

Użytkownik musi mieć możliwość zrealizowania kuponu, który wstawi wiersz do tabeli dziennika i oznaczy kupon jako wykorzystany (aktualizując usedkolumnę do true).

Oczywiście jest tutaj oczywisty problem z wyścigiem / kwestią bezpieczeństwa.

W przeszłości robiłem podobne rzeczy w świecie mySQL. W tym świecie zablokowałbym oba tabele globalnie, wykonałem logikę, wiedząc, że może się to zdarzyć tylko raz na raz, a następnie odblokowałem tabele, gdy skończyłem.

Czy jest to lepszy sposób w Postgres? W szczególności obawiam się, że blokada ma charakter globalny, ale nie musi tak być - naprawdę muszę tylko upewnić się, że nikt inny nie próbuje wprowadzić tego konkretnego kodu, więc może zadziałałoby blokowanie na poziomie wiersza?

Słyszałem wcześniej o takich problemach dotyczących współbieżności w MySQL. Nie w Postgres.

Wystarczy wbudowane blokady na poziomie wiersza w domyślnym READ COMMITTEDpoziomie izolacji transakcji .

Sugeruję pojedynczą instrukcję z modyfikującym dane CTE (coś, czego MySQL również nie ma), ponieważ wygodnie jest przekazywać wartości bezpośrednio z jednej tabeli do drugiej (jeśli jest to potrzebne). Jeśli nie potrzebujesz niczego z coupontabeli, możesz również użyć transakcji z osobnymi UPDATEi INSERTwyciągami.

WITH upd AS (
   UPDATE coupon
   SET    used = true
   WHERE  coupon_id = 123
   AND    NOT used
   RETURNING coupon_id, other_column
   )
INSERT INTO log (coupon_id, other_column)
SELECT coupon_id, other_column FROM upd;

To powinno być rzadkie rzeczą, która stara się więcej niż jednej transakcji, aby odkupić ten sam kupon. Mają unikalny numer, prawda? Jednak więcej niż jedna transakcja w tym samym momencie powinna być znacznie rzadsza. (Może błąd aplikacji lub ktoś próbuje zagrać w system?)

Tak czy inaczej, UPDATEtylko jedna transakcja się powiedzie, bez względu na wszystko. UPDATENabywa blokady na poziomie wiersza w każdym wierszu docelowym przed aktualizacją. Jeśli współbieżna transakcja próbuje UPDATEtego samego wiersza, zobaczy blokadę w wierszu i zaczeka na zakończenie transakcji blokującej ( ROLLBACKlub COMMIT), a następnie będzie pierwszą w kolejce blokowania:

• W przypadku zatwierdzenia sprawdź ponownie warunek. Jeśli nadal jest NOT used, zablokuj rząd i kontynuuj. W przeciwnym UPDATErazie teraz nie znajdzie żadnego kwalifikującego się wiersza i nic nie robi, nie zwraca żadnego wiersza, więc INSERTrównież nic nie robi.

• Jeśli wycofano, zablokuj rząd i kontynuuj.

Nie ma możliwości wystąpienia warunków wyścigu .

Nie ma możliwości impasu, chyba że włożysz więcej zapisów w tę samą transakcję lub w inny sposób zablokujesz więcej wierszy niż tylko jeden.

INSERTJest beztroskie. Jeśli przez jakiś błąd coupon_idjuż znajduje się w logtabeli (i masz ograniczenie UNIKALNE lub PK log.coupon_id), cała transakcja zostanie wycofana po unikalnym naruszeniu. Wskazuje na nielegalny stan w twojej bazie danych. Jeśli powyższa instrukcja jest jedynym sposobem zapisu do logtabeli, nigdy nie powinno to nastąpić.