PostgreSQL Locks and Deadlocks

The previous article on transaction isolation mentioned that PostgreSQL's MVCC ensures reads and writes do not block each other, so a SELECT is not held up by an UPDATE, and an UPDATE is not slowed down by a SELECT. But that is only half the story: when two transactions modify the same row at the same time, what prevents a conflict? Locks do.

Locks arbitrate concurrent writes, and they are also a common source of performance bottlenecks. Use them correctly, and the system runs smoothly; use them poorly, and slow queries, deadlocks, and timeouts arrive together. This article explains PostgreSQL's locking mechanisms in full: row locks, table locks, advisory locks, deadlocks, and how to minimize lock contention.

1. Why Locks Are Necessary: The Final Gate for Correctness

In a concurrent environment, allowing two transactions to modify the same row without constraints would cause lost updates, dirty writes, and broken constraints. A lock is fundamentally a queue: only one transaction at a time holds the right to modify a row, while other transactions either wait or move on.

Locks are exclusive, and queues imply waiting. The more lock conflicts there are, the lower the concurrency. The design philosophy for locks is therefore always: use only what you need, keep the granularity as fine as possible, and hold locks for as little time as possible.

PostgreSQL locks fall into two broad categories:

• Row-level locks: Apply to an individual row, technically a tuple. Their fine granularity makes them the primary tool for highly concurrent writes.
• Table-level locks: Apply to an entire table. They are essential for DDL but have coarse granularity.

There is also a special type called an advisory lock. It does not lock data; it locks an agreed-upon key and relies entirely on application-level coordination.

2. Row Locks: The Basic Unit of Write Contention

Row locks are the locks developers encounter most often. They come from two sources: implicit and explicit locking.

Implicit row locks: UPDATE, DELETE, INSERT, and INSERT ... ON CONFLICT DO UPDATE automatically acquire exclusive row locks on affected rows. The locks remain held until the transaction ends with COMMIT or ROLLBACK. You do not need to write any LOCK keyword; PostgreSQL handles it for you.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- This row is locked by the current transaction; other transactions trying to modify it will block
-- The lock remains until this COMMIT or a ROLLBACK
COMMIT;

Explicit row locks: The SELECT ... FOR UPDATE family locks rows as they are read. It offers four strengths, from weakest to strongest:

FOR UPDATE, which is fully exclusive, is the most common choice. FOR NO KEY UPDATE is the level acquired implicitly by UPDATE. If you only need to read a row before changing a non-key field, it is friendlier than FOR UPDATE because other sessions can still use FOR KEY SHARE without interference.

One important detail: row locks are recorded in the tuple header and do not consume shared memory. Even if you lock one million rows, you will not exhaust a lock table as you might in some other databases. This is a major strength of PostgreSQL's row-locking design.

3. Table Locks: DDL and Explicit Locking

In addition to row locks, PostgreSQL has eight table-lock levels that form a partially ordered conflict matrix. Developers rarely notice them because row locks already handle most highly concurrent writes. Table locks mainly appear in two places: DDL and explicit LOCK TABLE statements.

DDL operations change table structure and must acquire exclusive control of the table. For example:

ALTER TABLE orders ADD COLUMN status INT;
-- Internally requires AccessExclusiveLock, blocking all reads and writes on this table

AccessExclusive is the strongest table-lock level. It waits for all active queries and transactions to finish, then exclusively holds the table until the DDL completes. Running ALTER on a large production table is a high-risk operation. If the table has a long-running transaction or query, ALTER waits in line. Once it finally acquires the lock, every later request must wait for it in turn, making the service appear frozen. Schema changes on large production tables should use no-lock or low-lock approaches such as pg_repack and CREATE INDEX CONCURRENTLY; do not run a bare ALTER.

Explicit table locking uses LOCK TABLE. It is uncommon in ordinary application code and mainly appears during bulk data migrations:

BEGIN;
LOCK TABLE accounts IN SHARE MODE;  -- Blocks writes but allows reads
-- Perform operations that require a table-level consistent snapshot
COMMIT;

Remember one rule: use row locks whenever they are sufficient. Table locks are the nuclear option and should not be used unless necessary.

4. Advisory Locks: A Distributed Lock That Does Not Lock Data

Sometimes an application needs synchronization without locking any actual data, such as for rate limiting, job scheduling, or distributed mutual exclusion across multiple microservices. Advisory locks, provided through pg_advisory_lock, are convenient for these cases.

