Bazy danych, Matematyka

Ćwiczenia z transakcji

Dziś poobserwujemy, jak transakcje są realizowane w Postgresie. Musimy najpierw otworzyć dwie niezależne sesje psql. W każdej z nich wykonujemy następujące polecenia:

bd=> begin transaction;
BEGIN
bd=> set transaction isolation level serializable;
SET
bd=> select * from Gatunki where kontynent='Europa';
   nazwa    | ochrona | kontynent | przysmak  
------------+---------+-----------+-----------
 jelen      | nie     | Europa    | siano
 niedzwiedz | nie     | Europa    | miod
 rys        | tak     | Europa    | poledwica
 sarna      | nie     | Europa    | marchew
(4 wiersze)

Jak widać, zachowują się identycznie. Teraz w pierwszej sesji robimy

bd=> insert into Gatunki values('krowa','nie','Europa','siano');
INSERT 0 1
bd=> select * from Gatunki where kontynent='Europa';
   nazwa    | ochrona | kontynent | przysmak  
------------+---------+-----------+-----------
 jelen      | nie     | Europa    | siano
 niedzwiedz | nie     | Europa    | miod
 rys        | tak     | Europa    | poledwica
 sarna      | nie     | Europa    | marchew
 krowa      | nie     | Europa    | siano
(5 wierszy)

Można się było tego spodziewać. A co w drugiej sesji?

bd=> select * from Gatunki where kontynent='Europa';
   nazwa    | ochrona | kontynent | przysmak  
------------+---------+-----------+-----------
 jelen      | nie     | Europa    | siano
 niedzwiedz | nie     | Europa    | miod
 rys        | tak     | Europa    | poledwica
 sarna      | nie     | Europa    | marchew
(4 wiersze)

Nie ma krowy. Ale może w pierwszej sesji trzeba zatwierdzić transakcję?

bd=> commit;
COMMIT

Co teraz w drugiej?

bd=> select * from Gatunki where kontynent='Europa';
   nazwa    | ochrona | kontynent | przysmak  
------------+---------+-----------+-----------
 jelen      | nie     | Europa    | siano
 niedzwiedz | nie     | Europa    | miod
 rys        | tak     | Europa    | poledwica
 sarna      | nie     | Europa    | marchew
(4 wiersze)

Dalej nie zniechęceni kończymy transakcję w drugiej sesji i zaglądamy do tabeli

bd=> commit;
COMMIT
bd=> select * from Gatunki where kontynent='Europa';
   nazwa    | ochrona | kontynent | przysmak  
------------+---------+-----------+-----------
 jelen      | nie     | Europa    | siano
 niedzwiedz | nie     | Europa    | miod
 rys        | tak     | Europa    | poledwica
 sarna      | nie     | Europa    | marchew
 krowa      | nie     | Europa    | siano
(5 wierszy)

Wreszcie krowa się znalazła.

Co się stanie, jeśli pochopnie wykonamy jakąś zmianę w bazie danych? Jeśli nie byliśmy wewnątrz transakcji, to przepadło. Program psql normalnie pracuje z flagą AUTOCOMMIT ustawioną na ON, co oznacza, że każde polecenie jest osobną transakcją. Musimy więc wykonać kolejną modyfikację odwracającą skutki poprzedniej.

Jeśli byliśmy wewnątrz transakcji, to zamiast zatwierdzać wycofujemy transakcję

bd=> begin transaction;
BEGIN
bd=> set transaction isolation level serializable;
SET
bd=> delete from Gatunki where nazwa = 'krowa';
DELETE 1
bd=> select * from Gatunki where kontynent='Europa';
   nazwa    | ochrona | kontynent | przysmak  
------------+---------+-----------+-----------
 jelen      | nie     | Europa    | siano
 niedzwiedz | nie     | Europa    | miod
 rys        | tak     | Europa    | poledwica
 sarna      | nie     | Europa    | marchew
(4 wiersze)
bd=> rollback;
ROLLBACK
bd=> select * from Gatunki where kontynent='Europa';
   nazwa    | ochrona | kontynent | przysmak  
------------+---------+-----------+-----------
 jelen      | nie     | Europa    | siano
 niedzwiedz | nie     | Europa    | miod
 rys        | tak     | Europa    | poledwica
 sarna      | nie     | Europa    | marchew
 krowa      | nie     | Europa    | siano
(5 wierszy)

Jak widać, udało się krowę uratować. Uwaga: błąd w trakcie transakcji oznacza anulowanie transakcji:

