Database Locks and Latches Explained (with PostgreSQL Examples)

A database lock is a logical control that governs concurrent access to data, held for the duration of a transaction and released at commit or rollback. A latch (in PostgreSQL, a lightweight lock or spinlock) is a short-lived guard over an in-memory data structure such as a buffer header, held for nanoseconds to microseconds while a few instructions run. Locks are deadlock-detected and queryable; latches are not. The two solve different problems at different layers, and conflating them leads to misdiagnosed contention.

Locks vs Latches: What Actually Differs

Locks protect logical objects - rows, tables, the catalog - so that two transactions don't corrupt each other's view of the data. They live as long as the transaction does, are visible through the pg_locks system view, and participate in deadlock detection. If transaction A waits on a lock held by B while B waits on a lock held by A, PostgreSQL's deadlock detector breaks the cycle by aborting one transaction.

Latches protect physical in-memory structures while a handful of CPU instructions execute. PostgreSQL implements them as lightweight locks (LWLocks) and, at the lowest level, spinlocks. An LWLock guards things like the buffer mapping table or a WAL insertion slot; it supports shared and exclusive modes and is held for the briefest possible window. A spinlock is even cheaper - a busy-wait primitive used only where the protected critical section is a few instructions long and contention is rare. Neither is deadlock-detected, because lock ordering inside the engine is designed to be acyclic.

The practical takeaway: when a query stalls, you almost always care about locks, which you can inspect. When throughput collapses under high concurrency with no lock waits, suspect LWLock contention, visible as LWLock wait events in pg_stat_activity.wait_event.

PostgreSQL Table-Level Lock Modes

PostgreSQL acquires table-level locks automatically for most statements, and you can request them explicitly with LOCK TABLE. There are eight modes. The names are historical and some are misleading - ROW EXCLUSIVE is a table-level lock, not a row lock.

The rule that matters most in production: ACCESS EXCLUSIVE conflicts with everything, including plain SELECT. A blocking ALTER TABLE that takes ACCESS EXCLUSIVE will queue behind running reads and then block all new reads behind itself. Two transactions can hold conflicting table locks only if they don't actually overlap on the conflict matrix - for example, many concurrent INSERTs coexist because ROW EXCLUSIVE does not conflict with itself.

Row-Level Locks and Advisory Locks

Row-level locks let writers coordinate on individual rows without serializing the whole table. PostgreSQL offers four strengths, weakest to strongest: FOR KEY SHARE, FOR SHARE, FOR NO KEY UPDATE, and FOR UPDATE. FOR UPDATE is the one to reach for when implementing the classic read-modify-write under contention.

-- Reserve a row so no other transaction can update or delete it
-- until this transaction commits. NOWAIT errors instead of queueing.
BEGIN;
SELECT balance FROM accounts
WHERE id = 42
FOR UPDATE NOWAIT;
-- application computes new balance here
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
COMMIT;

Advisory locks are application-defined locks PostgreSQL never associates with any table or row. You assign the meaning by choosing a 64-bit key. They are useful for coordinating work that has no natural row to lock against - a singleton background job, a leader election, a migration step.

-- Session-level advisory lock: try once, do not block.
-- Returns true if acquired, false if another session holds key 10.
SELECT pg_try_advisory_lock(10);

-- ... do the exclusive work ...

-- Release explicitly; session-level locks are not freed on COMMIT.
SELECT pg_advisory_unlock(10);

Use pg_advisory_xact_lock(key) instead when you want the lock released automatically at transaction end. Session-level advisory locks survive across transactions and are only released by an explicit unlock or session disconnect, which makes leaks easy if you forget the unlock path.

Inspecting Locks and Tuning Wait Behavior

The pg_locks view shows every lock currently held or awaited. Joined to pg_stat_activity, it tells you which session blocks which - the first question to answer in any contention incident.

-- Who blocks whom: blocked PID, blocking PID, and the blocked query.
SELECT blocked.pid          AS blocked_pid,
       blocked_act.query    AS blocked_query,
       blocking.pid         AS blocking_pid,
       blocking_act.query   AS blocking_query
FROM pg_locks blocked
JOIN pg_stat_activity blocked_act  ON blocked_act.pid  = blocked.pid
JOIN pg_locks blocking
  ON blocking.locktype = blocked.locktype
 AND blocking.database IS NOT DISTINCT FROM blocked.database
 AND blocking.relation IS NOT DISTINCT FROM blocked.relation
 AND blocking.pid <> blocked.pid
JOIN pg_stat_activity blocking_act ON blocking_act.pid = blocking.pid
WHERE NOT blocked.granted AND blocking.granted;