An advisory lock does not lock any table or row; it locks only an agreed-upon key represented by one or two bigint values. Whoever acquires the lock for that key owns the critical section. It is purely a coordination protocol between sessions.

-- Session-level: released only explicitly or when the connection closes
SELECT pg_advisory_lock(12345);
-- ... execute critical-section code ...
SELECT pg_advisory_unlock(12345);

-- Transaction-level: released automatically when the transaction ends (recommended, prevents leaks)
BEGIN;
SELECT pg_advisory_xact_lock(67890);
-- ... this code executes serially within the same transaction ...
COMMIT;  -- The lock is released automatically

The core difference between the two variants is that pg_advisory_lock is session-level and remains held indefinitely if you forget to unlock it, while pg_advisory_xact_lock is transaction-level and is automatically released after COMMIT or ROLLBACK. Its lifecycle is bound to the database transaction, so leaks are extremely unlikely. The transaction-level variant is strongly recommended. Wrap the critical section in a transaction, and the lock lifecycle stays aligned with the transaction, keeping the design clean and safe.

Advisory locks are highly practical as distributed locks within a single PostgreSQL instance, such as when multiple workers compete for jobs. But keep the limitation clear: they work only between sessions connected to the same PostgreSQL instance and have no meaning across instances. True distributed locking across data centers still requires Redis, etcd, or a dedicated lock service.

5. Deadlocks: Transactions Waiting on Each Other, Automatically Detected by PostgreSQL

Deadlocks are the dark side of locking. The classic scenario is:

Transaction A: locks R1, then tries to lock R2
Transaction B: locks R2, then tries to lock R1

A waits for B to release R2, while B waits for A to release R1. Neither can proceed, producing a deadlock. Without intervention, both transactions would remain stuck forever.

PostgreSQL prevents that. A background deadlock detector wakes once per second by default, as controlled by deadlock_timeout, and scans the lock wait graph. When it finds a cycle, it immediately chooses one transaction, terminates it, and rolls it back, raising SQLSTATE 40P01 deadlock detected. The other transaction can then continue.

-- The terminated transaction receives:
-- ERROR: deadlock detected
-- DETAIL: Process ... waits for ShareLock on transaction ...; blocked by ...

This is an automatic safeguard, but one transaction is rolled back in full and the application must catch the error and retry. Deadlocks are therefore not the disaster; having no retry logic is the disaster.

The fundamental solution is not to depend on detection, but to eliminate the root cause of deadlocks: inconsistent lock acquisition order. The following sections explain how.

6. Concurrency Control with SELECT FOR UPDATE

The most complete practical use of row locks is SELECT ... FOR UPDATE. It makes compound operations of the form 'read, calculate, then write back' atomic. Consider debiting an account:

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- The application reads balance = 500
-- The application calculates 500 - 100 = 400
UPDATE accounts SET balance = 400 WHERE id = 1;
COMMIT;

Without FOR UPDATE, two transactions could both read 500 and independently calculate 400, with the later write overwriting the earlier one. That is a lost update. With FOR UPDATE, the second transaction queues at the SELECT, waits for the first transaction to COMMIT, and then reads the latest value. Modifications to that row are serialized.

Two common modifiers are worth remembering:

NOWAIT: If the lock cannot be acquired, fail immediately instead of waiting.

SELECT balance FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- Lock is held → ERROR: could not obtain lock on row

This suits cases where waiting is less desirable than abandoning the operation, such as an idempotency check for repeated user clicks.

SKIP LOCKED: Skip locked rows and immediately process rows that can be locked.

SELECT id FROM task_queue
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 10;

This is an ideal primitive for task queues. Multiple workers can claim jobs simultaneously, each receiving different unlocked rows and processing them in parallel without interference. It has been available since PostgreSQL 9.5; if you are still using an older version, it is time to upgrade.

7. Reducing Lock Contention: Four Practical Rules

Using more locks does not necessarily make a system safer; using fewer locks makes it faster. In practice, I follow four strict rules for reducing lock contention:

1. Keep transactions short and hold locks for as little time as possible. Locks are released only when the transaction ends, so longer transactions hold locks longer and increase the chance of conflicts. Never call an external HTTP API, send email, or perform expensive computation inside a database transaction. These operations can extend the transaction by several seconds, keeping locks held for the same duration. Under concurrency, the system quickly collapses. Move external calls outside the transaction and keep only database operations inside it. This is foundational practice.

