Locks, deadlocks, and reading a deadlock report

Locks are how the database stops two transactions corrupting each other. Mostly they are invisible. When they become visible it is because something is blocked or something deadlocked, and both are diagnosable in about a minute once you know where to look.

Two kinds

Row locks are taken automatically by UPDATE and DELETE, and deliberately by SELECT ... FOR UPDATE. They block other writers of the same row. Readers are never blocked by them — PostgreSQL's MVCC means a reader sees the previous version instead of waiting. That property is worth knowing: in PostgreSQL, readers do not block writers and writers do not block readers.

Table locks are taken by ALTER TABLE, CREATE INDEX, VACUUM FULL and so on. These are the ones that can block everybody, including readers, which is why module 4's lesson on schema changes was about lock modes.

Blocking

-- session A
BEGIN;
UPDATE seats SET free = 0 WHERE id = 1;
-- (no commit yet)

-- session B
UPDATE seats SET free = 1 WHERE id = 1;    -- waits

B waits until A commits or rolls back. That is correct — one of them has to go second.

It becomes a problem when A holds the lock for a long time, which is why the transactions lesson said to keep them short and never do network I/O inside one.

Finding what is blocked

SELECT pid,
       pg_blocking_pids(pid) AS blocked_by,
       now() - query_start   AS waiting_for,
       left(query, 60)       AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

pg_blocking_pids gives the process ids holding what this one wants. This is the query to run when the application is hanging, and it usually names the culprit immediately — frequently a session sitting idle in transaction.

And if you must:

SELECT pg_cancel_backend(12345);      -- cancel the current query, politely
SELECT pg_terminate_backend(12345);   -- close the connection entirely

Cancel first. Terminate only if cancel does not work.

Deadlocks

Two transactions each holding what the other wants.

-- session A                              -- session B
BEGIN;                                    BEGIN;
UPDATE acc SET bal=bal-10 WHERE id=1;     UPDATE acc SET bal=bal-10 WHERE id=2;
-- A now holds row 1                      -- B now holds row 2
UPDATE acc SET bal=bal+10 WHERE id=2;     UPDATE acc SET bal=bal+10 WHERE id=1;
-- waits for B                            -- waits for A

Neither can proceed. PostgreSQL detects it and kills one:

ERROR:  deadlock detected
DETAIL:  Process 418 waits for ShareLock on transaction 813; blocked by process 417.
HINT:  See server log for query details.

One transaction is aborted, the other completes. The database resolves it; it does not hang forever.

Detection is not instant — it runs after deadlock_timeout, one second by default. So a deadlock costs a second of waiting before anybody finds out.

The cause, and the fix

Almost every deadlock is two transactions taking the same locks in different orders.

The fix is nearly always the same: acquire locks in a consistent order. Sort by primary key:

-- both transactions do this, so they queue instead of deadlocking
SELECT * FROM acc WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

Whichever gets row 1 first will also get row 2 first. The other waits, then proceeds.

In application code: sort the ids before you touch them. A transfer between accounts should lock min(from, to) then max(from, to), regardless of which direction the money is going.

Other causes worth knowing:

• Foreign keys. Inserting into a child takes a lock on the parent row. Two transactions inserting children of two parents in opposite orders can deadlock.
• Indexes. Two UPDATEs touching the same index pages in different orders.
• A single UPDATE with no ORDER BY affecting many rows — two of them can process the same rows in different orders.

Reading the report

The client error is short. The server log has the detail: both statements, and which process held what. That is why the hint says to look there.

SHOW log_directory;
SHOW deadlock_timeout;
SET log_lock_waits = on;      -- also log long waits, not just deadlocks

log_lock_waits is worth turning on in production. It logs any wait longer than deadlock_timeout, so you see contention before it becomes a deadlock.

Deadlocks are retryable

SQLSTATE 40P01. Catch it and retry the whole transaction, exactly as for serialization failures. One of the two transactions always succeeds, so a retry normally works immediately.

A system with occasional deadlocks and a retry loop is fine. A system with frequent deadlocks has a lock-ordering bug worth finding.

The lock modes, briefly

