Locks: Rows, Tables, and Deadlocks

MVCC removes most of the locking a database would otherwise need, and what remains is small enough to learn exactly. Writers of the same row queue behind each other. DDL takes a table lock that conflicts with everything, including plain reads. A foreign-key check takes a lock on the parent row that shows up during bulk loads as blocking the loader never asked for.

The payoff for learning the modes is diagnostic speed. When an insert into order_items waits on an update to products, that is not a mystery to be solved by restarting the API; it is two named lock modes that conflict, visible in pg_locks, with a fix that follows from which pair they are.

The Four Row Lock Modes

Row locks come in four strengths, and every write takes one whether or not the statement mentions it. FOR UPDATE is the strongest and blocks every other row lock on that row; a DELETE takes it, and so does an UPDATE that changes a column covered by a unique index of the kind a foreign key can use. FOR NO KEY UPDATE is what an ordinary UPDATE of non-key columns takes. FOR SHARE is the shared read lock. FOR KEY SHARE is the weakest, and it exists so that referential integrity checks stop blocking ordinary updates.

held \ requested   KEY SHARE   SHARE   NO KEY UPDATE   UPDATE
KEY SHARE              -         -           -            X
SHARE                  -         -           X            X
NO KEY UPDATE          -         X           X            X
UPDATE                 X         X           X            X

X = the requester waits.  Note the empty top-left corner:
a foreign-key check and a non-key UPDATE do not conflict.

The empty corner is the whole point of the design. An UPDATE that changes no key column takes FOR NO KEY UPDATE, a foreign-key check takes FOR KEY SHARE, and those two do not conflict — so repricing a product no longer blocks orders that reference it. What still conflicts is a genuine key change or a delete of the parent, which is correct: the reference must not be allowed to break.

-- session 1: the load, holding FOR KEY SHARE on products(4471)
BEGIN;
INSERT INTO order_items (order_id, product_id, qty, unit_price)
VALUES (918273645, 4471, 1, 3.90),
       (918273646, 4471, 2, 3.90);

-- session 2: proceeds, it changes no key column
UPDATE products SET price = 4.20 WHERE id = 4471;   -- UPDATE 1

-- session 3: waits for session 1 to commit or roll back
DELETE FROM products WHERE id = 4471;

Session 3 is not the victim of a bug. It is being told, correctly, that a transaction in flight is creating rows that reference this product, and that the delete cannot be decided until that transaction's fate is known. The lesson for a loader is scope: hold that transaction open for a two-minute batch and every attempt to delete or re-key those parent rows waits two minutes.

Where a Row Lock Lives

Postgres does not keep a memory structure describing every locked row. The claim is written into the row version itself, in the header field Chapter 5 laid out for expiring a tuple, so the documentation can state flatly that there is no limit on the number of rows locked at one time. A transaction can lock ten million rows without exhausting anything, and the same manual notes the consequence in the next breath: SELECT … FOR UPDATE modifies the rows it locks, so a read-only-looking statement produces disk writes.

When more than one transaction holds a lock on the same row, one field cannot name them all, so the id stored there becomes a multixact, an entry in a side structure that lists the actual lockers. The trade is unlimited row locks, paid for in tuple writes and multixact bookkeeping rather than in a fixed lock table. Multixacts have their own counter, their own wraparound and their own freezing rules, and they turn up again in Chapter 7.

Table Locks and the Queue Behind Them

There are eight table-level modes and three that matter day to day. A SELECT takes ACCESS SHARE. Any INSERT, UPDATE, DELETE or MERGE takes ROW EXCLUSIVE. DDL takes ACCESS EXCLUSIVE: DROP TABLE, TRUNCATE, VACUUM FULL, CLUSTER, most forms of ALTER TABLE, and a bare LOCK TABLE. That is the only mode that conflicts with a plain read. Ordinary vacuum, ANALYZE, CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY take SHARE UPDATE EXCLUSIVE, which lets writes continue.