bd=> begin transaction;
BEGIN
bd=> set transaction isolation level serializable;
SET
bd=> insert into Gatunki values('koza','nie','Europa','siano');
INSERT 0 1
bd=> select * from Gatunki where kontynent=Europa;
BŁĄD:  kolumna "europa" nie istnieje
LINIA 1: select * from Gatunki where kontynent=Europa;
                                               ^
bd=> select * from Gatunki where kontynent='Europa';
BŁĄD:  bieżąca transakcja została przerwana, polecenia ignorowane do końca bloku transakcji
bd=> commit;
ROLLBACK

System wyraźnie przestaje nas lubić i ignoruje kolejne polecenia (w dodatku nas poucza!). Ewentualny COMMIT zamienia na ROLLBACK.

Spróbujmy osłabić poziom izolacji. Jeśli użyjemy

bd=> begin transaction;
BEGIN
bd=> set transaction isolation level repeatable read;
SET
...

to zgodnie ze standardem wstawienia wykonane przez inną transakcję (oczywiście zatwierdzoną) powinny być widoczne. W PostgreSQL 9 tak jednak nie jest. Zachowanie pozornie niczym się nie różni od SERIALIZABLE (standard pozwala na takie wzmocnienie izolacji). Dokumentacja twierdzi jednak, że możemy czasem dostać nieszeregowalne zachowania.

READ COMMITTED jest w Postgresie domyślne. Zobaczmy, jak działa. Na drugiej sesji

bd=> begin transaction;
BEGIN
bd=> select * from Gatunki where kontynent='Europa';
   nazwa    | ochrona | kontynent | przysmak  
------------+---------+-----------+-----------
 jelen      | nie     | Europa    | siano
 niedzwiedz | nie     | Europa    | miod
 rys        | tak     | Europa    | poledwica
 sarna      | nie     | Europa    | marchew
 krowa      | nie     | Europa    | siano
(5 wierszy)

Teraz na pierwszej

bd=> begin transaction;
BEGIN
bd=> insert into Gatunki values('koza','nie','Europa','siano');
INSERT 0 1
bd=> select * from Gatunki where kontynent='Europa';
   nazwa    | ochrona | kontynent | przysmak  
------------+---------+-----------+-----------
 jelen      | nie     | Europa    | siano
 niedzwiedz | nie     | Europa    | miod
 rys        | tak     | Europa    | poledwica
 sarna      | nie     | Europa    | marchew
 krowa      | nie     | Europa    | siano
 koza       | nie     | Europa    | siano
(6 wierszy)
bd=> commit;
COMMIT

i wracamy na drugą sesję

bd=> select * from Gatunki where kontynent='Europa';
   nazwa    | ochrona | kontynent | przysmak  
------------+---------+-----------+-----------
 jelen      | nie     | Europa    | siano
 niedzwiedz | nie     | Europa    | miod
 rys        | tak     | Europa    | poledwica
 sarna      | nie     | Europa    | marchew
 krowa      | nie     | Europa    | siano
 koza       | nie     | Europa    | siano
(6 wierszy)

A więc wewnątrz transakcji widać zmiany w bazie danych z innych zakończonych transakcji.

Co będzie z szeregowalnością, gdy dwie transakcje naraz wykonają konfliktowe modyfikacje? W pierwszej sesji

bd=> begin transaction;
BEGIN
bd=> set transaction isolation level serializable;
SET
bd=> update Gatunki set kontynent='Azja' where kontynent='Europa';
UPDATE 6

Teraz druga

bd=> begin transaction;
BEGIN
bd=> set transaction isolation level serializable;
SET
bd=> update Gatunki set kontynent='Europa' where kontynent='Azja';
UPDATE 1
bd=> commit;
COMMIT

I co dalej z pierwszą sesją?

bd=> commit;
BŁĄD:  nie można serializować dostępu ze względu na zależności odczytu/zapisu między transakcjami
SZCZEGÓŁY:  Reason code: Canceled on identification as a pivot, during commit attempt.
PODPOWIEDŹ:  Transakcja może się powieść po powtórzeniu.

Popatrzmy na różnicę (w Postgresie) między SERIALIZABLE i REPEATABLE READ. W pierwszej sesji

bd=> begin transaction;
BEGIN
bd=> set transaction isolation level serializable;
SET
bd=> insert into Gatunki values ('stonoga','nie','Azja','nie wiem');
INSERT 0 1
bd=> select * from Gatunki;

a w drugiej

bd=> begin transaction;
BEGIN
bd=> set transaction isolation level serializable;
SET
bd=> insert into Gatunki values ('kogut','nie','Azja','ziarno');
INSERT 0 1
bd=> select * from Gatunki;

