Row and Table Locks

1. Introduction

Locks are the mechanism PostgreSQL uses to prevent concurrent transactions from corrupting each other's data. Row-level locks (FOR UPDATE, FOR SHARE) prevent two transactions from modifying the same row simultaneously. Table-level locks prevent conflicting DDL operations (ALTER TABLE, DROP) from running while DML is active. Understanding lock modes and their compatibility matrix is essential for diagnosing deadlocks and performance bottlenecks.

2. Why It Matters

Lock contention is the primary cause of throughput degradation in high-concurrency databases. An unoptimized migration that takes an ACCESS EXCLUSIVE lock can block all reads and writes for minutes. Deadlocks cause transaction aborts that cascade into application errors. Knowing which lock modes conflict lets you design DDL operations and application queries that minimize blocking.

3. Real-World Analogy

Think of a shared office printer. A row lock is like putting a "in use" flag on a specific document you're editing — others can print different documents but can't touch yours. A table lock is like locking the entire printer room — nobody can print at all until you're done. The more granular the lock, the higher the concurrency.

4. How It Works

Row-level locks (acquired by DML or explicit locking clauses):

• FOR UPDATE: Exclusive row lock — blocks other FOR UPDATE, FOR SHARE, and FOR KEY SHARE. Used by UPDATE/DELETE.
• FOR NO KEY UPDATE: Similar but weaker — doesn't block FOR KEY SHARE. Used by UPDATE that doesn't touch unique columns.
• FOR SHARE: Shared lock — blocks FOR UPDATE but allows other FOR SHARE.
• FOR KEY SHARE: Weakest row lock — only blocks FOR UPDATE that modifies unique key columns.

Table-level locks (8 modes from ACCESS SHARE to ACCESS EXCLUSIVE) are acquired automatically by DDL and DML. The most critical: ACCESS EXCLUSIVE (blocks everything, used by ALTER TABLE, DROP) and SHARE UPDATE EXCLUSIVE (used by VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY).

5. Internal Architecture

Row locks are stored in the row's t_infomask header bits and the multixact system (for shared locks held by multiple transactions). Table locks are managed in the LockManager shared memory hash table. The pg_locks view exposes all active locks. Deadlock detection runs when a process waits longer than deadlock_timeout (default 1 second) — it builds a wait-for graph and looks for cycles.

6. Visual Explanation

The diagram shows the table-level lock compatibility matrix and a deadlock scenario where Transaction A holds a lock Transaction B needs, and vice versa.

7. Practical Example

8. Common Mistakes

• Using FOR UPDATE when FOR SHARE suffices: Over-locking reduces concurrency. Use FOR SHARE for read-then-validate patterns.
• Locking rows in different orders: Transaction A locks row 1 then 2, Transaction B locks row 2 then 1 — classic deadlock. Always lock in a consistent order (e.g., by ID ascending).
• Running ALTER TABLE without CONCURRENTLY: Adding an index without CREATE INDEX CONCURRENTLY takes a SHARE lock that blocks writes.
• Ignoring lock_wait_timeout: Without it, a blocked query waits forever. Set lock_timeout to fail fast.

9. Quick Quiz

10. Scenario-Based Challenge

Two microservices process orders concurrently. Service A reads an order row and locks it with FOR UPDATE, then tries to update the customer table. Service B locks the customer row first, then tries to update the order. Create a deadlock scenario, demonstrate the error, and fix it by establishing a consistent lock ordering.

11. Debugging Exercise

12. Interview Questions

• Q: What's the difference between row-level and table-level locks?
  A: Row locks block only concurrent modifications to specific rows (high concurrency). Table locks block operations on the entire table (lower concurrency). Row locks are stored in tuple headers; table locks in the LockManager.
• Q: How does PostgreSQL detect deadlocks?
  A: When a process waits longer than deadlock_timeout (1s default), the system builds a wait-for graph and checks for cycles. If found, one transaction is chosen as the victim and aborted.

13. Production Considerations

• lock_timeout: Always set this (e.g., 5-10s) to prevent indefinite lock waits from cascading failures.
• CREATE INDEX CONCURRENTLY: Never use plain CREATE INDEX on production tables — it takes a SHARE lock that blocks all writes.
• ALTER TABLE tricks: Some ALTER operations (ADD COLUMN with default, SET NOT NULL) can avoid ACCESS EXCLUSIVE in PG 11+. Others still require it.
• Monitoring: Alert on pg_locks WHERE NOT granted — any row waiting more than a few seconds indicates contention.