PostgreSQL Locks and Deadlocks: How They Work and How to Fix Them

Table, row, and advisory locks, safe migrations, deadlock detection, and the queries to run when production is stuck

PostgreSQL lets thousands of queries run at once without trampling each other's data. Locks are what make that safe. Deadlocks are what you get when the waiting goes in a circle.

Thanks to multi-version concurrency control (MVCC), a reader never blocks a writer, and a writer never blocks a reader. Every query works from a consistent snapshot. But two writers changing the same row, or a schema change on a busy table, still have to take turns. That waiting is where slow queries, stuck migrations and deadlock detected errors come from.

This post explains how Postgres locks work, how they go wrong, and how to fix them. Each idea comes with a runnable example and a real-world use case.

In this post:

• The three lock levels: table, row and advisory
• Why one ALTER TABLE can freeze an app, and how to migrate safely
• How deadlocks form, and how Postgres detects and breaks them
• Prevention patterns: consistent ordering, short transactions, retries and SKIP LOCKED
• Diagnosis: the queries to run when production is stuck

The big picture: three lock levels

Postgres locks at three levels. Most locks are taken for you. You choose explicitly only at the row and advisory levels.

three lock levels · table, row, advisory

Each level matters in a different situation:

• Table-level: schema changes and bulk operations such as ALTER TABLE and CREATE INDEX.
• Row-level: many transactions updating the same rows, such as balances, counters and inventory.
• Advisory: your application needs a mutex across servers, such as one nightly job at a time.

Locks live until the transaction ends. Postgres releases table and row locks at COMMIT or ROLLBACK, not when the statement finishes. This one rule explains most lock trouble, because a long transaction holds its locks for its whole lifetime.

Table-level locks

Every statement takes a table-level lock automatically. There are eight modes. Four of them cover most of what you will meet:

Mode	Taken by	In plain English
ACCESS SHARE	SELECT	The weakest lock. Only ACCESS EXCLUSIVE conflicts with it
ROW EXCLUSIVE	INSERT, UPDATE, DELETE	Many writers can hold it on one table at once
SHARE	CREATE INDEX (without CONCURRENTLY)	Others can read, but nobody can write
ACCESS EXCLUSIVE	ALTER TABLE (most forms), DROP TABLE, TRUNCATE, VACUUM FULL	Blocks everything, including plain SELECT

The other four modes sit in between. For example, CREATE INDEX CONCURRENTLY and regular VACUUM take SHARE UPDATE EXCLUSIVE, which lets reads and writes continue.

Can two sessions hold these at once?

This matrix shows which of the four modes can coexist on the same table.

	ACCESS SHARE	ROW EXCLUSIVE	SHARE	ACCESS EXCLUSIVE
ACCESS SHARE	Yes	Yes	Yes	No
ROW EXCLUSIVE	Yes	Yes	No	No
SHARE	Yes	No	Yes	No
ACCESS EXCLUSIVE	No	No	No	No

Two writers never block each other at the table level, because ROW EXCLUSIVE does not conflict with itself. If they collide, it happens at the row level (next section).

Example: a schema change that blocks reads

-- Session A
BEGIN;
ALTER TABLE orders ADD COLUMN note text;  -- takes ACCESS EXCLUSIVE
-- transaction stays open...

-- Session B
SELECT count(*) FROM orders;              -- needs ACCESS SHARE, so it waits

Session B hangs until Session A commits or rolls back. Remember the rule from earlier: locks live until the transaction ends.

Use case: running a migration without an outage

The dangerous case is not the ALTER TABLE itself, which is often instant. It is the waiting. Postgres queues lock requests in order, so a pending ACCESS EXCLUSIVE request makes every later query line up behind it.

lock queue · how one migration stalls an app

A safe migration practice has four parts:

• Set a lock_timeout so the migration gives up instead of queuing the whole app behind it.
• Build indexes with CREATE INDEX CONCURRENTLY so writes continue. It cannot run inside a transaction block, and a failed run can leave an invalid index to drop.
• Check for long-running transactions in pg_stat_activity before you start.
• Keep the migration transaction short and retry later if it times out.

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;  -- fails fast if it cannot get the lock

CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

Row-level locks

Row locks are what writers actually fight over. Postgres takes them automatically on UPDATE and DELETE, and you can request them yourself with SELECT ... FOR UPDATE.

Postgres records a row lock inside the row itself, in the xmax field of the tuple header. That has two consequences:

• No lock escalation. Some databases convert many row locks into one table lock when they run low on memory. Postgres does not, so it can lock millions of rows individually.
• Locking has a write cost. Marking a row changes its page on disk, so SELECT ... FOR UPDATE over many rows creates write I/O.

