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

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