Miesiąc marzec chce zapisać jako naukę o transakcjach. Zacznę od podstaw, o których powinien każdy developer/administrator baz danych wiedzieć.
Transakcja jest zbiorem operacji (SELECT/INSERT/ UPDATE/DELETE), którą chcemy potraktować jako całość. Jeśli w trakcie działania tych operacji, zajdzie błąd, wszystkie zmiany zostaną cofnięte, aż do miejsca gdzie transakcja się rozpoczęła. Dlatego albo wszystko, albo nic.
Transakcja powinna spełniać zasady
ACID (
Atomicity, Consistency, Isolation, Durability). Co one oznaczają?
- atomicity (atomowość) - każda transakcja jest wykonywana w całości, albo w ogóle,
- consistency (spójność) - po zatwierdzeniu transakcji, wszystkie dane będą spójne ze zdefiniowanymi zasadami tj. constraintami, triggerami i innymi.
- isolation (izolacja) - każda transakcja w trakcie jej działania nie widzi zmian innej transakcji, działającej współbieżnie (jest to zazwyczaj uzależnione od poziomu izolacji, ale o tym później)
- durability (trwałość) - zatwierdzona transakcja jest powtarzalna nawet po awarii.
W jaki sposób bazy danych, w moim przypadku MySQL (engine InnoDB) i Postgresql, zapewniają tą zasadę?
MySQL:
- polecenia SQL:
- START TRANSACTION lub BEGIN - rozpoczęcie transakcji,
- COMMIT - zatwierdzenie transakcji
- ROLLBACK - cofnięcie transakcji
- SET autocommit = {1 | 0} - włączenie/wyłączenie sesji jako autocommit (czyli każde pojedyncze zapytanie jest wykonywane w transakcji)
- doublewrite buffer - zanim zmiany zostaną zapisane w plikach danych, InnoDB w pierwszej kolejności zapisuje je do obszaru nazywanego 'doublewrite buffer'. Dopiero po tym, Innodb zapisuje zmiany w plikach danych w odpowiedniej pozycji. Jeśli MySQL padnie w trakcie zapisu do plików danych, InnoDB może łatwo znaleźć te strony z doublewrite buffer-a w trakcie działania procesu crash recovery.
- crash recovery - następuje po starcie serwera po jego crash-u, czyli niespodziewanym zamknięciu gdzie serwer nie mógł zrobić tego w normalnym trybie. Dla InnoDB transakcie niezakończone poprawnie, są odtwarzanie używając danych z redo log. Zmiany oznaczone jako commited przed crash-em, ale nie zapisane w plikach danych, są rekostruowane z doublewirte buffer-a
- domyślnie każda sesja jest ustawiona jako autocommit i każdej transakcji jest ustawiony poziom izolacji
PostgreSQL:
- polecenia SQL:
- START TRANSACTION lub BEGIN - rozpoczęcie transkacji,
- COMMIT - zatwierdzenie transakcji,
- ROLLBACK - cofnięcie zmian transakcji,
- SET AUTOCOMMIT { = | TO } { ON | OFF } - włączenie/wyłączenie sesji jako autocommit
- mechanizm MVCC (Multiversion Concurrency Control) - w trakcie querowania danych każda transakcja widzi swoją wersję danych. A tak szczegółowo: Kiedy transakcja się zaczyna, dla wiersza, na którym dokonywane są zmiany, przypisywany jest ID transakcji, czyli XID, jako xmin (INSERT) lub xmax (UPDATE, DELETE). Wszystkie wiersze/polecenia, które były zatwierdzone (np komendą COMMIT) i mają mniejszą wartość xmin/xmax od obecnej wartości transakcji XID, są widoczne (wynik tego polecenia jest widoczny) dla innych transakcji.
Aby podglądnąć ID bieżącej transakcji, możemy wykorzystać funkcję: txid_current.
postgres@test(127.0.0.1) # START TRANSACTION;
START TRANSACTION
postgres@test(127.0.0.1) # SELECT txid_current();
txid_current
--------------
4325
(1 row)
postgres@test(127.0.0.1) # insert into test (c1,c2,c3) values (7,'test7', now());
INSERT 0 1
postgres@test(127.0.0.1) # COMMIT;
COMMIT
postgres@test(127.0.0.1) # SELECT txid_current();
txid_current
--------------
4326
(1 row)
postgres@test(127.0.0.1) # select *, xmin, xmax from test;
c1 | c2 | c3 | xmin | xmax
----+-------+----------------------------+------+------
1 | test | 2014-01-07 13:30:23.820911 | 4192 | 0
2 | test2 | 2014-01-07 13:30:29.892732 | 4193 | 4202
3 | test3 | 2014-01-07 13:30:36.644758 | 4194 | 4207
4 | test4 | 2014-01-07 13:30:43.048741 | 4195 | 4210
5 | test5 | 2014-02-26 13:35:31.624215 | 4321 | 0
6 | test6 | 2014-02-26 13:36:22.57615 | 4323 | 0
7 | test7 | 2014-02-26 13:38:31.088144 | 4325 | 0
(7 rows)
- czytanie nie blokuje pisania i pisanie nie blokuje czytania. Zapisy tylko blokują się wzajemnie, gdy updatowane są te same wiersze
- zmiany w wierszu są widoczne dopiero po zakończeniu i zacommitowaniu transakcji. W trakcie działania transakcji w pliku pg_log dla danego XID, mamy 2-bitowy stan: 'in progress', 'commited' lub 'aborted'. Jeśli w trakcie działania transakcji nastąpi błąd, ale stan transakcji nie zostanie zmieniony na 'aborted' (transakcja nie działa ale nadal jest w stanie 'in progress'), po jakimś czasie inny proces go na ten stan zmieni. Żadne zmiany nie zostaną zapisane w tabeli, póki transakcja nie jest zatwierdzona
- mechanizm Write-Ahead Logging (WAL, link do dok tutaj) jest standardową metodą zapewniającą integralność danych gdzie wszystkie zamiany, zanim zostaną zapisane w tabelach i index-ach, w pierwszej kolejności są logowane w plikach WAL.
Poziomy izolacji
Poziomy izolacji definiują zasady w jaki sposób efekty działania danej transakcji są widoczne i wpływają na inne transakcje, działające współbieżnie.
- READ UNCOMMITED - w transakcjach są widoczne zmiany, które nie zostały jeszcze zatwierdzone przez inne transakcje
- READ COMMITED - widziane są tylko zmiany zatwierdzone
- REPEATABLE READS - zapewnia powtarzalność odczytu. Gdy transakcja A zlicza dane z tabeli, a transakcja B doda nowy rekord, to gdy transakcja A powtórzy swoja operację i tak otrzyma ten sam wynik co za pierwszym razem
- SERIALIZABLE - wymaga blokowania operacji czytania i pisania. Transakcje działają na "zamrożonym" stanie bazy danych
Anomalie - możliwe niepoprawne zdarzenia
- Dirty Reads - jedna transakcja widzi niezatwierdzone zmiany innej transakcji
- Non-Repeatable Reads - w obrębie jednej transakcji kilkukrotny odczyt tego samego wiersza daje różne wartości, bo wiersz został zmieniony przez inną transakcję
- Phantom Reads - jest specjalnym przypadkiem Non-Repeatable Reads. Zwracany jest różny zbór danych, bo warunek z tego SELECT-u zawiera zmiany innych transakcji
Anomalie a poziomy izolacji:
| Poziom izolacji | Dirty reads | Non-repeatable reads | Phantoms |
| Read Uncommitted | Są możliwe | Są możliwe | Są możliwe |
| Read Committed | - | Są możliwe | Są możliwe |
| Repeatable Read | - | - | Są możliwe |
| Serializable | - | - | - |
MySQL
Rozpoczęcie transakcji z ustawieniem poziomu izolacji:
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE }
Domyślnym poziomem izolacji dla InnoDB to REPEATABLE READ.
Aby sprawdzić jaki poziom izolacji jest ustawiony, wykonaj polecenie:
SHOW VARIABLES LIKE 'tx_isolation';
PostgreSQL
Rozpoczęcie transakcji z ustawieniem poziomu izolacji:
SET TRANSACTION ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
W praktyce PostgreSQL zapewnia tylko trzy pierwsze poziomy izolacji. Kiedy ustawiasz READ UNCOMMITED jako poziom izolacji swojej sesji, to tak naprawdę otrzymujesz READ COMMITED. Innym odstępstwem od standardów jest to że anomalia Phantom Reads nie jest możliwa przy użyciu Repeatable Read.
READ COMMITED jest domyślnym poziomem izolacji w PostgreSQL-u. Aby sprawdzić jaki jest ustawiony poziom izolacji, wykonaj polecenie:
SELECT CURRENT_SETTING('TRANSACTION_ISOLATION');
Chciała bym aby ten post był wstępem do postów na temat lokowania tabel/wierszy.
