Transakcje i poziomy izolacji w SQL

Transakcje w SQL zapewniają spójność danych w warunkach współbieżności. O tym, co zobaczysz w trakcie, decydują poziomy izolacji. Jeśli dopiero zaczynasz pracę z zapytaniami, zobacz też: Podstawy SQL: SELECT, WHERE, JOIN, GROUP BY.

Zasada ACID w praktyce

ACID to cztery gwarancje: Atomicity, Consistency, Isolation, Durability. Atomicity i Consistency egzekwują integralność przy błędach i naruszeniach constraintów. Isolation definiuje, jak izolacja transakcji wpływa na widoczność zmian innych transakcji. Durability oznacza trwałość po COMMIT (np. dzięki dziennikowi WAL i fsync).

W praktyce ACID widać np. w przelewie między kontami: odejmij z jednego, dodaj do drugiego, a jeśli drugi UPDATE się nie powiedzie, wycofaj całość. Durability gwarantuje, że po potwierdzeniu COMMIT nawet awaria serwera nie cofnie zmian.

SQL
Kopiuj
1
2
3
4
5
6
-- Przykład: prosty przelew w jednej transakcji
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- constrainty (np. balance >= 0) mogą przerwać transakcję
COMMIT; -- albo ROLLBACK w razie błędu

Jak działają poziomy izolacji

Standard SQL definiuje cztery poziomy izolacji: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Im wyższy poziom, tym więcej gwarancji spójności, ale zwykle mniejsza równoległość. Implementacje używają MVCC i/lub blokad. To, czy zobaczysz efekt innych transakcji, zależy od izolacji oraz typu odczytu (zwykły SELECT kontra SELECT ... FOR UPDATE).

Domyślnie PostgreSQL używa Read Committed, a MySQL/InnoDB – Repeatable Read. Warto świadomie ustawiać izolację dla krytycznych fragmentów. W wielu przypadkach wystarczy Read Committed, a operacje wymagające silnej izolacji zabezpiecza się przez SELECT ... FOR UPDATE.

SQL
Kopiuj
1
2
3
4
5
6
-- Ustawienie izolacji dla bieżącej transakcji i użycie blokującego odczytu
START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM orders WHERE id = 42 FOR UPDATE; -- blokuje wiersz
-- ... logika biznesowa ...
COMMIT;

Więcej o instalacji i konfiguracji środowiska znajdziesz tu: MySQL: wprowadzenie, instalacja i pierwsza baza danych.

Różnice między PostgreSQL a MySQL

PostgreSQL mapuje READ UNCOMMITTED na READ COMMITTED, więc nie dopuszcza dirty read. Jego Repeatable Read to snapshot isolation: w obrębie transakcji widzisz spójny zrzut danych z chwili pierwszego zapytania; eliminuje non-repeatable read i praktycznie phantom read, lecz dopuszcza write skew. SERIALIZABLE w PostgreSQL to SSI (Serializable Snapshot Isolation), które wykrywa konflikty i abortuje transakcje, utrzymując pełną serializowalność bez nadmiernego blokowania.

MySQL/InnoDB domyślnie działa w Repeatable Read, z mechanizmami MVCC, next-key i gap locks. SERIALIZABLE konwertuje zwykłe SELECT na blokujące, co zapobiega phantom read kosztem większej liczby blokad. InnoDB dopuszcza READ UNCOMMITTED z realnymi dirty reads, ale to poziom rzadko używany produkcyjnie.

Jeśli chcesz szerzej porównać podejścia, zobacz: PostgreSQL vs MySQL: różnice, które mają znaczenie.

Zjawiska: dirty read, non-repeatable read, phantom read

Dirty read to odczyt niezatwierdzonych zmian innej transakcji. Występuje na READ UNCOMMITTED (InnoDB), ale nie w PostgreSQL, gdzie ten poziom jest traktowany jak READ COMMITTED. Non-repeatable read to sytuacja, gdy ten sam wiersz zwraca inne wartości w jednej transakcji; pojawia się na READ COMMITTED. Phantom read dotyczy zakresów: powtarzany SELECT widzi nowe/ubyte wiersze spełniające warunek.

