Middle: питання на співбесіді з теми «Транзакції й блокування»

Стандарт SQL визначає чотири рівні - від найслабшого до найсуворішого. Кожен забороняє більше аномалій:

• Брудне читання - бачимо незакомічені зміни іншої транзакції.
• Неповторюване читання - той самий рядок, прочитаний двічі, змінився між читаннями.
• Фантом - повторний запит з тією ж умовою повертає нові рядки.

На практиці:

• PostgreSQL за замовчуванням Read Committed. Read Uncommitted поводиться як Read Committed. Його Repeatable Read фантомів не допускає (снапшот на всю транзакцію), а Serializable реалізований через SSI.
• MySQL InnoDB за замовчуванням Repeatable Read. Звичайні SELECT читають снапшот, але блокувальні читання (FOR UPDATE) і UPDATE бачать найсвіжіші дані.

Що це означає для коду: класична гонка «прочитав баланс → перевірив → записав» не захищена на Read Committed. Рішення - атомарний UPDATE ... SET balance = balance - 100 WHERE balance >= 100, блокування FOR UPDATE або Serializable.

На Repeatable Read і Serializable база може відхилити транзакцію з помилкою серіалізації. Застосунок мусить бути готовий повторити таку транзакцію - це частина контракту, а не збій.

Докладніше в документації: Ізоляція транзакцій

SELECT ... FOR UPDATE читає рядки й блокує їх до кінця транзакції. Інші транзакції, які захочуть змінити ці рядки чи теж взяти FOR UPDATE, чекатимуть.

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- тепер ніхто не змінить баланс, поки ми рахуємо
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Так закривають гонку «прочитав → перевірив → записав»: друга транзакція прочитає баланс лише після того, як перша закомітить.

FOR UPDATE SKIP LOCKED (PostgreSQL 9.5+, MySQL 8.0+) - не чекати, а пропустити вже заблоковані рядки. Ідеально для черги завдань у таблиці: кілька воркерів беруть різні завдання без конфліктів.

SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

Саме так працює драйвер черги database у Laravel.

NOWAIT - не чекати, а одразу отримати помилку, якщо рядок зайнятий.

Пастки:

• Блокування тримається до кінця транзакції. Без BEGIN (в autocommit) воно знімається одразу після запиту й нічого не захищає.
• Довга транзакція з FOR UPDATE - це черга з інших запитів. Усередині не роблять HTTP-запитів чи інших повільних дій.
• Блокування рядків у різному порядку в різних транзакціях - прямий шлях до deadlock.

Докладніше в документації: Явні блокування рядків

Справжніх вкладених транзакцій у PostgreSQL немає. Натомість є точки збереження (savepoints) - мітки всередині транзакції, до яких можна відкотити частину змін, не скасовуючи все.

BEGIN;
INSERT INTO orders (id, total) VALUES (1, 500);

SAVEPOINT before_bonus;
INSERT INTO bonuses (order_id, amount) VALUES (1, 50);   -- помилка: порушено обмеження
ROLLBACK TO SAVEPOINT before_bonus;                       -- скасовано лише бонус

COMMIT;   -- замовлення збережено

Навіщо:

• Обробити помилку й продовжити. У PostgreSQL будь-яка помилка переводить транзакцію в стан «aborted» - наступні команди відхиляються до ROLLBACK. Savepoint дозволяє відкотитися до точки й піти далі.
• Масовий імпорт з пропуском поганих рядків - savepoint перед кожним рядком (або порцією).
• Вкладені виклики коду, кожен з яких «хоче транзакцію».

Як це використовує Laravel: вкладений DB::transaction() усередині іншої транзакції створює savepoint, а не новий BEGIN:

DB::transaction(function () {
    Order::create([...]);

    try {
        DB::transaction(fn () => Bonus::create([...]));   // SAVEPOINT trans2
    } catch (QueryException $e) {
        // відкотилося лише створення бонусу
    }
});

Підводні камені:

• COMMIT savepoint'а нічого не фіксує: зміни всередині стануть постійними лише з COMMIT зовнішньої транзакції. Якщо вона відкотиться, відкотиться все.
• Ціна: кожен savepoint споживає ресурси. Тисячі savepoint'ів в одній транзакції (по одному на рядок) помітно сповільнюють роботу - для масового імпорту краще перевіряти дані до вставки чи використовувати ON CONFLICT.
• Побічні ефекти поза базою (листи, HTTP-запити) відкат savepoint'а не скасує.

Докладніше в документації: SAVEPOINT

Serializable гарантує, що результат паралельних транзакцій буде таким, ніби вони виконувалися по черзі в якомусь порядку. Жодних аномалій паралельності - навіть тих, від яких не рятує Repeatable Read.

Класична аномалія, яку ловить лише Serializable - write skew:

-- У лікарні має чергувати хоча б один лікар. Чергують двоє.
-- Транзакція A:                          -- Транзакція B:
SELECT count(*) FROM doctors              SELECT count(*) FROM doctors
WHERE on_call;          -- 2              WHERE on_call;          -- 2
UPDATE doctors SET on_call = false        UPDATE doctors SET on_call = false
WHERE id = 1;                             WHERE id = 2;
COMMIT;                                   COMMIT;
-- Результат: не чергує ніхто