Example: two writers, one row

-- Session A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks row 1

-- Session B
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- waits for A

-- Session C
SELECT balance FROM accounts WHERE id = 1;                 -- returns at once, old value

MVCC row locking · 3 sessions on one row

Session C is not blocked because MVCC gives it the last committed version of the row. Session B has to wait because only one transaction can modify a row at a time.

The four row-lock modes

Mode	Taken by	Conflicts with
FOR KEY SHARE	Foreign key checks, such as inserting a child row	FOR UPDATE
FOR SHARE	SELECT ... FOR SHARE	FOR NO KEY UPDATE, FOR UPDATE
FOR NO KEY UPDATE	UPDATE that leaves key columns alone, or SELECT ... FOR NO KEY UPDATE	FOR SHARE, FOR NO KEY UPDATE, FOR UPDATE
FOR UPDATE	DELETE, UPDATE of key columns, or SELECT ... FOR UPDATE	All four modes

The weaker modes exist so that foreign keys do not block unrelated work. A normal UPDATE takes FOR NO KEY UPDATE, which does not conflict with the FOR KEY SHARE that a foreign key check takes. You can update a customer row while new orders for that customer are being inserted.

Use case: selling the last item in stock

Two buyers check out at once and only one item is left. If both read stock = 1 and then both decrement it, you oversell.

BEGIN;
SELECT stock FROM products WHERE id = 42 FOR UPDATE;  -- the second buyer waits here
-- the application checks that stock > 0
UPDATE products SET stock = stock - 1 WHERE id = 42;
COMMIT;

The second buyer waits at the SELECT, then sees stock = 0 once the first buyer commits. When the check is simple, one atomic statement is lighter and needs no explicit lock:

UPDATE products SET stock = stock - 1 WHERE id = 42 AND stock > 0;
-- 1 row updated: success. 0 rows updated: sold out.

Advisory locks

Advisory locks are locks whose meaning only your application knows. You pass Postgres a number, and it guarantees that only one session holds that number at a time. It never ties the lock to any table or row. That makes it a handy mutex for coordinating work across servers.

Example: run a nightly job on one server

Every server schedules the same job. Only the one that wins the lock runs it.

SELECT pg_try_advisory_lock(42);  -- true: you hold it. false: someone else does.

-- ... run the job if you got true ...

SELECT pg_advisory_unlock(42);

advisory lock · 3 servers, 1 winner

Four functions to know

Function	If the lock is taken	Released
pg_advisory_lock(key)	Waits	When you call pg_advisory_unlock, or the session ends
pg_try_advisory_lock(key)	Returns false at once	Same
pg_advisory_xact_lock(key)	Waits	Automatically at the end of the transaction
pg_try_advisory_xact_lock(key)	Returns false at once	Automatically at the end of the transaction

Use cases

• Single-runner jobs: cron-style tasks that must not overlap across instances.
• Per-customer serialization: call pg_advisory_xact_lock(customer_id) at the start of a transaction so two workers never process the same customer at once.
• Migration guards: stop two deploys from running migrations at the same time. Several migration tools do this.

Two pitfalls

• Prefer the transaction-level versions. Session-level locks outlive commits and rollbacks, so a forgotten unlock holds the lock until the connection closes. They are also unsafe behind a pooler in transaction mode, such as PgBouncer, because your next statement may run on a different connection.
• Keys are shared by the whole database. Two unrelated features that both pick 42 will block each other. Use the two-integer form, pg_advisory_lock(namespace, id), or a documented key scheme.

How deadlocks form

A deadlock is a standstill. Two or more transactions each hold a lock the other needs, so each waits for the other forever. Waiting alone is normal in a database. A deadlock is waiting in a circle.

Deadlocks depend entirely on the order of operations. If every transaction touches resources in the same order, a cycle cannot form.

Example: two bank transfers in opposite directions

-- Transaction A: move 100 from account 1 to account 2
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- waits for B

-- Transaction B: move 50 from account 2 to account 1, at the same moment
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- waits for A

deadlock · 2 transactions, 2 rows, 1 cycle

Step by step:

1. A locks account 1.
2. B locks account 2.
3. A tries to lock account 2 and waits for B.
4. B tries to lock account 1 and waits for A.

Neither can finish, so neither will ever release its lock. Run each transfer alone and both succeed. The bug only appears when they overlap in time, which is why deadlocks often show up under load and not in testing.

Where deadlocks hide in real applications

