Row locks, SKIP LOCKED, lock timeouts and deadlocks

A lock is a wait until commit; only a circle of waits is a deadlock, so take shared rows in one order.

Before you read: predict

ExamHall's bulk re-grade and the proctor's "end exam" action each run in one Read Committed transaction. Re-grade updates a room's attempts one at a time in id order: attempt 11, then attempt 12. End-exam runs one statement, UPDATE attempts SET status = 'ended' WHERE room = 'R3', and its scan reaches room R3's rows in seat order: attempt 12 first, then attempt 11. Re-grade has updated attempt 11 and not committed. End-exam's UPDATE is sent and waits. Then re-grade updates attempt 12.

What happens next?

The 30-second version

Row locks last until commit, and a conflicting request waits; NOWAIT errors instead. A statement locks rows one by one as it reaches them, holding those it passed. Two transactions taking shared rows in opposite orders each wait on the other: after deadlock_timeout Postgres aborts one with 40P01. Take shared rows in one order.

A lock is a wait until commit; only a circle of waits is a deadlock, so take shared rows in one order.

How it works

Step 1 of 15

Row locks. FOR UPDATE and FOR NO KEY UPDATE are exclusive row locks; FOR SHARE and FOR KEY SHARE are shared. Each lasts until the transaction ends.

In the kitchen

a tool stays out until the member hands the whole project back → a row lock held to the end of the transaction (step 1)

Analogy: A neighbourhood tool library (community services)

Members borrow tools for weekend projects from a shared library with one librarian.

At the tool library	In Postgres
a tool stays out until the member hands the whole project back	a row lock held to the end of the transaction (step 1)
"is the drill in? if not, I'll go elsewhere"	NOWAIT (step 6)
a member with a long list collects shelf by shelf, keeping each tool while queuing for the next	a statement locking rows as it reaches them (step 7)
Ana holds the router and queues for the sander; Ben holds the sander and queues for the router	a deadlock (step 10)
the librarian looks at the queues only after someone has waited a while, then cancels one project's loans	`deadlock_timeout`, then 40P01 (step 11)
every member collects shared tools in the same order as everyone else	one lock order (step 12)

In real terms: a lock is a loan held to the end of the transaction; trouble starts only when the waits close into a circle.

Breaks down when: a librarian can phone Ben and ask for the sander back. Postgres cannot take back one row; it aborts a whole transaction, and its other changes go with it.

Contrasting case: a member who borrows one tool and returns it before taking the next. Nobody holds one tool while queuing for another, so no circle can form.

Examples, run for real

4.1 ExamHall EXH-5010: re-grade against "end exam"

During finals week a deadlock hit every 20 minutes (EXH-5010): re-grade updated a room's attempts in id order, while end-exam's one UPDATE reached them in seat order. MR !5310 makes end-exam lock the room's attempts in id order first:

room R3: attempts 11 and 12; re-grade updates 11, then 12; end-exam's one UPDATE reaches 12 first, then 11
  before MR !5310   end-exam waits at 11, holding 12 (a NOWAIT probe on 12 gets 55P03) · re-grade waits for 12 · re-grade: ERROR 40P01 deadlock detected, rolled back · end-exam commits · re-grade scores 0, 0 (lost)
  MR !5310          end-exam first runs SELECT id ... ORDER BY id FOR NO KEY UPDATE: waits at 11, holding none · re-grade updates 12, commits · end-exam ends both · re-grade scores 1, 1 · 40P01 0

Each held a row the other wanted, so one transaction was aborted (steps 7, 10–11); which one is Postgres's choice. After the MR, end-exam waited at attempt 11 holding nothing (step 12).

4.2 Ticketing: one held seat, asked for three ways

A checkout holds seat 14 FOR UPDATE in an open transaction. Another request asks for the seat:

seat 14 is held FOR UPDATE by an open checkout; another request runs SELECT id FROM seats WHERE id = 14 ...
  FOR UPDATE              waits (pg_blocking_pids) until the checkout commits, then locks seat 14 · 1 row
  FOR UPDATE NOWAIT       at once: ERROR 55P03 could not obtain lock on row in relation "seats"
  FOR UPDATE SKIP LOCKED  at once: 0 rows (seat 14 passed over)

