Knowledge Base
sqlserverpostgresqlmysqlconcurrency· Updated 2026-04-22
Deadlock triage
A deadlock is a cycle: session A holds lock 1 and wants lock 2; session B holds lock 2 and wants lock 1. The engine detects the cycle and kills one victim. Your job: make cycles impossible, not resurrect victims.
Get the evidence
| Engine | Where |
|---|---|
| SQL Server | system_health XE session (xml_deadlock_report) — already running on every instance |
| PostgreSQL | Server log (deadlock detected, logs both statements) |
| MySQL/InnoDB | SHOW ENGINE INNODB STATUS → LATEST DETECTED DEADLOCK; innodb_print_all_deadlocks=ON to log all |
Read it
Identify the two (or more) statements, the objects/indexes, and the lock modes. The overwhelming majority of deadlocks fall into three patterns:
- Opposite-order access — Txn A updates table X then Y; Txn B updates Y then X. Fix: standardize access order.
- Read-then-upgrade — both read the row (shared), then both try to update (exclusive). Fix: acquire the write intent up front (
SELECT ... FOR UPDATE, UPDLOCK hint), or restructure to a single UPDATE. - Missing index — a scan locks far more rows than intended, colliding with unrelated writes. Fix: index the predicate so locks touch only target rows.
Reduce the blast radius
- Short transactions; no user interaction or external calls inside a transaction.
- Consistent ordering by primary key when touching multiple rows.
- Retry logic: deadlock victims (SQL Server 1205, PostgreSQL 40P01, MySQL 1213) are safe to retry idempotent transactions — but retries are a mitigation, not a fix.