Deadlocks & Lock Types
A deadlock occurs when two transactions each hold a lock the other needs, waiting forever. PostgreSQL detects deadlocks and aborts one transaction with an error. Deadlocks are always caused by inconsistent lock ordering.
Deadlock is two cars at an intersection, each blocking the other, neither able to move. PostgreSQL is the traffic cop who flips a coin and sends one car backward (aborts transaction). Prevention: build a roundabout (consistent lock order).
PostgreSQL lock types: ShareLock (SELECT FOR SHARE), ShareRowExclusiveLock, ExclusiveLock (for index updates), AccessExclusiveLock (for ALTER TABLE, VACUUM FULL — blocks all access). Row-level locks: FOR UPDATE (exclusive row lock), FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE. Lock queues: a long-running transaction holding a lock causes all subsequent requests to queue, even readers (with AccessExclusiveLock). Prevention: always acquire locks in a consistent order across transactions (alphabetical table order, ascending ID order).
Deadlock example: T1 locks row A, then row B; T2 locks row B, then row A. They deadlock. Fix: both transactions must lock in the same order (A then B). PostgreSQL checks for deadlocks approximately every deadlock_timeout (default 1s) — don't reduce this to 0. Lock monitoring: pg_locks and pg_stat_activity reveal current locks and waiting queries. Aggressive locks: ALTER TABLE acquires AccessExclusiveLock — blocks all reads and writes for the duration. For zero-downtime schema changes: pg_repack, online DDL patterns (add nullable column first, then populate, then add constraint), or Percona Online Schema Change (MySQL). Advisory locks provide application-level locking (pg_advisory_lock) without associating to rows — useful for distributed coordination.
Deadlocks happen when transactions acquire locks in different orders. Prevention: consistent lock ordering (always lock tables and rows in the same sequence). Detection: PostgreSQL automatically resolves them by aborting one transaction. For production, I monitor pg_locks for long-held locks, set statement_timeout to prevent runaway queries, and design migrations as non-blocking (add nullable columns, populate in batches, then add constraints).
Running ALTER TABLE on a large production table without preparation. It acquires AccessExclusiveLock, blocking all reads and writes for potentially minutes. Use concurrent index creation (CREATE INDEX CONCURRENTLY) and stepwise migrations instead.