BrevFeed
Sign in

51. Locking and deadlocks

📖 Reading · 7 min

Isolation levels are about reads; locks are about writes

The previous lesson covered what a transaction is allowed to see. This lesson covers what happens when two transactions both try to write the same row — a genuinely different mechanism, based on locks, not snapshots.

Row-level locking: automatic, on every write

Every UPDATE/DELETE automatically takes a lock on the specific rows it touches — you never write LOCK ROW explicitly for ordinary writes. If a second transaction tries to update a row already locked by an in-flight transaction, it blocks, waiting until the first transaction commits or rolls back:

-- Session A:
BEGIN;
UPDATE balance_demo SET balance = balance - 50 WHERE id = 1;
-- (transaction still open, row 1 is now locked)

-- Session B, at the same time:
UPDATE balance_demo SET balance = balance + 10 WHERE id = 1;
-- BLOCKS here -- waits for session A to COMMIT or ROLLBACK

This blocking is Postgres protecting you automatically — without it, two concurrent updates to the same row could silently lose one of the changes (a "lost update"). The cost is exactly what you'd expect: transaction B waits, however long transaction A takes to finish.

SELECT FOR UPDATE: locking a row you only read, on purpose

Sometimes you need to lock a row before writing to it, based on a value you first have to read — the classic "check inventory, then decrement it" pattern. A plain SELECT takes no lock at all, so between your read and your subsequent UPDATE, another transaction could sneak in and change the value you based your decision on. SELECT ... FOR UPDATE closes this gap by taking the row lock at read time:

BEGIN;
SELECT quantity FROM inventory_demo WHERE id = 1 FOR UPDATE;  -- locks this row NOW
-- ... application logic decides how much to decrement, based on the value just read ...
UPDATE inventory_demo SET quantity = quantity - 1 WHERE id = 1;
COMMIT;

Any other transaction's SELECT ... FOR UPDATE (or plain UPDATE) on that same row now blocks until this transaction finishes — exactly the protection the naive read-then-write pattern was missing. This is the standard, correct way to implement "read a value and act on it atomically" without escalating all the way to SERIALIZABLE isolation.

Table-level locks

Less common in application code, but real: LOCK TABLE tablename takes a lock on the whole table, at one of several granularities (ACCESS SHARE, ROW EXCLUSIVE, ACCESS EXCLUSIVE, and others) — mostly relevant for DDL operations and bulk maintenance work, where you genuinely need exclusive access to an entire table rather than specific rows. Ordinary application queries essentially never need to take an explicit table lock; Postgres takes the appropriate lightweight table-level lock automatically alongside every statement (e.g. a plain SELECT takes ACCESS SHARE, compatible with everything except an exclusive DDL operation).

Deadlocks: two transactions, each waiting on the other

A deadlock happens when transaction A holds a lock transaction B needs, while B simultaneously holds a lock A needs — neither can proceed, ever, without intervention:

Session A:                          Session B:
BEGIN;                               BEGIN;
UPDATE t SET x=1 WHERE id=1;         UPDATE t SET x=2 WHERE id=2;
  (locks row 1)                        (locks row 2)
UPDATE t SET x=1 WHERE id=2;         UPDATE t SET x=2 WHERE id=1;
  (blocks -- B holds row 2)            (blocks -- A holds row 1)

Both sessions are now waiting on each other forever. Postgres detects this automatically (a periodic deadlock-detection check) and resolves it by choosing one transaction as the "victim" — aborting it with a deadlock detected error, freeing its locks, letting the other proceed. The aborted transaction's application code is expected to catch this error and retry the whole transaction — a deadlock is not a bug to prevent at all costs, it's an expected, handleable condition in any system with real write concurrency.

Preventing deadlocks: consistent lock ordering

The most reliable practical defense: always acquire locks on multiple rows/tables in the same order, everywhere in your codebase. The deadlock above only happened because session A locked row 1 then row 2, while session B locked row 2 then row 1 — the opposite order. If both sessions always locked rows in, say, ascending id order, this specific deadlock shape becomes structurally impossible. This is a discipline worth establishing explicitly (e.g. "always update accounts in order of account_id") rather than discovering the hard way in production once concurrent traffic makes it likely.

Try it

Like the previous lesson, deadlocks and blocking genuinely require two concurrent sessions. session_a.sql and session_b.sql in this folder reproduce the row-lock-blocks-a-second-writer scenario and the deadlock scenario above, both against a real scratch table — lesson.md in this folder explains exactly what you'll see and when to start each script.

Check yourself

  1. What's the difference between what an isolation level controls and what a row lock controls?
  2. Why does SELECT ... FOR UPDATE exist, given that a plain SELECT is faster and doesn't block anything?
  3. What's the standard practical defense against deadlocks, and why does it work?
← Previous Next →