• Ledgers and transfers: money moves between two accounts in either direction, as above.
• Overlapping batch jobs: two jobs update the same set of rows but walk through them in different orders.
• A single multi-row statement: UPDATE ... WHERE id IN (...) does not promise any lock order. Two concurrent statements over overlapping rows can lock them in opposite orders.
• Foreign keys: inserting child rows takes FOR KEY SHARE on the parent row, which can collide with another transaction that updates or deletes that parent.

How Postgres detects and breaks a deadlock

Postgres does not check for deadlocks on every lock wait. The check is expensive, and most waits end on their own within milliseconds. So Postgres is lazy: it waits first, and checks only if a transaction is still blocked after deadlock_timeout, which defaults to 1 second.

deadlock detection · 2 checks, 3 outcomes

The process has three steps:

1. Wait. A blocked transaction sleeps for deadlock_timeout.
2. Build the wait-for graph. If it is still blocked, Postgres traces who is waiting on whom.
3. Break the cycle. If the graph contains a loop, Postgres aborts one transaction. The others are unblocked and complete.

The transaction that runs the check is normally the one that gets aborted, which is usually the one whose wait timer fired first. Do not write code that depends on which side loses.

What your application sees

The aborted transaction fails with SQLSTATE 40P01 and its whole transaction is rolled back:

ERROR:  deadlock detected
DETAIL:  Process 6021 waits for ShareLock on transaction 8113; blocked by process 6044.
         Process 6044 waits for ShareLock on transaction 8112; blocked by process 6021.
HINT:  See server log for query details.

The process IDs and transaction IDs differ every time. Postgres also writes the full details, including the queries involved, to the server log.

Tuning deadlock_timeout

• Lower values find real deadlocks sooner, but add checking work to every lock wait that lasts longer than the timeout.
• Higher values reduce that work, but a real deadlock then stalls the stuck transactions for longer.

The 1 second default suits most systems. It is also the threshold for log_lock_waits, which logs any lock wait longer than deadlock_timeout and is worth enabling.

Preventing locks and deadlocks

You cannot remove locking, and you should not try. You can make waits short and cycles impossible. Five habits do most of the work.

1. Lock in a consistent order

This is the most effective fix. If every transaction locks rows in the same order, a cycle cannot form. The second transaction simply waits for the first.

lock ordering · deadlock vs short wait

In the transfer example, always lock the lower account ID first:

BEGIN;
SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;  -- lowest ID first
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

For batch jobs, sort the IDs in your application before you send a bulk UPDATE, so every job walks the rows in the same order.

2. Keep transactions short

A transaction holds its locks until it ends, so every millisecond inside it widens the window for a collision. Do slow work before BEGIN or after COMMIT, never between.

// Bad: the row stays locked during a slow network call
await client.query('BEGIN');
await client.query('UPDATE orders SET status = $1 WHERE id = $2', ['paid', id]);
await paymentApi.capture(id);
await client.query('COMMIT');

// Better: slow work first, then one quick statement
const receipt = await paymentApi.capture(id);
await client.query('UPDATE orders SET status = $1 WHERE id = $2', ['paid', id]);

Also set idle_in_transaction_session_timeout. It ends sessions that open a transaction and then sit idle, which would otherwise hold locks indefinitely.

3. Retry when a deadlock happens

In a busy system, an occasional deadlock is normal. Catch SQLSTATE 40P01 (deadlock) and 40001 (serialization failure), roll back, and run the whole transaction again after a short, randomized delay.

import { Pool, PoolClient } from 'pg';

const pool = new Pool();
const RETRYABLE = new Set(['40P01', '40001']);

async function withRetry<T>(
  work: (client: PoolClient) => Promise<T>,
  attempts = 5
): Promise<T> {
  for (let attempt = 1; ; attempt++) {
    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      const result = await work(client);
      await client.query('COMMIT');
      return result;
    } catch (err: any) {
      await client.query('ROLLBACK').catch(() => {});
      if (!RETRYABLE.has(err.code) || attempt >= attempts) throw err;
      // exponential backoff with jitter
      const delay = Math.min(1000, 50 * 2 ** attempt) * (0.5 + Math.random() / 2);
      await new Promise((resolve) => setTimeout(resolve, delay));
    } finally {
      client.release();
    }
  }
}

The retried work must be safe to run twice. Keep external side effects, such as emails and payments, outside the transaction.

4. Do not wait when you do not have to

Sometimes the best way to avoid lock trouble is to refuse to queue. Postgres gives you three tools:

• SKIP LOCKED: ignore rows another transaction has locked and take the next ones.
• NOWAIT: fail immediately with error 55P03 if a row is locked.
• lock_timeout: fail with the same error after a set time.

The classic use case is a job queue. With a plain FOR UPDATE, every worker reaches for the same first row and the rest line up behind it.