One of those modes behaves unlike the rest, and Chapter 3 spent a topic on the consequence: an ALTER TABLE asking for ACCESS EXCLUSIVE waits behind the readers already holding the table, and everything arriving afterwards queues behind the request instead of overtaking it. What the mode list adds is the scope of that queue. ACCESS EXCLUSIVE conflicts with all eight modes, ACCESS SHARE included, so it is the only lock in the set that can stop a plain SELECT — which is why one slow reader and one DDL statement between them halt reads and writes alike on a 40-million-row table. The defence is the one Chapter 3 named, lock_timeout on the migration session.

SKIP LOCKED and NOWAIT

There are two modifiers for what a locking SELECT does when the row it wants is already claimed. NOWAIT raises an error immediately instead of waiting, which is what a job that must fail fast wants. SKIP LOCKED passes over the locked rows without complaint and returns the ones it could claim, which turns a table into a work queue.

BEGIN;
SELECT id, customer_id
  FROM orders
 WHERE status = 'pending'
 ORDER BY placed_at
 LIMIT 10
   FOR UPDATE SKIP LOCKED;

-- hand the ten claimed orders to a courier, then
UPDATE orders SET status = 'dispatched' WHERE id = ANY($1);
COMMIT;   -- the claim ends here, with the rows already moved on

Each worker claims a different ten rows because the rows the others hold are invisible to its SELECT. The price is an inconsistent view of the data, which makes this a queue-consumption tool rather than a general query modifier. Keep the batch small, since every claimed row is held until commit. And the ordinary table-level ROW SHARE lock is still taken: SKIP LOCKED exempts a worker from waiting on rows, not from the table's own lock rules.

Deadlocks

A deadlock is two transactions each holding what the other needs next, and it needs no explicit locking to arise — two UPDATE statements touching the same two rows in opposite orders is enough. Postgres does not prevent it; it detects it. A backend that has been waiting for deadlock_timeout, one second by default, checks whether the wait is part of a cycle, and if it is, one transaction is aborted with 40P01 so the other can finish.

-- session A                          -- session B
BEGIN;                                BEGIN;
UPDATE inventory SET on_hand = ...    UPDATE inventory SET on_hand = ...
 WHERE product_id = 4471;              WHERE product_id = 8802;

UPDATE inventory SET on_hand = ...    UPDATE inventory SET on_hand = ...
 WHERE product_id = 8802;              WHERE product_id = 4471;
-- waits for B                        -- waits for A

-- one second later, one of them:
-- ERROR:  deadlock detected            (SQLSTATE 40P01)
-- DETAIL: Process 24911 waits for ShareLock on transaction 487355047

The fix is ordering, always. Sort every batch by primary key before writing it and take locks in one consistent order across every code path, and the cycle cannot form. Raising deadlock_timeout is the tempting non-fix: the setting controls how long a backend waits before it bothers to run the detection algorithm, so raising it does not reduce deadlocks — it makes each one hold its locks longer before anyone notices. The documentation's own framing is that increasing it saves needless checks at the cost of slower reporting of real deadlocks. If the log needs to show the waits that precede an incident, turn on log_lock_waits, which is off by default and uses the same threshold to decide when a wait is worth recording.

Advisory Locks

Advisory locks protect an idea rather than a row. The application picks a number, typically a job id, a tenant id or a hash of a name, and calls pg_advisory_lock() or its non-blocking sibling pg_try_advisory_lock(), and Postgres enforces mutual exclusion on that number without any table being involved. It is the cheapest correct answer to "only one instance of this cron job at a time" in a fleet where every instance can reach the database and nothing else is shared.

The mode to prefer is the transaction-scoped one, pg_advisory_xact_lock(), released at the end of the transaction with no unlock to forget. Session-scoped advisory locks do not honour transaction semantics at all: one taken inside a transaction that later rolls back is still held afterwards. In a pooled connection that means the lock outlives the request, and the next borrower of that connection inherits a session holding a lock it never took.