Row Locks, Deadlocks and Queues

Learn to work with explicit row locks in PostgreSQL — observe lock waits live, reproduce and diagnose a deadlock, use NOWAIT for fail-fast behaviour, build a race-free job queue with SKIP LOCKED, and coordinate processes with advisory locks.

Concurrency bugs are the hardest to reproduce and diagnose. This course gives you a working mental model by making them happen on purpose. We build a real PostgreSQL with three tables — accounts, inventory, and a jobs queue — and run through four canonical scenarios: a row lock wait where one session blocks another, a deadlock where two sessions block each other in a cycle, a NOWAIT that fails fast instead of waiting, and a SKIP LOCKED queue where two workers pick different tasks in parallel. Along the way we read pg_stat_activity to see sessions waiting for a Lock, use pg_blocking_pids to find the culprit in one call, inspect pg_locks to see which requests are not yet granted, and use advisory locks as an application-level mutex. Every scenario is run twice: once to see what goes wrong, then with the corrective pattern applied.

What you'll build

• Open two sessions, take a FOR UPDATE lock, and watch the second session wait
• Find a blocking transaction with pg_stat_activity and pg_blocking_pids
• Read pg_locks: locktype, mode, and granted
• Reproduce a deadlock and explain why PostgreSQL terminates one session
• Prevent deadlocks with consistent lock ordering
• Use FOR UPDATE NOWAIT for immediate fail-fast on a locked row
• Build a job queue worker with FOR UPDATE SKIP LOCKED
• Take and release an advisory lock; use pg_try_advisory_lock without blocking

Contents

1. A database for lock experiments
2. Start it
3. Connect with psql
4. Two sessions side by side
5. The accounts table
6. The inventory table
7. The jobs table
8. Create the tables
9. Seed accounts with deterministic balances
10. Seed inventory items
11. Seed the job queue
12. Load the data
13. Refresh statistics and confirm the server
14. Session A: take an exclusive row lock
15. Session B: try to write the same row
16. Seeing the wait in pg_stat_activity
17. Run the lock monitor while B is waiting
18. Find the blocker with pg_blocking_pids
19. See the blocker's pid
20. Read the raw lock record in pg_locks
21. A lock waiting to be granted
22. Session A commits — B gets the lock and runs
23. Deadlock setup: session A takes row 1
24. Session B takes row 2
25. Session A now wants row 2
26. Session B wants row 1 — the cycle closes
27. Reproduce the deadlock and read the error
28. Read the DETAIL line
29. Preventing deadlocks with consistent lock order
30. Session A locks an inventory row
31. Session B refuses to wait — NOWAIT
32. The immediate error
33. The NOWAIT pattern in application code
34. The naive worker — a race condition waiting to happen
35. Worker A: skip any locked row
36. Worker B: takes the next free job
37. Both workers get different jobs
38. The complete worker pattern
39. Advisory locks: an application-level mutex
40. Session B waits for the same key
41. The advisory wait shows up in pg_stat_activity
42. Unlock releases the waiting session
43. pg_try_advisory_lock — check without blocking
44. Try to acquire a held lock — returns false
45. Advisory locks in pg_locks
46. Four patterns, four solutions
47. Lock patterns at a glance