Locks, Deadlocks & MVCC
Advanced · 24 min read · ▶ live playground · ✦ checkpoint
Isolation levels decide which concurrent stories are allowed. Locks and MVCC are the machinery underneath: locks reserve rows for work, and MVCC — multiversion concurrency control — lets ordinary reads use snapshots instead of fighting every write.
This lesson stays honest about the lab. The playground has one session, so true waits and deadlocks appear as labeled two-session transcripts for real PostgreSQL audit. The live SQL still teaches the shapes you type: SELECT ... FOR UPDATE, stable lock order, and advisory-lock syntax.
A tiny stock table
Start with the same cafe stock idea from 12.2, but add a reserved column so we can model carts without hiding the row-level target.
CREATE TABLE stock_items (
sku text PRIMARY KEY,
item_name text NOT NULL,
stock integer NOT NULL CHECK (stock >= 0),
reserved integer NOT NULL DEFAULT 0 CHECK (reserved >= 0)
);
INSERT INTO stock_items (sku, item_name, stock, reserved)
VALUES
('beans', 'Espresso beans', 5, 0),
('cups', 'Paper cups', 12, 0),
('kit', 'Pour-over kit', 1, 0);
SELECT sku, item_name, stock, reserved
FROM stock_items
ORDER BY sku;CREATE TABLE
INSERT 0 3
sku | item_name | stock | reserved
-------+----------------+-------+----------
beans | Espresso beans | 5 | 0
cups | Paper cups | 12 | 0
kit | Pour-over kit | 1 | 0
(3 rows)Every lock example still starts with the Section 8 habit: rehearse the row set first. Locking the wrong row confidently is still wrong.
Row locks: reserve the row before deciding
SELECT ... FOR UPDATE reads rows and takes a row-level lock on them. In PostgreSQL, that prevents other transactions from updating, deleting, or taking conflicting locks on those same rows until your transaction ends.
Here the single session proves the syntax and the shape:
BEGIN;
SELECT sku, item_name, stock, reserved
FROM stock_items
WHERE sku = 'kit'
FOR UPDATE;
UPDATE stock_items
SET reserved = reserved + 1
WHERE sku = 'kit'
AND stock - reserved > 0;
SELECT sku, item_name, stock, reserved
FROM stock_items
WHERE sku = 'kit';
COMMIT;BEGIN
sku | item_name | stock | reserved
-----+---------------+-------+----------
kit | Pour-over kit | 1 | 0
(1 row)
UPDATE 1
sku | item_name | stock | reserved
-----+---------------+-------+----------
kit | Pour-over kit | 1 | 1
(1 row)
COMMITThe lock itself is not a special grid column. The effect matters in a second session: another writer that wants kit must wait until this transaction commits or rolls back.
Real-Postgres audit transcript — row lock wait, then guard recheck
(fresh stock_items: kit has stock 1, reserved 0 — two customers race for the last kit)
Session A | Session B
-----------------------------------------------+-----------------------------------------------
BEGIN; |
SELECT sku, stock, reserved |
FROM stock_items |
WHERE sku = 'kit' |
FOR UPDATE; |
sku | stock | reserved |
-----+-------+---------- |
kit | 1 | 0 |
(1 row) |
UPDATE stock_items |
SET reserved = reserved + 1 |
WHERE sku = 'kit' |
AND stock - reserved > 0; |
UPDATE 1 |
-- not committed yet |
| BEGIN;
| SELECT sku, stock, reserved
| FROM stock_items
| WHERE sku = 'kit';
| sku | stock | reserved
| -----+-------+----------
| kit | 1 | 0
| (1 row)
| UPDATE stock_items
| SET reserved = reserved + 1
| WHERE sku = 'kit'
| AND stock - reserved > 0;
| -- waits: the row version B can see matches
| -- the guard, and Session A holds its lock
COMMIT; |
COMMIT |
| UPDATE 0
| COMMIT;
| COMMIT
Audit note: Session B's plain SELECT read the committed snapshot (reserved 0). Its
UPDATE matched that old row version, so it had to wait for Session A's lock. After
Session A committed, PostgreSQL re-evaluated the guard against the new committed
version (reserved 1): stock - reserved is 0, the guard fails, and the update affects
0 rows. UPDATE 0 is Session B's truthful "someone else got the last one" signal.
One subtlety worth knowing: if the row version a session can SEE already fails the
guard, the UPDATE skips the row without waiting at all — blocking only happens when
the visible version matches and the row is locked.That is pessimistic concurrency: reserve the row before making a decision, then keep the transaction short.
Lock multiple rows in one order
Deadlocks usually start when two transactions need the same locks in opposite orders. The everyday defense is boring: every code path locks shared rows in the same order.
This transfer reserves beans and cups in primary-key order before changing either row:
BEGIN;
SELECT sku, item_name, stock, reserved
FROM stock_items
WHERE sku IN ('beans', 'cups')
ORDER BY sku
FOR UPDATE;
UPDATE stock_items
SET reserved = reserved + 1
WHERE sku = 'beans';
UPDATE stock_items
SET reserved = reserved + 2
WHERE sku = 'cups';
SELECT sku, item_name, stock, reserved
FROM stock_items
WHERE sku IN ('beans', 'cups')
ORDER BY sku;
COMMIT;BEGIN
sku | item_name | stock | reserved
-------+----------------+-------+----------
beans | Espresso beans | 5 | 0
cups | Paper cups | 12 | 0
(2 rows)
UPDATE 1
UPDATE 1
sku | item_name | stock | reserved
-------+----------------+-------+----------
beans | Espresso beans | 5 | 1
cups | Paper cups | 12 | 2
(2 rows)
COMMITThe important design rule is not "always alphabetical." It is "one shared order for every code path." Primary key order is a good default because it is stable and easy to review.
Deadlocks: two transactions each wait for the other
A deadlock happens when transactions form a wait cycle: A holds something B needs, while B holds something A needs. Waiting longer cannot fix that. PostgreSQL detects the cycle and aborts one transaction with SQLSTATE 40P01.
This cannot run in the one-session playground. It is a two-session transcript for real PostgreSQL audit.
Real-Postgres audit transcript — deadlock from opposite lock order
Session A | Session B
-----------------------------------------------+-----------------------------------------------
BEGIN; | BEGIN;
SELECT sku, stock, reserved | SELECT sku, stock, reserved
FROM stock_items | FROM stock_items
WHERE sku = 'beans' | WHERE sku = 'cups'
FOR UPDATE; | FOR UPDATE;
sku | stock | reserved | sku | stock | reserved
-------+-------+---------- | ------+-------+----------
beans | 5 | 1 | cups | 12 | 2
(1 row) | (1 row)
SELECT sku, stock, reserved | SELECT sku, stock, reserved
FROM stock_items | FROM stock_items
WHERE sku = 'cups' | WHERE sku = 'beans'
FOR UPDATE; | FOR UPDATE;
-- waits for Session B | -- waits for Session A
| ERROR: deadlock detected
| DETAIL: Process waits for ShareLock on transaction; blocked by another process.
| HINT: See server log for query details.
| [SQLSTATE 40P01 deadlock_detected]
sku | stock | reserved |
------+-------+---------- |
cups | 12 | 2 |
(1 row) |
ROLLBACK; | ROLLBACK;
ROLLBACK | ROLLBACK
Audit note: the victim session is not predictable. Application code should retry the whole aborted transaction when the work is safe to retry.Deadlock prevention is mostly code review: lock rows in the same order, take the strongest lock you know you will need first, and do not hold a transaction open while waiting for a person, API call, or slow job.
MVCC: readers and writers often pass each other
MVCC is PostgreSQL's snapshot model. A statement reads a snapshot — a consistent database version — instead of needing to block every writer that might be touching related rows.
The practical intuition:
- An ordinary
SELECTdoes not take row locks that block writers. - An ordinary
UPDATEdoes not block ordinary readers from seeing the last committed version available to their snapshot. - Writers still conflict with writers on the same row.
- Explicit locking reads, such as
SELECT ... FOR UPDATE, intentionally join the writer-conflict world.
Real-Postgres audit transcript — MVCC reader and writer passing each other
Session A | Session B
-----------------------------------------------+-----------------------------------------------
BEGIN; |
SELECT sku, stock, reserved |
FROM stock_items |
WHERE sku = 'beans'; |
sku | stock | reserved |
-------+-------+---------- |
beans | 5 | 1 |
(1 row) |
| BEGIN;
| SELECT sku, stock, reserved
| FROM stock_items
| WHERE sku = 'beans';
| sku | stock | reserved
| -------+-------+----------
| beans | 5 | 1
| (1 row)
| UPDATE stock_items
| SET reserved = reserved + 1
| WHERE sku = 'beans';
| UPDATE 1
| COMMIT;
| COMMIT
SELECT sku, stock, reserved |
FROM stock_items |
WHERE sku = 'beans'; |
sku | stock | reserved |
-------+-------+---------- |
beans | 5 | 2 |
(1 row) |
COMMIT; |
COMMIT |
Audit note: at READ COMMITTED, Session A's second SELECT sees Session B's committed write. The plain reader did not block the writer, and the writer did not block the plain reader.That is the "readers don't block writers" idea from PostgreSQL's MVCC design. It is not permission to ignore conflicts. It means you choose when to rely on snapshots, when to guard a single statement, and when to take an explicit lock.
Optimistic vs pessimistic
Optimistic concurrency assumes conflicts are uncommon: attempt the write with a guard, then retry or report failure if the affected row count or SQLSTATE says someone got there first. The guarded stock decrement from 12.2 is optimistic:
SELECT sku, item_name, stock, reserved
FROM stock_items
WHERE sku = 'beans';
UPDATE stock_items
SET stock = stock - 1
WHERE sku = 'beans'
AND stock > 0;
SELECT sku, item_name, stock, reserved
FROM stock_items
WHERE sku = 'beans'; sku | item_name | stock | reserved
-------+----------------+-------+----------
beans | Espresso beans | 5 | 1
(1 row)
UPDATE 1
sku | item_name | stock | reserved
-------+----------------+-------+----------
beans | Espresso beans | 4 | 1
(1 row)Pessimistic concurrency assumes the decision needs a reserved row: take FOR UPDATE, make the decision, write, and commit quickly. It is useful when the cost of retrying is high or the transaction needs several related reads before writing.
Advisory locks: a small peek
An advisory lock is an application-named lock PostgreSQL stores for you, but does not attach to a specific row. The database will not enforce what the number means. Your application has to agree that, for example, key 1203 means "one nightly stock reconciliation at a time."
Transaction-level advisory locks are tidy for short jobs because they release at transaction end:
BEGIN;
SELECT pg_try_advisory_xact_lock(1203) AS got_reconciliation_lock;
COMMIT;BEGIN
got_reconciliation_lock
-------------------------
t
(1 row)
COMMITThat is only a peek. Use advisory locks sparingly: they are powerful precisely because they are outside your schema's normal row and constraint model.
You now have the concurrency map: isolation levels decide allowed anomalies, locks reserve conflict points, deadlocks are retryable failures, and MVCC lets snapshots keep many reads and writes moving. The project next uses that map in a store checkout you can trust with money.
Checkpoint
Answer all three to mark this lesson complete