PostgreSQL "deadlock detected" — Which Two Transactions Locked Each Other?
Investigating PostgreSQL deadlock cycles, tracing lock trees via pg_blocking_pids(), enabling log_lock_waits, ordering multi-row UPDATEs, and application retry policies.
Operational Verification & Reliability Metadata
The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.
▸Symptoms
ERROR: deadlock detected
DETAIL: Process 4821 waits for ShareLock on transaction 9102; blocked by process 9102.
Process 9102 waits for ShareLock on transaction 4821; blocked by process 4821.
HINT: See server log for query details.▸Diagnostic Query
SELECT pid, pg_blocking_pids(pid) AS blockers, state, left(query, 100) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;Deterministic locking:
UPDATE hesap SET bakiye = bakiye - 10
WHERE id IN (SELECT unnest(ARRAY[42, 17, 99]) ORDER BY 1);- •Companion Guide (MySQL): MySQL Error 1213 Deadlock
- •Companion Guide (SQL Server): SQL Server Error 1205 Deadlock
- •Engine Hub: PostgreSQL Production Hub
Other Database Error Triage Playbooks
Other battle-tested diagnostic guides for high-severity database errors.
PostgreSQL Connection Exhaustion & too many clients Triage
Root causes of FATAL: sorry, too many clients already and remaining connection slots errors, read-only pg_stat_activity diagnosis with privilege boundaries, and two-tier pooling mitigation.
PostgreSQL password authentication failed: Root Cause and Fix
Four root causes of psql password authentication failed: incorrect password, md5/SCRAM mismatch, pg_hba.conf rule ordering, and port/cluster confusion. Step-by-step triage.
PostgreSQL connection refused: 8-Step Definitive Triage
Difference between connection refused, timeout, and no pg_hba entry; triage for listen_addresses, port conflicts, firewalls, and Docker container mappings.