For table-level locks, what matters is which conflict with which:

ACCESS SHARE          taken by SELECT. Conflicts only with ACCESS EXCLUSIVE.
ROW SHARE             SELECT ... FOR UPDATE
ROW EXCLUSIVE         INSERT, UPDATE, DELETE
SHARE UPDATE EXCLUSIVE  VACUUM, CREATE INDEX CONCURRENTLY, ANALYZE
SHARE                 CREATE INDEX (without CONCURRENTLY)
ACCESS EXCLUSIVE      ALTER TABLE, DROP TABLE, TRUNCATE, VACUUM FULL

The practical summary: ACCESS EXCLUSIVE blocks everything including SELECT. Every other pair of ordinary operations coexists.

Which is why the module 4 rule was to set lock_timeout before any schema change — and why CREATE INDEX CONCURRENTLY exists, taking the weaker SHARE UPDATE EXCLUSIVE so writes continue.

See what is held right now:

SELECT relation::regclass, mode, granted, pid
FROM pg_locks WHERE relation IS NOT NULL ORDER BY relation;

Avoiding lock trouble

The practical list, in order of how much it helps:

Keep transactions short. Most lock problems are a long transaction.

Never do network I/O inside a transaction. The single biggest cause of long ones.

Lock in a consistent order. Sort by primary key.

Prefer a conditional UPDATE to SELECT ... FOR UPDATE. No lock held across a think.

Do bulk updates in batches. UPDATE on a million rows locks a million rows for the duration. Ten thousand at a time, committing between, keeps the system usable.

Set lock_timeout for schema changes, so a migration fails rather than queueing the world behind it.

Set idle_in_transaction_session_timeout, so a forgotten session cannot hold locks forever.

Check your work

What a row lock blocks. Other writers of that row — never readers.

The MVCC property. Readers do not block writers, and writers do not block readers.

Which locks can block a SELECT. ACCESS EXCLUSIVE, taken by ALTER TABLE and friends.

The query to run when the application hangs. pg_blocking_pids over pg_stat_activity.

Cancel or terminate. Cancel first; terminate only if that fails.

What a deadlock is. Two transactions each holding what the other wants.

What PostgreSQL does about it. Detects it after deadlock_timeout and aborts one.

The usual cause. Locks taken in different orders.

The usual fix. A consistent order — sort by primary key.

Where the detail is. The server log, not the client error.

What log_lock_waits gives you. Contention visible before it becomes a deadlock.

Whether a deadlock is retryable. Yes — 40P01, retry the whole transaction.

Why bulk updates should be batched. A million-row UPDATE locks a million rows for its duration.

Practice

1. In two sessions, update the same row without committing the first. Watch the second wait.
2. Run the pg_blocking_pids query and identify the blocker.
3. Cancel the blocking query with pg_cancel_backend and watch the waiter proceed.
4. Reproduce a deadlock with two sessions updating two rows in opposite orders.
5. Read the full error, including the DETAIL line naming both processes.
6. Find the same deadlock in the server log and compare the detail.
7. Fix it by sorting the ids and confirm the two sessions now queue.
8. Turn on log_lock_waits and create a wait longer than deadlock_timeout.
9. Look at deadlock_timeout and reason about what lowering it would cost.
10. Run ALTER TABLE while a long SELECT is open, and watch a third session's SELECT queue behind the ALTER.
11. Do it again with SET lock_timeout = '2s' and confirm the ALTER fails instead.
12. Look at pg_locks during a transaction and identify every lock it holds.
13. Update 200,000 rows in one statement, then in batches of 10,000, and compare how long other sessions are blocked.
14. Write a retry loop that handles both 40001 and 40P01.

Official documentation

• PostgreSQL — Explicit locking — Every lock mode, the conflict table, deadlocks and advisory locks. The reference page for this lesson.
• PostgreSQL — pg_locks — What is held and what is waiting.
• PostgreSQL — Monitoring functions — pg_blocking_pids, pg_cancel_backend, pg_terminate_backend.
• PostgreSQL — Error reporting and logging — log_lock_waits and deadlock_timeout.

Next: upserts.