Teraz w drugiej

bd=> commit;
COMMIT

ale w pierwszej

bd=> commit;
BŁĄD:  nie można serializować dostępu ze względu na zależności odczytu/zapisu między transakcjami
SZCZEGÓŁY:  Reason code: Canceled on identification as a pivot, during commit attempt.
PODPOWIEDŹ:  Transakcja może się powieść po powtórzeniu.

A teraz niewielka zmiana w pierwszej

bd=> begin transaction;
BEGIN
bd=> set transaction isolation level repeatable read;;
SET
bd=> insert into Gatunki values ('stonoga','nie','Azja','nie wiem');
INSERT 0 1
bd=> select * from Gatunki where kontynent = 'Azja';
  nazwa  | ochrona | kontynent | przysmak 
---------+---------+-----------+----------
 kogut   | nie     | Azja      | ziarno
 stonoga | nie     | Azja      | nie wiem
(2 wiersze)

oraz w drugiej

bd=> begin transaction;
BEGIN
bd=> set transaction isolation level repeatable read;
SET
bd=> insert into Gatunki values ('kret','nie','Azja','nie wiem');
INSERT 0 1
bd=> select * from Gatunki where kontynent = 'Azja';
  nazwa  | ochrona | kontynent | przysmak 
---------+---------+-----------+----------
 kogut   | nie     | Azja      | ziarno
 kret    | nie     | Azja      | nie wiem
(2 wiersze)

Teraz w drugiej

bd=> commit;
COMMIT

i w pierwszej też

bd=> commit;
COMMIT

W obu sesjach to samo

bd=> select * from Gatunki where kontynent = 'Azja';
  nazwa  | ochrona | kontynent | przysmak 
---------+---------+-----------+----------
 kogut   | nie     | Azja      | ziarno
 stonoga | nie     | Azja      | nie wiem
 kret    | nie     | Azja      | nie wiem
(3 wiersze)

Czasem jednak się udaje nawet na poziomie SERIALIZABLE

bd=> begin transaction;
BEGIN
bd=> set transaction isolation level serializable;
SET
bd=> insert into Gatunki values ('stonoga','nie','Azja','nie wiem');
INSERT 0 1

a w drugiej

bd=> begin transaction;
BEGIN
bd=> set transaction isolation level serializable;
SET
bd=> insert into Gatunki values ('kret','nie','Azja','ziarno');
INSERT 0 1

Teraz w drugiej

bd=> commit;
COMMIT

i w pierwszej

bd=> commit;
COMMIT
bd=> select * from Gatunki where kontynent = 'Azja';
  nazwa  | ochrona | kontynent | przysmak 
---------+---------+-----------+----------
 stonoga | nie     | Azja      | nie wiem
 kret    | nie     | Azja      | nie wiem
(2 wiersze)

Wniosek: unikać mieszania modyfikacji i SELECTów w jednaj transakcji.

Na koniec zakleszczenie. W pierwszej sesji

bd=> begin transaction;
BEGIN
bd=> update Gatunki set przysmak='kasza' where nazwa='stonoga';
UPDATE 1

a w drugiej

bd=> begin transaction;
BEGIN
bd=> update Gatunki set przysmak='kasza' where nazwa='kret';
UPDATE 1

Teraz w pierwszej

bd=> update Gatunki set przysmak='korzonki' where nazwa='kret';

Zauważcie, że nie ma odpowiedzi! No to w drugiej

bd=> update Gatunki set przysmak='korzonki' where nazwa='stonoga';
BŁĄD:  wykryto zakleszczenie
SZCZEGÓŁY:  Proces 4934 oczekuje na ShareLock na transakcja 884; zablokowany przez 4946.
Proces 4946 oczekuje na ShareLock na transakcja 885; zablokowany przez 4934.
PODPOWIEDŹ:  Przejrzyj dziennik serwera by znaleźć szczegóły zapytania.
KONTEKST:  podczas modyfikacji krotki (0,45) w relacji "gatunki"

i teraz pierwsza sesja rusza

UPDATE 1
bd=> commit;
COMMIT

ale drugiej już nie ma

bd=> commit;
ROLLBACK
bd=> select * from Gatunki where kontynent = 'Azja';
  nazwa  | ochrona | kontynent | przysmak 
---------+---------+-----------+----------
 stonoga | nie     | Azja      | kasza
 kret    | nie     | Azja      | korzonki
(2 wiersze)

Coś się jednak zmieniło.

Wypróbujcie własne scenariusze -- może znajdziecie coś ciekawego?