W PostgreSQL Repeatable Read eliminuje non-repeatable i phantom read w obrębie transakcji. W InnoDB Repeatable Read zapewnia spójny snapshot dla zwykłych SELECT i używa next-key locking, żeby zapobiec phantomom przy odczytach blokujących. Pełna serializowalność w PG wymaga SERIALIZABLE (SSI), a w MySQL – SERIALIZABLE z dodatkowymi blokadami.

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Non-repeatable read pod READ COMMITTED (schemat dwóch sesji)
-- Sesja A
START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1; -- wynik: 100

-- Sesja B (równolegle)
START TRANSACTION;
UPDATE accounts SET balance = 150 WHERE id = 1;
COMMIT;

-- Sesja A (ciąg dalszy)
SELECT balance FROM accounts WHERE id = 1; -- wynik: 150 (zmiana widoczna)
COMMIT;

Savepoint i rollback – kontrola transakcji

SAVEPOINT pozwala cofnąć fragment transakcji bez anulowania całości. To praktyczne przy złożonych operacjach, gdzie część kroków jest opcjonalna lub awaryjna. ROLLBACK TO SAVEPOINT resetuje stan do zapisanego punktu, resztę działań można kontynuować i finalnie zatwierdzić.

Poniżej przykład przelewu z opcjonalną opłatą. Jeśli naliczenie opłaty się nie powiedzie, cofamy tylko ten krok, a sam przelew kończymy powodzeniem.

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
START TRANSACTION;

-- 1) Główna operacja
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 2) Opcjonalna opłata
SAVEPOINT fee_step;
UPDATE accounts SET balance = balance - 2 WHERE id = 1; -- opłata 2
-- Załóżmy błąd: saldo spada poniżej 0 lub constraint
-- ROLLBACK tylko opłaty:
-- ROLLBACK TO SAVEPOINT fee_step;

COMMIT; -- cała transakcja się udaje, z opłatą lub bez

Przykładowe scenariusze testowe

Test non-repeatable read: ustaw READ COMMITTED. W sesji A odczytaj wiersz. W sesji B zmień i zatwierdź. W sesji A ponownie odczytaj ten sam wiersz – zobaczysz nową wartość. Na REPEATABLE READ w obu silnikach ponowny odczyt w tej samej transakcji A zwróci pierwotną wartość (snapshot).

Test phantom read: w sesji A ustaw READ COMMITTED i wykonaj SELECT COUNT(*) FROM orders WHERE amount >= 100. W sesji B dodaj nowy wiersz spełniający warunek i zatwierdź. W sesji A powtórz SELECT – liczba wzrośnie. Na REPEATABLE READ sesja A utrzyma wynik, a przy SELECT ... FOR UPDATE w InnoDB next-key locks mogą zablokować wstawienia w zakresie, eliminując phantom.

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Setup do testów
CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT CHECK (balance >= 0));
INSERT INTO accounts (id, balance) VALUES (1, 100), (2, 100) ON DUPLICATE KEY UPDATE balance = VALUES(balance);

CREATE TABLE IF NOT EXISTS orders (id INT PRIMARY KEY, amount INT);
INSERT INTO orders (id, amount) VALUES (1, 50), (2, 200) ON DUPLICATE KEY UPDATE amount = VALUES(amount);

-- Phantom pod READ COMMITTED
-- Sesja A
START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT COUNT(*) FROM orders WHERE amount >= 100; -- np. 1

-- Sesja B
START TRANSACTION;
INSERT INTO orders (id, amount) VALUES (3, 150);
COMMIT;

-- Sesja A
SELECT COUNT(*) FROM orders WHERE amount >= 100; -- teraz 2
COMMIT;

Dla pełnego zrozumienia konfiguracji i narzędzi w ekosystemie PostgreSQL warto zajrzeć do: PostgreSQL: wprowadzenie, instalacja i pierwsza baza danych.