job queue · plain FOR UPDATE vs SKIP LOCKED

BEGIN;
SELECT id, payload FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- process the job, then:
UPDATE jobs SET status = 'done' WHERE id = $1;
COMMIT;

Workers no longer block each other on the same job. SKIP LOCKED gives each caller a deliberately incomplete view of the table, so use it for queues and work distribution, not for general reads. Strict first-in-first-out ordering is also not guaranteed.

5. Lock only what you need

• Pick the weakest row lock that works. If you only need to stop concurrent updates and are not changing key columns, FOR NO KEY UPDATE blocks less than FOR UPDATE.
• Touch fewer rows. Narrow the WHERE clause and do not update rows whose values will not change.
• Index foreign key columns. Without an index on the referencing column, checks and cascading deletes scan more of the table and hold locks for longer.

Diagnosing lock problems in production

There are two kinds of symptoms. Queries that hang point to a lock wait, and the fix is to find the blocker. deadlock detected errors point to a lock-ordering bug, and the fix is in your code. Start by finding out who is blocking whom.

Find the blocked queries

SELECT
  pid,
  pg_blocking_pids(pid)  AS blocked_by,
  wait_event_type,
  wait_event,
  now() - query_start    AS waiting_for,
  left(query, 80)        AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Each row is a session that is waiting. blocked_by lists the process IDs holding the lock it needs.

Find the blockers

SELECT pid, state, now() - xact_start AS transaction_age, left(query, 80) AS last_query
FROM pg_stat_activity
WHERE pid IN (SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity);

The usual culprit is a session in state idle in transaction. An application opened a transaction, did some work, and then stopped talking to the database without committing. Its locks stay held. The last_query column shows the last statement it ran, which usually points to the code path at fault.

If you must unblock production, pg_cancel_backend(pid) cancels the running query. pg_terminate_backend(pid) closes the connection and rolls back its transaction. Fix the cause afterwards, because the same blocker will come back.

For the full picture, the pg_locks view lists every lock that is held or awaited. A row-lock wait shows up there as a wait on the holder's transactionid.

Settings worth reviewing

Setting	What it does	Advice
log_lock_waits	Logs any lock wait longer than deadlock_timeout	Turn it on
deadlock_timeout	How long to wait before checking for a deadlock	Keep the 1 second default
lock_timeout	Makes a statement give up if it waits too long for a lock	Set it per session for migrations
idle_in_transaction_session_timeout	Ends sessions that sit idle inside a transaction	Set a value that fits your app

Reading a deadlock in the server log

When a deadlock happens, the log shows each process, the lock it waited for, and the query it was running. Find the two statements and write down the order in which each one locks rows. They will be opposite. Make both code paths lock in the same order and the deadlock goes away.

Cheat sheet: symptom, cause, fix

Symptom	Likely cause	Fix
Everything hangs right after a deploy	A migration is waiting for ACCESS EXCLUSIVE, and other queries have queued behind it	Cancel it. Rerun with lock_timeout, and use CREATE INDEX CONCURRENTLY
Many queries stuck on one row	A hot row updated inside long transactions	Shorten transactions, or use one atomic UPDATE
deadlock detected (40P01)	Transactions lock rows in different orders	Lock in a consistent order, and retry with backoff
Sessions idle in transaction holding locks	The app opened a transaction and then stalled, or crashed	Set idle_in_transaction_session_timeout, and keep transactions short
Workers queue up on a job table	Plain SELECT ... FOR UPDATE	Use FOR UPDATE SKIP LOCKED
Two servers run the same cron job	No coordination between instances	Use pg_try_advisory_lock

Conclusion

Locks are not the enemy. They are how Postgres keeps concurrent writers from corrupting each other's data. Trouble starts when locks are held too long or requested in conflicting orders. Keep three ideas in mind:

1. Locks last until the transaction ends. Short transactions mean short waits.
2. Readers never block writers, but writers block writers, and schema changes block everyone. Treat ALTER TABLE on a busy table with respect.
3. Deadlocks come from inconsistent ordering. Lock in the same order everywhere, and retry when one slips through.

When something does go wrong, pg_stat_activity and pg_blocking_pids() will usually show you the blocker within a minute.

Further reading

• PostgreSQL docs: Explicit Locking
• PostgreSQL docs: LOCK
• CYBERTEC: Understanding deadlocks in PostgreSQL
• PlanetScale: Deadlocks and downtime
• Hussein Nasser: Postgres locks, a deep dive

Comments

[Jason Y. (dev_in_the_fog)]

Handling connection pooling and failover semantics gracefully is an art. Thanks for sharing this detailed write-up!