PostgreSQL Deadlock Detected: Causes, Examples and Fixes

The PostgreSQL error ERROR: deadlock detected means two or more transactions were each waiting for a lock the other one held, so none of them could ever finish. PostgreSQL noticed the cycle, cancelled one of the transactions with SQLSTATE 40P01, and let the others continue. Nothing is broken in the server. The fix is in your code: take locks in the same order everywhere, keep transactions short, and retry the whole transaction when it does happen.

This tutorial reproduces a real deadlock step by step, shows you how to read the error and the server log, how to see who is blocking whom while it happens, and how to change the code so the deadlock cannot occur again. Every output below was copied from a real PostgreSQL 16 session.

How a deadlock happens in PostgreSQL

When a transaction updates or deletes a row, it holds a row-level lock on that row until it commits or rolls back. Any other transaction that wants to change the same row has to wait. Usually that is fine: the first transaction finishes, the lock is released, and the waiter carries on.

A deadlock is the special case where the waiting forms a circle. Transaction A holds row 1 and wants row 2. Transaction B holds row 2 and wants row 1. Neither can move. Without help they would wait forever, so PostgreSQL runs a deadlock check and aborts one of them.

The check is not instant. A waiting session first sleeps for deadlock_timeout (default 1s), and only then checks whether it is part of a cycle. The documentation explains why: the check is relatively expensive, so the server assumes most lock waits end on their own. This is why a deadlock usually shows up about one second after the second transaction starts waiting.

Sample setup

Create a small bank accounts table. Money transfers are the classic deadlock example because each transfer updates two rows.

CREATE TABLE accounts (
  account_id  int PRIMARY KEY,
  owner_name  text NOT NULL,
  balance     numeric(12,2) NOT NULL
);

INSERT INTO accounts VALUES
  (1, 'Asha',  5000.00),
  (2, 'Ravi',  3000.00),
  (3, 'Meena', 1200.00);

SELECT * FROM accounts ORDER BY account_id;

To follow along you need two psql windows connected to the same database. We will call them Session 1 and Session 2.

Reproduce the deadlock detected error

Session 1 moves 500 from Asha (account 1) to Ravi (account 2). At the same moment, Session 2 moves 200 from Ravi to Asha. Each session updates its own source account first, so they lock the two rows in opposite order.

Step 1: Session 1 locks account 1

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

The update succeeds and Session 1 now holds a row lock on account 1. It has not committed yet.

Step 2: Session 2 locks account 2

BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE account_id = 2;

This also succeeds, because nobody else has touched account 2.

Step 3: both sessions ask for the other row

-- Session 1
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

-- Session 2
UPDATE accounts SET balance = balance + 200 WHERE account_id = 1;

Session 1 starts waiting for account 2. Then Session 2 starts waiting for account 1. That is the circle. After about one second, one of the sessions gets this error:

ERROR:  deadlock detected
DETAIL:  Process 486 waits for ShareLock on transaction 733; blocked by process 483.
Process 483 waits for ShareLock on transaction 734; blocked by process 486.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,1) in relation "accounts"

In our run Session 2 was the one cancelled, and the moment it failed, Session 1's waiting update went through. Session 1 could then COMMIT normally. Do not count on it always being the second session; the documentation says which transaction gets aborted is difficult to predict and should not be relied upon.

Step 4: the failed transaction is already dead

Session 2 is now in an aborted transaction. Typing COMMIT does not save its first update. psql reports:

COMMIT;
ROLLBACK

Here is the table afterwards. Only Session 1's transfer exists. Session 2's 200 transfer was rolled back completely, including the update that had succeeded before the deadlock.

How to read the deadlock error message

The error looks cryptic at first, but each line tells you something useful.

Find the queries in the server log

The PostgreSQL log shows the full statements of every process in the cycle. On Ubuntu/Debian packages the log is under /var/log/postgresql/. Our run logged this:

ERROR:  deadlock detected
DETAIL:  Process 486 waits for ShareLock on transaction 733; blocked by process 483.
	Process 483 waits for ShareLock on transaction 734; blocked by process 486.
	Process 486: UPDATE accounts SET balance = balance + 200 WHERE account_id = 1;
	Process 483: UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
CONTEXT:  while updating tuple (0,1) in relation "accounts"
STATEMENT:  UPDATE accounts SET balance = balance + 200 WHERE account_id = 1;

These are the statements that were waiting when the cycle was found, not the earlier statements that took the first locks. You still need to look at the whole transaction in your application code to see the order in which rows were locked.

Check the SQLSTATE code

