Locking, Deadlocks & Advisory Locks

Learning outcomes

• Resolve transaction deadlock chains
• Protect critical sections using application advisory locks

Mental model

PostgreSQL manages concurrent access through a hierarchy of lock modes ranging from ACCESS SHARE (concurrent reads) to ACCESS EXCLUSIVE (exclusive table DDL modification).

Theory

When two transactions wait for locks held by each other, a Deadlock occurs. PostgreSQL's background deadlock detector inspects lock dependency graphs after deadlock_timeout (default 1s) and aborts one of the transactions with a deadlock detected error.

Application developers can also leverage PostgreSQL Advisory Locks (pg_advisory_lock(key)) to create application-level mutexes without creating dummy database rows.

-- Acquire transactional row lock
SELECT * FROM inventory WHERE item_id = 501 FOR UPDATE;

-- Application-level advisory lock (session-scoped)
SELECT pg_advisory_lock(123456);
-- ... Execute critical section ...
SELECT pg_advisory_unlock(123456);

-- Non-blocking try-lock for distributed workers
SELECT pg_try_advisory_lock(7890);

Alternatives and trade-offs

• Optimistic Concurrency: Uses version numbers (WHERE version = 1). Great for low contention; fails under heavy concurrent updates.
• Pessimistic Row Locks (FOR UPDATE): Prevents concurrent updates; risks deadlocks if lock acquisition order varies.
• Advisory Locks: Light-weight, zero-table-overhead application mutexes.

Failure modes and misconceptions

1. Inconsistent Lock Acquisition Order: Transaction A locks Account 1 then Account 2; Transaction B locks Account 2 then Account 1. This guarantees deadlocks under high concurrency. Always sort entity IDs before acquiring locks.
2. Leaking Session Advisory Locks: Failing to call pg_advisory_unlock() on session-scoped locks leaves locks active until connection termination.

Decision scenario

Use pg_try_advisory_lock(job_id) in background worker cron tasks to ensure only a single worker instance executes a distributed task at any given time.

Learning outcomes

• Classify table and row-level lock conflict matrices in PostgreSQL.
• Prevent deadlocks by enforcing deterministic lock acquisition ordering.
• Implement distributed application locks using pg_advisory_lock.

Trade-offs

Pessimistic row locks guarantee strict data consistency under concurrent modifications, but increase transaction waiting times and deadlock risks.

Primary sources

• PostgreSQL 16 Architecture, MVCC & Query Optimization Manual — PostgreSQL Global Development Group, verified 2026-07-22