Які рівні ізоляції транзакцій ви використовуєте і чому?
MySQL за замовчуванням REPEATABLE READ, PostgreSQL — READ COMMITTED; рівень визначає, які аномалії можливі, але втрачене оновлення закривають не рівнем, а атомарним UPDATE або SELECT FOR UPDATE.
Як питають
Що таке phantom read і на якому рівні він можливий?
Чому два паралельні запити списали з балансу більше, ніж там було?
Чому SERIALIZABLE вимагає повторювати транзакцію?
транзакціїізоляціяPostgreSQL
Пояснення
Рівень ізоляції визначає, що транзакція бачить із паралельних змін. READ UNCOMMITTED допускає читання незафіксованих даних і в PostgreSQL фактично дорівнює READ COMMITTED. READ COMMITTED бачить лише закомічені дані, але між двома своїми запитами може побачити різні значення. REPEATABLE READ фіксує знімок на початку транзакції; у PostgreSQL це також закриває фантоми, у MySQL InnoDB звичайні читання йдуть зі знімка, а locking reads захищені gap-локами. SERIALIZABLE гарантує результат, еквівалентний послідовному виконанню. За замовчуванням MySQL працює на REPEATABLE READ, PostgreSQL на READ COMMITTED, і для більшості застосунків дефолт лишають.
Практична проблема, яку рівень ізоляції не вирішує, — втрачене оновлення. Два запити читають баланс 100, обидва рахують у PHP і обидва записують результат: одне списання зникає. На READ COMMITTED і REPEATABLE READ це відбувається мовчки. Рішення — не читати й записувати окремо: UPDATE ... SET balance = balance - :amount WHERE balance >= :amount виконує обидві дії атомарно під блокуванням рядка, а нуль affected rows означає недостатньо коштів. Коли між читанням і записом потрібна логіка, застосовують SELECT ... FOR UPDATE у короткій транзакції.
SERIALIZABLE у PostgreSQL реалізований без блокувань: база відстежує залежності й відкидає одну з конфліктних транзакцій із помилкою serialization_failure. Це штатна ситуація, і код має повторити транзакцію цілком. Так само треба обробляти deadlock. У відповіді найкраще працює реальний випадок: подвійне списання, двічі використаний промокод, і те, як саме гонку закрили.
КодSQL
-- Lost update: обидві транзакції читають 100, обидві пишуть 100 - 70 = 30.
-- Рівень ізоляції нижче SERIALIZABLE цього не ловить.
SELECT balance FROM accounts WHERE id = 1; -- 100 в обох сесіях
UPDATE accounts SET balance = 30 WHERE id = 1; -- списали 140, баланс 30
-- Рішення 1: атомарний UPDATE з умовою, перевіряємо affected rows
UPDATE accounts
SET balance = balance - 70
WHERE id = 1 AND balance >= 70; -- друга сесія отримає 0 rows
-- Рішення 2: блокування рядка на час короткої логіки
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- друга сесія чекає
-- бізнес-перевірки в PHP
UPDATE accounts SET balance = balance - 70 WHERE id = 1;
INSERT INTO ledger (account_id, amount, operation_key) VALUES (1, -70, 'op-9f1c'); -- UNIQUE(operation_key)
COMMIT;
-- Рішення 3: SERIALIZABLE, готові повторити при 40001
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... на serialization_failure застосунок повторює всю транзакцію
-- Черга на базі: воркери беруть різні рядки без очікування
SELECT id FROM jobs WHERE status = 'pending'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
Що хоче почути інтервʼюер
Чотири рівні й аномалії, які кожен допускає: dirty read, non-repeatable read, phantom read, і lost update як окрема практична проблема.
Що READ COMMITTED бачить зміни інших транзакцій між своїми запитами, а REPEATABLE READ фіксує знімок на початку і в PostgreSQL також закриває фантоми.
Що патерн прочитати в PHP, порахувати, записати ламається на будь-якому рівні нижче SERIALIZABLE, а рішення це UPDATE з виразом і умовою або SELECT FOR UPDATE.
Що SERIALIZABLE у PostgreSQL це SSI без блокувань, який відкидає транзакцію з помилкою serialization_failure, тому код мусить бути готовий повторити її.
Досвід реальної гонки: подвійне списання, дублікат промокоду, перевищення ліміту, і як саме її закрили.
Типові помилки
Вважати, що REPEATABLE READ гарантує відсутність будь-яких аномалій, зокрема lost update.
Вважати, що вищий рівень ізоляції автоматично захищає від подвійного списання: без блокування або атомарного UPDATE дві транзакції все одно прочитають той самий баланс.
Використовувати SELECT FOR UPDATE без транзакції або з довгою логікою всередині, тримаючи блокування на час HTTP-запиту.
Не обробляти deadlock і serialization failure: замість повтору транзакції показувати користувачу 500.
Не знати, що в MySQL REPEATABLE READ фантоми частково закриті gap-локами, а в PostgreSQL знімком, і поведінка при UPDATE конфліктних рядків відрізняється.
ПОРАДА
Наведіть реальний випадок гонки, який ловили в продакшені — це переконує краще за перелік рівнів. І покажіть, що знаєте різницю дефолтів MySQL і PostgreSQL.
Додаткові питанняЗ ВІДПОВІДЯМИ
Повторний SELECT з тією ж умовою повертає нові рядки, які вставила інша транзакція. За стандартом можливий на READ COMMITTED і REPEATABLE READ. У PostgreSQL REPEATABLE READ працює на знімку й фантомів не показує; у MySQL InnoDB звичайні SELECT читають знімок, але locking reads і UPDATE можуть побачити нові рядки, від чого захищають gap-локи.
PostgreSQL реалізує SERIALIZABLE через SSI: транзакції не блокують одна одну, а база відстежує залежності читань і записів. Якщо виявляється цикл, який неможливо впорядкувати послідовно, одна з транзакцій відкидається з помилкою 40001 serialization_failure. Це не помилка коду, а нормальна ситуація, тому застосунок мусить повторити транзакцію цілком, у Laravel це другий аргумент DB::transaction.
Атомарний UPDATE accounts SET balance = balance - :amount WHERE id = :id AND balance >= :amount і перевірка affected rows: нуль означає недостатньо коштів. База виконує читання й запис одним кроком під блокуванням рядка. Якщо потрібна складна логіка між читанням і записом, SELECT ... FOR UPDATE у транзакції, коротка секція і запис. Плюс унікальний ключ операції для ідемпотентності.
FOR UPDATE блокує рядки на запис і читання іншими locking reads, FOR SHARE дозволяє іншим читати з FOR SHARE, але не змінювати. SKIP LOCKED пропускає вже заблоковані рядки замість очікування, що робить його ідеальним для черг на базі: кілька воркерів беруть різні завдання без конфліктів. NOWAIT замість очікування одразу повертає помилку.