Your application should recognise deadlocks by code, not by message text. Set VERBOSITY verbose in psql to see it:

\set VERBOSITY verbose

ERROR:  40P01: deadlock detected

40P01 is the condition deadlock_detected in the PostgreSQL error code list. Drivers expose it as the SQLSTATE of the exception.

See blocking sessions while they wait

A deadlock is resolved in about a second, so you will rarely catch one live. What you can catch is ordinary blocking, which is how every deadlock starts. pg_blocking_pids() returns the PIDs that are blocking a given session.

In this test, Session 1 updated account 3 and then slept with the transaction open, and Session 2 tried to update the same row. From a third session we ran:

SELECT pid,
       application_name,
       state,
       wait_event_type,
       wait_event,
       pg_blocking_pids(pid) AS blocked_by,
       left(query, 60) AS query
  FROM pg_stat_activity
 WHERE datname = current_database()
   AND pid <> pg_backend_pid()
 ORDER BY pid;

Session 2 (pid 507) has wait_event_type Lock and is blocked by pid 503. The query column shows the latest statement of the blocker, which is often not the one that took the lock. That is a common source of confusion when you investigate.

Log every long lock wait

Turn on log_lock_waits (default off). Then any session that waits longer than deadlock_timeout writes a log line, which helps you find the hot rows behind your deadlocks.

ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();

LOG:  process 507 still waiting for ShareLock on transaction 735 after 1000.153 ms
DETAIL:  Process holding the lock: 503. Wait queue: 507.
CONTEXT:  while updating tuple (0,3) in relation "accounts"
STATEMENT:  UPDATE accounts SET balance = balance + 100 WHERE account_id = 3;
LOG:  process 507 acquired ShareLock on transaction 735 after 3498.352 ms

Count deadlocks per database

SELECT datname, deadlocks
  FROM pg_stat_database
 WHERE datname = 'dl';

Our scratch database is called dl. The counter only grows, so track it over time in your monitoring. A rising number tells you a code path is locking rows in inconsistent order.

Fix 1: lock rows in a consistent order

The documentation calls this the best defence: make every transaction acquire its locks in the same order. If both transfers lock the lower account_id first, the second transfer simply waits for the first one to finish. A queue, not a circle.

The easy way is to lock all rows you will touch up front with SELECT ... ORDER BY ... FOR UPDATE, then do the updates in any order you like.

BEGIN;
SELECT account_id
  FROM accounts
 WHERE account_id IN (1, 2)
 ORDER BY account_id
   FOR UPDATE;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;

BEGIN;
SELECT account_id
  FROM accounts
 WHERE account_id IN (2, 1)
 ORDER BY account_id
   FOR UPDATE;

UPDATE accounts SET balance = balance - 200 WHERE account_id = 2;
UPDATE accounts SET balance = balance + 200 WHERE account_id = 1;
COMMIT;

We ran these at the same time, with Session 1 pausing for two seconds while holding its locks. Session 2's SELECT simply waited, then both sessions committed. No error:

Both transfers are applied: Asha went from 4500 to 4200 (minus 500, plus 200) and Ravi from 3500 to 3800.

Why ORDER BY controls the lock order

The plan shows that rows are sorted before they are locked:

EXPLAIN (COSTS OFF)
SELECT account_id
  FROM accounts
 WHERE account_id IN (2, 1)
 ORDER BY account_id
   FOR UPDATE;

LockRows
  ->  Sort
        Sort Key: account_id
        ->  Bitmap Heap Scan on accounts
              Recheck Cond: (account_id = ANY ('{2,1}'::integer[]))
              ->  Bitmap Index Scan on accounts_pkey
                    Index Cond: (account_id = ANY ('{2,1}'::integer[]))

The LockRows node sits on top of Sort, so rows are locked in account_id order. The SELECT documentation confirms that ORDER BY is applied first and locks are taken afterwards. Without the ORDER BY, the lock order would depend on the plan, and you would be back to luck.

Put the rule in one function

Deadlock fixes break when one code path forgets the rule. Putting the transfer in a single PL/pgSQL function means every caller locks in the same order.

CREATE OR REPLACE FUNCTION transfer(p_from int, p_to int, p_amount numeric)
RETURNS void
LANGUAGE plpgsql
AS $
BEGIN
  -- Lock both rows in a fixed order (lowest id first)
  PERFORM 1
     FROM accounts
    WHERE account_id IN (p_from, p_to)
    ORDER BY account_id
      FOR UPDATE;

  UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from;
  UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to;
END;
$;

