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

ACID - чотири гарантії, які дає транзакція в реляційній базі:

• Atomicity (атомарність) - усі операції транзакції виконуються або всі разом, або жодна. Переказ грошей не може зняти суму з одного рахунку й не зарахувати на інший.
• Consistency (узгодженість) - транзакція переводить базу з одного коректного стану в інший: обмеження (NOT NULL, унікальність, зовнішні ключі, CHECK) виконуються після кожної завершеної транзакції.
• Isolation (ізольованість) - паралельні транзакції не бачать проміжних станів одна одної. Наскільки суворо - визначає рівень ізоляції.
• Durability (довговічність) - після COMMIT дані не зникнуть навіть при падінні сервера: їх уже записано в журнал (WAL у PostgreSQL, redo log в InnoDB) на диск.

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

Якщо між двома UPDATE сервер впаде, після перезапуску жодної зміни не буде - атомарність.

Важливо для співбесіди: ізольованість не означає «транзакції виконуються по черзі». На рівнях за замовчуванням (Read Committed у PostgreSQL, Repeatable Read у MySQL) деякі аномалії паралельного виконання можливі, і їх треба враховувати в коді.

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

ROLLBACK скасовує всі зміни, зроблені в поточній транзакції, і завершує її. Так, ніби нічого не було.

BEGIN;
DELETE FROM orders WHERE created_at < '2020-01-01';
-- ой, не та дата
ROLLBACK;

Коли транзакція відкочується без явного ROLLBACK:

• Розірвалося з'єднання або впав процес застосунку посеред транзакції - база відкотить її сама.
• Помилка в PostgreSQL: після будь-якої помилки транзакція переходить у стан «aborted», і всі наступні команди відхиляються з current transaction is aborted, доки не буде ROLLBACK.
• Deadlock: база обирає одну з транзакцій-«жертв» і відкочує її.

Відмінність MySQL: помилка однієї команди (наприклад, порушення унікальності) за замовчуванням відкочує лише цю команду, а не всю транзакцію. Тому покладатися на «база сама все відкотить» небезпечно - застосунок має явно робити ROLLBACK при помилці.

Ще є SAVEPOINT - точка, до якої можна відкотити частину транзакції, не скасовуючи все. Laravel використовує їх для вкладених DB::transaction().

Пастка: DDL у MySQL (CREATE TABLE, ALTER TABLE) неявно комітить поточну транзакцію, тож відкотити міграцію з кількома змінами схеми не вийде. У PostgreSQL DDL транзакційний.

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

Autocommit - режим, у якому кожна команда - окрема транзакція: вона або виконується й одразу фіксується, або відкочується при помилці. Це поведінка PostgreSQL і більшості драйверів за замовчуванням.

UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- уже закомічено
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- окрема транзакція

Якщо між двома командами щось впаде, гроші спишуться, але не зарахуються.

Явна транзакція об'єднує команди:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Що варто знати:

• Одна команда атомарна й без BEGIN: UPDATE ... WHERE id IN (1, 2) або INSERT з кількома рядками виконається повністю або ніяк.
• Тригери й каскадні дії виконуються в межах тієї самої транзакції, що й команда, яка їх запустила.
• Незавершена транзакція (забули COMMIT) тримає блокування й заважає вакууму. Сесія в стані idle in transaction - поширена проблема на проді.
• У PHP: PDO працює в autocommit, доки не викликати beginTransaction(). Laravel DB::transaction(fn () => ...) робить BEGIN / COMMIT і ROLLBACK при винятку.

Неявні транзакції в інших СУБД: в Oracle й у SQL Server з IMPLICIT_TRANSACTIONS транзакція починається сама з першою командою й триває до явного COMMIT. А в MySQL DDL-команди (ALTER TABLE) неявно комітять поточну транзакцію - у PostgreSQL DDL транзакційний і відкочується разом з рештою.

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

DELETE FROM orders видаляє рядки по одному: позначає кожен як видалений (MVCC), запускає тригери ON DELETE, перевіряє зовнішні ключі, може мати WHERE. Місце звільняється пізніше, вакуумом.

TRUNCATE orders звільняє таблицю цілком - фактично підміняє файли таблиці порожніми. Миттєво навіть на мільярді рядків, і місце повертається одразу.

Відмінності:

Чи можна відкотити: у PostgreSQL так - TRUNCATE транзакційний:

BEGIN;
TRUNCATE orders;
ROLLBACK;   -- дані на місці

У MySQL TRUNCATE - DDL-команда з неявним комітом, і відкотити її не можна.

Застереження:

• TRUNCATE бере найсильніше блокування: поки транзакція з ним не завершилася, таблицю не може читати ніхто.
• TRUNCATE ... CASCADE очистить і всі таблиці, що посилаються на цю, - легко знищити більше, ніж планували.
• У тестах TRUNCATE між тестами - швидкий спосіб очищення (так працює DatabaseTruncation у Laravel), але транзакція з відкатом (RefreshDatabase) ще швидша.

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

Стандарт 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

MVCC (Multi-Version Concurrency Control) - спосіб дати паралельним транзакціям узгоджений знімок даних без блокувань на читання. Читачі не блокують письменників, письменники - читачів.

Як це влаштовано в PostgreSQL: UPDATE не змінює рядок на місці, а створює нову версію рядка, позначаючи стару як застарілу (з якої транзакції вона вже невидима). DELETE лише позначає рядок. Кожна транзакція бачить ті версії, що були актуальні на момент її знімка.

Звідси потреба у VACUUM: старі версії («мертві кортежі») лишаються в таблиці, доки жодна транзакція вже не може їх бачити. VACUUM:

• звільняє місце мертвих кортежів для повторного використання;
• оновлює visibility map (потрібна для index-only scan);
• «заморожує» старі ідентифікатори транзакцій, щоб уникнути переповнення лічильника транзакцій (transaction ID wraparound) - аварійної ситуації, за якої база перестає приймати запис.

Зазвичай усе це робить фоновий autovacuum.

Типові проблеми на проді:

• Роздування (bloat) таблиць з частими UPDATE: autovacuum не встигає, таблиця й індекси ростуть, запити сповільнюються. Лікують тонким налаштуванням autovacuum для конкретних таблиць.
• Довга транзакція (чи забута відкрита сесія idle in transaction) не дає вакууму прибирати навіть старі версії по всій базі.
• VACUUM FULL повертає місце ОС, але переписує таблицю під ексклюзивним блокуванням - на проді його уникають.

InnoDB теж використовує MVCC, але зберігає старі версії в undo log, тож окремого вакууму не потребує (його роль виконує фоновий purge).

Докладніше в документації: Регулярне очищення (VACUUM)

Advisory-блокування (PostgreSQL) - блокування за довільним числом, яке не прив'язане до жодного рядка чи таблиці. База лише гарантує, що один ключ одночасно тримає лише одна сесія; що цей ключ означає - вирішує застосунок.

-- Чекати, доки звільниться
SELECT pg_advisory_lock(42);
-- ... робота ...
SELECT pg_advisory_unlock(42);

-- Не чекати: true, якщо взяли, false - якщо зайнято
SELECT pg_try_advisory_lock(hashtext('import:prices'));

-- Знімається автоматично в кінці транзакції
SELECT pg_advisory_xact_lock(42);

Коли це доречно:

• Захистити дію, а не рядок: «лише один процес імпорту одночасно», «лише один сервер виконує міграції під час деплою».
• Блокування того, чого ще немає: не можна взяти FOR UPDATE на рядок, якого ще не створено, а advisory-ключ від, наприклад, email - можна.
• Без зайвої інфраструктури: коли Redis для розподілених блокувань немає, а PostgreSQL уже є.

Пастки:

• Сесійні блокування живуть, поки живе з'єднання. Якщо процес «забув» зняти блокування, але з'єднання лишилося в пулі, ключ буде зайнятий безстроково. Транзакційний варіант (_xact_) безпечніший.
• З PgBouncer у режимі transaction pooling сесійні блокування ламаються: наступний запит може піти іншим з'єднанням.
• Ключ - число; рядкові ідентифікатори перетворюють хешем, і про можливі колізії варто пам'ятати.

MySQL має схожі GET_LOCK('name', timeout) / RELEASE_LOCK('name') з рядковими іменами.

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

Навіть транзакція, що лише читає і не тримає конфліктних блокувань, впливає на всю базу через MVCC.

Горизонт вакууму. VACUUM може прибрати мертву версію рядка, лише якщо вона невидима жодній активній транзакції. Найстаріша відкрита транзакція (чи знімок) встановлює горизонт: усі версії, що з'явилися після її початку, захищені від очищення - у всіх таблицях бази, не лише тих, що вона читала.

Що відбувається, поки транзакція висить годинами:

• Мертві версії накопичуються в таблицях з частими оновленнями (черги, лічильники, сесії).
• Таблиці й індекси роздуваються, запити сповільнюються - бо читають сторінки, повні мертвих рядків.
• Autovacuum працює, але марно: «не може видалити, бо ще потрібні».
• Після закриття транзакції роздуття саме не зникає - місце повторно використовується, але файли лишаються великими.