Кожна транзакція окремо коректна, а разом вони порушили правило. На Repeatable Read обидві закомітяться.

Як PostgreSQL це реалізує - SSI (Serializable Snapshot Isolation): транзакції не блокують одна одну, а база відстежує залежності між тим, що кожна прочитала й записала. Якщо виявлено небезпечний цикл залежностей, одна з транзакцій отримує помилку:

ERROR: could not serialize access due to read/write dependencies among transactions
SQLSTATE 40001

Помилка серіалізації - не збій, а частина контракту. Застосунок мусить повторити всю транзакцію з початку:

DB::transaction(function () {
    // ...
}, attempts: 3);   // Laravel повторить при deadlock чи serialization failure

Ціна Serializable:

• Повтори транзакцій - логіка має бути ідемпотентною до коміту (без листів і HTTP-запитів усередині).
• Додаткові накладні витрати на відстеження залежностей.
• Більше відкатів під високою конкуренцією.

Коли обирати: складні бізнес-правила, що залежать від кількох рядків (бронювання, ліміти, інваріанти «хоча б один / не більше N»), і коли розставляти блокування вручну складно. Для простих випадків часто достатньо атомарного UPDATE чи SELECT ... FOR UPDATE.

Докладніше в документації: Рівень ізоляції Serializable

У PostgreSQL кожна команда бере на таблицю блокування певного режиму. Режими конфліктують між собою за таблицею сумісності:

• SELECT - ACCESS SHARE (найслабший);
• INSERT/UPDATE/DELETE - ROW EXCLUSIVE;
• CREATE INDEX - SHARE (блокує запис);
• більшість ALTER TABLE, DROP, TRUNCATE, VACUUM FULL - ACCESS EXCLUSIVE (конфліктує з усім, навіть із SELECT).

Ланцюжок, що кладе прод:

1. Довгий запит (звіт, забута транзакція в idle in transaction) тримає ACCESS SHARE на таблиці.
2. Міграція виконує ALTER TABLE orders ADD COLUMN note text - їй потрібен ACCESS EXCLUSIVE, і вона стає в чергу за звітом.
3. Усі нові запити до orders, навіть прості SELECT, стають у чергу за ALTER TABLE, бо черга блокувань упорядкована.
4. Таблиця фактично недоступна, доки не завершиться звіт - хвилини чи години. Пул з'єднань застосунку вичерпується, сайт лягає.

Сама зміна після отримання блокування займає мілісекунди - проблема саме в очікуванні.

Захист:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;

Якщо блокування не отримано за 3 секунди, команда падає й звільняє чергу. Міграцію повторюють (автоматично з паузами чи вручну).

Ще правила:

• Перед міграцією перевіряти pg_stat_activity на довгі запити й idle in transaction.
• Налаштувати idle_in_transaction_session_timeout, щоб забуті транзакції не жили вічно.
• Робити кожну міграцію короткою: одна зміна схеми - одна транзакція, без масового оновлення даних в тій самій транзакції.

Докладніше в документації: Блокування таблиць

Швидкий запит - хто чекає й на кого:

SELECT waiting.pid                          AS waiting_pid,
       waiting.query                        AS waiting_query,
       now() - waiting.query_start          AS waiting_for,
       blocking.pid                         AS blocking_pid,
       blocking.state                       AS blocking_state,
       blocking.query                       AS blocking_query
FROM pg_stat_activity waiting
JOIN LATERAL unnest(pg_blocking_pids(waiting.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = b.pid
WHERE waiting.wait_event_type = 'Lock';

pg_blocking_pids(pid) повертає процеси, що блокують даний, - найпростіший шлях до відповіді.

На що дивитися в результаті:

• blocking_state = 'idle in transaction' - класика: застосунок відкрив транзакцію, щось змінив і «забув» її закрити (чекає на HTTP-запит, впав посередині, тримає транзакцію в довгому циклі).
• Довгий blocking_query - звіт, міграція, масове оновлення.
• Ланцюжки - A чекає на B, B чекає на C; корінь - той, хто сам ні на кого не чекає.

Як розблокувати в аварійній ситуації:

SELECT pg_cancel_backend(12345);      -- скасувати поточний запит процесу
SELECT pg_terminate_backend(12345);   -- розірвати з'єднання повністю

pg_cancel_backend м'якший, але не допоможе з idle in transaction - там немає запиту, який можна скасувати, лише terminate.

Детальніше - pg_locks: усі утримувані й очікувані блокування з режимами й об'єктами (relation::regclass). Корисно, щоб зрозуміти, яке саме блокування конфліктує.

Профілактика: log_lock_waits = on (логувати очікування довші за deadlock_timeout), idle_in_transaction_session_timeout, lock_timeout для міграцій, моніторинг кількості очікуючих сесій.

Докладніше в документації: pg_locks