Locks, Deadlocks, and Concurrency Control

PostgreSQL locks coordinate conflicting operations, while MVCC handles visibility. Row locks protect selected rows; table locks protect broader operations; advisory locks coordinate application-defined resources. Deadlocks arise when transactions wait in a cycle, so PostgreSQL aborts one. Good designs lock narrowly, in consistent order, for short periods, with timeouts and complete retries.

What will you be able to do?

• Choose optimistic versions, row locks, atomic statements, or advisory locks.
• Use SELECT ... FOR UPDATE and SKIP LOCKED appropriately.
• Inspect blocking sessions and interpret deadlocks.
• Design consistent lock ordering and timeouts.

Prerequisites and mental model

Locks are queueing contracts. A transaction owns locks until its boundary. The question is not “do locks exist?” but “which resource, mode, order, duration, and failure response?”

How do you claim work safely?

For a database-backed job queue:

SKIP LOCKED is suitable for queue-like work where skipped rows may be processed by another worker. It is not a general consistency feature and can produce an intentionally incomplete view.

Inspect blockers with pg_stat_activity and pg_locks; use vetted queries and least privilege. Never terminate sessions until you understand owner, transaction, criticality, and rollback impact.

Deadlock prevention: access shared resources in a consistent order. If two transactions update tickets 1 and 2, both should lock lower ID first.

Why does this matter in AI-native systems?

Model workflows create long and variable work. Claim a short database job, commit, perform the model call, then store the result in another short transaction with lease/version checks. Do not hold a row lock across the call. Advisory locks may deduplicate a bounded operation, but persisted state remains necessary for recovery.

Prove it worked

Use two sessions to create a deadlock on disposable rows by locking them in opposite order; capture PostgreSQL’s deadlock error and full rollback. Repeat with consistent ordering. Then run two queue workers and prove they claim different jobs.

Production judgment

• Set task-appropriate lock_timeout and statement_timeout.
• idle in transaction sessions can retain locks and snapshots.
• Advisory locks are not automatically tied to rows or protected by foreign keys.
• Killing a backend can trigger large rollback and application retry storms.

Common mistakes

• Worker holds lock during model call: transaction too broad → lease/claim then commit.
• Deadlocks recur: inconsistent resource order → establish canonical order.
• SKIP LOCKED used for reports: incomplete results accepted → restrict to queue semantics.

AI Pair-Programmer Prompt

Hands-on exercise

Design an ai_jobs lease with locked_by, locked_until, attempts, and terminal state. Explain reclaim after worker crash.

Acceptance criterion: two workers cannot own one live lease, an expired lease is recoverable, and duplicate completion is rejected or idempotent.

Knowledge check

1. What does PostgreSQL do on a deadlock?
2. When is SKIP LOCKED appropriate?
3. Why avoid a transaction across a model call?

---

Answers

1. Detects the cycle and aborts one transaction.
2. Queue-like work where locked candidates can safely be skipped.
3. It holds locks/snapshots through unpredictable latency and failure.

Completion checklist

• Lock scope and order are documented.
• Transactions remain short.
• Deadlock and crash recovery tests exist.
• Queue semantics do not leak into normal reads.

Primary references

• Explicit locking
• Lock monitoring
• SELECT locking clauses