SELECT transfer(3, 1, 150);
SELECT * FROM accounts ORDER BY account_id;

The same idea works for batch jobs. If a job updates a list of IDs, sort the list first (in the application or with ORDER BY) so two jobs never walk the same rows in opposite directions.

Fix 2: keep transactions short

A lock is held until the transaction ends. The longer a transaction stays open, the more time another transaction has to grab a row in the wrong order. The documentation warns that it is a bad idea to hold transactions open for long periods, for example while waiting for user input.

• Do not call external APIs, send emails or wait for a user inside an open transaction.
• Read and validate data before BEGIN when you can, then lock and write in one quick burst.
• Watch for sessions stuck in idle in transaction state in pg_stat_activity; they hold their locks while doing nothing.

Fix 3: retry the whole transaction

Even with careful ordering, complex systems can still hit the occasional deadlock, and the documentation suggests retrying transactions that abort because of one. The key word is transaction. After 40P01 every statement in that transaction has been rolled back, as you saw in Step 4.

1. Catch errors whose SQLSTATE is 40P01.
2. Roll back (most drivers need an explicit ROLLBACK to leave the aborted state).
3. Run the whole unit of work again from BEGIN, including any reads, because the data may have changed.
4. Limit the number of retries and add a short random delay, so the same two transactions do not collide again straight away.

Common mistakes

• Raising or lowering deadlock_timeout to fix deadlocks. It only changes how long PostgreSQL waits before checking. A cycle is still a cycle. A higher value just makes users wait longer before the error.
• Calling COMMIT after the error. The transaction is already aborted and the commit becomes a ROLLBACK. Check for errors instead of assuming success.
• Looking only at the error in the application log. The client message hides the other session's SQL. Read the server log, which lists the statement of every process in the cycle.
• Trusting the query column of the blocker. In pg_stat_activity it is the latest statement, not necessarily the one that took the lock.
• Fixing one code path only. Lock ordering works only when every path that touches those rows follows the same order. A single report or cron job that locks in reverse order brings the deadlocks back.
• Confusing deadlocks with slow queries. A session that waits for minutes without an error is blocked, not deadlocked. Find the blocker with pg_blocking_pids() instead.

Best practices

• Decide one lock order per table (usually primary key ascending) and document it.
• Lock all rows a transaction needs at the start with SELECT ... ORDER BY ... FOR UPDATE.
• Keep transactions short and free of network calls and user waits.
• Enable log_lock_waits so long waits are logged with the holder's PID.
• Track pg_stat_database.deadlocks in monitoring and investigate when it grows.
• Leave deadlock_timeout at the default unless you have a reason. The docs call 1s probably about the smallest value you would want, and suggest it should ideally exceed your typical transaction time.

Frequently asked questions

What does deadlock detected mean in PostgreSQL?

It means two or more transactions were waiting on each other's locks in a circle. PostgreSQL broke the circle by aborting one transaction with error code 40P01. The other transactions continue normally.

What is ShareLock on transaction in a PostgreSQL deadlock?

When a session needs a row that another transaction has locked, it waits for that transaction to finish by requesting a ShareLock on its transaction ID. So "waits for ShareLock on transaction 733" means "is waiting for transaction 733 to commit or roll back". It is a row-lock conflict, not a table lock.

Which transaction does PostgreSQL kill in a deadlock?

One of the transactions in the cycle is aborted. The documentation says exactly which one is difficult to predict and should not be relied upon, so make every transaction that can deadlock safe to retry.

How do I prevent deadlocks in PostgreSQL?

Lock rows in the same order in every transaction, lock everything you need at the start, and keep transactions short. Then retry the whole transaction on 40P01 for the rare cases that remain.

Is a deadlock the same as a lock timeout?

No. A deadlock is a circle that PostgreSQL detects and breaks. A plain lock wait has no circle and, by default, waits until the other transaction ends. If you want a time limit on ordinary waits, set lock_timeout, which raises a different error.

Summary

• deadlock detected (SQLSTATE 40P01) means a circle of lock waits; PostgreSQL aborts one transaction to break it.
• The aborted transaction is fully rolled back, and COMMIT after the error returns ROLLBACK.
• Read the server log to see the SQL of every process in the cycle.
• Use pg_stat_activity with pg_blocking_pids() to see live blocking, and log_lock_waits to log long waits.
• Lock rows in one consistent order, ideally with SELECT ... ORDER BY ... FOR UPDATE at the start.
• Keep transactions short and never wait for users or external services inside them.
• Retry the whole transaction, with a retry limit, when 40P01 happens.