Deadlocks, Lock Queues, Timeouts, SKIP LOCKED, NOWAIT, and Worker Queues

Learning outcomes

Locks become operational incidents when sessions wait, form cycles, or hold resources longer than the business operation. ServiceHub will create a safe deadlock, distinguish three timeout mechanisms, observe blockers, and then use row-lock skipping to coordinate multiple workers over a queue. The objective is not to eliminate all waiting—it is to make waiting bounded, observable, and semantically correct.

1. Prepare deadlock and queue tables

DROP TABLE IF EXISTS app.ch07_deadlock_item;DROP TABLE IF EXISTS app.ch07_job;CREATE TABLE app.ch07_deadlock_item (    item_id integer PRIMARY KEY,    amount integer NOT NULL);INSERT INTO app.ch07_deadlock_item VALUES (1,100), (2,200);CREATE TABLE app.ch07_job (    job_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,    job_key text NOT NULL UNIQUE,    payload text NOT NULL,    priority integer NOT NULL DEFAULT 100,    status text NOT NULL DEFAULT 'ready'        CHECK (status IN ('ready','running','done','failed')),    claimed_by text,    claimed_at timestamptz,    attempts integer NOT NULL DEFAULT 0 CHECK (attempts >= 0),    created_at timestamptz NOT NULL DEFAULT clock_timestamp());INSERT INTO app.ch07_job(job_key,payload,priority) VALUES('wo-101','dispatch north',10),('wo-102','dispatch south',10),('wo-103','replace seal',20),('wo-104','inspect pump',20),('wo-105','call customer',30);

2. A deadlock is a wait cycle, not merely a long wait

A lock wait has a holder and a waiter. A deadlock exists when a cycle means nobody in that cycle can progress without aborting one transaction. PostgreSQL detects such cycles and aborts one participant. Which transaction becomes the victim is not something application logic should depend on.

-- Session ABEGIN;UPDATE app.ch07_deadlock_item SET amount = amount + 1 WHERE item_id = 1;-- Session BBEGIN;UPDATE app.ch07_deadlock_item SET amount = amount + 1 WHERE item_id = 2;-- Session A: send this; it waits on BUPDATE app.ch07_deadlock_item SET amount = amount + 1 WHERE item_id = 2;-- While A is waiting, Session B sends this; now B waits on AUPDATE app.ch07_deadlock_item SET amount = amount + 1 WHERE item_id = 1;-- PostgreSQL detects the cycle after its deadlock detection interval.-- One transaction receives SQLSTATE 40P01 and is aborted.

ERROR:  deadlock detectedDETAIL:  Process ... waits for ...; blocked by process ...HINT:  See server log for query details.SQLSTATE: 40P01 (deadlock_detected)Exact PIDs, lock types, detail ordering, and victim vary.

The surviving transaction's blocked statement can then proceed because the victim releases its locks. Explicitly ROLLBACK the failed transaction and finish/rollback the survivor so the lab does not leave locks open.

3. Observe a wait before it becomes an incident

SELECT a.pid,       a.state,       a.xact_start,       a.wait_event_type,       a.wait_event,       pg_blocking_pids(a.pid) AS blockers,       left(a.query, 100) AS queryFROM pg_stat_activity AS aWHERE a.datname = current_database()ORDER BY a.xact_start NULLS LAST;

During a plain lock wait, expect the waiting backend to show a lock-related wait event and a blocker PID. During the brief deadlock-resolution moment the graph can change quickly; monitoring is a snapshot, not an immutable incident record. Server logs are often the durable evidence for deadlocks.

4. Three timeout concepts answer different questions

Do not set lock_timeout equal to or larger than a nonzero statement_timeout and then expect it to win; the statement timeout can fire first. Prefer transaction/session-local settings for specific workflows instead of blunt cluster-wide values.

-- Session ABEGIN;UPDATE app.ch07_deadlock_item SET amount = amount + 1 WHERE item_id = 1;-- Session BBEGIN;SET LOCAL lock_timeout = '750ms';UPDATE app.ch07_deadlock_item SET amount = amount + 10 WHERE item_id = 1;\errverboseROLLBACK;-- Session AROLLBACK;

BEGIN;SET LOCAL statement_timeout = '500ms';SELECT pg_sleep(2);\errverboseROLLBACK;-- Expected SQLSTATE: 57014 query_canceled.

5. NOWAIT is explicit fail-fast locking

When the business workflow prefers “someone else owns this resource; return immediately” over waiting, use NOWAIT. It applies to row locking; PostgreSQL still takes the required table-level lock normally.

-- Session ABEGIN;SELECT * FROM app.ch07_deadlock_item WHERE item_id = 1 FOR UPDATE;-- Session BBEGIN;SELECT * FROM app.ch07_deadlock_item WHERE item_id = 1 FOR UPDATE NOWAIT;\errverboseROLLBACK;-- Expect 55P03 lock_not_available.-- Session AROLLBACK;

6. SKIP LOCKED is for queue semantics, not general queries

SKIP LOCKED omits rows that cannot be locked immediately. PostgreSQL explicitly warns that this yields an inconsistent view of the table. That is usually unacceptable for reports, balances, or “show all matching orders.” It is useful for multiple consumers taking different jobs from a queue.

