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.
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
- 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.
- 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.