# Database Transactions — every proven transcript
Each section below is one scenario, replayed against a real database by `bun run gen`.
The structured index of the same runs is at https://svyatov.github.io/database-transactions/ledger.jsonl.
## mysql/01-basics/aborted-transaction.yaml
- engine: mysql (MySQL 8.4.10)
- claim: After an error inside a transaction, MySQL keeps the transaction alive — only the failed statement is rolled back, and everything else commits normally.
```transcript
A> BEGIN;
Query OK
A> INSERT INTO items VALUES (2, 'gadget');
Query OK, 1 row affected
A> INSERT INTO items VALUES (3, 'widget'); -- ER_DUP_ENTRY
ERROR 1062 (23000): Duplicate entry 'widget' for key 'items.name'
```
*PostgreSQL would now refuse every statement until ROLLBACK. MySQL just carries on.*
```transcript
A> INSERT INTO items VALUES (3, 'doohickey');
Query OK, 1 row affected
A> COMMIT;
Query OK
A> SELECT id, name FROM items ORDER BY id; -- survived the error in the middle
id | name
----+-----------
1 | widget
2 | gadget
3 | doohickey
(3 rows)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/01-basics/aborted-transaction.yaml)
## mysql/01-basics/atomicity.yaml
- engine: mysql (MySQL 8.4.10)
- claim: A transaction that fails halfway leaves zero partial writes — even statements that already succeeded inside it are undone.
```timeline
A: credit bob +150 (uncommitted)
B: read bob → 50 (credit hidden)
A: debit alice −150 ← 3819 Check constraint 'accounts_chk_1' is violated.
A: ROLLBACK — bob's credit gone too
B: read → alice 100, bob 50 (untouched)
```
*A transfers 150 from alice to bob. Crediting bob works fine…*
```transcript
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = balance + 150 WHERE owner = 'bob';
Query OK, 1 row affected
B> SELECT balance FROM accounts WHERE owner = 'bob'; -- B can't see A's uncommitted credit
balance
---------
50
(1 row)
```
*…but debiting alice violates the CHECK constraint — she only has 100.*
```transcript
A> UPDATE accounts SET balance = balance - 150 WHERE owner = 'alice'; -- ER_CHECK_CONSTRAINT_VIOLATED
ERROR 3819 (HY000): Check constraint 'accounts_chk_1' is violated.
```
*Roll the transaction back. Bob's credit — which had succeeded — evaporates with it.*
```transcript
A> ROLLBACK;
Query OK
B> SELECT owner, balance FROM accounts ORDER BY id;
owner | balance
-------+---------
alice | 100
bob | 50
(2 rows)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/01-basics/atomicity.yaml)
## mysql/01-basics/autocommit-visibility.yaml
- engine: mysql (MySQL 8.4.10)
- claim: Without BEGIN, every statement commits instantly and is immediately visible to everyone; inside BEGIN, changes stay private until COMMIT.
```timeline
A: UPDATE balance = 150 (autocommit)
B: SELECT balance → 150 (visible at once)
A: UPDATE balance = 999 (uncommitted)
B: SELECT balance → 150 (A's change hidden)
A: COMMIT
B: SELECT balance → 999 (now visible)
```
*No BEGIN — the UPDATE is its own transaction, committed the instant it finishes.*
```transcript
A> UPDATE accounts SET balance = 150 WHERE id = 1;
Query OK, 1 row affected
B> SELECT balance FROM accounts WHERE id = 1; -- B sees it immediately
balance
---------
150
(1 row)
```
*Inside an explicit transaction, A's change is invisible to B…*
```transcript
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = 999 WHERE id = 1;
Query OK, 1 row affected
B> SELECT balance FROM accounts WHERE id = 1; -- still the old value
balance
---------
150
(1 row)
```
*…until A commits.*
```transcript
A> COMMIT;
Query OK
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
999
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/01-basics/autocommit-visibility.yaml)
## mysql/01-basics/savepoint-nesting.yaml
- engine: mysql (MySQL 8.4.10)
- claim: Rolling back to an outer savepoint discards inner savepoints along with their work; RELEASE keeps the changes but forfeits the rollback point.
```transcript
A> BEGIN;
Query OK
A> INSERT INTO steps VALUES (1);
Query OK, 1 row affected
A> SAVEPOINT outer_sp;
Query OK
A> INSERT INTO steps VALUES (2);
Query OK, 1 row affected
A> SAVEPOINT inner_sp;
Query OK
A> INSERT INTO steps VALUES (3);
Query OK, 1 row affected
```
*Rolling back to the OUTER savepoint discards rows 2 and 3 — and inner_sp itself.*
```transcript
A> ROLLBACK TO SAVEPOINT outer_sp;
Query OK
A> ROLLBACK TO SAVEPOINT inner_sp; -- SAVEPOINT inner_sp does not exist — destroyed by the outer rollback
ERROR 1305 (42000): SAVEPOINT inner_sp does not exist
```
*RELEASE keeps the work done after the savepoint, but you can no longer rewind to it.*
```transcript
A> INSERT INTO steps VALUES (4);
Query OK, 1 row affected
A> RELEASE SAVEPOINT outer_sp;
Query OK
A> COMMIT;
Query OK
A> SELECT n FROM steps ORDER BY n;
n
---
1
4
(2 rows)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/01-basics/savepoint-nesting.yaml)
## mysql/01-basics/savepoint-recovery.yaml
- engine: mysql (MySQL 8.4.10)
- claim: ROLLBACK TO SAVEPOINT discards only the work done after the savepoint — the rest of the transaction commits normally.
```transcript
A> BEGIN;
Query OK
A> INSERT INTO items VALUES (2, 'gadget');
Query OK, 1 row affected
A> SAVEPOINT before_risky;
Query OK
```
*The risky branch makes real progress before it fails…*
```transcript
A> INSERT INTO items VALUES (3, 'gizmo');
Query OK, 1 row affected
A> INSERT INTO items VALUES (4, 'widget'); -- ER_DUP_ENTRY
ERROR 1062 (23000): Duplicate entry 'widget' for key 'items.name'
```
*The transaction is still alive — but the branch is half-done. Rewind all of it in one go.*
```transcript
A> ROLLBACK TO SAVEPOINT before_risky;
Query OK
A> INSERT INTO items VALUES (3, 'doohickey');
Query OK, 1 row affected
A> COMMIT;
Query OK
A> SELECT id, name FROM items ORDER BY id; -- survived — it predates the savepoint; 'gizmo' is gone with the branch
id | name
----+-----------
1 | widget
2 | gadget
3 | doohickey
(3 rows)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/01-basics/savepoint-recovery.yaml)
## mysql/02-isolation/circular-information-flow.yaml
- engine: mysql (MySQL 8.4.10)
- claim: At READ UNCOMMITTED two concurrent transactions can each read the other's uncommitted write (Adya's G1c, "circular information flow") — an exchange no serial order can explain. READ COMMITTED makes it impossible.
```timeline
A: UPDATE alice = 111 (uncommitted)
B: UPDATE bob = 222 (uncommitted)
A: read bob → 222 (B's uncommitted)
B: read alice → 111 (A's uncommitted)
A: ROLLBACK
B: ROLLBACK
```
*A adjusts alice while B adjusts bob — then each peeks at the other's row. In any serial order, at most one of them can see the other's write.*
```transcript
A> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK
B> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK
A> BEGIN;
Query OK
B> BEGIN;
Query OK
A> UPDATE accounts SET balance = 111 WHERE id = 1;
Query OK, 1 row affected
B> UPDATE accounts SET balance = 222 WHERE id = 2;
Query OK, 1 row affected
A> SELECT balance FROM accounts WHERE id = 2; -- A sees B's uncommitted write…
balance
---------
222
(1 row)
B> SELECT balance FROM accounts WHERE id = 1; -- …and B sees A's. Information flowed in a circle.
balance
---------
111
(1 row)
```
*Neither value was committed when it was read — if either side now rolls back, the other has computed on data that never existed. Both roll back:*
```transcript
A> ROLLBACK;
Query OK
B> ROLLBACK;
Query OK
```
*The same dance at READ COMMITTED: each sees the world before the other.*
```transcript
A> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
B> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
A> BEGIN;
Query OK
B> BEGIN;
Query OK
A> UPDATE accounts SET balance = 111 WHERE id = 1;
Query OK, 1 row affected
B> UPDATE accounts SET balance = 222 WHERE id = 2;
Query OK, 1 row affected
A> SELECT balance FROM accounts WHERE id = 2;
balance
---------
100
(1 row)
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
A> COMMIT;
Query OK
B> COMMIT;
Query OK
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/02-isolation/circular-information-flow.yaml)
## mysql/02-isolation/current-reads.yaml
- engine: mysql (MySQL 8.4.10)
- claim: A REPEATABLE READ transaction's UPDATE operates on the CURRENT committed row, not on its snapshot — and afterwards the transaction sees its own write, so the 'repeatable' read changes.
```timeline
A: SELECT balance → 100 (snapshot)
B: UPDATE balance = 150 (committed)
A: SELECT balance → 100 (snapshot holds)
A: UPDATE +50 (current read of 150)
A: SELECT balance → 200 (150 + 50 — the hole)
A: COMMIT
```
```transcript
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1; -- snapshot taken
balance
---------
100
(1 row)
```
*B commits a change. A keeps READING its stale snapshot…*
```transcript
B> UPDATE accounts SET balance = 150 WHERE id = 1;
Query OK, 1 row affected
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
```
*…but A's UPDATE is a current read: it computes from B's committed 150, not from the snapshot's 100.*
```transcript
A> UPDATE accounts SET balance = balance + 50 WHERE id = 1;
Query OK, 1 row affected
A> SELECT balance FROM accounts WHERE id = 1; -- 150 + 50 — and now A sees it: the snapshot has a hole
balance
---------
200
(1 row)
A> COMMIT;
Query OK
```
*PostgreSQL would have aborted A's UPDATE with 40001 instead. MySQL quietly switches world views.*
*If the competing write is NOT yet committed, A first waits on the row lock…*
```transcript
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1; -- snapshot taken
balance
---------
200
(1 row)
B> BEGIN;
Query OK
B> UPDATE accounts SET balance = 300 WHERE id = 1;
Query OK, 1 row affected
A> UPDATE accounts SET balance = balance + 50 WHERE id = 1;
⏳ A is waiting for a lock…
```
*…and proceeds from B's value the moment B commits. No error here either.*
```transcript
B> COMMIT;
Query OK
⏵ A resumes:
Query OK, 1 row affected
A> SELECT balance FROM accounts WHERE id = 1; -- 300 + 50
balance
---------
350
(1 row)
A> COMMIT;
Query OK
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/02-isolation/current-reads.yaml)
## mysql/02-isolation/dirty-read.yaml
- engine: mysql (MySQL 8.4.10)
- claim: At READ UNCOMMITTED, MySQL serves other transactions' uncommitted changes — including values that are later rolled back and thus never existed.
```timeline
A: UPDATE balance = 999 (uncommitted)
B: SELECT balance → 999 (dirty read)
A: ROLLBACK — 999 never existed
B: SELECT balance → 100 (the real value)
```
```transcript
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = 999 WHERE id = 1;
Query OK, 1 row affected
```
*B opts into READ UNCOMMITTED — and sees A's uncommitted 999.*
```transcript
B> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK
B> BEGIN;
Query OK
B> SELECT @@transaction_isolation AS isolation;
isolation
------------------
READ-UNCOMMITTED
(1 row)
B> SELECT balance FROM accounts WHERE id = 1; -- a dirty read — A never committed this
balance
---------
999
(1 row)
```
*A rolls back. The 999 B just read never existed.*
```transcript
A> ROLLBACK;
Query OK
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
B> COMMIT;
Query OK
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/02-isolation/dirty-read.yaml)
## mysql/02-isolation/dirty-write.yaml
- engine: mysql (MySQL 8.4.10)
- claim: InnoDB always takes exclusive row locks for writes, so two transactions repricing the same rows cannot interleave their writes (Adya's G0, "dirty write") at any isolation level — even the one that allows dirty reads.
```timeline
A: UPDATE mug = 11
B: UPDATE mug = 12 → ⏳ waits
A: UPDATE cap = 21
A: COMMIT
B: ⏵ UPDATE mug = 12 → completes
B: UPDATE cap = 22
B: COMMIT
A: SELECT → 12, 22 (all B, never mixed)
```
*Two batch jobs reprice the whole catalog concurrently — at READ UNCOMMITTED, the weakest level MySQL has. A mix of their prices (A's mug with B's cap) would be a dirty write: a state no serial order could produce.*
```transcript
A> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK
B> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK
A> BEGIN;
Query OK
B> BEGIN;
Query OK
A> UPDATE items SET price = 11 WHERE id = 1;
Query OK, 1 row affected
```
*B may READ A's uncommitted rows at this level — but it may not overwrite them.*
```transcript
B> UPDATE items SET price = 12 WHERE id = 1;
⏳ B is waiting for a lock…
A> UPDATE items SET price = 21 WHERE id = 2;
Query OK, 1 row affected
A> COMMIT; -- releases both row locks
Query OK
⏵ B resumes:
Query OK, 1 row affected
B> UPDATE items SET price = 22 WHERE id = 2;
Query OK, 1 row affected
B> COMMIT;
Query OK
A> SELECT id, price FROM items ORDER BY id; -- all B — as if B ran after A. Never 12/21 or 11/22.
id | price
----+-------
1 | 12
2 | 22
(2 rows)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/02-isolation/dirty-write.yaml)
## mysql/02-isolation/intermediate-read.yaml
- engine: mysql (MySQL 8.4.10)
- claim: At READ UNCOMMITTED a reader can observe an intermediate value the writer later overwrites before committing (Adya's G1b, "intermediate read") — a number that is never part of any committed state. READ COMMITTED stops this.
```timeline
A: UPDATE balance = 999 (a draft)
B: SELECT balance → 999 (reads the draft)
A: UPDATE balance = 110 (final value)
A: COMMIT
B: SELECT balance → 110 (999 was never real)
```
*A recalculates alice's balance in two steps. B watches at READ UNCOMMITTED.*
```transcript
B> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK
B> BEGIN;
Query OK
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = 999 WHERE id = 1; -- a working draft — A isn't done yet
Query OK, 1 row affected
B> SELECT balance FROM accounts WHERE id = 1; -- B just read a draft
balance
---------
999
(1 row)
A> UPDATE accounts SET balance = 110 WHERE id = 1; -- A settles on the final value…
Query OK, 1 row affected
A> COMMIT;
Query OK
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
110
(1 row)
B> COMMIT;
Query OK
```
*The committed history is 100 → 110. The 999 B acted on was never true — not even for an instant of committed time. At READ COMMITTED the same watch shows only 100, then 110:*
```transcript
B> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
B> BEGIN;
Query OK
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = 555 WHERE id = 1;
Query OK, 1 row affected
B> SELECT balance FROM accounts WHERE id = 1; -- no drafts served here
balance
---------
110
(1 row)
A> ROLLBACK;
Query OK
B> COMMIT;
Query OK
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/02-isolation/intermediate-read.yaml)
## mysql/02-isolation/lost-update-read-committed.yaml
- engine: mysql (MySQL 8.4.10)
- claim: Two read-modify-write transactions at READ COMMITTED can silently overwrite each other: two deposits of 10 grow the balance by only 10.
```timeline
A: SELECT balance → 100
B: SELECT balance → 100 (same stale read)
A: UPDATE balance = 100 + 10
A: COMMIT
B: UPDATE balance = 100 + 10 (stale math)
B: COMMIT
A: SELECT balance → 110 — one deposit gone
```
*Two app servers process a +10 deposit each: read the balance, add 10 in code, write it back.*
```transcript
A> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
B> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
B> BEGIN;
Query OK
B> SELECT balance FROM accounts WHERE id = 1; -- B reads the same 100 — A hasn't committed
balance
---------
100
(1 row)
A> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
Query OK, 1 row affected
A> COMMIT;
Query OK
```
*B computed 100 + 10 from its stale read. Nothing stops the write — A's transaction is long gone.*
```transcript
B> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
Query OK, 0 rows affected
B> COMMIT;
Query OK
A> SELECT balance FROM accounts WHERE id = 1; -- two +10 deposits, but only one survived
balance
---------
110
(1 row)
```
*A's deposit vanished without any error. Fixes: atomic UPDATE or SELECT FOR UPDATE — see the lesson.*
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/02-isolation/lost-update-read-committed.yaml)
## mysql/02-isolation/lost-update-repeatable-read.yaml
- engine: mysql (MySQL 8.4.10)
- claim: The interleaving that loses an update at READ COMMITTED loses it at REPEATABLE READ too — MySQL raises no error, unlike PostgreSQL's 40001. The fix must be a locking read or an atomic UPDATE.
```timeline
A: SELECT balance → 100
B: SELECT balance → 100 (same snapshot)
A: UPDATE balance = 100 + 10
A: COMMIT
B: UPDATE balance = 100 + 10 — no error!
B: COMMIT
A: SELECT balance → 110 — deposit gone, silently
```
*The same two +10 deposits — this time at REPEATABLE READ, MySQL's default.*
```transcript
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
B> BEGIN;
Query OK
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
A> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
Query OK, 1 row affected
A> COMMIT;
Query OK
```
*B's snapshot predates A's commit — but MySQL's UPDATE acts on the CURRENT row and raises nothing.*
```transcript
B> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
Query OK, 0 rows affected
B> COMMIT;
Query OK
A> SELECT balance FROM accounts WHERE id = 1; -- A's deposit is gone — silently, even at REPEATABLE READ
balance
---------
110
(1 row)
```
*PostgreSQL refuses B's write here (SQLSTATE 40001). MySQL does not — don't port that assumption.*
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/02-isolation/lost-update-repeatable-read.yaml)
## mysql/02-isolation/non-repeatable-read.yaml
- engine: mysql (MySQL 8.4.10)
- claim: At READ COMMITTED, a transaction can read two different values for the same row — other transactions' commits become visible between its statements.
```timeline
A: SELECT balance → 100
B: UPDATE balance = 200 (autocommit)
A: SELECT balance → 200 — same txn, different answer
```
```transcript
A> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
```
*While A's transaction is still open, B updates the row and commits.*
```transcript
B> UPDATE accounts SET balance = 200 WHERE id = 1;
Query OK, 1 row affected
A> SELECT balance FROM accounts WHERE id = 1; -- same query, same transaction — different answer
balance
---------
200
(1 row)
A> COMMIT;
Query OK
```
*Readers never block — but writers do. The same interleaving with UPDATEs makes B wait for A's row lock.*
```transcript
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = 300 WHERE id = 1;
Query OK, 1 row affected
B> UPDATE accounts SET balance = 400 WHERE id = 1;
⏳ B is waiting for a lock…
A> COMMIT; -- releases the row lock
Query OK
⏵ B resumes:
Query OK, 1 row affected
B> SELECT balance FROM accounts WHERE id = 1; -- B's write landed on top of A's committed 300
balance
---------
400
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/02-isolation/non-repeatable-read.yaml)
## mysql/02-isolation/observed-transaction-vanishes.yaml
- engine: mysql (MySQL 8.4.10)
- claim: At READ UNCOMMITTED a reader can see a later transaction's uncommitted overwrite of one row while relying on the overwritten transaction's other row (Adya's OTV, "observed transaction vanishes") — the committed transaction is visible only in pieces. READ COMMITTED restores atomic visibility.
```timeline
A: id1 = 11
A: id2 = 19
B: UPDATE id1 = 12 → ⏳ waits
A: COMMIT (11 and 19)
B: ⏵ UPDATE id1 = 12 → completes
C: C reads → 12, 19 (A's 11 already gone)
```
*A rewrites both balances and commits. B overwrites one of them. C watches — dirtily.*
```transcript
C> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK
C> BEGIN;
Query OK
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = 11 WHERE id = 1;
Query OK, 1 row affected
A> UPDATE accounts SET balance = 19 WHERE id = 2;
Query OK, 1 row affected
B> BEGIN;
Query OK
B> UPDATE accounts SET balance = 12 WHERE id = 1;
⏳ B is waiting for a lock…
A> COMMIT; -- A is committed — its writes are 11 and 19
Query OK
⏵ B resumes:
Query OK, 1 row affected
C> SELECT id, balance FROM accounts ORDER BY id; -- 12 is B's uncommitted draft, 19 is A's commit — A's 11 already vanished
id | balance
----+---------
1 | 12
2 | 19
(2 rows)
```
*C never saw the committed state {11, 19}. Half of A was overwritten before C ever observed it — as far as C can tell, A only ever wrote one row. B now finishes:*
```transcript
B> UPDATE accounts SET balance = 18 WHERE id = 2;
Query OK, 1 row affected
B> COMMIT;
Query OK
C> SELECT id, balance FROM accounts ORDER BY id;
id | balance
----+---------
1 | 12
2 | 18
(2 rows)
C> COMMIT;
Query OK
```
*READ COMMITTED never shows a committed transaction in pieces:*
```transcript
C> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
C> BEGIN;
Query OK
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = 51 WHERE id = 1;
Query OK, 1 row affected
A> UPDATE accounts SET balance = 59 WHERE id = 2;
Query OK, 1 row affected
C> SELECT id, balance FROM accounts ORDER BY id; -- all of the last committed state — nothing of A's draft
id | balance
----+---------
1 | 12
2 | 18
(2 rows)
A> COMMIT;
Query OK
C> SELECT id, balance FROM accounts ORDER BY id; -- and now all of A, atomically
id | balance
----+---------
1 | 51
2 | 59
(2 rows)
C> COMMIT;
Query OK
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/02-isolation/observed-transaction-vanishes.yaml)
## mysql/02-isolation/phantom-read.yaml
- engine: mysql (MySQL 8.4.10)
- claim: At READ COMMITTED, re-running the same range query inside one transaction can return rows that weren't there before — phantoms.
```timeline
A: count(amount ≥ 100) → 2
B: INSERT order 3 (autocommit)
A: recount → 3, total 1500 — a phantom
```
*A computes a report twice inside one transaction: count first, then the total.*
```transcript
A> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
A> BEGIN;
Query OK
A> SELECT count(*) AS n FROM orders WHERE amount >= 100;
n
---
2
(1 row)
```
*Between A's two statements, B commits a new order that matches A's WHERE clause.*
```transcript
B> INSERT INTO orders VALUES (3, 700);
Query OK, 1 row affected
A> SELECT count(*) AS n, CAST(sum(amount) AS SIGNED) AS total FROM orders WHERE amount >= 100; -- a third row appeared out of nowhere — a phantom
n | total
---+-------
3 | 1500
(1 row)
A> COMMIT;
Query OK
```
*A's report now says '2 orders' in one place and '3 orders, total 1500' in another.*
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/02-isolation/phantom-read.yaml)
## mysql/02-isolation/read-skew.yaml
- engine: mysql (MySQL 8.4.10)
- claim: At READ COMMITTED, a transaction reading two rows around a concurrent transfer observes money that never existed (Adya's G-single, "read skew"); REPEATABLE READ's transaction-wide snapshot keeps plain SELECTs consistent.
```timeline
A: read alice → 50
B: alice −25
B: bob +25
B: COMMIT (transfer done)
A: read bob → 75 (50 + 75 = 125!)
```
*The invariant: alice + bob = 100 at every moment. A is an auditor summing the accounts row by row; B transfers 25 between them mid-audit.*
```transcript
A> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE owner = 'alice';
balance
---------
50
(1 row)
B> BEGIN;
Query OK
B> UPDATE accounts SET balance = balance - 25 WHERE owner = 'alice';
Query OK, 1 row affected
B> UPDATE accounts SET balance = balance + 25 WHERE owner = 'bob';
Query OK, 1 row affected
B> COMMIT; -- a perfectly correct transfer — atomic, invariant preserved
Query OK
A> SELECT balance FROM accounts WHERE owner = 'bob'; -- 50 + 75 = 125. The auditor found 25 that never existed.
balance
---------
75
(1 row)
A> COMMIT;
Query OK
```
*Neither row was ever wrong — A read alice BEFORE the transfer and bob AFTER it. REPEATABLE READ pins every plain SELECT to one snapshot:*
```transcript
A> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE owner = 'alice';
balance
---------
25
(1 row)
B> BEGIN;
Query OK
B> UPDATE accounts SET balance = balance - 10 WHERE owner = 'alice';
Query OK, 1 row affected
B> UPDATE accounts SET balance = balance + 10 WHERE owner = 'bob';
Query OK, 1 row affected
B> COMMIT;
Query OK
A> SELECT balance FROM accounts WHERE owner = 'bob'; -- 25 + 75 = 100 — one snapshot, one moment in time
balance
---------
75
(1 row)
A> COMMIT;
Query OK
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/02-isolation/read-skew.yaml)
## mysql/02-isolation/stable-snapshot.yaml
- engine: mysql (MySQL 8.4.10)
- claim: At REPEATABLE READ — MySQL's default — plain SELECTs use a single snapshot for the entire transaction: no non-repeatable reads and no phantoms.
```timeline
A: SELECT → 100, 50 (snapshot taken)
B: UPDATE id1 = 999 (committed)
B: INSERT carol (committed)
A: SELECT → 100, 50 — no 999, no carol
A: COMMIT
A: SELECT → 999 + carol (new snapshot)
```
```transcript
A> BEGIN;
Query OK
```
*The snapshot is taken by the FIRST query, not by BEGIN.*
```transcript
A> SELECT id, balance FROM accounts ORDER BY id;
id | balance
----+---------
1 | 100
2 | 50
(2 rows)
```
*B changes an existing row AND inserts a new one — both committed instantly.*
```transcript
B> UPDATE accounts SET balance = 999 WHERE id = 1;
Query OK, 1 row affected
B> INSERT INTO accounts VALUES (3, 'carol', 300);
Query OK, 1 row affected
A> SELECT id, balance FROM accounts ORDER BY id; -- not 999 — no non-repeatable read; and no carol — no phantom
id | balance
----+---------
1 | 100
2 | 50
(2 rows)
A> COMMIT;
Query OK
```
*Only a NEW transaction gets a new snapshot.*
```transcript
A> SELECT id, balance FROM accounts ORDER BY id;
id | balance
----+---------
1 | 999
2 | 50
3 | 300
(3 rows)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/02-isolation/stable-snapshot.yaml)
## mysql/02-isolation/update-recheck.yaml
- engine: mysql (MySQL 8.4.10)
- claim: An UPDATE at READ COMMITTED that waited for a lock re-evaluates its WHERE clause against the new row version — rows that no longer match are silently skipped.
```timeline
A: UPDATE row1 → 20 (uncommitted)
B: UPDATE WHERE value = 10 → ⏳ waits
A: COMMIT — row1 is now 20
B: ⏵ UPDATE WHERE value = 10 → completes
```
```transcript
A> BEGIN;
Query OK
A> UPDATE items SET value = value * 2 WHERE id = 1; -- row 1: 10 → 20, uncommitted
Query OK, 1 row affected
```
*B targets WHERE value = 10. The latest committed version of row 1 still qualifies — but it's locked by A, so B waits.*
```transcript
B> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
B> BEGIN;
Query OK
B> UPDATE items SET value = 99 WHERE value = 10;
⏳ B is waiting for a lock…
```
*A commits. B wakes up and re-checks the row it waited for — against the NEW version, where value is 20.*
```transcript
A> COMMIT;
Query OK
⏵ B resumes:
Query OK, 0 rows affected
```
*0 rows affected — the row slipped away.*
```transcript
B> COMMIT;
Query OK
B> SELECT id, value FROM items ORDER BY id;
id | value
----+-------
1 | 20
2 | 30
(2 rows)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/02-isolation/update-recheck.yaml)
## mysql/02-isolation/write-predicate-skew.yaml
- engine: mysql (MySQL 8.4.10)
- claim: Under REPEATABLE READ a write's WHERE clause is evaluated against the CURRENT committed data while SELECTs keep showing the snapshot — so a transaction can "delete every row matching X", delete nothing, and still see rows matching X (Hermitage's G-single write-predicate anomaly).
```timeline
A: SELECT → 10, 20 (snapshot)
B: +5 to every balance
B: COMMIT (now 15, 25)
A: DELETE balance = 20 → 0 rows
A: SELECT balance = 20 → still sees bob
```
*A opens a snapshot. B then reshuffles every balance and commits.*
```transcript
A> BEGIN;
Query OK
A> SELECT id, balance FROM accounts ORDER BY id; -- snapshot taken
id | balance
----+---------
1 | 10
2 | 20
(2 rows)
B> BEGIN;
Query OK
B> UPDATE accounts SET balance = balance + 5;
Query OK, 2 rows affected
B> COMMIT; -- current data is now 15 and 25
Query OK
```
*A closes every account with balance 20 — its snapshot says that's bob. But the DELETE is a current read: it scans the committed 15 and 25, finds nothing, and deletes nothing.*
```transcript
A> DELETE FROM accounts WHERE balance = 20;
Query OK, 0 rows affected
A> SELECT id, balance FROM accounts WHERE balance = 20 ORDER BY id; -- …yet A still SEES a row with balance 20. Deleted: no such rows. Visible: one.
id | balance
----+---------
2 | 20
(1 row)
A> COMMIT;
Query OK
```
*A's writes ran in one world, its reads in another. PostgreSQL's REPEATABLE READ aborts the DELETE with a serialization failure instead; on MySQL the cure is a locking read (FOR UPDATE) or SERIALIZABLE.*
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/02-isolation/write-predicate-skew.yaml)
## mysql/02-isolation/write-skew-rr.yaml
- engine: mysql (MySQL 8.4.10)
- claim: Two REPEATABLE READ transactions can each validate an invariant against their snapshots, write to different rows, and both commit — leaving the invariant broken. This is write skew.
```timeline
A: on-call count → 2
B: on-call count → 2
A: alice off call
B: bob off call
A: COMMIT
B: COMMIT (both succeed)
A: on-call count → 0 — invariant broken
```
*Hospital rule: at least one doctor must stay on call. Alice and Bob both want the night off.*
```transcript
A> BEGIN;
Query OK
B> BEGIN;
Query OK
A> SELECT count(*) AS on_call FROM doctors WHERE on_call; -- "two of us — safe for me to leave"
on_call
---------
2
(1 row)
B> SELECT count(*) AS on_call FROM doctors WHERE on_call; -- "two of us — safe for me to leave"
on_call
---------
2
(1 row)
```
*Each updates a DIFFERENT row, so there is no write-write conflict to detect.*
```transcript
A> UPDATE doctors SET on_call = false WHERE name = 'alice';
Query OK, 1 row affected
B> UPDATE doctors SET on_call = false WHERE name = 'bob';
Query OK, 1 row affected
A> COMMIT;
Query OK
B> COMMIT; -- both succeed!
Query OK
A> SELECT count(*) AS on_call FROM doctors WHERE on_call; -- nobody is on call — the invariant is broken
on_call
---------
0
(1 row)
```
*Each transaction was internally consistent; together they broke the rule. Only SERIALIZABLE catches this.*
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/02-isolation/write-skew-rr.yaml)
## mysql/02-isolation/write-skew-serializable.yaml
- engine: mysql (MySQL 8.4.10)
- claim: Under MySQL's SERIALIZABLE, plain SELECTs take shared locks, so the write-skew interleaving deadlocks: one transaction is rolled back with errno 1213, and the invariant survives.
```timeline
A: on-call count → 2 (takes shared locks)
B: on-call count → 2 (takes shared locks)
A: alice off call → ⏳ waits
B: bob off call ← 1213 Deadlock found when trying to get lock
A: ⏵ alice off call → completes
A: COMMIT
A: on-call count → 1 — invariant survived
```
*Same story, same statements, same order — only the isolation level differs.*
```transcript
A> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK
B> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK
A> BEGIN;
Query OK
B> BEGIN;
Query OK
A> SELECT count(*) AS on_call FROM doctors WHERE on_call;
on_call
---------
2
(1 row)
B> SELECT count(*) AS on_call FROM doctors WHERE on_call;
on_call
---------
2
(1 row)
```
*Those SELECTs took shared locks on the rows they read. A's write now waits for B…*
```transcript
A> UPDATE doctors SET on_call = false WHERE name = 'alice';
⏳ A is waiting for a lock…
```
*…and B's write closes the cycle. InnoDB detects the deadlock and rolls B back entirely.*
```transcript
B> UPDATE doctors SET on_call = false WHERE name = 'bob'; -- ER_LOCK_DEADLOCK
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
```
*B's rollback freed the locks — A's update proceeds.*
```transcript
⏵ A resumes:
Query OK, 1 row affected
A> COMMIT;
Query OK
A> SELECT count(*) AS on_call FROM doctors WHERE on_call; -- the invariant survived
on_call
---------
1
(1 row)
```
*PostgreSQL detects the same skew without blocking (SSI, at COMMIT). MySQL prevents it the classic way: locks and a deadlock victim.*
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/02-isolation/write-skew-serializable.yaml)
## mysql/03-locking/alter-table-outage.yaml
- engine: mysql (MySQL 8.4.10)
- claim: An ALTER TABLE queued behind one long transaction blocks every later query on that table — even plain SELECTs — because they must queue behind its exclusive metadata-lock request.
```timeline
A: SELECT (a long-lived transaction)
B: ALTER TABLE … ADD COLUMN → ⏳ waits
C: SELECT — just a read! → ⏳ waits
A: COMMIT
B: ⏵ ALTER TABLE … ADD COLUMN → completes
C: ⏵ SELECT — just a read! → completes
```
*A is any long-lived transaction that has touched the table — a report, a stuck job…*
```transcript
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
```
*The migration needs an exclusive metadata lock, so it waits for A. Expected. But now —*
```transcript
B> ALTER TABLE accounts ADD COLUMN note varchar(50);
⏳ B is waiting for a lock…
```
*— every new query on the table queues behind the *waiting* ALTER. This is the outage.*
```transcript
C> SELECT balance FROM accounts WHERE id = 1;
⏳ C is waiting for a lock…
M> SELECT state, info FROM performance_schema.processlist
WHERE state = 'Waiting for table metadata lock' ORDER BY info;
state | info
---------------------------------+--------------------------------------------------
Waiting for table metadata lock | ALTER TABLE accounts ADD COLUMN note varchar(50)
Waiting for table metadata lock | SELECT balance FROM accounts WHERE id = 1
(2 rows)
```
*Only when A ends does the pile-up drain — migration first, then the reads.*
```transcript
A> COMMIT;
Query OK
⏵ B resumes:
Query OK
⏵ C resumes:
balance
---------
100
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/03-locking/alter-table-outage.yaml)
## mysql/03-locking/ddl-lock-timeout.yaml
- engine: mysql (MySQL 8.4.10)
- claim: With lock_wait_timeout set, a migration that can't get its metadata lock fails fast (errno 1205) instead of queueing — and other sessions' queries are never blocked behind it.
```transcript
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1; -- the same long transaction as before
balance
---------
100
(1 row)
```
*Same migration — but this time it gives up after a second instead of camping in the queue.*
```transcript
B> SET SESSION lock_wait_timeout = 1;
Query OK
B> ALTER TABLE accounts ADD COLUMN note varchar(50); -- ER_LOCK_WAIT_TIMEOUT
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```
*No waiting ALTER in the queue means no outage: C's read is instant.*
```transcript
C> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
A> COMMIT;
Query OK
```
*Retry the migration when it can actually get the lock — now it sails through.*
```transcript
B> ALTER TABLE accounts ADD COLUMN note varchar(50);
Query OK
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/03-locking/ddl-lock-timeout.yaml)
## mysql/03-locking/deadlock-avoidance.yaml
- engine: mysql (MySQL 8.4.10)
- claim: The same two opposite-direction transfers cannot deadlock if both transactions lock the rows in the same (id) order first — the second simply waits its turn.
```timeline
A: locks id 1,2 (ordered)
B: same lock request → ⏳ waits
A: COMMIT
B: ⏵ same lock request → completes
```
*Both transfers grab *all* their row locks up front, ordered by id.*
```transcript
A> BEGIN;
Query OK
A> SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
id
----
1
2
(2 rows)
B> BEGIN;
Query OK
B> SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
⏳ B is waiting for a lock…
```
*No cycle is possible: B parks at the first row and holds nothing A needs.*
```transcript
A> UPDATE accounts SET balance = balance - 10 WHERE id = 1;
Query OK, 1 row affected
A> UPDATE accounts SET balance = balance + 10 WHERE id = 2;
Query OK, 1 row affected
A> COMMIT;
Query OK
⏵ B resumes:
id
----
1
2
(2 rows)
B> UPDATE accounts SET balance = balance - 25 WHERE id = 2;
Query OK, 1 row affected
B> UPDATE accounts SET balance = balance + 25 WHERE id = 1;
Query OK, 1 row affected
B> COMMIT;
Query OK
A> SELECT owner, balance FROM accounts ORDER BY id; -- both transfers landed — same workload, zero deadlocks
owner | balance
-------+---------
alice | 115
bob | 85
(2 rows)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/03-locking/deadlock-avoidance.yaml)
## mysql/03-locking/deadlock.yaml
- engine: mysql (MySQL 8.4.10)
- claim: Two transactions locking the same rows in opposite order deadlock; InnoDB detects the cycle instantly and rolls back one of them with errno 1213 so the other can finish.
```timeline
A: locks alice (id=1)
B: locks bob (id=2)
A: needs bob (id=2) → ⏳ waits
B: needs alice (id=1) ← 1213 Deadlock found when trying to get lock
A: ⏵ needs bob (id=2) → completes
```
*A transfers 10 from alice to bob; B transfers 25 from bob to alice.*
```transcript
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- A locks alice
Query OK, 1 row affected
B> BEGIN;
Query OK
B> UPDATE accounts SET balance = balance - 25 WHERE id = 2; -- B locks bob
Query OK, 1 row affected
```
*A now needs bob's row (B has it) — it waits.*
```transcript
A> UPDATE accounts SET balance = balance + 10 WHERE id = 2;
⏳ A is waiting for a lock…
```
*B now needs alice's row (A has it). A waits for B, B waits for A: a cycle.*
```transcript
B> UPDATE accounts SET balance = balance + 25 WHERE id = 1; -- ER_LOCK_DEADLOCK — B's whole transaction is rolled back…
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
```
*The victim's abort frees bob's row, so A's stuck UPDATE completes.*
```transcript
⏵ A resumes:
Query OK, 1 row affected
A> COMMIT;
Query OK
A> SELECT owner, balance FROM accounts ORDER BY id; -- A's transfer survived; B's evaporated — retry it
owner | balance
-------+---------
alice | 90
bob | 110
(2 rows)
```
*Unlike a lock timeout, a deadlock rolls back B's entire transaction — there is nothing left to ROLLBACK.*
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/03-locking/deadlock.yaml)
## mysql/03-locking/fk-shared-lock.yaml
- engine: mysql (MySQL 8.4.10)
- claim: INSERTing a child row locks the referenced parent row with a plain shared lock: even a non-key UPDATE of the parent blocks until the child commits (PostgreSQL's FOR KEY SHARE would allow it), and deleting a referenced parent fails outright.
```timeline
A: INSERT child (S-locks parent)
B: UPDATE parent balance → ⏳ waits
A: COMMIT — releases S lock
B: ⏵ UPDATE parent balance → completes
B: DELETE parent ← 1451 Cannot delete or update a parent row: a foreign key constraint fails (`app`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))
```
```transcript
A> BEGIN;
Query OK
A> INSERT INTO orders VALUES (1, 1); -- FK check takes an S lock on customers row 1
Query OK, 1 row affected
```
*InnoDB has no key-share granularity: B's harmless balance update needs an X lock and waits.*
```transcript
B> UPDATE customers SET balance = 50 WHERE id = 1;
⏳ B is waiting for a lock…
A> COMMIT;
Query OK
⏵ B resumes:
Query OK, 1 row affected
```
*With the order committed, deleting the parent doesn't block — it fails on the spot.*
```transcript
B> DELETE FROM customers WHERE id = 1; -- ER_ROW_IS_REFERENCED_2
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`app`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))
B> SELECT balance FROM customers WHERE id = 1; -- the parent survived, with B's update applied after the wait
balance
---------
50
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/03-locking/fk-shared-lock.yaml)
## mysql/03-locking/for-update-blocks.yaml
- engine: mysql (MySQL 8.4.10)
- claim: A row locked with SELECT FOR UPDATE can still be read by everyone, but any write to it waits until the locking transaction ends.
```timeline
A: SELECT … FOR UPDATE (locks the row)
B: SELECT balance → 100 (readers don't block)
B: UPDATE -10 → ⏳ waits
A: COMMIT (releases the lock)
B: ⏵ UPDATE -10 → completes
```
```transcript
A> BEGIN;
Query OK
A> SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
id | owner | balance
----+-------+---------
1 | alice | 100
(1 row)
```
*Reading the locked row costs B nothing — MVCC readers don't take row locks.*
```transcript
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
```
*Writing to it is another story: B's UPDATE must wait for A.*
```transcript
B> UPDATE accounts SET balance = balance - 10 WHERE id = 1;
⏳ B is waiting for a lock…
A> UPDATE accounts SET balance = 150 WHERE id = 1;
Query OK, 1 row affected
A> COMMIT; -- releases the row lock
Query OK
⏵ B resumes:
Query OK, 1 row affected
B> SELECT balance FROM accounts WHERE id = 1; -- B's -10 applied on top of A's committed 150
balance
---------
140
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/03-locking/for-update-blocks.yaml)
## mysql/03-locking/gap-locks.yaml
- engine: mysql (MySQL 8.4.10)
- claim: Under REPEATABLE READ a locking read on a range takes next-key locks — the matching rows AND the gaps between them — so INSERTs into the range block until the reader finishes. Under READ COMMITTED only the found rows are locked and the same INSERT sails through.
```timeline
A: SELECT 10–20 FOR UPDATE
B: INSERT slot 15 → ⏳ waits
A: waiting lock is X,GAP,INSERT_INTENTION
A: COMMIT — gap opens
B: ⏵ INSERT slot 15 → completes
A: same read, READ COMMITTED
B: INSERT slot 17 — no wait
```
*A runs the classic "hold the range while I decide" query. Slots 10 and 20 exist; 11–19 is a gap — nothing there to lock. Or is there?*
```transcript
A> BEGIN;
Query OK
A> SELECT slot FROM bookings WHERE slot BETWEEN 10 AND 20 FOR UPDATE;
slot
------
10
20
(2 rows)
```
*B books slot 15. No such row exists — yet the INSERT blocks.*
```transcript
B> BEGIN;
Query OK
B> INSERT INTO bookings VALUES (15, 'mallory');
⏳ B is waiting for a lock…
A> SELECT lock_mode FROM performance_schema.data_locks WHERE object_name = 'bookings' AND lock_status = 'WAITING'; -- B waits for an insert-intention lock on the gap A's range scan locked
lock_mode
------------------------
X,GAP,INSERT_INTENTION
(1 row)
A> COMMIT; -- the gap opens
Query OK
⏵ B resumes:
Query OK, 1 row affected
B> COMMIT;
Query OK
```
*Same story at READ COMMITTED: the locking read locks only the three rows it found — the gaps stay free.*
```transcript
A> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
A> BEGIN;
Query OK
A> SELECT slot FROM bookings WHERE slot BETWEEN 10 AND 20 FOR UPDATE;
slot
------
10
15
20
(3 rows)
B> INSERT INTO bookings VALUES (17, 'trent'); -- straight through — no gap lock to wait on
Query OK, 1 row affected
A> COMMIT;
Query OK
A> SELECT slot FROM bookings ORDER BY slot;
slot
------
10
15
17
20
30
(5 rows)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/03-locking/gap-locks.yaml)
## mysql/03-locking/lock-mode-matrix.yaml
- engine: mysql (MySQL 8.4.10)
- claim: InnoDB row locks come in exactly two strengths — S locks coexist with each other but block writers, X locks block everything. There is no PostgreSQL-style four-mode ladder.
```timeline
A: FOR SHARE (S)
B: FOR SHARE (S) — coexists
B: UPDATE (X) — needs the row → ⏳ waits
A: COMMIT
B: ⏵ UPDATE (X) — needs the row → completes
A: UPDATE (X)
B: FOR SHARE (S) — vs X → ⏳ waits
A: COMMIT
B: ⏵ FOR SHARE (S) — vs X → completes
```
*Two FOR SHARE locks on the same row coexist happily…*
```transcript
A> BEGIN;
Query OK
A> SELECT id FROM accounts WHERE id = 1 FOR SHARE;
id
----
1
(1 row)
B> BEGIN;
Query OK
B> SELECT id FROM accounts WHERE id = 1 FOR SHARE;
id
----
1
(1 row)
B> COMMIT;
Query OK
```
*…but a FOR SHARE still blocks a plain UPDATE (which needs an X lock).*
```transcript
B> UPDATE accounts SET balance = 200 WHERE id = 1;
⏳ B is waiting for a lock…
A> COMMIT;
Query OK
⏵ B resumes:
Query OK, 1 row affected
```
*And an X lock blocks even the friendliest reader: FOR SHARE has to wait for a running UPDATE.*
```transcript
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = 300 WHERE id = 1;
Query OK, 1 row affected
B> SELECT id FROM accounts WHERE id = 1 FOR SHARE;
⏳ B is waiting for a lock…
A> COMMIT;
Query OK
⏵ B resumes:
id
----
1
(1 row)
```
*PostgreSQL's FOR KEY SHARE would coexist with that UPDATE — InnoDB has no lock that weak.*
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/03-locking/lock-mode-matrix.yaml)
## mysql/03-locking/lock-queue.yaml
- engine: mysql (MySQL 8.4.10)
- claim: Sessions waiting for the same row lock pile up behind the holder, visible live in sys.innodb_lock_waits — and every queued update lands once the holder commits.
```timeline
A: UPDATE +1 (takes the row lock)
B: UPDATE +10 → ⏳ waits
M: who's waiting? → B blocked by A
C: UPDATE +100 → ⏳ waits
A: COMMIT → waiters drain
B: ⏵ UPDATE +10 → completes
C: ⏵ UPDATE +100 → completes
```
```transcript
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = balance + 1 WHERE id = 1;
Query OK, 1 row affected
B> UPDATE accounts SET balance = balance + 10 WHERE id = 1;
⏳ B is waiting for a lock…
```
*A fourth session, M, can watch the wait live.*
```transcript
M> SELECT waiting_pid, blocking_pid FROM sys.innodb_lock_waits;
waiting_pid | blocking_pid
-------------+--------------
pid(B) | pid(A)
(1 row)
C> UPDATE accounts SET balance = balance + 100 WHERE id = 1;
⏳ C is waiting for a lock…
```
*A commits — the waiters drain. (InnoDB's CATS scheduler does not promise strict FIFO order, but every update lands.)*
```transcript
A> COMMIT;
Query OK
⏵ B resumes:
Query OK, 1 row affected
⏵ C resumes:
Query OK, 1 row affected
C> SELECT balance FROM accounts WHERE id = 1; -- 100 + 1 + 10 + 100 — nothing was lost in the pile-up
balance
---------
211
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/03-locking/lock-queue.yaml)
## mysql/03-locking/lock-timeout.yaml
- engine: mysql (MySQL 8.4.10)
- claim: With innodb_lock_wait_timeout set, a statement waits for a row lock only that long, then fails with errno 1205 — only the statement is rolled back, the transaction survives.
```timeline
A: UPDATE (holds row lock)
B: UPDATE — waits 1s ← 1205 Lock wait timeout exceeded
A: COMMIT
B: retry UPDATE → 300
```
```transcript
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = 200 WHERE id = 1;
Query OK, 1 row affected
B> SET SESSION innodb_lock_wait_timeout = 1;
Query OK
```
*B queues for the row lock like anyone else — but gives up after a second.*
```transcript
B> UPDATE accounts SET balance = 300 WHERE id = 1; -- ER_LOCK_WAIT_TIMEOUT, raised after the timeout
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```
*The failure canceled only B's statement — a retry after A commits works.*
```transcript
A> COMMIT;
Query OK
B> UPDATE accounts SET balance = 300 WHERE id = 1;
Query OK, 1 row affected
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
300
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/03-locking/lock-timeout.yaml)
## mysql/03-locking/monitoring-locks.yaml
- engine: mysql (MySQL 8.4.10)
- claim: data_locks shows every lock a transaction holds — a single-row UPDATE takes an intention lock on the table plus a record lock on the row — and a waiter shows up as WAITING, findable via sys.innodb_lock_waits.
```transcript
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = 200 WHERE id = 1;
Query OK, 1 row affected
```
*One innocent UPDATE = two locks: an intention-exclusive on the table, an exclusive on the row.*
```transcript
M> SELECT object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
ORDER BY lock_type DESC;
object_name | index_name | lock_type | lock_mode | lock_status | lock_data
-------------+------------+-----------+---------------+-------------+-----------
accounts | | TABLE | IX | GRANTED |
accounts | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1
(2 rows)
B> UPDATE accounts SET balance = 300 WHERE id = 1;
⏳ B is waiting for a lock…
```
*The waiter's tell: a record-lock request with lock_status = WAITING.*
```transcript
M> SELECT object_name, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE lock_status = 'WAITING';
object_name | lock_mode | lock_status | lock_data
-------------+---------------+-------------+-----------
accounts | X,REC_NOT_GAP | WAITING | 1
(1 row)
```
*You rarely need to decode data_locks by hand — sys.innodb_lock_waits names the culprit.*
```transcript
M> SELECT waiting_pid, blocking_pid FROM sys.innodb_lock_waits;
waiting_pid | blocking_pid
-------------+--------------
pid(B) | pid(A)
(1 row)
A> COMMIT;
Query OK
⏵ B resumes:
Query OK, 1 row affected
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/03-locking/monitoring-locks.yaml)
## mysql/03-locking/nowait.yaml
- engine: mysql (MySQL 8.4.10)
- claim: SELECT ... FOR UPDATE NOWAIT refuses to wait: if the row is locked it fails immediately with errno 3572 instead of joining the lock queue.
```transcript
A> BEGIN;
Query OK
A> SELECT id FROM accounts WHERE id = 1 FOR UPDATE;
id
----
1
(1 row)
B> SELECT id FROM accounts WHERE id = 1 FOR UPDATE NOWAIT; -- ER_LOCK_NOWAIT — instantly, no waiting
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
```
*Once A is done, the same statement succeeds.*
```transcript
A> COMMIT;
Query OK
B> SELECT id FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
id
----
1
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/03-locking/nowait.yaml)
## mysql/03-locking/skip-locked.yaml
- engine: mysql (MySQL 8.4.10)
- claim: SELECT ... FOR UPDATE SKIP LOCKED silently skips locked rows, so concurrent workers each grab a different row without ever waiting — the backbone of SQL job queues.
```timeline
A: grabs job 1
B: job 1 taken → grabs job 2
C: grabs job 3
D: queue empty → nothing
A: crash (rollback) → job 1 freed
D: grabs job 1
```
*Four workers run the exact same query at the same time.*
```transcript
A> BEGIN;
Query OK
A> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+------------
1 | send email
(1 row)
B> BEGIN;
Query OK
B> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; -- job 1 is locked by A — skipped, no waiting
id | task
----+--------------
2 | resize image
(1 row)
C> BEGIN;
Query OK
C> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+--------------
3 | build report
(1 row)
```
*Worker D finds the queue empty — an instant answer, not a wait.*
```transcript
D> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
Empty set
```
*A worker crash (rollback) puts its job straight back on the queue.*
```transcript
A> ROLLBACK;
Query OK
D> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+------------
1 | send email
(1 row)
B> COMMIT;
Query OK
C> COMMIT;
Query OK
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/03-locking/skip-locked.yaml)
## mysql/04-mvcc/history-list-length.yaml
- engine: mysql (MySQL 8.4.10)
- claim: While any read view stays open, purge cannot remove the undo history behind it: every committed write transaction adds to the history list, and the history list length — InnoDB's bloat metric — grows for as long as the reader lives.
```timeline
R: read view opens
A: 200 commits pile up undo
A: history_len ≥ 200 — pinned
R: R still sees v=0
R: COMMIT → purge can drain it
```
*R opens a read view — and then does nothing.*
```transcript
R> BEGIN;
Query OK
R> SELECT v FROM counters;
v
---
0
(1 row)
```
*Meanwhile the application hums along: 200 small transactions, each updating one row and committing. Every one of them must keep its undo — R's read view might still need any of those versions.*
```transcript
A> CALL bump(200); -- 200 committed single-row updates
Query OK
A> SELECT count >= 200 AS pinned_by_reader FROM information_schema.INNODB_METRICS WHERE name = 'trx_rseg_history_len'; -- none of those 200 undo logs can be purged while R's read view lives
pinned_by_reader
------------------
1
(1 row)
R> SELECT v FROM counters; -- R still reads its original world — that's what all that undo buys
v
---
0
(1 row)
R> COMMIT;
Query OK
```
*With the read view gone, purge is free to reclaim all 200 versions in the background. The one number to watch in production is exactly this counter — trx_rseg_history_len.*
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/04-mvcc/history-list-length.yaml)
## mysql/04-mvcc/read-views.yaml
- engine: mysql (MySQL 8.4.10)
- claim: BEGIN does not take the snapshot — the transaction's first read does. START TRANSACTION WITH CONSISTENT SNAPSHOT takes it immediately. And until it writes, a transaction doesn't even get a real transaction ID — readers are cheap by design.
```timeline
A: UPDATE balance=999 (+ commit)
R: first read → 999 (view opens here, not at BEGIN)
R: consistent snapshot — view opens now
A: UPDATE balance=111
R: read → 999 (snapshot predates it)
```
```transcript
R> BEGIN;
Query OK
```
*R has "begun" — but hasn't read anything. A commits a change meanwhile.*
```transcript
A> UPDATE accounts SET balance = 999 WHERE id = 1;
Query OK, 1 row affected
R> SELECT balance FROM accounts WHERE id = 1; -- A's update is visible — the read view was created just now, by this SELECT
balance
---------
999
(1 row)
R> COMMIT;
Query OK
```
*START TRANSACTION WITH CONSISTENT SNAPSHOT moves the snapshot to the start:*
```transcript
R> START TRANSACTION WITH CONSISTENT SNAPSHOT;
Query OK
A> UPDATE accounts SET balance = 111 WHERE id = 1;
Query OK, 1 row affected
R> SELECT balance FROM accounts WHERE id = 1; -- this time the read view predates A's update
balance
---------
999
(1 row)
```
*One more thing R hasn't needed so far: a transaction ID. InnoDB hands read-only transactions a placeholder above 2^48 instead of consuming a real, persistent ID.*
```transcript
A> SELECT CAST(trx_id AS UNSIGNED) > 281474976710656 AS placeholder_id FROM information_schema.innodb_trx; -- R is the only open transaction — and its ID is fake
placeholder_id
----------------
1
(1 row)
R> UPDATE accounts SET balance = balance + 1 WHERE id = 1; -- R's first write…
Query OK, 1 row affected
A> SELECT CAST(trx_id AS UNSIGNED) > 281474976710656 AS placeholder_id FROM information_schema.innodb_trx; -- …and only now does it get a real transaction ID
placeholder_id
----------------
0
(1 row)
R> COMMIT;
Query OK
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/04-mvcc/read-views.yaml)
## mysql/04-mvcc/undo-logs.yaml
- engine: mysql (MySQL 8.4.10)
- claim: A committed DELETE removes rows only from the current version of the table. A transaction whose read view predates it keeps reading the old versions, rebuilt on demand from the undo log — long after the "real" rows are gone.
```timeline
R: reads 3 rows — snapshot opens
A: DELETE all 3 (+ commit)
A: table now empty
R: still sees 3 rows (from undo)
R: COMMIT — snapshot released
R: now sees 0 too
```
```transcript
R> BEGIN;
Query OK
R> SELECT id, balance FROM accounts ORDER BY id; -- the read view opens here
id | balance
----+---------
1 | 100
2 | 200
3 | 300
(3 rows)
```
*A deletes every row — and commits. This is not a trick; the data is gone.*
```transcript
A> DELETE FROM accounts;
Query OK, 3 rows affected
A> SELECT count(*) AS remaining FROM accounts;
remaining
-----------
0
(1 row)
```
*R's SELECT finds three rows anyway. InnoDB follows each deleted record's roll pointer into the undo log and rebuilds the version R's read view is entitled to — row by row, on every read.*
```transcript
R> SELECT id, balance FROM accounts ORDER BY id; -- reconstructed from undo, not read from the table
id | balance
----+---------
1 | 100
2 | 200
3 | 300
(3 rows)
R> COMMIT;
Query OK
R> SELECT count(*) AS remaining FROM accounts; -- the read view is gone — and with it, R's claim on that history
remaining
-----------
0
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/04-mvcc/undo-logs.yaml)
## mysql/05-patterns/advisory-locks.yaml
- engine: mysql (MySQL 8.4.10)
- claim: GET_LOCK serializes application-defined work with no table involved: timeout 0 gives an instant yes/no, a positive timeout gives a bounded wait, and the lock ignores transactions entirely — only RELEASE_LOCK (or disconnecting) lets go.
```timeline
A: GET_LOCK → held
B: GET_LOCK(0) → 0 (no wait)
B: GET_LOCK(1s) → 0 (waited)
A: COMMIT — releases nothing
B: GET_LOCK → 0 (still A's)
A: RELEASE_LOCK
B: GET_LOCK → held
```
*Two deploy runners must not migrate the same database at once. They agree the name 'migration' means "migration in progress".*
```transcript
A> SELECT GET_LOCK('migration', 0) AS got_it;
got_it
--------
1
(1 row)
B> SELECT GET_LOCK('migration', 0) AS got_it; -- timeout 0 — an instant answer, no waiting
got_it
--------
0
(1 row)
```
*A positive timeout waits, but only that long. B gives it one second:*
```transcript
B> SELECT GET_LOCK('migration', 1) AS got_it; -- A still holds it — B gets 0 after the deadline instead of an error
got_it
--------
0
(1 row)
B> SELECT IS_FREE_LOCK('migration') AS free; -- you can ask without trying to take it
free
------
0
(1 row)
```
*Advisory locks ignore transaction boundaries entirely — COMMIT releases nothing.*
```transcript
A> BEGIN;
Query OK
A> COMMIT;
Query OK
B> SELECT GET_LOCK('migration', 0) AS got_it; -- A's COMMIT changed nothing — the lock belongs to A's SESSION
got_it
--------
0
(1 row)
A> SELECT RELEASE_LOCK('migration') AS released;
released
----------
1
(1 row)
B> SELECT GET_LOCK('migration', 0) AS got_it; -- now B is the migration runner
got_it
--------
1
(1 row)
```
*One session can hold many named locks; RELEASE_ALL_LOCKS drops the lot. Disconnecting releases them too — a crashed runner can't jam the queue forever.*
```transcript
B> SELECT GET_LOCK('cache-rebuild', 0) AS got_it;
got_it
--------
1
(1 row)
B> SELECT RELEASE_ALL_LOCKS() AS released;
released
----------
2
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/05-patterns/advisory-locks.yaml)
## mysql/05-patterns/check-then-insert-race.yaml
- engine: mysql (MySQL 8.4.10)
- claim: "SELECT first, INSERT if absent" cannot enforce uniqueness: two concurrent requests both see 0 rows, both insert, and the duplicate lands without any error.
```timeline
A: any bob? → 0
B: any bob? → 0
A: INSERT bob
A: COMMIT
B: INSERT bob (check already passed)
B: COMMIT
A: count bobs → 2 — the "impossible" duplicate
```
*The same person double-clicks 'Sign up'. Two app servers each run: check, then insert.*
```transcript
A> BEGIN;
Query OK
A> SELECT count(*) AS existing FROM signups WHERE email = 'bob@example.com';
existing
----------
0
(1 row)
B> BEGIN;
Query OK
B> SELECT count(*) AS existing FROM signups WHERE email = 'bob@example.com'; -- B's check also passes — A hasn't committed anything
existing
----------
0
(1 row)
```
*Both checks passed, so both insert. Nothing blocks, nothing errors.*
```transcript
A> INSERT INTO signups (email) VALUES ('bob@example.com');
Query OK, 1 row affected
A> COMMIT;
Query OK
B> INSERT INTO signups (email) VALUES ('bob@example.com');
Query OK, 1 row affected
B> COMMIT;
Query OK
A> SELECT count(*) AS bobs FROM signups WHERE email = 'bob@example.com'; -- the "impossible" duplicate
bobs
------
2
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/05-patterns/check-then-insert-race.yaml)
## mysql/05-patterns/fix-lost-update-atomic.yaml
- engine: mysql (MySQL 8.4.10)
- claim: UPDATE ... SET balance = balance + 10 reads and writes in one statement: the second writer waits for the first and stacks on top — both deposits survive, at any isolation level.
*Same two +10 deposits that lost an update in chapter 2 — but the math moved into the UPDATE itself.*
```transcript
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = balance + 10 WHERE id = 1;
Query OK, 1 row affected
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
110
(1 row)
B> BEGIN;
Query OK
B> UPDATE accounts SET balance = balance + 10 WHERE id = 1;
⏳ B is waiting for a lock…
```
*No stale read exists to write back: B waits for A's row lock, then computes from the committed 110.*
```transcript
A> COMMIT;
Query OK
⏵ B resumes:
Query OK, 1 row affected
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
120
(1 row)
B> COMMIT;
Query OK
A> SELECT balance FROM accounts WHERE id = 1; -- both deposits survived
balance
---------
120
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/05-patterns/fix-lost-update-atomic.yaml)
## mysql/05-patterns/fix-lost-update-for-update.yaml
- engine: mysql (MySQL 8.4.10)
- claim: When the new value must be computed in application code, SELECT ... FOR UPDATE serializes the read-modify-write: the second reader waits and then reads the committed 110 — a locking read pierces the snapshot by design — so both deposits land.
*The app must apply business rules to the balance in code — so it locks the row while it reads.*
```transcript
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
balance
---------
100
(1 row)
B> BEGIN;
Query OK
B> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
⏳ B is waiting for a lock…
A> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
Query OK, 1 row affected
A> COMMIT;
Query OK
```
*B's locked read waited out A's transaction — and returns the fresh 110, not the 100 it would have seen.*
```transcript
⏵ B resumes:
balance
---------
110
(1 row)
B> UPDATE accounts SET balance = 110 + 10 WHERE id = 1;
Query OK, 1 row affected
B> COMMIT;
Query OK
A> SELECT balance FROM accounts WHERE id = 1; -- both deposits survived
balance
---------
120
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/05-patterns/fix-lost-update-for-update.yaml)
## mysql/05-patterns/fix-lost-update-version-column.yaml
- engine: mysql (MySQL 8.4.10)
- claim: A version column makes the lost update detectable instead of silent: the stale write matches 0 rows, and retrying against the new version lands both deposits — no locks held while the app thinks.
*Both app servers read the row — including its version — with no locks held.*
```transcript
A> BEGIN;
Query OK
A> SELECT balance, version FROM accounts WHERE id = 1;
balance | version
---------+---------
100 | 1
(1 row)
B> BEGIN;
Query OK
B> SELECT balance, version FROM accounts WHERE id = 1;
balance | version
---------+---------
100 | 1
(1 row)
```
*Every write bumps the version and only matches the version it read.*
```transcript
A> UPDATE accounts SET balance = 100 + 10, version = version + 1
WHERE id = 1 AND version = 1;
Query OK, 1 row affected
A> COMMIT;
Query OK
B> UPDATE accounts SET balance = 100 + 10, version = version + 1
WHERE id = 1 AND version = 1; -- 0 rows matched — the row moved on; B's deposit did NOT silently vanish
Query OK, 0 rows affected
```
*Zero affected rows is the signal to retry: roll back, re-read, write against the new version.*
```transcript
B> ROLLBACK;
Query OK
B> BEGIN;
Query OK
B> SELECT balance, version FROM accounts WHERE id = 1;
balance | version
---------+---------
110 | 2
(1 row)
B> UPDATE accounts SET balance = 110 + 10, version = version + 1
WHERE id = 1 AND version = 2;
Query OK, 1 row affected
B> COMMIT;
Query OK
A> SELECT balance, version FROM accounts WHERE id = 1; -- both deposits survived
balance | version
---------+---------
120 | 3
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/05-patterns/fix-lost-update-version-column.yaml)
## mysql/05-patterns/idempotency-key.yaml
- engine: mysql (MySQL 8.4.10)
- claim: An INSERT on an idempotency key reports 1 affected row exactly once: the first request charges, every retry — even one racing the original in flight — gets 0 affected rows and charges nothing.
*The client sends 'charge $30' with idempotency key req-42. Server A processes it.*
```transcript
A> BEGIN;
Query OK
A> INSERT INTO payments VALUES ('req-42', 30)
ON DUPLICATE KEY UPDATE amount = amount; -- 1 row inserted: this key is new — do the work
Query OK, 1 row affected
A> UPDATE accounts SET balance = balance - 30 WHERE id = 1;
Query OK, 1 row affected
A> COMMIT;
Query OK
```
*The response is lost in the network. The client retries the same request; server B picks it up.*
```transcript
B> BEGIN;
Query OK
B> INSERT INTO payments VALUES ('req-42', 30)
ON DUPLICATE KEY UPDATE amount = amount; -- 0 rows: already processed — skip the charge, return the stored result
Query OK, 0 rows affected
B> SELECT amount FROM payments WHERE idempotency_key = 'req-42';
amount
--------
30
(1 row)
B> COMMIT;
Query OK
A> SELECT balance FROM accounts WHERE id = 1; -- charged exactly once
balance
---------
70
(1 row)
```
*The nasty case: the retry arrives while the original is still in flight, uncommitted.*
```transcript
A> BEGIN;
Query OK
A> INSERT INTO payments VALUES ('req-99', 25)
ON DUPLICATE KEY UPDATE amount = amount;
Query OK, 1 row affected
A> UPDATE accounts SET balance = balance - 25 WHERE id = 1;
Query OK, 1 row affected
B> INSERT INTO payments VALUES ('req-99', 25)
ON DUPLICATE KEY UPDATE amount = amount;
⏳ B is waiting for a lock…
```
*The unique index parks the retry until the original decides. A commits — the retry absorbs to 0 rows.*
```transcript
A> COMMIT;
Query OK
⏵ B resumes:
Query OK, 0 rows affected
```
*Even the in-flight duplicate cannot double-charge.*
```transcript
A> SELECT balance FROM accounts WHERE id = 1; -- 100 - 30 - 25: every charge applied exactly once
balance
---------
45
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/05-patterns/idempotency-key.yaml)
## mysql/05-patterns/implicit-commit.yaml
- engine: mysql (MySQL 8.4.10)
- claim: Every DDL statement performs an implicit COMMIT of the current transaction first: after a mid-transaction CREATE INDEX, the "uncommitted" work is permanently committed and a later ROLLBACK undoes nothing.
*A migration script wraps its work in a transaction, believing that makes it atomic: insert data, add an index, and if anything fails — roll back.*
```transcript
A> BEGIN;
Query OK
A> INSERT INTO orders VALUES (1, 'draft');
Query OK, 1 row affected
B> SELECT count(*) AS visible FROM orders; -- uncommitted, invisible — so far so good
visible
---------
0
(1 row)
A> CREATE INDEX idx_orders_state ON orders (state); -- DDL — and an implicit COMMIT, right here
Query OK
B> SELECT count(*) AS visible FROM orders; -- A never said COMMIT. The CREATE INDEX did.
visible
---------
1
(1 row)
```
*The script now hits an error and rolls back, trusting the transaction to clean up:*
```transcript
A> ROLLBACK;
Query OK
B> SELECT count(*) AS visible FROM orders; -- rolled back nothing — the INSERT was already committed
visible
---------
1
(1 row)
```
*There is no transactional DDL in MySQL. A migration that mixes data and schema changes has commit points at every DDL statement — design for re-runnability instead of atomicity.*
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/05-patterns/implicit-commit.yaml)
## mysql/05-patterns/job-queue.yaml
- engine: mysql (MySQL 8.4.10)
- claim: The full worker loop — claim with FOR UPDATE SKIP LOCKED, work, mark done, commit — never double-processes a job and never loses one: a worker crash returns its job to the queue automatically.
```timeline
A: claim → job 1
B: claim → job 2 (skips locked job 1)
A: job 1 done
A: COMMIT
B: crash → job 2 requeued
B: claim → job 2 (back in queue)
B: job 2 done
B: COMMIT
A: both jobs done
```
*Two workers run the same loop: claim the oldest queued job, do the work, mark it done, commit.*
```transcript
A> BEGIN;
Query OK
A> SELECT id, task FROM jobs WHERE state = 'queued'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+--------------------
1 | send welcome email
(1 row)
B> BEGIN;
Query OK
B> SELECT id, task FROM jobs WHERE state = 'queued'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; -- job 1 is claimed — skipped without waiting
id | task
----+------------------
2 | generate invoice
(1 row)
```
*A finishes and commits. B crashes mid-job — its claim evaporates with its transaction.*
```transcript
A> UPDATE jobs SET state = 'done' WHERE id = 1;
Query OK, 1 row affected
A> COMMIT;
Query OK
B> ROLLBACK;
Query OK
```
*A restarted worker finds job 2 right back in the queue — nothing was lost, nothing ran twice.*
```transcript
B> BEGIN;
Query OK
B> SELECT id, task FROM jobs WHERE state = 'queued'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+------------------
2 | generate invoice
(1 row)
B> UPDATE jobs SET state = 'done' WHERE id = 2;
Query OK, 1 row affected
B> COMMIT;
Query OK
A> SELECT id, state FROM jobs ORDER BY id;
id | state
----+-------
1 | done
2 | done
(2 rows)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/05-patterns/job-queue.yaml)
## mysql/05-patterns/on-duplicate-key.yaml
- engine: mysql (MySQL 8.4.10)
- claim: A UNIQUE constraint makes the concurrent duplicate wait for the first insert's fate and then fail with errno 1062 — and ON DUPLICATE KEY UPDATE turns that error into a clean upsert (INSERT IGNORE absorbs it, at a price).
```timeline
A: INSERT bob
B: INSERT bob (duplicate) → ⏳ waits
A: COMMIT
B: ⏵ INSERT bob (duplicate) ← 1062 Duplicate entry 'bob@example.com' for key 'signups.email'
```
*Same double-click as the previous lesson, but now the table has UNIQUE (email).*
```transcript
A> BEGIN;
Query OK
A> INSERT INTO signups (email) VALUES ('bob@example.com');
Query OK, 1 row affected
```
*B's insert can't decide yet — the winner hasn't committed. It waits on A's transaction.*
```transcript
B> INSERT INTO signups (email) VALUES ('bob@example.com');
⏳ B is waiting for a lock…
A> COMMIT;
Query OK
⏵ B's blocked statement fails:
ERROR 1062 (23000): Duplicate entry 'bob@example.com' for key 'signups.email'
```
*ER_DUP_ENTRY — the race is now loud instead of silent.*
*ON DUPLICATE KEY UPDATE makes the duplicate do something useful instead of erroring:*
```transcript
B> INSERT INTO signups (email) VALUES ('bob@example.com')
ON DUPLICATE KEY UPDATE attempts = attempts + 1; -- affected = 2 is MySQL's way of saying 'existing row updated'
Query OK, 2 rows affected
B> SELECT email, attempts FROM signups; -- one row, and it counted the duplicate attempt
email | attempts
-----------------+----------
bob@example.com | 2
(1 row)
```
*INSERT IGNORE also absorbs the duplicate (0 rows affected) — but it downgrades EVERY error on the statement to a warning, not just 1062. Prefer ON DUPLICATE KEY UPDATE: it targets exactly the race you mean.*
```transcript
B> INSERT IGNORE INTO signups (email) VALUES ('bob@example.com');
Query OK, 0 rows affected
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/05-patterns/on-duplicate-key.yaml)
## mysql/05-patterns/retry-deadlocks.ts
- engine: mysql (MySQL 8.4.10)
- claim: Errno 1213 is transient, not fatal: the victim's transaction rerun from the top sees the survivor's committed state and succeeds — withRetry needs exactly two attempts here.
```transcript
B> BEGIN;
Query OK
B> UPDATE accounts SET balance = balance - 25 WHERE id = 2;
Query OK, 1 row affected
```
*B holds bob. A locks alice, then wants bob — and blocks. B then wants alice: a cycle.*
```transcript
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = balance - 10 WHERE id = 1;
Query OK, 1 row affected
A> UPDATE accounts SET balance = balance + 10 WHERE id = 2;
⏳ A is waiting for a lock…
B> UPDATE accounts SET balance = balance + 25 WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
⏵ A resumes:
Query OK, 1 row affected
A> COMMIT;
Query OK
B> BEGIN;
Query OK
B> UPDATE accounts SET balance = balance - 25 WHERE id = 2;
Query OK, 1 row affected
```
*Attempt 2 is a brand-new transaction. A is done — B's transfer sails through.*
```transcript
B> UPDATE accounts SET balance = balance + 25 WHERE id = 1;
Query OK, 1 row affected
B> COMMIT;
Query OK
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
115
(1 row)
A> SELECT balance FROM accounts WHERE id = 2;
balance
---------
85
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/05-patterns/retry-deadlocks.ts)
## mysql/06-distributed/dual-write-problem.yaml
- engine: mysql (MySQL 8.4.10)
- claim: Two systems, two writes, no shared transaction: whichever write goes first, a failure between them leaves the database and the broker permanently disagreeing.
*Attempt 1 — write, then publish. The order commits…*
```transcript
App> BEGIN;
Query OK
App> INSERT INTO orders VALUES (1, 'alice', 90);
Query OK, 1 row affected
App> COMMIT;
Query OK
```
*…and the process crashes before the publish step ever runs. The broker never hears about order 1.*
```transcript
App> SELECT (SELECT count(*) FROM orders) AS orders,
(SELECT count(*) FROM broker) AS events;
orders | events
--------+--------
1 | 0
(1 row)
```
*Attempt 2 — publish first, then write. The event goes out…*
```transcript
App> INSERT INTO broker VALUES ('order_placed: order 2');
Query OK, 1 row affected
```
*…and then the order INSERT fails — a constraint, a crash, a timeout, anything.*
```transcript
App> INSERT INTO orders VALUES (2, 'mallory', -5); -- ER_CHECK_CONSTRAINT_VIOLATED
ERROR 3819 (HY000): Check constraint 'orders_chk_1' is violated.
```
*Downstream services now process an order that never existed.*
```transcript
App> SELECT (SELECT count(*) FROM orders WHERE id = 2) AS orders,
(SELECT count(*) FROM broker WHERE event LIKE '%order 2%') AS events;
orders | events
--------+--------
0 | 1
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/06-distributed/dual-write-problem.yaml)
## mysql/06-distributed/saga-compensation.yaml
- engine: mysql (MySQL 8.4.10)
- claim: A saga replaces one distributed transaction with a chain of local ones: every step commits immediately and is visible to everyone, and a failed step is undone by a new compensating transaction — not by ROLLBACK.
*Step 1 — book the flight. A local transaction, committed immediately.*
```transcript
Saga> BEGIN;
Query OK
Saga> UPDATE flights SET seats = seats - 1 WHERE id = 1;
Query OK, 1 row affected
Saga> SELECT seats FROM flights WHERE id = 1;
seats
-------
4
(1 row)
Saga> COMMIT;
Query OK
```
*A saga has no isolation: between steps, the whole world sees the half-done trip.*
```transcript
Reader> SELECT seats FROM flights WHERE id = 1;
seats
-------
4
(1 row)
```
*Step 2 — book the hotel. No rooms left: the step fails as a business outcome, not an error.*
```transcript
Saga> BEGIN;
Query OK
Saga> UPDATE hotels SET rooms = rooms - 1 WHERE id = 1 AND rooms > 0; -- 0 rows — nothing to book
Query OK, 0 rows affected
Saga> ROLLBACK;
Query OK
```
*Step 1 already committed — there is nothing a ROLLBACK could undo. The saga runs a COMPENSATING transaction: a new forward transaction that reverses the booking.*
```transcript
Saga> BEGIN;
Query OK
Saga> UPDATE flights SET seats = seats + 1 WHERE id = 1;
Query OK, 1 row affected
Saga> SELECT seats FROM flights WHERE id = 1;
seats
-------
5
(1 row)
Saga> COMMIT;
Query OK
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/06-distributed/saga-compensation.yaml)
## mysql/06-distributed/transactional-outbox.yaml
- engine: mysql (MySQL 8.4.10)
- claim: Writing the order and its event in one transaction makes them atomic, and a SKIP LOCKED relay gives at-least-once delivery: a crashed relay redelivers the event instead of losing it.
*The order and its event are written in ONE transaction — both land in the same database.*
```transcript
App> BEGIN;
Query OK
App> INSERT INTO orders VALUES (1, 'alice', 90);
Query OK, 1 row affected
App> INSERT INTO outbox (event) VALUES ('order_placed: order 1');
Query OK, 1 row affected
App> COMMIT;
Query OK
```
*Atomicity covers the failure path too: no committed order, no event.*
```transcript
App> BEGIN;
Query OK
App> INSERT INTO orders VALUES (2, 'bob', 75);
Query OK, 1 row affected
App> INSERT INTO outbox (event) VALUES ('order_placed: order 2');
Query OK, 1 row affected
App> ROLLBACK;
Query OK
App> SELECT id, event FROM outbox ORDER BY id;
id | event
----+-----------------------
1 | order_placed: order 1
(1 row)
```
*A relay claims the event exactly like a chapter-5 job-queue worker…*
```transcript
Relay> BEGIN;
Query OK
Relay> SELECT id, event FROM outbox ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | event
----+-----------------------
1 | order_placed: order 1
(1 row)
```
*…publishes it to the broker (an HTTP call — outside any transaction), deletes it, and crashes before COMMIT.*
```transcript
Relay> DELETE FROM outbox WHERE id = 1;
Query OK, 1 row affected
Relay> ROLLBACK;
Query OK
```
*The delete evaporated with the crash — the event is still in the outbox. The restarted relay publishes it AGAIN: at-least-once delivery, so consumers must be idempotent.*
```transcript
Relay> BEGIN;
Query OK
Relay> SELECT id, event FROM outbox ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | event
----+-----------------------
1 | order_placed: order 1
(1 row)
Relay> DELETE FROM outbox WHERE id = 1;
Query OK, 1 row affected
Relay> COMMIT;
Query OK
Relay> SELECT count(*) AS pending FROM outbox;
pending
---------
0
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/06-distributed/transactional-outbox.yaml)
## mysql/06-distributed/xa-transactions.yaml
- engine: mysql (MySQL 8.4.10)
- claim: XA PREPARE detaches a transaction from its session: it survives the session's death, keeps holding its locks, and any later session can finish it by name with XA COMMIT.
*A is one participant in a distributed transfer. It does its work, then PREPARES — phase one.*
```transcript
A> XA START 'transfer-42';
Query OK
A> UPDATE accounts SET balance = 200 WHERE id = 1;
Query OK, 1 row affected
A> XA END 'transfer-42';
Query OK
A> XA PREPARE 'transfer-42';
Query OK
```
*XA PREPARE detached the transaction from the session: A itself no longer sees its own change.*
```transcript
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
M> XA RECOVER;
formatID | gtrid_length | bqual_length | data
----------+--------------+--------------+-------------
1 | 11 | 0 | transfer-42
(1 row)
```
*The prepared transaction still holds its row locks — with no session attached.*
```transcript
B> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE NOWAIT; -- ER_LOCK_NOWAIT
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
```
*Now the coordinator crashes: A's connection is killed outright.*
```transcript
M> CALL kill_session('A');
Query OK
A> SELECT 1;
ERROR ERR_MYSQL_CONNECTION_CLOSED (HY000): Connection closed
```
*The session is gone. The prepared transaction is not — it survives anything short of XA COMMIT/XA ROLLBACK, including a full server restart. And it still holds its locks:*
```transcript
M> XA RECOVER;
formatID | gtrid_length | bqual_length | data
----------+--------------+--------------+-------------
1 | 11 | 0 | transfer-42
(1 row)
B> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
```
*Phase two — any session can finish the job by name. B commits the orphan.*
```transcript
B> XA COMMIT 'transfer-42';
Query OK
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
200
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/06-distributed/xa-transactions.yaml)
## mysql/08-production/deadlock-counter.yaml
- engine: mysql (MySQL 8.4.10)
- claim: Every deadlock increments the server-wide lock_deadlocks counter in INNODB_METRICS — deadlocks are countable and alertable even if nobody saw the error, because the counter only ever goes up.
```timeline
A: debit id=1
B: debit id=2
A: credit id=2 → ⏳ waits
B: credit id=1 ← 1213 Deadlock found when trying to get lock
A: ⏵ credit id=2 → completes
```
*The same two opposite-order transfers as chapter 3 — one will be the victim.*
```transcript
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = balance - 10 WHERE id = 1;
Query OK, 1 row affected
B> BEGIN;
Query OK
B> UPDATE accounts SET balance = balance - 25 WHERE id = 2;
Query OK, 1 row affected
A> UPDATE accounts SET balance = balance + 10 WHERE id = 2;
⏳ A is waiting for a lock…
B> UPDATE accounts SET balance = balance + 25 WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
⏵ A resumes:
Query OK, 1 row affected
A> COMMIT;
Query OK
```
*The error flashed by in one client's logs. The server remembers it forever:*
```transcript
M> SELECT im.count - b.n >= 1 AS deadlocks_since_snapshot
FROM information_schema.INNODB_METRICS im, deadlocks_before b
WHERE im.name = 'lock_deadlocks'
-- a monotonic counter — graph its rate and alert on spikes
deadlocks_since_snapshot
--------------------------
1
(1 row)
```
*For the full story of the LAST deadlock (both statements, both lock chains), read the LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS — or set innodb_print_all_deadlocks=ON to log every one.*
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/08-production/deadlock-counter.yaml)
## mysql/08-production/find-long-transactions.yaml
- engine: mysql (MySQL 8.4.10)
- claim: Two joins on information_schema.innodb_trx find the forgotten transaction: the oldest open transaction by trx_started, and the session that holds one open while doing nothing — including the read-only kind that never shows up in write metrics but still pins purge.
*A starts a 'quick report'… and never gets around to committing.*
```transcript
A> BEGIN;
Query OK
A> SELECT count(*) AS orders FROM orders;
orders
--------
2
(1 row)
```
*Detector 1 — the oldest open transaction, its owner, and how long it's been open:*
```transcript
M> SELECT CAST(sn.variable_value AS CHAR) AS session_name, t.trx_state,
timestampdiff(SECOND, t.trx_started, now()) >= 1 AS older_than_1s
FROM information_schema.innodb_trx t
JOIN performance_schema.threads th ON th.processlist_id = t.trx_mysql_thread_id
JOIN performance_schema.user_variables_by_thread sn ON sn.thread_id = th.thread_id
WHERE sn.variable_name = 'session_name'
ORDER BY t.trx_started LIMIT 1;
session_name | trx_state | older_than_1s
--------------+-----------+---------------
A | RUNNING | 1
(1 row)
```
*Detector 2 — transactions whose session is idle (command Sleep) while holding them open:*
```transcript
M> SELECT CAST(sn.variable_value AS CHAR) AS session_name, p.command
FROM information_schema.innodb_trx t
JOIN performance_schema.processlist p ON p.id = t.trx_mysql_thread_id
JOIN performance_schema.threads th ON th.processlist_id = p.id
JOIN performance_schema.user_variables_by_thread sn ON sn.thread_id = th.thread_id
WHERE sn.variable_name = 'session_name' AND p.command = 'Sleep' AND p.time >= 1;
session_name | command
--------------+---------
A | Sleep
(1 row)
```
*Detector 3 — why it matters even for a read-only report: A never wrote a row (it doesn't even have a real transaction ID), yet its read view is exactly what purge must wait for.*
```transcript
M> SELECT t.trx_rows_modified AS rows_modified,
CAST(t.trx_id AS UNSIGNED) > 281474976710656 AS never_wrote
FROM information_schema.innodb_trx t
-- read-only — and still the oldest read view on the server
rows_modified | never_wrote
---------------+-------------
0 | 1
(1 row)
A> COMMIT;
Query OK
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/08-production/find-long-transactions.yaml)
## mysql/08-production/history-list-health.yaml
- engine: mysql (MySQL 8.4.10)
- claim: Two queries make undo bloat diagnosable: INNODB_METRICS' history list length says purge is falling behind, and a join on innodb_trx names the session whose old read view is holding it back.
*R opens a read view and goes quiet; the write workload keeps humming.*
```transcript
R> BEGIN;
Query OK
R> SELECT v FROM counters;
v
---
0
(1 row)
A> CALL bump(150); -- 150 committed single-row transactions
Query OK
```
*Check 1 — is history piling up? (Baseline is 'usually less than a few thousand'.)*
```transcript
M> SELECT count >= 150 AS history_backlog
FROM information_schema.INNODB_METRICS WHERE name = 'trx_rseg_history_len';
history_backlog
-----------------
1
(1 row)
```
*Check 2 — who is the oldest read view that purge is waiting for?*
```transcript
M> SELECT CAST(sn.variable_value AS CHAR) AS session_name,
timestampdiff(SECOND, t.trx_started, now()) >= 1 AS older_than_1s
FROM information_schema.innodb_trx t
JOIN performance_schema.threads th ON th.processlist_id = t.trx_mysql_thread_id
JOIN performance_schema.user_variables_by_thread sn ON sn.thread_id = th.thread_id
WHERE sn.variable_name = 'session_name'
ORDER BY t.trx_started LIMIT 1
-- end this transaction and purge catches up on its own
session_name | older_than_1s
--------------+---------------
R | 1
(1 row)
R> COMMIT;
Query OK
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/08-production/history-list-health.yaml)
## mysql/08-production/timeout-guardrails.yaml
- engine: mysql (MySQL 8.4.10)
- claim: max_execution_time cuts a runaway SELECT short without touching the session, and wait_timeout kills a connection that goes idle — rolling back whatever transaction it silently held open.
*Guardrail 1 — max_execution_time: a per-session (or per-query) ceiling for SELECTs.*
```transcript
A> SET SESSION max_execution_time = 100;
Query OK
A> SELECT SLEEP(2) AS interrupted; -- cut short at 100ms — SLEEP reports 1 when its wait is interrupted
interrupted
-------------
1
(1 row)
A> SET SESSION max_execution_time = 0;
Query OK
```
*Guardrail 2 — wait_timeout: the server hangs up on a session that goes quiet. A opens a transaction, updates a row… and stops talking.*
```transcript
A> SET SESSION wait_timeout = 1;
Query OK
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = 999 WHERE id = 1;
Query OK, 1 row affected
A> SELECT balance FROM accounts WHERE id = 1;
ERROR ERR_MYSQL_CONNECTION_CLOSED (HY000): Connection closed
```
*The app discovers the corpse on its next statement — and the uncommitted UPDATE is gone.*
```transcript
B> SELECT balance FROM accounts WHERE id = 1; -- rolled back with the session
balance
---------
100
(1 row)
```
*The third guardrail, innodb_lock_wait_timeout, was proven in chapter 3 — remember it rolls back the STATEMENT, not the transaction.*
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/08-production/timeout-guardrails.yaml)
## mysql/08-production/who-is-blocking-whom.yaml
- engine: mysql (MySQL 8.4.10)
- claim: One query on sys.innodb_lock_waits names the waiter and the blocker, and a join to the processlist shows the blocker is sitting idle in transaction — and killing it drains the queue.
```timeline
A: UPDATE, then goes to lunch (idle in txn)
B: UPDATE balance = 300 → ⏳ waits
M: who blocks whom? → B waits on sleeping A
M: KILL A
B: ⏵ UPDATE balance = 300 → completes
```
*A updates a row and then… goes to lunch. The transaction stays open.*
```transcript
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = 200 WHERE id = 1;
Query OK, 1 row affected
B> UPDATE accounts SET balance = 300 WHERE id = 1;
⏳ B is waiting for a lock…
```
*Someone pages you: 'updates are hanging'. This is the query you paste:*
```transcript
M> SELECT w.waiting_pid, w.waiting_query,
w.blocking_pid, bp.command AS blocker_state, bp.info AS blocker_running
FROM sys.innodb_lock_waits w
JOIN performance_schema.processlist bp ON bp.id = w.blocking_pid;
waiting_pid | waiting_query | blocking_pid | blocker_state | blocker_running
-------------+------------------------------------------------+--------------+---------------+-----------------
pid(B) | UPDATE accounts SET balance = 300 WHERE id = 1 | pid(A) | Sleep |
(1 row)
```
*The culprit isn't running anything — command Sleep, no current statement, holding locks. sys.innodb_lock_waits even pre-writes the KILL statement for you (sql_kill_blocking_connection). The fix is blunt:*
```transcript
M> CALL kill_session('A');
Query OK
```
*A's transaction dies and rolls back; B gets the lock and finishes at last.*
```transcript
⏵ B resumes:
Query OK, 1 row affected
B> SELECT balance FROM accounts WHERE id = 1; -- A's 200 rolled back with its death; B's 300 committed
balance
---------
300
(1 row)
```
Verified against MySQL 8.4.10 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/mysql/08-production/who-is-blocking-whom.yaml)
## postgres/01-basics/aborted-transaction.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: After any error inside a transaction, PostgreSQL rejects every further statement with 25P02 until you ROLLBACK.
```transcript
A> BEGIN;
BEGIN
A> SELECT 1 / 0; -- division_by_zero
ERROR: 22012: division by zero
```
*The transaction is now aborted. Even a perfectly innocent statement is refused.*
```transcript
A> SELECT 1 AS innocent; -- in_failed_sql_transaction
ERROR: 25P02: current transaction is aborted, commands ignored until end of transaction block
```
*ROLLBACK is the only way out. Afterwards, the session works normally again.*
```transcript
A> ROLLBACK;
ROLLBACK
A> SELECT 1 AS innocent;
innocent
----------
1
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/01-basics/aborted-transaction.yaml)
## postgres/01-basics/atomicity.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: A transaction that fails halfway leaves zero partial writes — even statements that already succeeded inside it are undone.
```timeline
A: credit bob +150 (uncommitted)
B: SELECT bob → 50 ← A's credit is invisible
A: debit alice −150 ← 23514 new row for relation "accounts" violates check constraint "accounts_balance_check"
A: ROLLBACK ← bob's credit vanishes too
B: SELECT → alice 100, bob 50 ← nothing changed
```
*A transfers 150 from alice to bob. Crediting bob works fine…*
```transcript
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = balance + 150 WHERE owner = 'bob';
UPDATE 1
B> SELECT balance FROM accounts WHERE owner = 'bob'; -- B can't see A's uncommitted credit
balance
---------
50
(1 row)
```
*…but debiting alice violates the CHECK constraint — she only has 100.*
```transcript
A> UPDATE accounts SET balance = balance - 150 WHERE owner = 'alice'; -- check_violation
ERROR: 23514: new row for relation "accounts" violates check constraint "accounts_balance_check"
```
*The failed transaction can only be rolled back. Bob's credit — which had succeeded — evaporates with it.*
```transcript
A> ROLLBACK;
ROLLBACK
B> SELECT owner, balance FROM accounts ORDER BY id;
owner | balance
-------+---------
alice | 100
bob | 50
(2 rows)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/01-basics/atomicity.yaml)
## postgres/01-basics/autocommit-visibility.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: Without BEGIN, every statement commits instantly and is immediately visible to everyone; inside BEGIN, changes stay private until COMMIT.
```timeline
A: UPDATE balance = 150 (autocommit, committed at once)
B: SELECT → 150 ← visible right away
A: UPDATE balance = 999 (uncommitted)
B: SELECT → 150 ← still the old value
A: COMMIT
B: SELECT → 999 ← now B sees it
```
*No BEGIN — the UPDATE is its own transaction, committed the instant it finishes.*
```transcript
A> UPDATE accounts SET balance = 150 WHERE id = 1;
UPDATE 1
B> SELECT balance FROM accounts WHERE id = 1; -- B sees it immediately
balance
---------
150
(1 row)
```
*Inside an explicit transaction, A's change is invisible to B…*
```transcript
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 999 WHERE id = 1;
UPDATE 1
B> SELECT balance FROM accounts WHERE id = 1; -- still the old value
balance
---------
150
(1 row)
```
*…until A commits.*
```transcript
A> COMMIT;
COMMIT
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
999
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/01-basics/autocommit-visibility.yaml)
## postgres/01-basics/ddl-rollback.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: Ordinary DDL participates in the transaction like any other statement: a CREATE INDEX and a CREATE TABLE stay invisible until COMMIT, and a ROLLBACK removes the schema they created along with the rows.
*A migration script wraps its work in a transaction, believing that makes it atomic: insert data, add an index, create a table — and if anything fails, roll back.*
```transcript
A> BEGIN;
BEGIN
A> INSERT INTO orders VALUES (1, 'draft');
INSERT 0 1
B> SELECT count(*)::int AS visible FROM orders; -- uncommitted, invisible — so far so good
visible
---------
0
(1 row)
A> CREATE INDEX idx_orders_state ON orders (state); -- DDL — on MySQL this is where the implicit COMMIT would fire
CREATE INDEX
A> CREATE TABLE migration_log (id int PRIMARY KEY, note text);
CREATE TABLE
B> SELECT count(*)::int AS visible FROM orders; -- still nothing committed — the DDL joined the transaction
visible
---------
0
(1 row)
B> SELECT count(*) FROM migration_log; -- undefined_table — uncommitted schema is invisible, not merely empty
ERROR: 42P01: relation "migration_log" does not exist
```
*The script now hits an error and rolls back, trusting the transaction to clean up:*
```transcript
A> ROLLBACK;
ROLLBACK
B> SELECT count(*)::int AS visible FROM orders; -- the row is gone
visible
---------
0
(1 row)
B> SELECT to_regclass('migration_log') IS NULL AS table_gone,
to_regclass('idx_orders_state') IS NULL AS index_gone; -- so is the table, and so is the index
table_gone | index_gone
------------+------------
t | t
(1 row)
```
*PostgreSQL keeps schema changes in the same transaction as the data. A migration that inserts, indexes, and creates its way to an error leaves nothing behind — design for atomicity, not for re-runnability.*
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/01-basics/ddl-rollback.yaml)
## postgres/01-basics/savepoint-nesting.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: Rolling back to an outer savepoint discards inner savepoints along with their work; RELEASE keeps the changes but forfeits the rollback point.
```transcript
A> BEGIN;
BEGIN
A> INSERT INTO steps VALUES (1);
INSERT 0 1
A> SAVEPOINT outer_sp;
SAVEPOINT
A> INSERT INTO steps VALUES (2);
INSERT 0 1
A> SAVEPOINT inner_sp;
SAVEPOINT
A> INSERT INTO steps VALUES (3);
INSERT 0 1
```
*Rolling back to the OUTER savepoint discards rows 2 and 3 — and inner_sp itself.*
```transcript
A> ROLLBACK TO SAVEPOINT outer_sp;
ROLLBACK
A> ROLLBACK TO SAVEPOINT inner_sp; -- no such savepoint — it was destroyed by the outer rollback
ERROR: 3B001: savepoint "inner_sp" does not exist
A> ROLLBACK TO SAVEPOINT outer_sp; -- recover from that error, same trick as before
ROLLBACK
```
*RELEASE keeps the work done after the savepoint, but you can no longer rewind to it.*
```transcript
A> INSERT INTO steps VALUES (4);
INSERT 0 1
A> RELEASE SAVEPOINT outer_sp;
RELEASE
A> COMMIT;
COMMIT
A> SELECT n FROM steps ORDER BY n;
n
---
1
4
(2 rows)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/01-basics/savepoint-nesting.yaml)
## postgres/01-basics/savepoint-recovery.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: ROLLBACK TO SAVEPOINT un-aborts a failed transaction, discarding only the work done after the savepoint — the rest commits normally.
```transcript
A> BEGIN;
BEGIN
A> INSERT INTO items VALUES (2, 'gadget');
INSERT 0 1
A> SAVEPOINT before_risky;
SAVEPOINT
A> INSERT INTO items VALUES (3, 'widget'); -- unique_violation — the transaction is aborted
ERROR: 23505: duplicate key value violates unique constraint "items_name_key"
```
*Without the savepoint this transaction would be doomed. With it, we rewind past the failure and carry on.*
```transcript
A> ROLLBACK TO SAVEPOINT before_risky;
ROLLBACK
A> INSERT INTO items VALUES (3, 'doohickey');
INSERT 0 1
A> COMMIT;
COMMIT
A> SELECT id, name FROM items ORDER BY id; -- survived — it predates the savepoint
id | name
----+-----------
1 | widget
2 | gadget
3 | doohickey
(3 rows)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/01-basics/savepoint-recovery.yaml)
## postgres/02-isolation/circular-information-flow.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: Two concurrent transactions cannot read each other's uncommitted writes (Adya's G1c, "circular information flow"): each sees the pre-existing value, as one serial order or the other would dictate.
```timeline
A: UPDATE alice = 111 (uncommitted)
B: UPDATE bob = 222 (uncommitted)
A: SELECT bob → 100 ← B's 222 invisible
B: SELECT alice → 100 ← A's 111 invisible
A: COMMIT
B: COMMIT
A: SELECT → 111, 222 ← both writes, no cross-read
```
*A adjusts alice's balance while B adjusts bob's — then each peeks at the other's row. If both saw the other's uncommitted write, information would flow in a circle: A → B → A. No serial order can do that.*
```transcript
A> BEGIN;
BEGIN
B> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 111 WHERE id = 1;
UPDATE 1
B> UPDATE accounts SET balance = 222 WHERE id = 2;
UPDATE 1
A> SELECT balance FROM accounts WHERE id = 2; -- B's uncommitted 222 is invisible to A
balance
---------
100
(1 row)
B> SELECT balance FROM accounts WHERE id = 1; -- and A's uncommitted 111 is invisible to B
balance
---------
100
(1 row)
A> COMMIT;
COMMIT
B> COMMIT;
COMMIT
A> SELECT id, balance FROM accounts ORDER BY id; -- both writes landed — but neither transaction ever saw the other's
id | balance
----+---------
1 | 111
2 | 222
(2 rows)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/02-isolation/circular-information-flow.yaml)
## postgres/02-isolation/concurrent-update-40001.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: A REPEATABLE READ transaction that tries to UPDATE a row modified since its snapshot fails with SQLSTATE 40001 — immediately if the change is committed, or after waiting if it isn't.
```timeline
A: SELECT (snapshot taken)
B: UPDATE balance = 300 (uncommitted)
A: UPDATE balance = 999 → ⏳ waits
B: COMMIT
A: ⏵ UPDATE balance = 999 ← 40001 could not serialize access due to concurrent update
```
```transcript
A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
A> SELECT balance FROM accounts WHERE id = 1; -- snapshot taken
balance
---------
100
(1 row)
```
*B commits a change. A can keep READING its stale snapshot — but writing that row is refused.*
```transcript
B> UPDATE accounts SET balance = 150 WHERE id = 1;
UPDATE 1
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
A> UPDATE accounts SET balance = 200 WHERE id = 1; -- serialization_failure — retry the whole transaction
ERROR: 40001: could not serialize access due to concurrent update
A> ROLLBACK;
ROLLBACK
```
*If the competing write is NOT yet committed, the outcome is decided later: A first waits on the row lock…*
```transcript
A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
A> SELECT balance FROM accounts WHERE id = 1; -- snapshot taken
balance
---------
150
(1 row)
B> BEGIN;
BEGIN
B> UPDATE accounts SET balance = 300 WHERE id = 1;
UPDATE 1
A> UPDATE accounts SET balance = 999 WHERE id = 1;
⏳ A is waiting for a lock…
```
*…and fails with 40001 the moment B commits. (Had B rolled back, A would have proceeded.)*
```transcript
B> COMMIT;
COMMIT
⏵ A's blocked statement fails:
ERROR: 40001: could not serialize access due to concurrent update
A> ROLLBACK;
ROLLBACK
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/02-isolation/concurrent-update-40001.yaml)
## postgres/02-isolation/dirty-write.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: Two transactions repricing the same rows cannot interleave their writes (Adya's G0, "dirty write"): the second writer waits for the first to finish, so the final state is one transaction's prices, never a mix.
```timeline
A: UPDATE id=1 price=11
B: UPDATE id=1 price=12 → ⏳ waits
A: UPDATE id=2 price=21
A: COMMIT ← locks released, B wakes up
B: ⏵ UPDATE id=1 price=12 → completes
B: UPDATE id=2 price=22
B: COMMIT
A: SELECT → 12, 22 ← all B, never a mix
```
*Two batch jobs reprice the whole catalog concurrently. A mix of their prices (A's mug with B's cap) would be a dirty write — a state no serial order could produce.*
```transcript
A> BEGIN;
BEGIN
B> BEGIN;
BEGIN
A> UPDATE items SET price = 11 WHERE id = 1;
UPDATE 1
```
*B wants the same row. Even at READ COMMITTED it must wait for A's lock.*
```transcript
B> UPDATE items SET price = 12 WHERE id = 1;
⏳ B is waiting for a lock…
A> UPDATE items SET price = 21 WHERE id = 2;
UPDATE 1
A> COMMIT; -- releases both row locks
COMMIT
⏵ B resumes:
UPDATE 1
B> UPDATE items SET price = 22 WHERE id = 2;
UPDATE 1
B> COMMIT;
COMMIT
A> SELECT id, price FROM items ORDER BY id; -- all B — as if B had run after A. Never 12/21 or 11/22.
id | price
----+-------
1 | 12
2 | 22
(2 rows)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/02-isolation/dirty-write.yaml)
## postgres/02-isolation/intermediate-read.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: A transaction that changes a value twice exposes neither draft to other transactions (Adya's G1b, "intermediate read"): readers see the old value until commit, then only the final one.
```timeline
A: UPDATE balance = 999 (draft, uncommitted)
B: SELECT → 100 ← draft 999 invisible
A: UPDATE balance = 110 (final value)
A: COMMIT
B: SELECT → 110 ← only the final value, never 999
```
```transcript
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 999 WHERE id = 1; -- a working draft — A isn't done yet
UPDATE 1
B> SELECT balance FROM accounts WHERE id = 1; -- the draft 999 is invisible
balance
---------
100
(1 row)
A> UPDATE accounts SET balance = 110 WHERE id = 1; -- A settles on the final value…
UPDATE 1
A> COMMIT;
COMMIT
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
110
(1 row)
```
*To every other transaction, the balance went 100 → 110 in one step. The intermediate 999 never existed outside A.*
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/02-isolation/intermediate-read.yaml)
## postgres/02-isolation/lost-update-read-committed.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: Two read-modify-write transactions at READ COMMITTED can silently overwrite each other: two deposits of 10 grow the balance by only 10.
```timeline
A: SELECT balance → 100
B: SELECT balance → 100 (same stale read)
A: UPDATE balance = 100 + 10
A: COMMIT
B: UPDATE balance = 100 + 10 (stale math)
B: COMMIT
A: SELECT balance → 110 — one deposit gone
```
*Two app servers process a +10 deposit each: read the balance, add 10 in code, write it back.*
```transcript
A> BEGIN;
BEGIN
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
B> BEGIN;
BEGIN
B> SELECT balance FROM accounts WHERE id = 1; -- B reads the same 100 — A hasn't committed
balance
---------
100
(1 row)
A> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
UPDATE 1
A> COMMIT;
COMMIT
```
*B computed 100 + 10 from its stale read. Nothing stops the write — A's transaction is long gone.*
```transcript
B> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
UPDATE 1
B> COMMIT;
COMMIT
A> SELECT balance FROM accounts WHERE id = 1; -- two +10 deposits, but only one survived
balance
---------
110
(1 row)
```
*A's deposit vanished without any error. Fixes: atomic UPDATE, SELECT FOR UPDATE, or REPEATABLE READ — see the lesson.*
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/02-isolation/lost-update-read-committed.yaml)
## postgres/02-isolation/lost-update-repeatable-read.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: The interleaving that silently loses an update at READ COMMITTED fails loudly with SQLSTATE 40001 at REPEATABLE READ — no data is lost, the loser just retries.
```timeline
A: SELECT balance → 100
B: SELECT balance → 100 (same snapshot)
A: UPDATE balance = 100 + 10
A: COMMIT
B: UPDATE balance = 100 + 10 ← 40001 could not serialize access due to concurrent update
A: SELECT balance → 110 — nothing lost, B retries
```
*The same two +10 deposits — but this time at REPEATABLE READ.*
```transcript
A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
B> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
A> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
UPDATE 1
A> COMMIT;
COMMIT
```
*B's snapshot predates A's commit, so B's write is refused instead of silently clobbering it.*
```transcript
B> UPDATE accounts SET balance = 100 + 10 WHERE id = 1; -- serialization_failure
ERROR: 40001: could not serialize access due to concurrent update
B> ROLLBACK;
ROLLBACK
A> SELECT balance FROM accounts WHERE id = 1; -- A's deposit is safe; B retries and lands on 120
balance
---------
110
(1 row)
```
*Retrying B from scratch reads the fresh 110 and correctly produces 120.*
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/02-isolation/lost-update-repeatable-read.yaml)
## postgres/02-isolation/no-dirty-reads.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: PostgreSQL accepts READ UNCOMMITTED syntax but behaves as READ COMMITTED: uncommitted changes from other transactions are never visible.
```timeline
A: UPDATE balance = 999 (uncommitted)
B: isolation = read uncommitted ← setting accepted
B: SELECT → 100 ← A's 999 stays invisible
```
```transcript
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 999 WHERE id = 1;
UPDATE 1
```
*B explicitly requests READ UNCOMMITTED — the one level that would permit dirty reads.*
```transcript
B> BEGIN ISOLATION LEVEL READ UNCOMMITTED;
BEGIN
B> SELECT current_setting('transaction_isolation') AS isolation; -- the setting is accepted…
isolation
------------------
read uncommitted
(1 row)
B> SELECT balance FROM accounts WHERE id = 1; -- …but A's uncommitted 999 stays invisible
balance
---------
100
(1 row)
B> COMMIT;
COMMIT
A> ROLLBACK;
ROLLBACK
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/02-isolation/no-dirty-reads.yaml)
## postgres/02-isolation/non-repeatable-read.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: At READ COMMITTED, a transaction can read two different values for the same row — other transactions' commits become visible between its statements.
```timeline
A: SELECT balance → 100
B: UPDATE balance = 200 (autocommit)
A: SELECT balance → 200 — same txn, different answer
```
```transcript
A> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
```
*While A's transaction is still open, B updates the row and commits.*
```transcript
B> UPDATE accounts SET balance = 200 WHERE id = 1;
UPDATE 1
A> SELECT balance FROM accounts WHERE id = 1; -- same query, same transaction — different answer
balance
---------
200
(1 row)
A> COMMIT;
COMMIT
```
*Readers never block — but writers do. The same interleaving with UPDATEs makes B wait for A's row lock.*
```transcript
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 300 WHERE id = 1;
UPDATE 1
B> UPDATE accounts SET balance = 400 WHERE id = 1;
⏳ B is waiting for a lock…
A> COMMIT; -- releases the row lock
COMMIT
⏵ B resumes:
UPDATE 1
B> SELECT balance FROM accounts WHERE id = 1; -- B's write landed on top of A's committed 300
balance
---------
400
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/02-isolation/non-repeatable-read.yaml)
## postgres/02-isolation/observed-transaction-vanishes.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: Once a transaction commits, every reader sees ALL of its writes or none (Adya's OTV, "observed transaction vanishes"): a later overwrite of one row cannot make the rest of the committed transaction disappear.
```timeline
A: UPDATE alice = 11
A: UPDATE bob = 19
B: UPDATE alice = 12 → ⏳ waits
A: COMMIT ← lock released, B proceeds
B: ⏵ UPDATE alice = 12 → completes
C: SELECT → 11, 19 ← all of A, together
B: UPDATE bob = 18 (uncommitted)
C: SELECT → 11, 19 ← B's drafts don't count
B: COMMIT
C: SELECT → 12, 18 ← now all of B, atomically
```
*A rewrites both balances. B will overwrite one of them right after.*
```transcript
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 11 WHERE id = 1;
UPDATE 1
A> UPDATE accounts SET balance = 19 WHERE id = 2;
UPDATE 1
B> BEGIN;
BEGIN
B> UPDATE accounts SET balance = 12 WHERE id = 1;
⏳ B is waiting for a lock…
A> COMMIT; -- A's lock released — B's overwrite of alice proceeds
COMMIT
⏵ B resumes:
UPDATE 1
```
*C now watches from the side. B holds an uncommitted overwrite of alice — but A's committed transaction must still be visible in full.*
```transcript
C> SELECT id, balance FROM accounts ORDER BY id; -- A's writes appear together — 11 AND 19
id | balance
----+---------
1 | 11
2 | 19
(2 rows)
B> UPDATE accounts SET balance = 18 WHERE id = 2;
UPDATE 1
C> SELECT id, balance FROM accounts ORDER BY id; -- B's drafts change nothing for C
id | balance
----+---------
1 | 11
2 | 19
(2 rows)
B> COMMIT;
COMMIT
C> SELECT id, balance FROM accounts ORDER BY id; -- only now does C move on — to ALL of B, atomically
id | balance
----+---------
1 | 12
2 | 18
(2 rows)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/02-isolation/observed-transaction-vanishes.yaml)
## postgres/02-isolation/phantom-read.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: At READ COMMITTED, re-running the same range query inside one transaction can return rows that weren't there before — phantoms.
```timeline
A: SELECT count → 2
B: INSERT order 3 (700), committed
A: SELECT count → 3, total 1500 ← a phantom row
A: COMMIT
```
*A computes a report twice inside one transaction: count first, then the total.*
```transcript
A> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN
A> SELECT count(*)::int AS n FROM orders WHERE amount >= 100;
n
---
2
(1 row)
```
*Between A's two statements, B commits a new order that matches A's WHERE clause.*
```transcript
B> INSERT INTO orders VALUES (3, 700);
INSERT 0 1
A> SELECT count(*)::int AS n, sum(amount)::int AS total FROM orders WHERE amount >= 100; -- a third row appeared out of nowhere — a phantom
n | total
---+-------
3 | 1500
(1 row)
A> COMMIT;
COMMIT
```
*A's report now says '2 orders' in one place and '3 orders, total 1500' in another.*
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/02-isolation/phantom-read.yaml)
## postgres/02-isolation/read-only-anomaly.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: At REPEATABLE READ, even a read-only transaction can observe a state no serial execution could produce; at SERIALIZABLE, PostgreSQL aborts the offending writer instead (SQLSTATE 40001).
```timeline
Cashier: SELECT deposit_no → 1 (current batch)
Cashier: INSERT receipt 3 into batch 1 (slow, uncommitted)
Closer: UPDATE deposit_no = 2 (batch 1 closed)
Closer: COMMIT
Report: SELECT deposit_no → 2 ← batch 1 is closed
Report: SELECT batch 1 → receipts 1, 2 (total 300) ← report published
Report: COMMIT
Cashier: COMMIT ← receipt 3 lands in the closed batch 1
Report: re-read batch 1 → 3 receipts ← the published report was wrong
```
*A bank tracks receipts per deposit batch. The cashier files a receipt into the current batch (1) — slowly.*
```transcript
Cashier> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
Cashier> SELECT deposit_no FROM control;
deposit_no
------------
1
(1 row)
Cashier> INSERT INTO receipts VALUES (3, 1, 400);
INSERT 0 1
```
*Meanwhile the closer moves the bank to batch 2 — from now on, batch 1 is supposedly complete.*
```transcript
Closer> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
Closer> UPDATE control SET deposit_no = 2;
UPDATE 1
Closer> COMMIT;
COMMIT
```
*An auditor prints the report for the closed batch 1.*
```transcript
Report> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
Report> SELECT deposit_no FROM control; -- batch 1 is closed…
deposit_no
------------
2
(1 row)
Report> SELECT receipt_no, amount FROM receipts WHERE deposit_no = 1 ORDER BY receipt_no; -- …and contains receipts 1 and 2, total 300. The report is published.
receipt_no | amount
------------+--------
1 | 100
2 | 200
(2 rows)
Report> COMMIT;
COMMIT
```
*Now the cashier's receipt lands — in batch 1, which the report just declared final.*
```transcript
Cashier> COMMIT;
COMMIT
Report> SELECT receipt_no, amount FROM receipts WHERE deposit_no = 1 ORDER BY receipt_no; -- the published report was wrong: no serial order of these three transactions produces it
receipt_no | amount
------------+--------
1 | 100
2 | 200
3 | 400
(3 rows)
```
*Rewind and replay the exact same interleaving — all three transactions SERIALIZABLE.*
```transcript
Report> DELETE FROM receipts WHERE receipt_no = 3;
DELETE 1
Report> UPDATE control SET deposit_no = 1;
UPDATE 1
Cashier> BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
Cashier> SELECT deposit_no FROM control;
deposit_no
------------
1
(1 row)
Cashier> INSERT INTO receipts VALUES (3, 1, 400);
INSERT 0 1
Closer> BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
Closer> UPDATE control SET deposit_no = 2;
UPDATE 1
Closer> COMMIT;
COMMIT
Report> BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
Report> SELECT deposit_no FROM control;
deposit_no
------------
2
(1 row)
Report> SELECT receipt_no, amount FROM receipts WHERE deposit_no = 1 ORDER BY receipt_no;
receipt_no | amount
------------+--------
1 | 100
2 | 200
(2 rows)
Report> COMMIT; -- the report itself commits fine —
COMMIT
```
*— because SERIALIZABLE protects it by aborting the transaction that would invalidate it: the cashier.*
```transcript
Cashier> COMMIT;
ERROR: 40001: could not serialize access due to read/write dependencies among transactions
Report> SELECT receipt_no, amount FROM receipts WHERE deposit_no = 1 ORDER BY receipt_no; -- batch 1 still matches the published report; the cashier retries into batch 2
receipt_no | amount
------------+--------
1 | 100
2 | 200
(2 rows)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/02-isolation/read-only-anomaly.yaml)
## postgres/02-isolation/read-skew.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: At READ COMMITTED, a transaction reading two rows around a concurrent transfer observes money that never existed (Adya's G-single, "read skew"); REPEATABLE READ's single snapshot makes the same interleaving consistent.
```timeline
A: SELECT alice → 50 (before the transfer)
B: UPDATE alice −25
B: UPDATE bob +25
B: COMMIT ← transfer done, invariant intact
A: SELECT bob → 75 ← 50+75=125, money that never existed
```
*The invariant: alice + bob = 100 at every moment. A is an auditor summing the accounts row by row; B transfers 25 between them mid-audit.*
```transcript
A> BEGIN;
BEGIN
A> SELECT balance FROM accounts WHERE owner = 'alice';
balance
---------
50
(1 row)
B> BEGIN;
BEGIN
B> UPDATE accounts SET balance = balance - 25 WHERE owner = 'alice';
UPDATE 1
B> UPDATE accounts SET balance = balance + 25 WHERE owner = 'bob';
UPDATE 1
B> COMMIT; -- a perfectly correct transfer — atomic, invariant preserved
COMMIT
A> SELECT balance FROM accounts WHERE owner = 'bob'; -- 50 + 75 = 125. The auditor found 25 that never existed.
balance
---------
75
(1 row)
A> COMMIT;
COMMIT
```
*Neither row was ever wrong — A read alice BEFORE the transfer and bob AFTER it. Fresh-snapshot-per-statement cannot hold two rows still. REPEATABLE READ can:*
```transcript
A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
A> SELECT balance FROM accounts WHERE owner = 'alice';
balance
---------
25
(1 row)
B> BEGIN;
BEGIN
B> UPDATE accounts SET balance = balance - 10 WHERE owner = 'alice';
UPDATE 1
B> UPDATE accounts SET balance = balance + 10 WHERE owner = 'bob';
UPDATE 1
B> COMMIT;
COMMIT
A> SELECT balance FROM accounts WHERE owner = 'bob'; -- 25 + 75 = 100 — one snapshot, one moment in time
balance
---------
75
(1 row)
A> COMMIT;
COMMIT
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/02-isolation/read-skew.yaml)
## postgres/02-isolation/stable-snapshot.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: At REPEATABLE READ, PostgreSQL uses a single snapshot for the entire transaction — no non-repeatable reads AND no phantoms (stronger than the SQL standard requires).
```timeline
A: SELECT → alice 100, bob 50 (snapshot frozen here)
B: UPDATE alice = 999 (committed)
B: INSERT carol (committed)
A: SELECT → 100, 50 ← no 999, no carol
A: COMMIT
A: SELECT → 999 and carol ← new txn, new snapshot
```
```transcript
A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
```
*The snapshot is taken by the FIRST query, not by BEGIN.*
```transcript
A> SELECT id, balance FROM accounts ORDER BY id;
id | balance
----+---------
1 | 100
2 | 50
(2 rows)
```
*B changes an existing row AND inserts a new one — both committed instantly.*
```transcript
B> UPDATE accounts SET balance = 999 WHERE id = 1;
UPDATE 1
B> INSERT INTO accounts VALUES (3, 'carol', 300);
INSERT 0 1
A> SELECT id, balance FROM accounts ORDER BY id; -- not 999 — no non-repeatable read; and no carol — no phantom
id | balance
----+---------
1 | 100
2 | 50
(2 rows)
A> COMMIT;
COMMIT
```
*Only a NEW transaction gets a new snapshot.*
```transcript
A> SELECT id, balance FROM accounts ORDER BY id;
id | balance
----+---------
1 | 999
2 | 50
3 | 300
(3 rows)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/02-isolation/stable-snapshot.yaml)
## postgres/02-isolation/update-recheck.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: An UPDATE at READ COMMITTED that waited for a lock re-evaluates its WHERE clause against the new row version — rows that no longer match are silently skipped.
```timeline
A: UPDATE id=1 value 10→20 (uncommitted)
B: UPDATE WHERE value=10 → ⏳ waits
A: COMMIT ← row 1 is now 20
B: ⏵ UPDATE WHERE value=10 → completes
B: SELECT → 20, 30 ← B re-checked WHERE, matched 0 rows
```
```transcript
A> BEGIN;
BEGIN
A> UPDATE items SET value = value * 2 WHERE id = 1; -- row 1: 10 → 20, uncommitted
UPDATE 1
```
*B targets WHERE value = 10. In B's snapshot row 1 still qualifies — but it's locked by A, so B waits.*
```transcript
B> UPDATE items SET value = 99 WHERE value = 10;
⏳ B is waiting for a lock…
```
*A commits. B wakes up and re-checks the row it waited for — against the NEW version, where value is 20.*
```transcript
A> COMMIT;
COMMIT
⏵ B resumes:
UPDATE 0
```
*UPDATE 0 — the row slipped away.*
```transcript
B> SELECT id, value FROM items ORDER BY id;
id | value
----+-------
1 | 20
2 | 30
(2 rows)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/02-isolation/update-recheck.yaml)
## postgres/02-isolation/write-skew-rr.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: Two REPEATABLE READ transactions can each validate an invariant against their snapshots, write to different rows, and both commit — leaving the invariant broken. This is write skew.
```timeline
A: SELECT on_call → 2 ← "safe for me to leave"
B: SELECT on_call → 2 ← "safe for me to leave"
A: UPDATE alice off-call
B: UPDATE bob off-call (a different row)
A: COMMIT
B: COMMIT ← both succeed, no conflict to detect
A: SELECT on_call → 0 ← invariant broken
```
*Hospital rule: at least one doctor must stay on call. Alice and Bob both want the night off.*
```transcript
A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
B> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
A> SELECT count(*)::int AS on_call FROM doctors WHERE on_call; -- "two of us — safe for me to leave"
on_call
---------
2
(1 row)
B> SELECT count(*)::int AS on_call FROM doctors WHERE on_call; -- "two of us — safe for me to leave"
on_call
---------
2
(1 row)
```
*Each updates a DIFFERENT row, so there is no write-write conflict to detect.*
```transcript
A> UPDATE doctors SET on_call = false WHERE name = 'alice';
UPDATE 1
B> UPDATE doctors SET on_call = false WHERE name = 'bob';
UPDATE 1
A> COMMIT;
COMMIT
B> COMMIT; -- both succeed!
COMMIT
A> SELECT count(*)::int AS on_call FROM doctors WHERE on_call; -- nobody is on call — the invariant is broken
on_call
---------
0
(1 row)
```
*Each transaction was internally consistent; together they broke the rule. Only SERIALIZABLE catches this.*
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/02-isolation/write-skew-rr.yaml)
## postgres/02-isolation/write-skew-serializable.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: The exact interleaving that breaks the invariant under REPEATABLE READ fails with SQLSTATE 40001 under SERIALIZABLE — one transaction commits, the other must retry.
```timeline
A: on-call count → 2
B: on-call count → 2
A: alice off call
B: bob off call
A: COMMIT — first committer wins
B: COMMIT ← 40001 could not serialize access due to read/write dependencies among transactions
```
*Same story, same statements, same order — only the isolation level differs.*
```transcript
A> BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
B> BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
A> SELECT count(*)::int AS on_call FROM doctors WHERE on_call;
on_call
---------
2
(1 row)
B> SELECT count(*)::int AS on_call FROM doctors WHERE on_call;
on_call
---------
2
(1 row)
A> UPDATE doctors SET on_call = false WHERE name = 'alice';
UPDATE 1
B> UPDATE doctors SET on_call = false WHERE name = 'bob';
UPDATE 1
```
*The first committer wins. The second cannot be serialized against it and is aborted.*
```transcript
A> COMMIT;
COMMIT
B> COMMIT; -- serialization_failure
ERROR: 40001: could not serialize access due to read/write dependencies among transactions
A> SELECT count(*)::int AS on_call FROM doctors WHERE on_call; -- the invariant survived
on_call
---------
1
(1 row)
```
*B's job is to retry. On retry it would see only one doctor on call — and refuse the night off.*
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/02-isolation/write-skew-serializable.yaml)
## postgres/03-locking/alter-table-outage.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: An ALTER TABLE queued behind one long transaction blocks every later query on that table — even plain SELECTs — because they must queue behind its ACCESS EXCLUSIVE request.
```timeline
A: SELECT (a long-lived transaction)
B: ALTER TABLE … ADD COLUMN → ⏳ waits
C: SELECT — just a read! → ⏳ waits
A: COMMIT
B: ⏵ ALTER TABLE … ADD COLUMN → completes
C: ⏵ SELECT — just a read! → completes
```
*A is any long-lived transaction that has touched the table — a report, a stuck job…*
```transcript
A> BEGIN;
BEGIN
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
```
*The migration needs ACCESS EXCLUSIVE, so it waits for A. Expected. But now —*
```transcript
B> ALTER TABLE accounts ADD COLUMN note text;
⏳ B is waiting for a lock…
```
*— every new query on the table queues behind the *waiting* ALTER. This is the outage.*
```transcript
C> SELECT balance FROM accounts WHERE id = 1;
⏳ C is waiting for a lock…
M> SELECT waiter.application_name AS waiter, blocker.application_name AS blocker
FROM pg_stat_activity waiter
JOIN pg_stat_activity blocker ON blocker.pid = ANY (pg_blocking_pids(waiter.pid))
WHERE waiter.wait_event_type = 'Lock'
ORDER BY waiter.application_name, blocker.application_name;
waiter | blocker
--------+---------
B | A
C | B
(2 rows)
```
*Only when A ends does the pile-up drain — migration first, then the reads.*
```transcript
A> COMMIT;
COMMIT
⏵ B resumes:
ALTER TABLE
⏵ C resumes:
balance
---------
100
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/03-locking/alter-table-outage.yaml)
## postgres/03-locking/ddl-lock-timeout.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: With lock_timeout set, a migration that can't get its lock fails fast (55P03) instead of queueing — and other sessions' queries are never blocked behind it.
```timeline
A: SELECT (long-lived txn) → holds ACCESS SHARE
B: ALTER TABLE … → gives up after 100ms ← 55P03 canceling statement due to lock timeout
C: SELECT → instant, nothing queued behind the ALTER
A: COMMIT
B: ALTER TABLE … (retry sails through)
```
```transcript
A> BEGIN;
BEGIN
A> SELECT balance FROM accounts WHERE id = 1; -- the same long transaction as before
balance
---------
100
(1 row)
```
*Same migration — but this time it gives up after 100ms instead of camping in the queue.*
```transcript
B> SET lock_timeout = '100ms';
SET
B> ALTER TABLE accounts ADD COLUMN note text;
ERROR: 55P03: canceling statement due to lock timeout
```
*No waiting ALTER in the queue means no outage: C's read is instant.*
```transcript
C> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
A> COMMIT;
COMMIT
```
*Retry the migration when it can actually get the lock — now it sails through.*
```transcript
B> ALTER TABLE accounts ADD COLUMN note text;
ALTER TABLE
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/03-locking/ddl-lock-timeout.yaml)
## postgres/03-locking/deadlock-avoidance.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: The same two opposite-direction transfers cannot deadlock if both transactions lock the rows in the same (id) order first — the second simply waits its turn.
```timeline
A: SELECT id IN (1,2) … FOR UPDATE → locks both, in id order
B: SELECT id IN (1,2) … FOR UPDATE → ⏳ waits
A: COMMIT → releases both
B: ⏵ SELECT id IN (1,2) … FOR UPDATE → completes
```
*Both transfers grab *all* their row locks up front, ordered by id.*
```transcript
A> BEGIN;
BEGIN
A> SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
id
----
1
2
(2 rows)
B> BEGIN;
BEGIN
B> SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
⏳ B is waiting for a lock…
```
*No cycle is possible: B parks at the first row and holds nothing A needs.*
```transcript
A> UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE 1
A> UPDATE accounts SET balance = balance + 10 WHERE id = 2;
UPDATE 1
A> COMMIT;
COMMIT
⏵ B resumes:
id
----
1
2
(2 rows)
B> UPDATE accounts SET balance = balance - 25 WHERE id = 2;
UPDATE 1
B> UPDATE accounts SET balance = balance + 25 WHERE id = 1;
UPDATE 1
B> COMMIT;
COMMIT
A> SELECT owner, balance FROM accounts ORDER BY id; -- both transfers landed — same workload, zero deadlocks
owner | balance
-------+---------
alice | 115
bob | 85
(2 rows)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/03-locking/deadlock-avoidance.yaml)
## postgres/03-locking/deadlock.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: Two transactions locking the same rows in opposite order deadlock; PostgreSQL detects the cycle and aborts one of them with SQLSTATE 40P01 so the other can finish.
```timeline
A: locks alice (id=1)
B: locks bob (id=2)
A: needs bob (id=2) → ⏳ waits
B: needs alice (id=1) ← 40P01 deadlock detected
A: ⏵ needs bob (id=2) → completes
```
```transcript
A> SET deadlock_timeout = '10s'; -- In production the victim is effectively arbitrary — whichever waiter's deadlock_timeout (default 1s) fires first runs the check and aborts itself. We pin it here so the transcript is reproducible: B checks first.
SET
B> SET deadlock_timeout = '50ms';
SET
```
*A transfers 10 from alice to bob; B transfers 25 from bob to alice.*
```transcript
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- A locks alice
UPDATE 1
B> BEGIN;
BEGIN
B> UPDATE accounts SET balance = balance - 25 WHERE id = 2; -- B locks bob
UPDATE 1
```
*A now needs bob's row (B has it) — it waits.*
```transcript
A> UPDATE accounts SET balance = balance + 10 WHERE id = 2;
⏳ A is waiting for a lock…
```
*B now needs alice's row (A has it). A waits for B, B waits for A: a cycle.*
```transcript
B> UPDATE accounts SET balance = balance + 25 WHERE id = 1; -- deadlock_detected — B is aborted…
ERROR: 40P01: deadlock detected
```
*The victim's abort frees bob's row, so A's stuck UPDATE completes.*
```transcript
⏵ A resumes:
UPDATE 1
A> COMMIT;
COMMIT
B> ROLLBACK;
ROLLBACK
A> SELECT owner, balance FROM accounts ORDER BY id; -- A's transfer survived; B's evaporated — retry it
owner | balance
-------+---------
alice | 90
bob | 110
(2 rows)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/03-locking/deadlock.yaml)
## postgres/03-locking/fk-key-share.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: INSERTing a child row locks the referenced parent row with FOR KEY SHARE: updating the parent's other columns still works, but deleting it blocks — and then fails the moment the child commits.
```timeline
A: INSERT child → FK locks parent FOR KEY SHARE
B: UPDATE parent balance → allowed (non-key)
B: DELETE parent → needs FOR UPDATE → ⏳ waits
A: COMMIT
B: ⏵ DELETE parent → needs FOR UPDATE ← 23503 update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
```
```transcript
A> BEGIN;
BEGIN
A> INSERT INTO orders VALUES (1, 1); -- FK check locks customers row 1 FOR KEY SHARE
INSERT 0 1
```
*FOR KEY SHARE doesn't mind non-key updates — B can change the balance.*
```transcript
B> UPDATE customers SET balance = 50 WHERE id = 1;
UPDATE 1
```
*But DELETE needs the strongest row lock (FOR UPDATE) — B has to wait.*
```transcript
B> DELETE FROM customers WHERE id = 1;
⏳ B is waiting for a lock…
A> COMMIT;
COMMIT
```
*A's order is now committed, so B's DELETE resumes — straight into the FK.*
```transcript
⏵ B's blocked statement fails:
ERROR: 23503: update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
B> SELECT balance FROM customers WHERE id = 1; -- the parent survived, with B's earlier non-key update intact
balance
---------
50
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/03-locking/fk-key-share.yaml)
## postgres/03-locking/for-update-blocks.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: A row locked with SELECT FOR UPDATE can still be read by everyone, but any write to it waits until the locking transaction ends.
```timeline
A: SELECT … FOR UPDATE (locks the row)
B: SELECT balance → 100 (readers don't block)
B: UPDATE -10 → ⏳ waits
A: COMMIT (releases the lock)
B: ⏵ UPDATE -10 → completes
```
```transcript
A> BEGIN;
BEGIN
A> SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
id | owner | balance
----+-------+---------
1 | alice | 100
(1 row)
```
*Reading the locked row costs B nothing — MVCC readers don't take row locks.*
```transcript
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
```
*Writing to it is another story: B's UPDATE must wait for A.*
```transcript
B> UPDATE accounts SET balance = balance - 10 WHERE id = 1;
⏳ B is waiting for a lock…
A> UPDATE accounts SET balance = 150 WHERE id = 1;
UPDATE 1
A> COMMIT; -- releases the row lock
COMMIT
⏵ B resumes:
UPDATE 1
B> SELECT balance FROM accounts WHERE id = 1; -- B's -10 applied on top of A's committed 150
balance
---------
140
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/03-locking/for-update-blocks.yaml)
## postgres/03-locking/lock-mode-matrix.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: FOR KEY SHARE < FOR SHARE < FOR NO KEY UPDATE < FOR UPDATE: two share-mode locks coexist, but a share lock still stops writers, and FOR UPDATE stops everything.
```timeline
A: SELECT … FOR SHARE (A)
B: SELECT … FOR SHARE (B) → coexists
B: UPDATE → FOR NO KEY UPDATE → ⏳ waits
A: COMMIT (releases A's FOR SHARE)
B: ⏵ UPDATE → FOR NO KEY UPDATE → completes
A: UPDATE → FOR NO KEY UPDATE (A)
B: SELECT … FOR KEY SHARE → coexists with the UPDATE
B: SELECT … FOR UPDATE → ⏳ waits
A: COMMIT (releases A)
B: ⏵ SELECT … FOR UPDATE → completes
```
*Two FOR SHARE locks on the same row coexist happily…*
```transcript
A> BEGIN;
BEGIN
A> SELECT id FROM accounts WHERE id = 1 FOR SHARE;
id
----
1
(1 row)
B> BEGIN;
BEGIN
B> SELECT id FROM accounts WHERE id = 1 FOR SHARE;
id
----
1
(1 row)
B> COMMIT;
COMMIT
```
*…but FOR SHARE still blocks a plain UPDATE (which takes FOR NO KEY UPDATE).*
```transcript
B> UPDATE accounts SET balance = 200 WHERE id = 1;
⏳ B is waiting for a lock…
A> COMMIT;
COMMIT
⏵ B resumes:
UPDATE 1
```
*The weakest lock, FOR KEY SHARE, even coexists with a running UPDATE…*
```transcript
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 300 WHERE id = 1;
UPDATE 1
B> SELECT id FROM accounts WHERE id = 1 FOR KEY SHARE;
id
----
1
(1 row)
```
*…while the strongest, FOR UPDATE, has to wait for it.*
```transcript
B> SELECT id FROM accounts WHERE id = 1 FOR UPDATE;
⏳ B is waiting for a lock…
A> COMMIT;
COMMIT
⏵ B resumes:
id
----
1
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/03-locking/lock-mode-matrix.yaml)
## postgres/03-locking/lock-queue-fifo.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: Sessions waiting for the same row lock queue up: when the holder commits, the lock goes to the first waiter, and everyone else keeps waiting behind it.
```timeline
A: UPDATE +1 (takes the row lock)
B: UPDATE +10 → ⏳ waits
C: UPDATE +100 → ⏳ waits
M: who's waiting? → B and C
A: COMMIT → lock passes to B
B: ⏵ UPDATE +10 → completes
B: COMMIT → lock passes to C
C: ⏵ UPDATE +100 → completes
```
```transcript
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = balance + 1 WHERE id = 1;
UPDATE 1
B> BEGIN;
BEGIN
B> UPDATE accounts SET balance = balance + 10 WHERE id = 1;
⏳ B is waiting for a lock…
C> UPDATE accounts SET balance = balance + 100 WHERE id = 1;
⏳ C is waiting for a lock…
```
*A fourth session, M, can watch the pile-up in pg_stat_activity.*
```transcript
M> SELECT application_name AS waiting, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY application_name;
waiting | blocked_by
---------+------------
B | {pid(A)}
C | {pid(B)}
(2 rows)
```
*A commits. The lock goes to B — the head of the queue — not to C.*
```transcript
A> COMMIT;
COMMIT
⏵ B resumes:
UPDATE 1
M> SELECT application_name AS waiting, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY application_name
-- C is still in line, now behind B
waiting | blocked_by
---------+------------
C | {pid(B)}
(1 row)
B> COMMIT;
COMMIT
⏵ C resumes:
UPDATE 1
C> SELECT balance FROM accounts WHERE id = 1; -- 100 + 1 + 10 + 100 — every update landed, in queue order
balance
---------
211
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/03-locking/lock-queue-fifo.yaml)
## postgres/03-locking/lock-timeout.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: With lock_timeout set, a statement waits for a lock only that long, then fails with SQLSTATE 55P03 — the same code NOWAIT raises immediately.
```timeline
A: UPDATE 200 (locks row 1)
B: UPDATE 300 → gives up after 100ms ← 55P03 canceling statement due to lock timeout
A: COMMIT
B: UPDATE 300 (retry succeeds)
```
```transcript
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 200 WHERE id = 1;
UPDATE 1
B> SET lock_timeout = '100ms';
SET
```
*B queues for the row lock like anyone else — but gives up after 100ms.*
```transcript
B> UPDATE accounts SET balance = 300 WHERE id = 1; -- lock_not_available, raised after the timeout
ERROR: 55P03: canceling statement due to lock timeout
```
*The failure canceled only B's statement — a retry after A commits works.*
```transcript
A> COMMIT;
COMMIT
B> UPDATE accounts SET balance = 300 WHERE id = 1;
UPDATE 1
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
300
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/03-locking/lock-timeout.yaml)
## postgres/03-locking/monitoring-locks.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: pg_locks shows every lock a transaction holds — a single-row UPDATE takes four — and a waiter shows up as granted = false, findable via pg_blocking_pids().
```timeline
A: UPDATE (takes the row lock)
B: UPDATE → waits on A → ⏳ waits
A: COMMIT
B: ⏵ UPDATE → waits on A → completes
```
```transcript
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 200 WHERE id = 1;
UPDATE 1
```
*One innocent UPDATE = four locks: table, index, its own xid, its own virtual xid.*
```transcript
M> SELECT l.locktype, l.relation::regclass AS relation, l.mode, l.granted
FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
WHERE a.application_name = 'A'
ORDER BY l.locktype, l.relation::regclass::text, l.mode;
locktype | relation | mode | granted
---------------+---------------+------------------+---------
relation | accounts | RowExclusiveLock | t
relation | accounts_pkey | RowExclusiveLock | t
transactionid | | ExclusiveLock | t
virtualxid | | ExclusiveLock | t
(4 rows)
B> UPDATE accounts SET balance = 300 WHERE id = 1;
⏳ B is waiting for a lock…
```
*The waiter's tell: a lock row with granted = f — it wants A's transaction id.*
```transcript
M> SELECT l.locktype, l.mode, l.granted
FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
WHERE a.application_name = 'B' AND NOT l.granted;
locktype | mode | granted
---------------+-----------+---------
transactionid | ShareLock | f
(1 row)
```
*You rarely need to decode pg_locks by hand — pg_blocking_pids() names the culprit.*
```transcript
M> SELECT waiter.application_name AS waiter, blocker.application_name AS blocker
FROM pg_stat_activity waiter
JOIN pg_stat_activity blocker ON blocker.pid = ANY (pg_blocking_pids(waiter.pid))
WHERE waiter.wait_event_type = 'Lock'
ORDER BY waiter.application_name, blocker.application_name;
waiter | blocker
--------+---------
B | A
(1 row)
A> COMMIT;
COMMIT
⏵ B resumes:
UPDATE 1
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/03-locking/monitoring-locks.yaml)
## postgres/03-locking/nowait.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: SELECT ... FOR UPDATE NOWAIT refuses to wait: if the row is locked it fails immediately with SQLSTATE 55P03 instead of joining the lock queue.
```timeline
A: SELECT … FOR UPDATE (locks row 1)
B: … FOR UPDATE NOWAIT ← 55P03 could not obtain lock on row in relation "accounts"
A: COMMIT (releases the lock)
B: … NOWAIT → row 1
```
```transcript
A> BEGIN;
BEGIN
A> SELECT id FROM accounts WHERE id = 1 FOR UPDATE;
id
----
1
(1 row)
B> SELECT id FROM accounts WHERE id = 1 FOR UPDATE NOWAIT; -- lock_not_available — instantly, no waiting
ERROR: 55P03: could not obtain lock on row in relation "accounts"
```
*Once A is done, the same statement succeeds.*
```transcript
A> COMMIT;
COMMIT
B> SELECT id FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
id
----
1
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/03-locking/nowait.yaml)
## postgres/03-locking/skip-locked.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: SELECT ... FOR UPDATE SKIP LOCKED silently skips locked rows, so concurrent workers each grab a different row without ever waiting — the backbone of Postgres job queues.
```timeline
A: SKIP LOCKED → job 1
B: SKIP LOCKED → job 2 (skips 1)
C: SKIP LOCKED → job 3 (skips 1, 2)
D: SKIP LOCKED → nothing free
A: ROLLBACK → job 1 back on the queue
D: SKIP LOCKED → job 1
```
*Four workers run the exact same query at the same time.*
```transcript
A> BEGIN;
BEGIN
A> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+------------
1 | send email
(1 row)
B> BEGIN;
BEGIN
B> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; -- job 1 is locked by A — skipped, no waiting
id | task
----+--------------
2 | resize image
(1 row)
C> BEGIN;
BEGIN
C> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+--------------
3 | build report
(1 row)
```
*Worker D finds the queue empty — an instant answer, not a wait.*
```transcript
D> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
(0 rows)
```
*A worker crash (rollback) puts its job straight back on the queue.*
```transcript
A> ROLLBACK;
ROLLBACK
D> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+------------
1 | send email
(1 row)
B> COMMIT;
COMMIT
C> COMMIT;
COMMIT
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/03-locking/skip-locked.yaml)
## postgres/04-mvcc/dead-tuples-and-bloat.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: Every UPDATE leaves a dead tuple behind and every DELETE leaves the row on disk — so a table's file only ever grows, no matter how much data you delete.
```transcript
A> INSERT INTO counters VALUES (1, 0) RETURNING xmin, n;
xmin | n
------+---
1001 | 0
(1 row)
```
*Three updates to one row. Watch each one get a fresh xid — and leave a corpse.*
```transcript
A> UPDATE counters SET n = 1 WHERE id = 1 RETURNING xmin, n;
xmin | n
------+---
1002 | 1
(1 row)
A> UPDATE counters SET n = 2 WHERE id = 1 RETURNING xmin, n;
xmin | n
------+---
1003 | 2
(1 row)
A> UPDATE counters SET n = 3 WHERE id = 1 RETURNING xmin, n;
xmin | n
------+---
1004 | 3
(1 row)
```
*SELECT sees one row. The page holds four tuples — a version chain, three of them dead.*
```transcript
A> SELECT n FROM counters;
n
---
3
(1 row)
A> SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('counters', 0)) ORDER BY lp;
lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
1 | 1001 | 1002 | (0,2)
2 | 1002 | 1003 | (0,3)
3 | 1003 | 1004 | (0,4)
4 | 1004 | 0 | (0,4)
(4 rows)
```
*The same effect at scale: 1000 rows fit in 5 pages of 8 kB.*
```transcript
A> INSERT INTO bloat SELECT g, 'x' FROM generate_series(1, 1000) g;
INSERT 0 1000
A> SELECT (pg_relation_size('bloat') / 8192)::int AS pages;
pages
-------
5
(1 row)
```
*One UPDATE of every row = a full second copy of the table.*
```transcript
A> UPDATE bloat SET filler = 'y';
UPDATE 1000
A> SELECT (pg_relation_size('bloat') / 8192)::int AS pages;
pages
-------
9
(1 row)
```
*Now delete everything. Zero rows — and not a single byte returned.*
```transcript
A> DELETE FROM bloat;
DELETE 1000
A> SELECT count(*)::int AS live_rows FROM bloat;
live_rows
-----------
0
(1 row)
A> SELECT (pg_relation_size('bloat') / 8192)::int AS pages;
pages
-------
9
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/04-mvcc/dead-tuples-and-bloat.yaml)
## postgres/04-mvcc/long-transactions.ts
- engine: postgres (PostgreSQL 18.4)
- claim: VACUUM can only remove tuples no snapshot can still see — one long-running transaction holds the horizon back for every table, and VACUUM silently reclaims nothing until it ends.
*A opens a long report transaction — one query, then it sits there idle.*
```transcript
A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
A> SELECT xmin, id, status FROM jobs;
xmin | id | status
------+----+--------
1001 | 1 | new
(1 row)
```
*Meanwhile B churns through the row, leaving dead versions behind.*
```transcript
B> UPDATE jobs SET status = 'running' WHERE id = 1 RETURNING xmin, status;
xmin | status
------+---------
1002 | running
(1 row)
B> UPDATE jobs SET status = 'done' WHERE id = 1 RETURNING xmin, status;
xmin | status
------+--------
1003 | done
(1 row)
B> UPDATE jobs SET status = 'archived' WHERE id = 1 RETURNING xmin, status;
xmin | status
------+----------
1004 | archived
(1 row)
B> SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('jobs', 0)) ORDER BY lp;
lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
1 | 1001 | 1002 | (0,2)
2 | 1002 | 1003 | (0,3)
3 | 1003 | 1004 | (0,4)
4 | 1004 | 0 | (0,4)
(4 rows)
B> VACUUM jobs;
VACUUM
```
*VACUUM ran, reported success — and removed nothing. A's snapshot might still need every one of those versions.*
```transcript
B> SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('jobs', 0)) ORDER BY lp;
lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
1 | 1001 | 1002 | (0,2)
2 | 1002 | 1003 | (0,3)
3 | 1003 | 1004 | (0,4)
4 | 1004 | 0 | (0,4)
(4 rows)
```
*And indeed: A still reads the version from before all three updates.*
```transcript
A> SELECT id, status FROM jobs;
id | status
----+--------
1 | new
(1 row)
A> COMMIT;
COMMIT
B> VACUUM jobs;
VACUUM
```
*Same command, a moment after A commits — now the three dead versions are gone.*
```transcript
B> SELECT lp, lp_flags, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('jobs', 0)) ORDER BY lp;
lp | lp_flags | t_xmin | t_xmax | t_ctid
----+----------+--------+--------+--------
1 | 2 | | |
2 | 0 | | |
3 | 0 | | |
4 | 1 | 1004 | 0 | (0,4)
(4 rows)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/04-mvcc/long-transactions.ts)
## postgres/04-mvcc/row-versions.ts
- engine: postgres (PostgreSQL 18.4)
- claim: UPDATE never modifies a row in place — it writes a new version stamped with its xid (xmin) and stamps the old one's xmax; DELETE only stamps xmax. Both versions stay on disk, visible with pageinspect.
*Every row carries hidden system columns: xmin = the transaction that created this version, xmax = the one that deleted or replaced it (0 = nobody yet), ctid = its physical address (page, slot).*
```transcript
A> SELECT xmin, xmax, ctid, balance FROM accounts WHERE id = 1;
xmin | xmax | ctid | balance
------+------+-------+---------
1001 | 0 | (0,1) | 100
(1 row)
B> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
B> SELECT xmin, xmax, ctid, balance FROM accounts WHERE id = 1;
xmin | xmax | ctid | balance
------+------+-------+---------
1001 | 0 | (0,1) | 100
(1 row)
```
*A's UPDATE doesn't touch that version — it writes a brand-new one at a new ctid.*
```transcript
A> UPDATE accounts SET balance = 200 WHERE id = 1
RETURNING xmin, xmax, ctid, balance;
xmin | xmax | ctid | balance
------+------+-------+---------
1002 | 0 | (0,2) | 200
(1 row)
```
*B still reads the old version — but its xmax is no longer 0: A's xid is stamped on it.*
```transcript
B> SELECT xmin, xmax, ctid, balance FROM accounts WHERE id = 1;
xmin | xmax | ctid | balance
------+------+-------+---------
1001 | 1002 | (0,1) | 100
(1 row)
B> COMMIT;
COMMIT
B> SELECT xmin, xmax, ctid, balance FROM accounts WHERE id = 1;
xmin | xmax | ctid | balance
------+------+-------+---------
1002 | 0 | (0,2) | 200
(1 row)
```
*pageinspect shows both versions physically on page 0 — the old one points at its successor.*
```transcript
A> SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('accounts', 0)) ORDER BY lp;
lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
1 | 1001 | 1002 | (0,2)
2 | 1002 | 0 | (0,2)
(2 rows)
```
*DELETE doesn't erase anything either — it only stamps xmax on the current version.*
```transcript
A> DELETE FROM accounts WHERE id = 1 RETURNING xmin, xmax, ctid;
xmin | xmax | ctid
------+------+-------
1002 | 1003 | (0,2)
(1 row)
A> SELECT count(*)::int AS live_rows FROM accounts;
live_rows
-----------
0
(1 row)
```
*Zero rows for SELECT — yet both versions are still on disk, awaiting VACUUM.*
```transcript
A> SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('accounts', 0)) ORDER BY lp;
lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
1 | 1001 | 1002 | (0,2)
2 | 1002 | 1003 | (0,2)
(2 rows)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/04-mvcc/row-versions.ts)
## postgres/04-mvcc/snapshots-under-the-hood.ts
- engine: postgres (PostgreSQL 18.4)
- claim: A snapshot is three numbers — xmin, xmax, and the in-progress list — and visibility is pure arithmetic on them: committed before xmax and not in xip = visible. Commit order doesn't matter; the snapshot already decided.
*Transaction ids are handed out lazily — a transaction that only reads never gets one.*
```transcript
A> BEGIN;
BEGIN
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
A> SELECT pg_current_xact_id_if_assigned() AS xid;
xid
-----
(1 row)
A> UPDATE accounts SET balance = 150 WHERE id = 1;
UPDATE 1
A> SELECT pg_current_xact_id_if_assigned() AS xid;
xid
------
1001
(1 row)
```
*B grabs the next xid and commits immediately. A is still in progress.*
```transcript
B> UPDATE accounts SET balance = 200 WHERE id = 2 RETURNING xmin, balance;
xmin | balance
------+---------
1002 | 200
(1 row)
```
*C opens a transaction and inspects its own snapshot: the three numbers that decide all visibility.*
```transcript
C> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
C> SELECT pg_snapshot_xmin(pg_current_snapshot()) AS xmin,
pg_snapshot_xmax(pg_current_snapshot()) AS xmax;
xmin | xmax
------+------
1001 | 1003
(1 row)
```
*xip = the xids that were in progress at snapshot time. A is on the list — so A is invisible even after it commits.*
```transcript
C> SELECT pg_snapshot_xip(pg_current_snapshot()) AS xid;
xid
------
1001
(1 row)
```
*The arithmetic in action: B (committed, below xmax) is visible; A (in xip) is not.*
```transcript
C> SELECT id, owner, balance FROM accounts ORDER BY id;
id | owner | balance
----+-------+---------
1 | alice | 100
2 | bob | 200
(2 rows)
A> COMMIT;
COMMIT
```
*A has now committed — but C's snapshot already decided: still invisible.*
```transcript
C> SELECT id, owner, balance FROM accounts ORDER BY id;
id | owner | balance
----+-------+---------
1 | alice | 100
2 | bob | 200
(2 rows)
C> COMMIT;
COMMIT
```
*Only a NEW snapshot changes the verdict.*
```transcript
C> SELECT id, owner, balance FROM accounts ORDER BY id;
id | owner | balance
----+-------+---------
1 | alice | 150
2 | bob | 200
(2 rows)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/04-mvcc/snapshots-under-the-hood.ts)
## postgres/04-mvcc/vacuum.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: VACUUM turns dead tuples into reusable free space inside the table's file — new rows land in the freed slots — but it does not shrink the file; only VACUUM FULL rewrites the table to its minimal size.
*Three updates leave three dead tuples on the page (the bloat lesson showed the chain).*
```transcript
A> UPDATE counters SET n = 1 WHERE id = 1;
UPDATE 1
A> UPDATE counters SET n = 2 WHERE id = 1;
UPDATE 1
A> UPDATE counters SET n = 3 WHERE id = 1 RETURNING xmin, n;
xmin | n
------+---
1001 | 3
(1 row)
A> VACUUM counters;
VACUUM
```
*After VACUUM: slot 1 is a redirect to the live version, slots 2–3 are unused (reusable), only slot 4 still holds a tuple. The corpses are gone.*
```transcript
A> SELECT lp, lp_flags, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('counters', 0)) ORDER BY lp;
lp | lp_flags | t_xmin | t_xmax | t_ctid
----+----------+--------+--------+--------
1 | 2 | | |
2 | 0 | | |
3 | 0 | | |
4 | 1 | 1001 | 0 | (0,4)
(4 rows)
```
*The freed space is immediately reusable: the next INSERT lands in slot 2.*
```transcript
A> INSERT INTO counters VALUES (2, 0) RETURNING ctid, id;
ctid | id
-------+----
(0,2) | 2
(1 row)
```
*Same story at file level — 1000 rows in 5 pages, doubled to 9 by an UPDATE of every row.*
```transcript
A> SELECT (pg_relation_size('bloat') / 8192)::int AS pages;
pages
-------
5
(1 row)
A> UPDATE bloat SET filler = 'y';
UPDATE 1000
A> SELECT (pg_relation_size('bloat') / 8192)::int AS pages;
pages
-------
9
(1 row)
A> VACUUM bloat;
VACUUM
```
*VACUUM cleaned 1000 dead tuples — and the file is still 9 pages. The space is free *inside* the file.*
```transcript
A> SELECT (pg_relation_size('bloat') / 8192)::int AS pages;
pages
-------
9
(1 row)
A> VACUUM FULL bloat;
VACUUM
```
*VACUUM FULL rewrites the table from scratch — back to 5 pages. The price: it holds ACCESS EXCLUSIVE for the whole rewrite.*
```transcript
A> SELECT (pg_relation_size('bloat') / 8192)::int AS pages;
pages
-------
5
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/04-mvcc/vacuum.yaml)
## postgres/05-patterns/advisory-locks.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: Advisory locks serialize application-defined work with no table involved: try-lock answers instantly, session-level locks survive COMMIT and need an explicit unlock, transaction-level locks vanish at COMMIT.
```timeline
A: lock(42) → held
B: try-lock(42) → false (no wait)
B: lock(42) → ⏳ waits
A: unlock(42)
B: ⏵ lock(42) → completes
```
*Two deploy runners must not migrate the same database at once. They agree lock 42 means 'migration in progress'.*
```transcript
A> SELECT pg_advisory_lock(42);
pg_advisory_lock
------------------
(1 row)
B> SELECT pg_try_advisory_lock(42) AS got_it; -- an instant answer — no waiting
got_it
--------
f
(1 row)
```
*try-lock says no without waiting. A runner can also queue up for the lock:*
```transcript
B> SELECT pg_advisory_lock(42);
⏳ B is waiting for a lock…
A> SELECT pg_advisory_unlock(42) AS released;
released
----------
t
(1 row)
⏵ B resumes:
pg_advisory_lock
------------------
(1 row)
```
*Session-level locks ignore transaction boundaries entirely — COMMIT releases nothing.*
```transcript
A> BEGIN;
BEGIN
A> SELECT pg_advisory_lock(9);
pg_advisory_lock
------------------
(1 row)
A> COMMIT;
COMMIT
B> SELECT pg_try_advisory_lock(9) AS got_it;
got_it
--------
f
(1 row)
A> SELECT pg_advisory_unlock(9) AS released;
released
----------
t
(1 row)
```
*pg_advisory_xact_lock, by contrast, releases itself at COMMIT — there is no unlock function for it.*
```transcript
A> BEGIN;
BEGIN
A> SELECT pg_advisory_xact_lock(7);
pg_advisory_xact_lock
-----------------------
(1 row)
B> SELECT pg_try_advisory_lock(7) AS got_it;
got_it
--------
f
(1 row)
A> COMMIT;
COMMIT
B> SELECT pg_try_advisory_lock(7) AS got_it; -- freed by A's COMMIT alone
got_it
--------
t
(1 row)
B> SELECT pg_advisory_unlock(7);
pg_advisory_unlock
--------------------
t
(1 row)
B> SELECT pg_advisory_unlock(42);
pg_advisory_unlock
--------------------
t
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/05-patterns/advisory-locks.yaml)
## postgres/05-patterns/check-then-insert-race.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: "SELECT first, INSERT if absent" cannot enforce uniqueness: two concurrent requests both see 0 rows, both insert, and the duplicate lands without any error.
```timeline
A: any bob? → 0
B: any bob? → 0
A: INSERT bob
A: COMMIT
B: INSERT bob (check already passed)
B: COMMIT
A: count bobs → 2 — the "impossible" duplicate
```
*The same person double-clicks 'Sign up'. Two app servers each run: check, then insert.*
```transcript
A> BEGIN;
BEGIN
A> SELECT count(*)::int AS existing FROM signups WHERE email = 'bob@example.com';
existing
----------
0
(1 row)
B> BEGIN;
BEGIN
B> SELECT count(*)::int AS existing FROM signups WHERE email = 'bob@example.com'; -- B's check also passes — A hasn't committed anything
existing
----------
0
(1 row)
```
*Both checks passed, so both insert. Nothing blocks, nothing errors.*
```transcript
A> INSERT INTO signups (email) VALUES ('bob@example.com');
INSERT 0 1
A> COMMIT;
COMMIT
B> INSERT INTO signups (email) VALUES ('bob@example.com');
INSERT 0 1
B> COMMIT;
COMMIT
A> SELECT count(*)::int AS bobs FROM signups WHERE email = 'bob@example.com'; -- the "impossible" duplicate
bobs
------
2
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/05-patterns/check-then-insert-race.yaml)
## postgres/05-patterns/fix-lost-update-atomic.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: UPDATE ... SET balance = balance + 10 reads and writes in one statement: the second writer waits for the first and stacks on top — both deposits survive.
*Same two +10 deposits that lost an update in chapter 2 — but the math moved into the UPDATE itself.*
```transcript
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = balance + 10 WHERE id = 1 RETURNING balance;
balance
---------
110
(1 row)
B> BEGIN;
BEGIN
B> UPDATE accounts SET balance = balance + 10 WHERE id = 1 RETURNING balance;
⏳ B is waiting for a lock…
```
*No stale read exists to write back: B waits for A's row lock, then re-reads the committed 110.*
```transcript
A> COMMIT;
COMMIT
⏵ B resumes:
balance
---------
120
(1 row)
B> COMMIT;
COMMIT
A> SELECT balance FROM accounts WHERE id = 1; -- both deposits survived
balance
---------
120
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/05-patterns/fix-lost-update-atomic.yaml)
## postgres/05-patterns/fix-lost-update-for-update.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: When the new value must be computed in application code, SELECT ... FOR UPDATE serializes the read-modify-write: the second reader waits and then reads the committed 110, so both deposits land.
*The app must apply business rules to the balance in code — so it locks the row while it reads.*
```transcript
A> BEGIN;
BEGIN
A> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
balance
---------
100
(1 row)
B> BEGIN;
BEGIN
B> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
⏳ B is waiting for a lock…
A> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
UPDATE 1
A> COMMIT;
COMMIT
```
*B's locked read waited out A's transaction — and returns the fresh 110, not the 100 it would have seen.*
```transcript
⏵ B resumes:
balance
---------
110
(1 row)
B> UPDATE accounts SET balance = 110 + 10 WHERE id = 1;
UPDATE 1
B> COMMIT;
COMMIT
A> SELECT balance FROM accounts WHERE id = 1; -- both deposits survived
balance
---------
120
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/05-patterns/fix-lost-update-for-update.yaml)
## postgres/05-patterns/fix-lost-update-version-column.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: A version column makes the lost update detectable instead of silent: the stale write matches 0 rows (UPDATE 0), and retrying against the new version lands both deposits.
*Both app servers read the row — including its version — with no locks held.*
```transcript
A> BEGIN;
BEGIN
A> SELECT balance, version FROM accounts WHERE id = 1;
balance | version
---------+---------
100 | 1
(1 row)
B> BEGIN;
BEGIN
B> SELECT balance, version FROM accounts WHERE id = 1;
balance | version
---------+---------
100 | 1
(1 row)
```
*Every write bumps the version and only matches the version it read.*
```transcript
A> UPDATE accounts SET balance = 100 + 10, version = version + 1
WHERE id = 1 AND version = 1;
UPDATE 1
A> COMMIT;
COMMIT
B> UPDATE accounts SET balance = 100 + 10, version = version + 1
WHERE id = 1 AND version = 1; -- UPDATE 0 — the row moved on; B's deposit did NOT silently vanish
UPDATE 0
```
*UPDATE 0 is the signal to retry: roll back, re-read, write against the new version.*
```transcript
B> ROLLBACK;
ROLLBACK
B> BEGIN;
BEGIN
B> SELECT balance, version FROM accounts WHERE id = 1;
balance | version
---------+---------
110 | 2
(1 row)
B> UPDATE accounts SET balance = 110 + 10, version = version + 1
WHERE id = 1 AND version = 2;
UPDATE 1
B> COMMIT;
COMMIT
A> SELECT balance, version FROM accounts WHERE id = 1; -- both deposits survived
balance | version
---------+---------
120 | 3
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/05-patterns/fix-lost-update-version-column.yaml)
## postgres/05-patterns/idempotency-key.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: INSERT ... ON CONFLICT DO NOTHING RETURNING on an idempotency key makes retries safe: the first request charges, every retry — even one racing the original in flight — gets 0 rows back and charges nothing.
*The client sends 'charge $30' with idempotency key req-42. Server A processes it.*
```transcript
A> BEGIN;
BEGIN
A> INSERT INTO payments VALUES ('req-42', 30)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING idempotency_key; -- a row came back: this key is new — do the work
idempotency_key
-----------------
req-42
(1 row)
A> UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE 1
A> COMMIT;
COMMIT
```
*The response is lost in the network. The client retries the same request; server B picks it up.*
```transcript
B> BEGIN;
BEGIN
B> INSERT INTO payments VALUES ('req-42', 30)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING idempotency_key; -- 0 rows: already processed — skip the charge, return the stored result
INSERT 0 0
B> SELECT amount FROM payments WHERE idempotency_key = 'req-42';
amount
--------
30
(1 row)
B> COMMIT;
COMMIT
A> SELECT balance FROM accounts WHERE id = 1; -- charged exactly once
balance
---------
70
(1 row)
```
*The nasty case: the retry arrives while the original is still in flight, uncommitted.*
```transcript
A> BEGIN;
BEGIN
A> INSERT INTO payments VALUES ('req-99', 25)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING idempotency_key;
idempotency_key
-----------------
req-99
(1 row)
A> UPDATE accounts SET balance = balance - 25 WHERE id = 1;
UPDATE 1
B> INSERT INTO payments VALUES ('req-99', 25)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING idempotency_key;
⏳ B is waiting for a lock…
```
*The unique index parks the retry until the original decides. A commits — the retry absorbs to 0 rows.*
```transcript
A> COMMIT;
COMMIT
⏵ B resumes:
INSERT 0 0
```
*Even the in-flight duplicate cannot double-charge.*
```transcript
A> SELECT balance FROM accounts WHERE id = 1; -- 100 - 30 - 25: every charge applied exactly once
balance
---------
45
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/05-patterns/idempotency-key.yaml)
## postgres/05-patterns/idle-in-transaction-timeout.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: A session that goes idle mid-transaction (the classic ORM failure mode) is killed by idle_in_transaction_session_timeout: its work is rolled back and the app discovers the corpse on its next statement.
*An ORM opens a transaction, updates a row… and then the request handler calls a slow external API.*
```transcript
A> SET idle_in_transaction_session_timeout = '500ms';
SET
A> BEGIN;
BEGIN
A> UPDATE orders SET status = 'paid' WHERE id = 1;
UPDATE 1
```
*From the outside this is the classic pathology — a session holding locks while doing nothing:*
```transcript
M> SELECT state FROM pg_stat_activity WHERE application_name = 'A';
state
---------------------
idle in transaction
(1 row)
```
*…the API call drags on past the timeout. The server kills the session (FATAL 25P03).*
```transcript
M> SELECT state FROM pg_stat_activity WHERE application_name = 'A'; -- the backend is gone
(0 rows)
```
*The app finds out only when it finally comes back to commit:*
```transcript
A> COMMIT;
ERROR: ERR_POSTGRES_CONNECTION_CLOSED: Connection closed
M> SELECT status FROM orders WHERE id = 1; -- the UPDATE was rolled back with the killed session
status
---------
pending
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/05-patterns/idle-in-transaction-timeout.yaml)
## postgres/05-patterns/job-queue.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: The full worker loop — claim with FOR UPDATE SKIP LOCKED, work, mark done, commit — never double-processes a job and never loses one: a worker crash returns its job to the queue automatically.
```timeline
A: claim → job 1
B: claim → job 2 (skips locked job 1)
A: job 1 done
A: COMMIT
B: crash → job 2 requeued
B: claim → job 2 (back in queue)
B: job 2 done
B: COMMIT
A: both jobs done
```
*Two workers run the same loop: claim the oldest queued job, do the work, mark it done, commit.*
```transcript
A> BEGIN;
BEGIN
A> SELECT id, task FROM jobs WHERE state = 'queued'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+--------------------
1 | send welcome email
(1 row)
B> BEGIN;
BEGIN
B> SELECT id, task FROM jobs WHERE state = 'queued'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; -- job 1 is claimed — skipped without waiting
id | task
----+------------------
2 | generate invoice
(1 row)
```
*A finishes and commits. B crashes mid-job — its claim evaporates with its transaction.*
```transcript
A> UPDATE jobs SET state = 'done' WHERE id = 1;
UPDATE 1
A> COMMIT;
COMMIT
B> ROLLBACK;
ROLLBACK
```
*A restarted worker finds job 2 right back in the queue — nothing was lost, nothing ran twice.*
```transcript
B> BEGIN;
BEGIN
B> SELECT id, task FROM jobs WHERE state = 'queued'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+------------------
2 | generate invoice
(1 row)
B> UPDATE jobs SET state = 'done' WHERE id = 2;
UPDATE 1
B> COMMIT;
COMMIT
A> SELECT id, state FROM jobs ORDER BY id;
id | state
----+-------
1 | done
2 | done
(2 rows)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/05-patterns/job-queue.yaml)
## postgres/05-patterns/on-conflict.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: A UNIQUE constraint makes the concurrent duplicate wait for the first insert's fate and then fail with 23505 — and ON CONFLICT turns that error into a clean no-op or upsert.
```timeline
A: INSERT bob
B: INSERT bob (duplicate) → ⏳ waits
A: COMMIT
B: ⏵ INSERT bob (duplicate) ← 23505 duplicate key value violates unique constraint "signups_email_key"
```
*Same double-click, but now the table has UNIQUE (email).*
```transcript
A> BEGIN;
BEGIN
A> INSERT INTO signups (email) VALUES ('bob@example.com');
INSERT 0 1
```
*B's insert can't decide yet — the winner hasn't committed. It waits on A's transaction.*
```transcript
B> INSERT INTO signups (email) VALUES ('bob@example.com');
⏳ B is waiting for a lock…
A> COMMIT;
COMMIT
⏵ B's blocked statement fails:
ERROR: 23505: duplicate key value violates unique constraint "signups_email_key"
```
*unique_violation — the race is now loud instead of silent.*
*ON CONFLICT DO NOTHING absorbs the duplicate instead of erroring:*
```transcript
B> INSERT INTO signups (email) VALUES ('bob@example.com')
ON CONFLICT (email) DO NOTHING; -- INSERT 0 0
INSERT 0 0
```
*And DO UPDATE makes it an upsert — here just to read back the existing row's id:*
```transcript
B> INSERT INTO signups (email) VALUES ('bob@example.com')
ON CONFLICT (email) DO UPDATE SET email = EXCLUDED.email
RETURNING id, email;
id | email
----+-----------------
1 | bob@example.com
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/05-patterns/on-conflict.yaml)
## postgres/05-patterns/retry-serialization-failures.ts
- engine: postgres (PostgreSQL 18.4)
- claim: A 40001 is transient, not fatal: rerunning the identical transaction reads the fresh state and succeeds — withRetry needs exactly two attempts here.
```transcript
B> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
```
*B read 100. Before it writes, A commits a conflicting deposit — B's write is now doomed.*
```transcript
A> UPDATE accounts SET balance = balance + 10 WHERE id = 1;
UPDATE 1
B> UPDATE accounts SET balance = 105 WHERE id = 1;
ERROR: 40001: could not serialize access due to concurrent update
B> ROLLBACK;
ROLLBACK
B> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
110
(1 row)
```
*Attempt 2 is a brand-new transaction: it reads 110 — the state that made attempt 1 impossible.*
```transcript
B> UPDATE accounts SET balance = 115 WHERE id = 1;
UPDATE 1
B> COMMIT;
COMMIT
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
115
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/05-patterns/retry-serialization-failures.ts)
## postgres/06-distributed/dual-write-problem.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: Two systems, two writes, no shared transaction: whichever write goes first, a failure between them leaves the database and the broker permanently disagreeing.
*Attempt 1 — write, then publish. The order commits…*
```transcript
App> BEGIN;
BEGIN
App> INSERT INTO orders VALUES (1, 'alice', 90);
INSERT 0 1
App> COMMIT;
COMMIT
```
*…and the process crashes before the publish step ever runs. The broker never hears about order 1.*
```transcript
App> SELECT (SELECT count(*)::int FROM orders) AS orders,
(SELECT count(*)::int FROM broker) AS events;
orders | events
--------+--------
1 | 0
(1 row)
```
*Attempt 2 — publish first, then write. The event goes out…*
```transcript
App> INSERT INTO broker VALUES ('order_placed: order 2');
INSERT 0 1
```
*…and then the order INSERT fails — a constraint, a crash, a timeout, anything.*
```transcript
App> INSERT INTO orders VALUES (2, 'mallory', -5); -- check_violation
ERROR: 23514: new row for relation "orders" violates check constraint "orders_amount_check"
```
*Downstream services now process an order that never existed.*
```transcript
App> SELECT (SELECT count(*)::int FROM orders WHERE id = 2) AS orders,
(SELECT count(*)::int FROM broker WHERE event LIKE '%order 2%') AS events;
orders | events
--------+--------
0 | 1
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/06-distributed/dual-write-problem.yaml)
## postgres/06-distributed/listen-notify.ts
- engine: postgres (PostgreSQL 18.4)
- claim: NOTIFY delivers nothing until COMMIT, a rolled-back NOTIFY is never delivered, and identical notifications within one transaction are folded into one.
*A psql process is attached and ran LISTEN orders; — a separate client, not one of our sessions.*
```transcript
A> SELECT pg_backend_pid() AS pid;
pid
--------
pid(A)
(1 row)
A> BEGIN;
BEGIN
A> INSERT INTO orders VALUES (1, 'alice');
INSERT 0 1
A> NOTIFY orders, 'order 1 placed';
NOTIFY
```
*The listener hears nothing — the notification is queued inside A's transaction.*
```transcript
A> COMMIT;
COMMIT
```
*The listener wakes up: Asynchronous notification "orders" with payload "order 1 placed" received from server process with PID pid(A).*
*A rolled-back NOTIFY never happened at all:*
```transcript
A> BEGIN;
BEGIN
A> NOTIFY orders, 'order 2 placed';
NOTIFY
A> ROLLBACK;
ROLLBACK
```
*Silence — order 2's notification died with its transaction.*
*Identical notifications in one transaction are de-duplicated:*
```transcript
A> BEGIN;
BEGIN
A> NOTIFY orders, 'order 3 placed';
NOTIFY
A> NOTIFY orders, 'order 3 placed';
NOTIFY
A> COMMIT;
COMMIT
```
*Two NOTIFYs went in, one notification came out.*
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/06-distributed/listen-notify.ts)
## postgres/06-distributed/saga-compensation.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: A saga replaces one distributed transaction with a chain of local ones: every step commits immediately and is visible to everyone, and a failed step is undone by a new compensating transaction — not by ROLLBACK.
*Step 1 — book the flight. A local transaction, committed immediately.*
```transcript
Saga> BEGIN;
BEGIN
Saga> UPDATE flights SET seats = seats - 1 WHERE id = 1 RETURNING seats;
seats
-------
4
(1 row)
Saga> COMMIT;
COMMIT
```
*A saga has no isolation: between steps, the whole world sees the half-done trip.*
```transcript
Reader> SELECT seats FROM flights WHERE id = 1;
seats
-------
4
(1 row)
```
*Step 2 — book the hotel. No rooms left: the step fails as a business outcome, not an error.*
```transcript
Saga> BEGIN;
BEGIN
Saga> UPDATE hotels SET rooms = rooms - 1 WHERE id = 1 AND rooms > 0; -- UPDATE 0 — nothing to book
UPDATE 0
Saga> ROLLBACK;
ROLLBACK
```
*Step 1 already committed — there is nothing a ROLLBACK could undo. The saga runs a COMPENSATING transaction: a new forward transaction that reverses the booking.*
```transcript
Saga> BEGIN;
BEGIN
Saga> UPDATE flights SET seats = seats + 1 WHERE id = 1 RETURNING seats;
seats
-------
5
(1 row)
Saga> COMMIT;
COMMIT
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/06-distributed/saga-compensation.yaml)
## postgres/06-distributed/transactional-outbox.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: Writing the order and its event in one transaction makes them atomic, and a SKIP LOCKED relay gives at-least-once delivery: a crashed relay redelivers the event instead of losing it.
*The order and its event are written in ONE transaction — both land in the same database.*
```transcript
App> BEGIN;
BEGIN
App> INSERT INTO orders VALUES (1, 'alice', 90);
INSERT 0 1
App> INSERT INTO outbox (event) VALUES ('order_placed: order 1');
INSERT 0 1
App> COMMIT;
COMMIT
```
*Atomicity covers the failure path too: no committed order, no event.*
```transcript
App> BEGIN;
BEGIN
App> INSERT INTO orders VALUES (2, 'bob', 75);
INSERT 0 1
App> INSERT INTO outbox (event) VALUES ('order_placed: order 2');
INSERT 0 1
App> ROLLBACK;
ROLLBACK
App> SELECT id, event FROM outbox ORDER BY id;
id | event
----+-----------------------
1 | order_placed: order 1
(1 row)
```
*A relay claims the event exactly like a chapter-5 job-queue worker…*
```transcript
Relay> BEGIN;
BEGIN
Relay> SELECT id, event FROM outbox ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | event
----+-----------------------
1 | order_placed: order 1
(1 row)
```
*…publishes it to the broker (an HTTP call — outside any transaction), deletes it, and crashes before COMMIT.*
```transcript
Relay> DELETE FROM outbox WHERE id = 1;
DELETE 1
Relay> ROLLBACK;
ROLLBACK
```
*The delete evaporated with the crash — the event is still in the outbox. The restarted relay publishes it AGAIN: at-least-once delivery, so consumers must be idempotent.*
```transcript
Relay> BEGIN;
BEGIN
Relay> SELECT id, event FROM outbox ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | event
----+-----------------------
1 | order_placed: order 1
(1 row)
Relay> DELETE FROM outbox WHERE id = 1;
DELETE 1
Relay> COMMIT;
COMMIT
Relay> SELECT count(*)::int AS pending FROM outbox;
pending
---------
0
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/06-distributed/transactional-outbox.yaml)
## postgres/06-distributed/two-phase-commit.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: PREPARE TRANSACTION detaches a transaction from its session: it survives the session's death, keeps holding its locks, holds the xid horizon back so VACUUM reclaims nothing in any table, and any later session can finish it by name with COMMIT PREPARED.
*A is one participant in a distributed transfer. It does its work, then PREPARES — phase one.*
```transcript
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 200 WHERE id = 1;
UPDATE 1
A> PREPARE TRANSACTION 'transfer-42';
PREPARE TRANSACTION
```
*PREPARE detached the transaction from the session: A itself no longer sees its own change.*
```transcript
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
M> SELECT gid, owner, database FROM pg_prepared_xacts;
gid | owner | database
-------------+----------+----------
transfer-42 | postgres | postgres
(1 row)
```
*The prepared transaction still holds its row locks — with no session attached.*
```transcript
B> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE NOWAIT; -- lock_not_available
ERROR: 55P03: could not obtain lock on row in relation "accounts"
```
*Now the coordinator crashes: A's backend is killed outright.*
```transcript
M> SELECT pg_terminate_backend(pid) AS terminated
FROM pg_stat_activity WHERE application_name = 'A';
terminated
------------
t
(1 row)
A> SELECT 1;
ERROR: ERR_POSTGRES_CONNECTION_CLOSED: Connection closed
```
*The session is gone. The prepared transaction is not — it survives anything short of COMMIT/ROLLBACK PREPARED, including a full server restart. And it still holds its locks:*
```transcript
M> SELECT gid FROM pg_prepared_xacts;
gid
-------------
transfer-42
(1 row)
B> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
ERROR: 55P03: could not obtain lock on row in relation "accounts"
```
*The locks are the visible damage. The quiet damage is the xid horizon: the orphan still counts as a running transaction, so no snapshot taken since it prepared can be ruled out. Watch it freeze VACUUM in `ledger` — a table the prepared transaction never touched.*
```transcript
B> UPDATE ledger SET n = 1 WHERE id = 1;
UPDATE 1
B> UPDATE ledger SET n = 2 WHERE id = 1;
UPDATE 1
B> UPDATE ledger SET n = 3 WHERE id = 1;
UPDATE 1
```
*Four versions of one row on the page — three of them dead.*
```transcript
B> SELECT count(*)::int AS versions_on_page
FROM heap_page_items(get_raw_page('ledger', 0)) WHERE lp_flags = 1;
versions_on_page
------------------
4
(1 row)
B> VACUUM ledger;
VACUUM
```
*VACUUM ran, reported success, and reclaimed nothing. The orphan's horizon still covers every one of those versions.*
```transcript
B> SELECT count(*)::int AS versions_on_page
FROM heap_page_items(get_raw_page('ledger', 0)) WHERE lp_flags = 1;
versions_on_page
------------------
4
(1 row)
```
*Phase two — any session can finish the job by name. B commits the orphan.*
```transcript
B> COMMIT PREPARED 'transfer-42';
COMMIT PREPARED
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
200
(1 row)
```
*And the horizon moves. The same VACUUM, a moment later, finally collects the three dead versions.*
```transcript
B> VACUUM ledger;
VACUUM
B> SELECT count(*)::int AS versions_on_page
FROM heap_page_items(get_raw_page('ledger', 0)) WHERE lp_flags = 1;
versions_on_page
------------------
1
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/06-distributed/two-phase-commit.yaml)
## postgres/07-pitfalls/queue-bloat.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: A stuck worker's open transaction pins the vacuum horizon, so every claim/complete cycle the queue drains during the hang leaves a dead row version VACUUM cannot reclaim — the queue table grows with its throughput, not its backlog, and the identical VACUUM frees all of it the moment the worker's transaction ends.
```timeline
A: A claims job 1, then hangs
B: 9 pages
B: B claims job 2 (skips A's job 1)
B: job 2 done, one dead version
B: COMMIT
B: drain 250
B: 9 → 11 pages
B: drain 750
B: 11 → 17 pages
B: 2201 line pointers (1200 live + 1001 dead)
B: VACUUM, while A still hangs
B: still 2201, nothing reclaimed
A: A finally commits, the hang ends
B: same VACUUM, a moment later
B: 2201 → 1200, reclaimed
B: still 17 pages, space is free inside, not returned
```
*A is a worker that claims a job and then hangs: the connection stays open and the transaction never commits. This is the queue's own flavor of "idle in transaction."*
```transcript
A> BEGIN;
BEGIN
A> SELECT id, task FROM jobs WHERE state = 'queued'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+--------------------
1 | send welcome email
(1 row)
```
*The queue is 1200 rows in 9 pages, and it keeps humming while A sits there. B is a healthy worker running the exact chapter 5 loop. Watch one cycle produce one dead version: B skips A's locked job 1 and claims job 2.*
```transcript
B> SELECT (pg_relation_size('jobs') / 8192)::int AS pages;
pages
-------
9
(1 row)
B> BEGIN;
BEGIN
B> SELECT id, task FROM jobs WHERE state = 'queued'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; -- job 1 is claimed by A, skipped without waiting
id | task
----+--------------------
2 | send welcome email
(1 row)
B> UPDATE jobs SET state = 'done' WHERE id = 2;
UPDATE 1
B> COMMIT;
COMMIT
```
*That cycle left exactly one dead version: the old 'queued' tuple of job 2. Now B runs the same body a thousand more times. drain(n) is that cycle in a procedure — claim, mark done, COMMIT, repeat — so the per-job COMMIT is why CALL runs outside a transaction block. The SQL a reader trusts is the SQL they just watched.*
```transcript
B> CALL drain(250); -- 250 more claim/complete cycles
CALL
B> SELECT (pg_relation_size('jobs') / 8192)::int AS pages;
pages
-------
11
(1 row)
B> CALL drain(750); -- 750 more
CALL
B> SELECT (pg_relation_size('jobs') / 8192)::int AS pages;
pages
-------
17
(1 row)
```
*The table is still 1200 live rows and has nearly doubled on disk. Count the line pointers across every page: 1200 live plus 1001 dead, none of the dead ones removable while A's snapshot might still need the 'queued' versions they replaced.*
```transcript
B> SELECT count(*)::int AS occupied
FROM generate_series(0, (pg_relation_size('jobs') / 8192)::int - 1) AS p,
heap_page_items(get_raw_page('jobs', p::int))
WHERE lp_flags = 1;
occupied
----------
2201
(1 row)
B> VACUUM jobs;
VACUUM
```
*VACUUM ran, reported success, and reclaimed nothing. Same silent no-op as the long transactions lesson, now with a price tag: the line-pointer count has not moved.*
```transcript
B> SELECT count(*)::int AS occupied
FROM generate_series(0, (pg_relation_size('jobs') / 8192)::int - 1) AS p,
heap_page_items(get_raw_page('jobs', p::int))
WHERE lp_flags = 1;
occupied
----------
2201
(1 row)
A> COMMIT;
COMMIT
```
*The instant A's snapshot is gone, the identical VACUUM can remove all 1001 dead versions. The occupied count falls to the 1200 live rows.*
```transcript
B> VACUUM jobs;
VACUUM
B> SELECT count(*)::int AS occupied
FROM generate_series(0, (pg_relation_size('jobs') / 8192)::int - 1) AS p,
heap_page_items(get_raw_page('jobs', p::int))
WHERE lp_flags = 1;
occupied
----------
1200
(1 row)
```
*But the file never shrank. VACUUM freed the space inside the 17 pages for the queue to reuse; it did not hand it back to the OS, and only VACUUM FULL would, under a table lock. Page count is why you can't measure this bug with pg_relation_size alone: the disk you bought during the hang stays bought.*
```transcript
B> SELECT (pg_relation_size('jobs') / 8192)::int AS pages;
pages
-------
17
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/07-pitfalls/queue-bloat.yaml)
## postgres/08-production/deadlock-counter.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: Every deadlock increments pg_stat_database.deadlocks — a counter you can alert on even when nobody was watching the error logs.
```timeline
A: holds row 1
B: holds row 2
A: wants row 2 (B holds it) → ⏳ waits
B: wants row 1 (A holds it) ← 40P01 deadlock detected
A: ⏵ wants row 2 (B holds it) → completes
```
```transcript
A> SET deadlock_timeout = '10s'; -- Pin the victim, as in the chapter-3 deadlock lesson, so the transcript is reproducible.
SET
B> SET deadlock_timeout = '50ms';
SET
```
*The classic: A and B lock the same two rows in opposite order.*
```transcript
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE 1
B> BEGIN;
BEGIN
B> UPDATE accounts SET balance = balance - 25 WHERE id = 2;
UPDATE 1
A> UPDATE accounts SET balance = balance + 10 WHERE id = 2;
⏳ A is waiting for a lock…
B> UPDATE accounts SET balance = balance + 25 WHERE id = 1; -- deadlock_detected
ERROR: 40P01: deadlock detected
⏵ A resumes:
UPDATE 1
A> COMMIT;
COMMIT
B> ROLLBACK;
ROLLBACK
```
*The app retried, the users never noticed. But the database remembers:*
```transcript
B> SELECT pg_stat_force_next_flush();
pg_stat_force_next_flush
--------------------------
(1 row)
M> SELECT (d.deadlocks - s.deadlocks)::int AS new_deadlocks
FROM pg_stat_database d, stats_before s
WHERE d.datname = current_database();
new_deadlocks
---------------
1
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/08-production/deadlock-counter.yaml)
## postgres/08-production/find-long-transactions.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: Three pg_stat_activity queries find the forgotten transaction: the oldest xact_start, sessions sitting idle in transaction, and the backend_xmin that pins VACUUM's horizon.
*A starts a 'quick report' at REPEATABLE READ… and never gets around to committing.*
```transcript
A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
A> SELECT count(*)::int AS orders FROM orders;
orders
--------
2
(1 row)
```
*Detector 1 — the oldest open transaction, and how long it's been open:*
```transcript
M> SELECT application_name, state, now() - xact_start > interval '1 second' AS older_than_1s
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid()
ORDER BY xact_start LIMIT 1;
application_name | state | older_than_1s
------------------+---------------------+---------------
A | idle in transaction | t
(1 row)
```
*Detector 2 — sessions holding a transaction open while doing nothing:*
```transcript
M> SELECT application_name
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND now() - state_change > interval '1 second';
application_name
------------------
A
(1 row)
```
*Detector 3 — the reason it matters even for a read-only report: A's snapshot (backend_xmin) is what VACUUM must preserve.*
```transcript
M> SELECT application_name, backend_xid IS NOT NULL AS wrote_anything,
backend_xmin IS NOT NULL AS pins_vacuum_horizon
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL AND pid <> pg_backend_pid();
application_name | wrote_anything | pins_vacuum_horizon
------------------+----------------+---------------------
A | f | t
(1 row)
A> COMMIT;
COMMIT
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/08-production/find-long-transactions.yaml)
## postgres/08-production/timeout-guardrails.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: statement_timeout cancels a runaway statement (SQLSTATE 57014) but keeps the session; transaction_timeout kills the whole transaction — connection and all — and its work rolls back.
*statement_timeout: the per-statement seatbelt.*
```transcript
A> SET statement_timeout = '100ms';
SET
A> SELECT pg_sleep(2); -- query_canceled
ERROR: 57014: canceling statement due to statement timeout
```
*Only the statement died — the session and its transaction state are fine.*
```transcript
A> SELECT 'still here' AS session;
session
------------
still here
(1 row)
```
*transaction_timeout (PostgreSQL 17+): a hard ceiling on the whole transaction — idle or busy.*
```transcript
A> RESET statement_timeout;
RESET
A> SET transaction_timeout = '500ms';
SET
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 999 WHERE id = 1;
UPDATE 1
```
*This timeout doesn't cancel a statement — it terminates the backend:*
```transcript
M> SELECT count(*)::int AS backends FROM pg_stat_activity WHERE application_name = 'A';
backends
----------
0
(1 row)
A> COMMIT; -- the server logged FATAL; Bun sees a dead socket
ERROR: ERR_POSTGRES_CONNECTION_CLOSED: Connection closed
```
*The killed transaction's work rolled back, as always:*
```transcript
M> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/08-production/timeout-guardrails.yaml)
## postgres/08-production/vacuum-health.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: pg_stat_user_tables shows dead tuples accumulating and when vacuum last ran, and age(datfrozenxid) vs autovacuum_freeze_max_age is the wraparound alert.
```transcript
A> INSERT INTO inventory SELECT g, 10 FROM generate_series(1, 5) g;
INSERT 0 5
A> UPDATE inventory SET qty = qty + 1 WHERE id <= 3;
UPDATE 3
A> SELECT pg_stat_force_next_flush(); -- stats reach the views lazily; force it for the demo
pg_stat_force_next_flush
--------------------------
(1 row)
```
*Chapter 4 proved updates leave dead tuples behind; pg_stat_user_tables is where you SEE them:*
```transcript
M> SELECT relname, n_live_tup::int, n_dead_tup::int, last_vacuum IS NULL AS never_vacuumed
FROM pg_stat_user_tables WHERE relname = 'inventory';
relname | n_live_tup | n_dead_tup | never_vacuumed
-----------+------------+------------+----------------
inventory | 5 | 3 | t
(1 row)
```
*VACUUM cleans up — and the same view proves it happened:*
```transcript
A> VACUUM inventory;
VACUUM
M> SELECT relname, n_live_tup::int, n_dead_tup::int, last_vacuum IS NOT NULL AS vacuumed
FROM pg_stat_user_tables WHERE relname = 'inventory';
relname | n_live_tup | n_dead_tup | vacuumed
-----------+------------+------------+----------
inventory | 5 | 0 | t
(1 row)
```
*The one number that must never reach its limit: the database's xid age vs the emergency threshold.*
```transcript
M> SELECT age(datfrozenxid) < current_setting('autovacuum_freeze_max_age')::int AS wraparound_ok
FROM pg_database WHERE datname = current_database();
wraparound_ok
---------------
t
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/08-production/vacuum-health.yaml)
## postgres/08-production/who-is-blocking-whom.yaml
- engine: postgres (PostgreSQL 18.4)
- claim: One join on pg_stat_activity + pg_blocking_pids() names the blocker, shows that it's sitting idle in transaction, and shows the last statement it ran — and pg_terminate_backend() drains the queue.
```timeline
A: UPDATE, then goes to lunch (idle in txn)
B: UPDATE balance = 300 → ⏳ waits
M: who blocks whom? → B waits on idle A
M: pg_terminate_backend(A)
B: ⏵ UPDATE balance = 300 → completes
```
*A updates a row and then… goes to lunch. The transaction stays open.*
```transcript
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = 200 WHERE id = 1;
UPDATE 1
B> UPDATE accounts SET balance = 300 WHERE id = 1;
⏳ B is waiting for a lock…
```
*Someone pages you: 'updates are hanging'. This is the query you paste:*
```transcript
M> SELECT waiter.application_name AS waiter,
waiter.query AS waiting_query,
blocker.application_name AS blocker,
blocker.state AS blocker_state,
blocker.query AS blocker_last_query
FROM pg_stat_activity waiter
JOIN pg_stat_activity blocker ON blocker.pid = ANY (pg_blocking_pids(waiter.pid))
WHERE waiter.wait_event_type = 'Lock';
waiter | waiting_query | blocker | blocker_state | blocker_last_query
--------+------------------------------------------------+---------+---------------------+------------------------------------------------
B | UPDATE accounts SET balance = 300 WHERE id = 1 | A | idle in transaction | UPDATE accounts SET balance = 200 WHERE id = 1
(1 row)
```
*The culprit isn't running anything — it's IDLE, holding locks. The fix is blunt:*
```transcript
M> SELECT pg_terminate_backend(pid) AS terminated
FROM pg_stat_activity WHERE application_name = 'A';
terminated
------------
t
(1 row)
```
*A's transaction dies and rolls back; B gets the lock and finishes at last.*
```transcript
⏵ B resumes:
UPDATE 1
B> SELECT balance FROM accounts WHERE id = 1; -- A's 200 rolled back with its termination; B's 300 committed
balance
---------
300
(1 row)
```
Verified against PostgreSQL 18.4 · [Run it yourself](/about/run-locally) · [Scenario source](https://github.com/svyatov/database-transactions/blob/main/scenarios/postgres/08-production/who-is-blocking-whom.yaml)