PostgreSQL "ERROR: deadlock detected" - Find the Cycle and Fix It

A deadlock happens when two or more transactions each hold a lock the other needs, so none can proceed. PostgreSQL notices the cycle, aborts one transaction (the victim) and lets the others continue. The victim's client receives ERROR: deadlock detected with SQLSTATE 40P01, and everything that transaction did is rolled back.

ERROR:  deadlock detected
DETAIL:  Process 18211 waits for ShareLock on transaction 904412; blocked by process 18305.
Process 18305 waits for ShareLock on transaction 904409; blocked by process 18211.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,7) in relation "accounts"

Deadlocks are an application design issue, not corruption. Rare ones are normal and should be retried. Frequent ones point to inconsistent lock ordering. The InnoDB version of this problem is described in MySQL error 1213.

Deadlock or just blocking?

A plain lock wait is not an error. One transaction waits while another finishes. PostgreSQL only checks for a cycle after waiting for deadlock_timeout (default 1 second). If the wait ends normally, there is no error. If you see statements stuck for a long time without a deadlock message, look at blockers instead:

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

pg_blocking_pids() is the quickest way to see who is waiting on whom. A session left idle in transaction holds its locks until it commits, so also check for that. The MySQL form of simple blocking is lock wait timeout.

Step 1: read the server log

The client message is brief. The server log lists both queries involved:

sudo grep -A12 "deadlock detected" /var/log/postgresql/postgresql-*-main.log

Look for the two Process ... : statement lines. Settings that make the log more useful:

ALTER SYSTEM SET log_lock_waits = on;       -- logs any wait longer than deadlock_timeout
ALTER SYSTEM SET deadlock_timeout = '1s';   -- default, shown for clarity
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d ';
SELECT pg_reload_conf();

Do not set deadlock_timeout very low to "detect faster". The check is relatively expensive and short waits are usually harmless. Increasing it slightly can reduce checking cost on busy systems, at the price of slower detection.

Step 2: the usual patterns

Opposite order. Transaction A updates row 1 then row 2, while B updates row 2 then row 1.

-- Session A                         -- Session B
BEGIN;                               BEGIN;
UPDATE accounts SET bal = bal - 10   UPDATE accounts SET bal = bal - 5
  WHERE id = 1;                        WHERE id = 2;
UPDATE accounts SET bal = bal + 10   UPDATE accounts SET bal = bal + 5
  WHERE id = 2;  -- waits for B        WHERE id = 1;  -- waits for A: deadlock

Batch updates in random order. UPDATE ... WHERE id IN (...) without a defined order lets concurrent batches lock rows differently.

Foreign keys. Inserting a child row takes a FOR KEY SHARE lock on the parent row, which conflicts with updates that change the parent key. A busy parent row can cause cycles with child inserts.

Upserts and unique indexes. Concurrent INSERT ... ON CONFLICT on overlapping keys, or inserts into multiple tables in different orders.

Explicit table locks. LOCK TABLE and DDL such as ALTER TABLE need strong locks that conflict with ordinary traffic.

Step 3: fix the design

1. Lock in a consistent order. Sort the keys before touching them, in every code path:

SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- then update both rows

1. Keep transactions short. Do not call external APIs, send email or wait for user input inside a transaction.
2. Lock early when you intend to write. SELECT ... FOR UPDATE at the start avoids upgrade conflicts. Use FOR NO KEY UPDATE when you are not changing key columns, since it conflicts less with foreign key checks.
3. Process queues without contention using FOR UPDATE SKIP LOCKED:

SELECT id FROM jobs WHERE status = 'new'
ORDER BY id LIMIT 10
FOR UPDATE SKIP LOCKED;

1. Batch deterministically. Break big updates into chunks ordered by primary key.
2. Move schema changes off-peak and set lock_timeout so a migration gives up instead of queuing behind traffic and blocking everything else:

SET lock_timeout = '5s';
ALTER TABLE accounts ADD COLUMN note text;

Step 4: retry safely

Because PostgreSQL rolls the victim back completely, the correct response is to rerun the whole transaction. Catch SQLSTATE 40P01 (and 40001 for serialization failures when using higher isolation levels), wait a short random delay, and try again, with a limit of a few attempts. Only do this if the transaction is idempotent and has no external side effects. Many drivers expose the code, for example psycopg.errors.DeadlockDetected in Python.

Verify

Count deadlocks per database over time and alert on increases:

SELECT datname, deadlocks FROM pg_stat_database ORDER BY deadlocks DESC;

After a fix, the counter should stop climbing. Check the user-facing side with the वेबसाइट हेल्थ चेक, and watch an endpoint that writes using an अपटाइम मॉनिटर. Batch jobs that deadlock overnight can be tracked with cron monitoring.

अगले कदम

• Pile-ups of waiting sessions: sorry, too many clients already.
• Read the InnoDB equivalent, MySQL deadlock 1213.
• Monitor database-backed endpoints with the अपटाइम मॉनिटर.
• Learn how to monitor APIs in how to monitor API endpoints.

सामान्य प्रश्न

Which transaction does PostgreSQL abort?

The one that was waiting and ran the deadlock check, which is not necessarily the oldest or cheapest. Do not depend on which side loses.

Can I turn deadlock detection off?

No. It is always on. You can only adjust how long PostgreSQL waits before checking, with deadlock_timeout.

Is a single deadlock a problem?

Not usually. Retry it. Investigate if they recur or appear on a hot code path.

Do deadlocks cause data corruption?

No. The aborted transaction is rolled back and the database stays consistent.

यह पेज सामान्य जानकारी है, जिसे हमारी संपादकीय टीम ने लिखा है और दस्तावेज़ीकरण बदलने पर अपडेट किया जाता है। सर्वर और प्रदाता अलग-अलग होते हैं, इसलिए बदलाव पहले सुरक्षित तरीके से जाँचें। देखें: संपादकीय नीति और अस्वीकरण.