BEGIN;WITH picked AS (    SELECT job_id    FROM app.ch07_job    WHERE status = 'ready'    ORDER BY priority, created_at, job_id    FOR UPDATE SKIP LOCKED    LIMIT 1)UPDATE app.ch07_job AS jSET status = 'running',    claimed_by = 'worker-A',    claimed_at = clock_timestamp(),    attempts = attempts + 1FROM pickedWHERE j.job_id = picked.job_idRETURNING j.job_id, j.job_key, j.status, j.claimed_by, j.attempts;-- Keep transaction open briefly.

BEGIN;WITH picked AS (    SELECT job_id    FROM app.ch07_job    WHERE status = 'ready'    ORDER BY priority, created_at, job_id    FOR UPDATE SKIP LOCKED    LIMIT 1)UPDATE app.ch07_job AS jSET status = 'running',    claimed_by = 'worker-B',    claimed_at = clock_timestamp(),    attempts = attempts + 1FROM pickedWHERE j.job_id = picked.job_idRETURNING j.job_id, j.job_key, j.status, j.claimed_by, j.attempts;COMMIT;

Because A holds a row lock, B skips that candidate and claims the next ready row according to the ordering among rows it can lock. The WITH ... UPDATE ... RETURNING keeps selection and state transition inside one transaction. If A rolls back, A's state transition also rolls back and its job becomes ready again.

UPDATE app.ch07_jobSET status = 'done'WHERE status = 'running' AND claimed_by = 'worker-A'RETURNING job_id, job_key, status, claimed_by;COMMIT;

7. Fairness and abandoned-work policy are application concerns

SKIP LOCKED avoids waiting; it does not guarantee strict global fairness. A frequently locked high-priority row can be skipped repeatedly while other work proceeds. A worker that commits running and then crashes also needs a recovery protocol. Common designs record claim time, worker identity, attempt count, lease expiry, or heartbeat state, then safely requeue stale claims. The correct policy depends on whether work is idempotent and whether external side effects can be repeated.

8. A queue protocol is more than SKIP LOCKED syntax

The SQL claim query is only one state transition in a production worker protocol. Before deployment, define the lifecycle for every failure point: worker crashes before COMMIT, worker crashes after COMMIT but before starting external work, external work succeeds but the worker dies before marking the job done, and repeated failures exhaust the attempt budget. These cases determine whether jobs need leases, heartbeats, deduplication keys, an outbox/inbox protocol, or a dead-letter state.

Fairness is not guaranteed by ORDER BY once rows are skipped

The ORDER BY priority, created_at, job_id clause gives a deterministic preference among rows available to the worker. It cannot force the worker to wait for an earlier row that SKIP LOCKED deliberately skips. Under sustained contention, a repeatedly locked high-priority row can be starved. If strict fairness is a business requirement, SKIP LOCKED may be the wrong primitive or must be augmented with aging, partitions, ownership shards, or a scheduler that periodically revisits skipped work.

SELECT job_id, job_key, claimed_by, claimed_at, attempts,       clock_timestamp() - claimed_at AS claim_ageFROM app.ch07_jobWHERE status = 'running'  AND claimed_at < clock_timestamp() - interval '5 minutes'ORDER BY claimed_at, job_id;-- Do not blindly UPDATE these rows to ready in production.-- First define whether the external work can be safely repeated.

Timeouts should preserve meaning

A lock timeout and a statement timeout are not interchangeable application outcomes. A 55P03 lock timeout says the statement spent too long waiting for a lock; a 57014 statement cancellation might reflect a lock wait, CPU work, I/O, pg_sleep, or another execution phase. If the application exposes “resource busy” separately from “operation exceeded request deadline,” preserve those distinctions instead of collapsing every cancellation into a generic retry.

deadlock_timeout is also not a request latency limit. Lowering it causes PostgreSQL to run deadlock detection sooner and can increase detection overhead on normal waits; raising it delays deadlock diagnosis. It is usually an operational tuning choice informed by expected transaction duration and lock-wait logging goals, not a per-query SLA knob.

Deadlock logs are part of the debugging contract

The client receives a victim error, but server logs can provide the participating statements and process relationships needed to understand the cycle. Preserve enough application metadata—such as application_name, request identifiers outside secrets, and transaction-purpose tags in logs/traces—to connect database PIDs back to business operations. Fix the lock acquisition order or transaction design; do not simply retry forever and accept a permanent deadlock pattern as normal.

9. Verify queue state and cleanup

SELECT job_id, job_key, priority, status, claimed_by, attemptsFROM app.ch07_jobORDER BY priority, created_at, job_id;SELECT status, count(*)FROM app.ch07_jobGROUP BY statusORDER BY status;

ROLLBACK;DROP TABLE IF EXISTS app.ch07_job;DROP TABLE IF EXISTS app.ch07_deadlock_item;

10. Production judgment and bridge

Set timeouts per workload, log enough evidence to diagnose waits, and use deterministic resource ordering to prevent avoidable deadlocks. Queue consumers should define starvation, stale-claim, external-side-effect, and retry policies explicitly. Lesson 5 generalizes these ideas into a transaction-retry contract: which SQLSTATEs are transient, what “retry the whole transaction” means, and how idempotency prevents retries from duplicating business effects.

Authoritative references

• PostgreSQL 18 — Explicit Locking and Deadlocks
• PostgreSQL 18 — SELECT NOWAIT / SKIP LOCKED
• PostgreSQL 18 — Client Connection Defaults / Timeouts
• PostgreSQL 18 — Error Codes
• PostgreSQL 18 — pg_stat_activity / Monitoring