Джерела довгих транзакцій:

• idle in transaction - застосунок відкрив транзакцію й робить щось повільне поза базою (HTTP-запит, обробка файлу) або впав, не закривши її.
• Довгі звіти й аналітика на primary.
• Репліка з hot_standby_feedback = on, де йде довгий запит, - вона теж тримає горизонт на primary.
• Забуті prepared transactions і неактивні слоти реплікації (утримують WAL і горизонт).

Як знайти:

SELECT pid, state, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

Захист:

• idle_in_transaction_session_timeout - розривати сесії, що зависли в транзакції.
• transaction_timeout (PostgreSQL 17+) - обмеження на тривалість усієї транзакції.
• Аналітику - на репліку без hot_standby_feedback чи в окреме сховище.
• У коді - не робити повільних зовнішніх дій усередині DB::transaction().

Докладніше в документації: Налаштування клієнтських з'єднань

За замовчуванням PostgreSQL чекає безкінечно: запит може виконуватися годинами, а очікування блокування - тривати вічно. Таймаути перетворюють «завис» на зрозумілу помилку.

• statement_timeout - максимальний час виконання однієї команди. Після нього запит скасовується з помилкою.
• lock_timeout - максимальний час очікування блокування. Сам запит може йти довго, але чекати на чужі блокування - не довше вказаного.
• idle_in_transaction_session_timeout - розірвати сесію, що простоює всередині відкритої транзакції.
• transaction_timeout (PostgreSQL 17+) - ліміт на всю транзакцію.

Де налаштовувати - на різних рівнях:

-- для ролі застосунку
ALTER ROLE app_user SET statement_timeout = '30s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';

-- для окремої сесії чи транзакції
SET lock_timeout = '3s';                    -- для сесії
SET LOCAL statement_timeout = '5min';       -- лише до кінця поточної транзакції

Типові значення:

• Веб-запити: statement_timeout кілька секунд - веб-запит, що чекає базу хвилину, однаково нікому не потрібен, а тримає з'єднання з пулу.
• Міграції: lock_timeout 2-5 секунд, щоб ALTER TABLE не вишикував чергу з усіх запитів; statement_timeout - побільше або вимкнений для довгих операцій на зразок CREATE INDEX CONCURRENTLY.
• Звіти й фонові задачі: окрема роль чи SET LOCAL з більшими лімітами.

Застереження:

• Глобальний statement_timeout у postgresql.conf зачепить і адміністративні операції - pg_dump, вакуум вручну. Краще налаштовувати на роль застосунку.
• З PgBouncer у режимі transaction pooling SET без LOCAL «протікає» в інші сесії - використовувати SET LOCAL або налаштування ролі.
• Помилку таймауту застосунок має обробляти: логувати з текстом запиту й показувати користувачу зрозуміле повідомлення, а не 500.

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

PostgreSQL можна використати як надійну чергу без окремого брокера - так працюють драйвер database у Laravel, good_job у Rails, pg-boss, River.

Основа - FOR UPDATE SKIP LOCKED:

BEGIN;

SELECT id, payload
FROM jobs
WHERE queue = 'default' AND available_at <= now()
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- обробили завдання
DELETE FROM jobs WHERE id = $1;
COMMIT;

Кілька воркерів одночасно беруть різні рядки: заблокований іншим воркером рядок просто пропускається. Якщо воркер впав, транзакція відкотиться, блокування зніметься - завдання повернеться в чергу автоматично.

LISTEN / NOTIFY - щоб воркери не опитували таблицю постійно:

-- воркер
LISTEN new_job;

-- при додаванні завдання (можна тригером)
NOTIFY new_job;

Повідомлення доставляються після коміту транзакції-відправника, тож воркер не прокинеться раніше, ніж завдання стане видимим.

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

Межі:

• Навантаження: тисячі завдань на секунду - уже відчутно для бази: кожне завдання - вставка, оновлення, видалення, мертві версії й вакуум. Таблиця черги роздувається й потребує агресивного autovacuum.
• NOTIFY не надійний як черга: повідомлення не зберігаються, якщо слухача немає; розмір payload обмежений (8000 байтів). Це лише «будильник», джерело правди - таблиця.
• Конкуренція з основним навантаженням бази за ресурси.

Коли брати Redis/RabbitMQ/SQS: високий потік завдань, потреба в pub/sub, затримках і пріоритетах без навантаження на основну базу. Для типового застосунку з десятками-сотнями завдань на хвилину черга в PostgreSQL - цілком розумний вибір.

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