A plain lock waits for the holder's commit (step 1); NOWAIT turns the wait into an error (step 6); SKIP LOCKED returns only what it could lock (step 9).

Picture it

two workers at two separate benches face each other: one in a green shirt holds a large green adjustable wrench, the other, grey-haired, in a blue shirt, holds a blue sledgehammer; a vise, a screwdriver and a mallet lie on the benches. The asset map describes each as waiting for the tool the other holds; the picture itself shows only the holding. — An illustration to help it stick. The mechanism is in the sections above.

Try it

Answer first, then check. Nothing is saved.

Question 1 of 7

A ticketing service's refund workers claim jobs from refunds (id, status, worker), where refunds 1–5 are 'queued'. Each worker runs this in its own Read Committed transaction:

UPDATE refunds SET status = 'running', worker = $1
WHERE id = (SELECT id FROM refunds WHERE status = 'queued' ORDER BY id LIMIT 1)
RETURNING id;

Worker A's claim returns refund 1 and is not yet committed. Worker B's claim is sent and waits. Then A commits.

What does B's claim return?

Question 2 of 7

A ticketing service swaps two seats' holders in one Read Committed transaction: UPDATE seats SET holder = $2 WHERE id = $1 for the first seat, then for the second, in the order the request names them. Its connection pool has 10 connections. When one request swaps seats 21 and 22 while another swaps 22 and 21, one of them fails with 40P01.

In a test that runs three such overlapping pairs, which one change lets all six swaps commit with no 40P01?

Question 3 of 7

A ticketing database has events (id, seats_left) and orders (id, event_id REFERENCES events (id), seat). A seat-map refresh runs SELECT seats_left FROM events WHERE id = 7 FOR UPDATE and keeps its Read Committed transaction open while it recomputes the map. Meanwhile a buyer's checkout runs INSERT INTO orders (event_id, seat) VALUES (7, 'F12').

What happens to the checkout?

Question 4 of 7

A ticketing database has orders (id, status) and refunds (id, order_id REFERENCES orders (id), amount). Three Read Committed transactions are open, none committed: support ran UPDATE orders SET status = 'disputed' WHERE id = 31, an admin ran DELETE FROM orders WHERE id = 32, and a report ran SELECT status FROM orders WHERE id = 33 FOR SHARE. Now three refund requests, each in its own session, run INSERT INTO refunds (order_id, amount) VALUES ($1, 20) for orders 31, 32 and 33.

How many of the three INSERTs wait while those three transactions are still open?

Question 5 of 7

Ten refund workers share refunds (id, status, worker). Write the one statement each worker runs in its own transaction, so that workers claiming at the same moment each get a different queued refund, none waits for another, and none ever gets a refund another worker holds.

How would you write that statement?

Your answer (optional, stays on this page)

Question 6 of 7

Two seat-map jobs for event 7 run at the same moment, each in its own Read Committed transaction:

SELECT seats_left FROM events WHERE id = 7 FOR SHARE;
-- recompute the seat map
UPDATE events SET seats_left = seats_left - 1 WHERE id = 7;

Both SELECTs return; job 1's UPDATE waits, and then job 2's UPDATE fails with 40P01.

Which one change lets both jobs commit with no 40P01 and seats_left down by 2?

Question 7 of 7

A ticketing database's seat-map refresh runs SELECT seats_left FROM events WHERE id = $1 FOR UPDATE and keeps its transaction open while it recomputes the map. Checkouts, which insert into orders (id, event_id REFERENCES events (id), seat), wait for it. A second refresh of the same event must still wait for the first.

Which one change to the refresh's SELECT lets checkouts insert at once and keeps a second refresh waiting?

Go deeper

Pro tips