Two timeout settings bound how long a statement tolerates a wait. lock_timeout (default 0, disabled) aborts a statement that waits longer than the limit for any lock - set it before risky DDL so a migration fails fast instead of queuing every other query behind it. deadlock_timeout (default 1s) is how long a transaction waits on a lock before PostgreSQL runs the deadlock detector; if a cycle exists it aborts one transaction with a deadlock error, otherwise it keeps waiting. Lowering deadlock_timeout makes detection faster but runs the check more often, which costs CPU under heavy lock traffic. For deadlock specifics, see PostgreSQL deadlock detected.

Coordination across nodes adds another layer. Two-phase commit (PREPARE TRANSACTION) lets a coordinator hold a transaction in a prepared state across multiple databases, then commit or roll back atomically - prepared transactions keep their locks until resolved, so an orphaned prepared transaction silently blocks others. The transactional outbox pattern sidesteps distributed locking entirely by writing state changes and outgoing events in one local transaction, then publishing asynchronously.

Lock contention rarely announces itself cleanly. A blocked query shows up as latency somewhere downstream, while the lock holder - a long-running transaction, an idle-in-transaction session, an unindexed foreign-key check escalating to wider locks - sits elsewhere. Pulse continuously samples pg_locks and wait events, surfaces the blocking chain in real time, and traces a latency spike back to the specific transaction and statement holding the lock, so you find the holder without hand-joining system views during an incident.

Frequently Asked Questions

Q: What is the difference between a lock and a latch in a database?
A: A lock is a logical control over data such as a row or table, held for the whole transaction and protected by deadlock detection. A latch (a lightweight lock or spinlock in PostgreSQL) guards an in-memory structure for nanoseconds to microseconds and has no deadlock detection. Locks coordinate transactions; latches coordinate threads touching shared memory.

Q: How do I see what is blocking a query in PostgreSQL?
A: Query pg_locks joined to pg_stat_activity and filter for rows where granted is false to find waiting requests, then match them to the granted lock on the same resource to identify the blocking PID. PostgreSQL also exposes blocking relationships through the pg_blocking_pids() function, which returns the array of PIDs blocking a given backend.

Q: What does ROW EXCLUSIVE lock mean in PostgreSQL?
A: ROW EXCLUSIVE is a table-level lock acquired automatically by INSERT, UPDATE, and DELETE, despite the name. It does not conflict with itself, so many concurrent write statements run together, but it conflicts with SHARE and stronger modes, which is why a CREATE INDEX (taking SHARE) blocks writes.

Q: When should I use PostgreSQL advisory locks?
A: Use advisory locks when you need mutual exclusion over something with no natural row or table to lock, such as a singleton cron job, leader election, or a one-time migration step. You choose a 64-bit key as the lock identity. Prefer pg_advisory_xact_lock so the lock releases at transaction end, avoiding leaked session-level locks.

Q: What is deadlock_timeout in PostgreSQL?
A: deadlock_timeout (default 1s) is how long a transaction waits on a lock before PostgreSQL runs the deadlock detector. If a cycle is found, one transaction is aborted with a deadlock error; otherwise the wait continues. It is a detection delay, not a wait cap - use lock_timeout to bound the total wait.

Q: Does FOR UPDATE lock the whole table?
A: No. SELECT ... FOR UPDATE takes a ROW SHARE lock on the table and an exclusive row-level lock only on the rows it returns, so other transactions can still read and modify different rows. Add NOWAIT to fail immediately or SKIP LOCKED to ignore rows another transaction has already locked, a common pattern for queue workers.

Q: Can I detect latch (LWLock) contention in PostgreSQL?
A: Yes, indirectly. LWLocks are not in pg_locks, but waits on them appear in pg_stat_activity.wait_event with wait_event_type = 'LWLock'. Sampling that column over time shows which internal lock (for example WALWrite or BufferMapping) backends contend on, which points to a configuration or workload bottleneck rather than a transaction-level lock.

Related Reading

• PostgreSQL Lock Timeout Error: What causes lock timeout errors and how to bound wait time safely.
• PostgreSQL Deadlock Detected: How the deadlock detector works and how to prevent cycles.
• Transaction Isolation Levels: How isolation interacts with the locks PostgreSQL takes.
• Concurrency Control in Databases: MVCC, optimistic versus pessimistic strategies, and where locking fits.
• PostgreSQL Serialization Failure: When serializable isolation aborts a transaction instead of locking.
• PostgreSQL High CPU Usage: Diagnosing CPU spikes, including those from frequent deadlock checks and spinlock contention.