sdlc.dev

SQL and PostgreSQL path · Many writers at once · Topic 10 of 13

Locks and deadlocks

Not started

MVCC removes most locking between readers and writers, but not all of it. Two transactions that change the same row must take turns, and schema changes need the table to themselves. PostgreSQL takes these locks automatically. Problems start when locks are held for a long time, or taken in an order that lets two transactions wait for each other.

Why it matters: A migration that waits for one lock can stall every query behind it, and two transactions locking rows in opposite orders will deadlock under load.

  1. 1Writers lock rows, not readers

    An UPDATE or DELETE locks the rows it changes until the transaction ends; plain SELECTs take no row locks and are never blocked by them.

  2. 2Table locks come from DDL

    Most ALTER TABLE forms take ACCESS EXCLUSIVE, which conflicts with everything, including SELECT.

  3. 3Lock queues stack up

    A DDL statement waiting for a lock also blocks every later query that needs a conflicting lock, so set lock_timeout.

  4. 4Deadlocks are detected, not prevented

    PostgreSQL notices a cycle after deadlock_timeout (1 s by default) and aborts one transaction; lock in a consistent order to avoid them.

1

How it works

The explanation, with examples you can run.

Row locks

UPDATE, DELETE and SELECT ... FOR UPDATE lock the rows they touch until the transaction commits or rolls back. Another writer of the same row waits; plain readers do not, they see the last committed version. Variants let you say what you need:

-- Lock the row you are about to change, so nobody changes it under youBEGIN;SELECT balance_cents FROM accounts WHERE id = 1 FOR UPDATE;UPDATE accounts SET balance_cents = balance_cents - 100 WHERE id = 1;COMMIT; -- Fail at once instead of waitingSELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT; -- Work queue: each worker takes a job nobody else holdsSELECT id FROM jobs WHERE state = 'ready'ORDER BY id LIMIT 1FOR UPDATE SKIP LOCKED;

FOR SHARE, FOR NO KEY UPDATE and FOR KEY SHARE are weaker modes; foreign key checks use FOR KEY SHARE on the parent row, so inserting an order does not block an update of the customer's name.

Table locks

Every statement takes a table-level lock too, but the ordinary ones (ACCESS SHARE for SELECT, ROW EXCLUSIVE for writes) do not conflict with each other. The one to fear is ACCESS EXCLUSIVE, taken by most forms of ALTER TABLE, DROP, TRUNCATE and VACUUM FULL. It conflicts with everything.

Lock requests wait in a queue. If an ALTER TABLE is waiting behind a long query, every new query on that table queues behind the ALTER, and an instant change takes the site down. Set a timeout for migrations and retry:

SET lock_timeout = '2s';ALTER TABLE orders ADD COLUMN note text;

To see who is blocking whom:

SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, left(query, 60)FROM pg_stat_activityWHERE cardinality(pg_blocking_pids(pid)) > 0;

Deadlocks

Two transfers, one from account 1 to 2 and one from 2 to 1, started together:

ERROR:  deadlock detectedDETAIL:  Process 96066 waits for ShareLock on transaction 893; blocked by process 96064.Process 96064 waits for ShareLock on transaction 894; blocked by process 96066.CONTEXT:  while updating tuple (0,11) in relation "accounts"

A waiting backend checks for a cycle after deadlock_timeout (1 second by default). If it finds one, one transaction is aborted and the others proceed. Which one is not predictable. The fixes are ordinary:

  • Take locks in a consistent order: update the lower account id first, whichever direction the money goes.
  • Keep transactions short, so the window for a cycle is small.
  • Treat a deadlock (SQLSTATE 40P01) like a serialization failure: roll back and retry.

Advisory locks

For coordination that is not about a particular row, such as "only one instance runs this job", pg_advisory_lock(key) and pg_try_advisory_lock(key) take a lock on a number of your choosing. Session-level advisory locks last until released or the connection closes, which matters behind a transaction-mode pooler; pg_advisory_xact_lock is released at the end of the transaction.

2

Check yourself

3 questions. Get them all right to finish this topic.

  1. 1.Transaction 1 updates account 1 then account 2. Transaction 2 updates account 2 then account 1. Both start at the same moment. What happens?
  2. 2.A long report query is running on orders. You run ALTER TABLE orders ADD COLUMN note text. Why do all new SELECTs on orders start hanging?
  3. 3.Several workers take jobs from a table. How can each claim a different job without waiting on rows other workers have locked?

0 of 3 right so far. Get all three to finish this topic.

Go deeper