Bound lock waits on the application's role. When: configuring a service's database role (step 15). Without lock_timeout, a request waits behind a forgotten transaction for as long as it stays open; with it, the request fails fast and its connection returns to the pool. Set it per role, for example ALTER ROLE app SET lock_timeout = '2s'. Verify (RAN, tips.mjs):
```
seat 14 held by an open checkout · UPDATE with no lock_timeout: waits until the holder commits, then updates 1 row
SET LOCAL lock_timeout = '50ms' · same UPDATE: ERROR 55P03 canceling statement due to lock timeout, while the holder is still open
```

Read the deadlock report before changing code. When: a 40P01 appears in the logs (steps 10–12). The server log's deadlock detected entry names each waiting process, what it waits for and which process blocks it; the two statements show the two lock orders to align. Verify (RAN, tips.mjs):

ERROR: deadlock detected
DETAIL: Process re-grade waits for ShareLock on transaction T1; blocked by process end-exam.
  Process end-exam waits for ShareLock on transaction T2; blocked by process re-grade.
  Process re-grade: UPDATE attempts SET n = n + 1 WHERE id = $1
  Process end-exam: UPDATE attempts SET status = 'ended' WHERE room = 'R3'
HINT: See server log for query details.
STATEMENT: UPDATE attempts SET n = n + 1 WHERE id = $1

Source

Every graded key is executed on PostgreSQL 17.11; these passages are what the steps say. Valid as of 2026-10-18.

PostgreSQL 17 documentation, 13.3 Explicit Locking (the row's first source), https://www.postgresql.org/docs/17/explicit-locking.html (pg17-explicit-locking.txt).
- §13.3.2 Row-Level Locks: FOR UPDATE "prevents them from being locked, modified or deleted by other transactions until the current transaction ends" (step 1); "The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values of certain columns" (step 2); FOR NO KEY UPDATE "will not block SELECT FOR KEY SHARE commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock." (steps 2–3); FOR SHARE "blocks other transactions from performing UPDATE, DELETE, SELECT FOR UPDATE or SELECT FOR NO KEY UPDATE on these rows, but it does not prevent them from performing SELECT FOR SHARE or SELECT FOR KEY SHARE" (step 4); FOR KEY SHARE: "SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE" (step 4).
- §13.3.4 Deadlocks: "PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete. (Exactly which transaction will be aborted is difficult to predict and should not be relied upon.)" (step 11); "if both transactions had updated the rows in the same order, no deadlock would have occurred" (step 12); "One should also ensure that the first lock acquired on an object in a transaction is the most restrictive mode that will be needed for that object" (step 13); "deadlocks can be handled on-the-fly by retrying transactions that abort due to deadlocks" (step 14); "So long as no deadlock situation is detected, a transaction seeking either a table-level or row-level lock will wait indefinitely for conflicting locks to be released." (step 15).
PostgreSQL 17 documentation, SELECT, The Locking Clause (the row's second source), https://www.postgresql.org/docs/17/sql-select.html (pg17-sql-select.txt): "With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately. With SKIP LOCKED, any selected rows that cannot be immediately locked are skipped." (steps 6, 9); "When a locking clause appears in a sub-SELECT, the rows locked are those returned to the outer query by the sub-query." (step 9).
PostgreSQL 17 documentation, 19.12 Lock Management, https://www.postgresql.org/docs/17/runtime-config-locks.html (pg17-runtime-config-locks.txt): deadlock_timeout "is the amount of time to wait on a lock before checking to see if there is a deadlock condition"; "When log_lock_waits is set, this parameter also determines the amount of time to wait before a log message is issued about the lock wait." (step 11).
PostgreSQL 17 documentation, 19.11 Client Connection Defaults, https://www.postgresql.org/docs/17/runtime-config-client.html (pg17-runtime-config-client.txt): lock_timeout and statement_timeout.
PostgreSQL 17 documentation, Appendix A. Error Codes, https://www.postgresql.org/docs/17/errcodes-appendix.html (pg17-errcodes.txt): 40P01 deadlock_detected, 55P03 lock_not_available, 57014 query_canceled (steps 6, 11, 15).
Tools, not sources: PostgreSQL 17.11 (postgres:17-alpine, local image), pg 8.23.0 (npm), whose behaviour the scripts grade as executed fact about those versions.

Checked against the tools and docs as of 2026-10-18.

Predict first