2. Use a consistent lock order to eliminate deadlocks at the source. The root cause of deadlocks is inconsistent lock acquisition order. If all code acquires locks in ascending primary-key order, the pattern where A locks R1 and waits for R2 while B locks R2 and waits for R1 cannot occur. Every transaction queues in the same order. When updating multiple rows, use WHERE id IN (...) ORDER BY id or directly use UPDATE ... WHERE id IN (ordered_list) so PostgreSQL also acquires locks in order. Once this rule is established, deadlocks nearly disappear.

3. Use optimistic locking when conflicts are rare and pessimistic locking when they are frequent. Pessimistic locking with FOR UPDATE is reliable under frequent contention but provides lower concurrency. Optimistic locking, using a version column plus compare-and-swap, introduces almost no lock waits when conflicts are rare and provides high throughput.

-- Optimistic locking: a version mismatch means another transaction changed the row;
-- affected_rows = 0, so the application retries
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = :read_version;

For read-heavy, write-light workloads with a low probability of conflict, which describes most web applications, prefer optimistic locking because it creates almost no lock waits. Use pessimistic locking when contention is high, such as frequent debits against a hot account.

4. Do not raise the isolation level indiscriminately. Although SERIALIZABLE does not rely on explicit locks, it uses predicate locks internally to track dependencies. Under highly concurrent writes, throughput drops significantly, and the application must retry 40001 errors. The default READ COMMITTED level combined with explicit FOR UPDATE where necessary usually offers the best cost-benefit ratio.

8. Diagnosing Lock Problems with the pg_locks View

When production requests all appear to be hanging, lock waits are often the cause. PostgreSQL provides built-in views for diagnosis.

See which locks currently exist:

SELECT relation::regclass, locktype, mode, pid, granted
FROM pg_locks
WHERE NOT granted;  -- Show only locks that have not been acquired and are still waiting

See who is waiting for whom, forming the lock wait graph:

SELECT
  blocked.pid     AS blocked_pid,
  blocked.query   AS blocked_query,
  blocking.pid    AS blocking_pid,
  blocking.query  AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks ul ON ul.locktype = bl.locktype
                 AND ul.relation IS NOT DISTINCT FROM bl.relation
                 AND ul.granted
JOIN pg_stat_activity blocking ON blocking.pid = ul.pid;

Running this SQL immediately reveals who is blocking whom and which queries they are executing. Once you identify the culprit, terminate an abnormal long-running transaction directly with pg_terminate_backend(pid). If it is a legitimate long-running query, return to the application design and shorten the transaction or split the operation.

One frequently overlooked point is that long-running transactions do more than hold locks: they also block VACUUM and prevent autovacuum from reclaiming dead tuples, causing table bloat. When investigating lock problems, also check pg_stat_activity for sessions that have remained idle in transaction for hours. These transactions that forgot to COMMIT are PostgreSQL's leading hidden performance killer.

Summary

1. Locks arbitrate concurrent writes: MVCC prevents reads and writes from blocking each other, but two transactions modifying the same row must queue through locks. Finer granularity and shorter hold times improve concurrency.
2. Row locks are the basic unit: UPDATE, DELETE, and INSERT acquire them implicitly, while SELECT FOR UPDATE acquires them explicitly. The four strengths range from FOR KEY SHARE to FOR UPDATE, and locks remain held until the transaction ends. PostgreSQL records row locks on tuples rather than in shared memory.
3. Table locks mainly come from DDL: ALTER TABLE acquires AccessExclusiveLock over the whole table, so schema changes on large production tables require low-lock approaches. Use row locks whenever they are sufficient.
4. Advisory locks are application-level distributed locks: pg_advisory_xact_lock locks an agreed-upon key rather than data and is released automatically with the transaction, making it suitable for job scheduling and rate limiting. It works only within one PostgreSQL instance.
5. PostgreSQL detects deadlocks automatically and terminates one transaction, but consistent lock acquisition order is the fundamental solution. The four-part approach to reducing contention is short transactions, consistent ordering, optimistic instead of pessimistic locking when appropriate, and avoiding unnecessarily strict isolation levels. Use the pg_locks view to build the lock wait graph during diagnosis.

In one sentence: The goal is not fewer locks, but more precise locks. Keep transactions short, use a consistent order, and choose the right granularity; do those three things well, and deadlocks and lock waits will stop becoming late-night emergencies.