Reading Deadlock Reports

Scenario

Example app: an order system on Postgres (and a legacy reporting service on MySQL). After a release, the on-call engineer gets an alert: deadlocks went from ~2 per day to 300 per hour, all on checkout.

What you see in production

Postgres server log:

2026-10-13 10:14:02 UTC [81723] ERROR:  deadlock detected
2026-10-13 10:14:02 UTC [81723] DETAIL:  Process 81723 waits for ShareLock on transaction 551208; blocked by process 81731.
        Process 81731 waits for ShareLock on transaction 551207; blocked by process 81723.
        Process 81723: UPDATE inventory SET reserved = reserved + 1 WHERE sku = 'SKU-RED-42'
        Process 81731: UPDATE inventory SET reserved = reserved + 1 WHERE sku = 'SKU-BLUE-7'
2026-10-13 10:14:02 UTC [81723] HINT:  See server log for query details.
2026-10-13 10:14:02 UTC [81723] CONTEXT:  while updating tuple (12,7) in relation "inventory"

The naive code

The new release reserves stock for every cart line in cart order:

for line in cart.lines:                      # cart order = order the customer added items
    s.execute(update(Inventory).where(Inventory.sku == line.sku)
              .values(reserved=Inventory.reserved + line.qty))

for (CartLine line : cart.getLines()) {      // customer's order: RED then BLUE, or BLUE then RED
    inventory.reserve(line.sku(), line.qty());
}

Reproduce it

A small parser that extracts the cycle and the statements from a Postgres deadlock report:

import re

log = '''ERROR:  deadlock detected
DETAIL:  Process 81723 waits for ShareLock on transaction 551208; blocked by process 81731.
Process 81731 waits for ShareLock on transaction 551207; blocked by process 81723.
Process 81723: UPDATE inventory SET reserved = reserved + 1 WHERE sku = 'SKU-RED-42'
Process 81731: UPDATE inventory SET reserved = reserved + 1 WHERE sku = 'SKU-BLUE-7'
CONTEXT:  while updating tuple (12,7) in relation "inventory"'''

edges = re.findall(r"Process (\d+) waits for (\w+) on (transaction \d+|relation \d+[^;]*); blocked by process (\d+)", log)
queries = dict(re.findall(r"Process (\d+): (.+)", log))
table = re.search(r'relation "(\w+)"', log).group(1)

print(f"table: {table}")
for waiter, mode, target, blocker in edges:
    print(f"pid {waiter} waits ({mode} on {target}) for pid {blocker}")
for pid, query in queries.items():
    print(f"pid {pid} was running: {query}")
cycle = " -> ".join([edges[0][0]] + [e[3] for e in edges])
print("cycle:", cycle)
print("diagnosis: two transactions update the same table rows in different orders")

table: inventory
pid 81723 waits (ShareLock on transaction 551208) for pid 81731
pid 81731 waits (ShareLock on transaction 551207) for pid 81723
pid 81723 was running: UPDATE inventory SET reserved = reserved + 1 WHERE sku = 'SKU-RED-42'
pid 81731 was running: UPDATE inventory SET reserved = reserved + 1 WHERE sku = 'SKU-BLUE-7'
cycle: 81723 -> 81731 -> 81723
diagnosis: two transactions update the same table rows in different orders

Root cause

Reading the report:

• "waits for ShareLock on transaction N" — in Postgres, waiting for a row lock appears as waiting for the transaction that holds it to finish.
• The statements shown are the ones each process was blocked on, not the ones that took the first lock. Each transaction already updated the other SKU earlier.
• So: T1 updated BLUE then wants RED; T2 updated RED then wants BLUE. Both loops lock rows in cart order, which differs between customers.

MySQL's SHOW ENGINE INNODB STATUS has a LATEST DETECTED DEADLOCK section with the same shape:

*** (1) TRANSACTION:   … UPDATE inventory SET reserved = reserved + 1 WHERE sku = 'SKU-RED-42'
*** (1) HOLDS THE LOCK(S):        RECORD LOCKS … index PRIMARY of table `shop`.`inventory` … lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS … lock_mode X locks rec but not gap
*** (2) TRANSACTION:   … UPDATE inventory SET reserved = reserved + 1 WHERE sku = 'SKU-BLUE-7'
*** WE ROLL BACK TRANSACTION (2)

The fix

Sort the work so every transaction locks rows in the same order:

for line in sorted(cart.lines, key=lambda l: l.sku):      # deterministic order
    s.execute(update(Inventory).where(Inventory.sku == line.sku)
              .values(reserved=Inventory.reserved + line.qty))

cart.getLines().stream()
    .sorted(Comparator.comparing(CartLine::sku))
    .forEach(line -> inventory.reserve(line.sku(), line.qty()));

foreach ($cart->lines->sortBy('sku') as $line) {
    Inventory::where('sku', $line->sku)->increment('reserved', $line->qty);
}

for (const line of [...cart.lines].sort((a, b) => a.sku.localeCompare(b.sku))) {
  await tx.inventory.update({ where: { sku: line.sku }, data: { reserved: { increment: line.qty } } });
}

Trade-offs

• Logs show only the last statement per process; to see the earlier lock, enable statement logging for the affected transactions temporarily or add tracing that tags each transaction with its endpoint.
• log_lock_waits = on (Postgres) logs any lock wait longer than deadlock_timeout, which reveals contention before it becomes deadlocks.
• MySQL keeps only the latest deadlock in SHOW ENGINE INNODB STATUS; enable innodb_print_all_deadlocks in production.

Detect it in production

• Ship database logs to your log platform and alert on deadlock detected / Deadlock found rates.
• Correlate deadlock spikes with deploys — new code paths that touch the same tables in a new order are the usual cause.
• Keep a small runbook: find the two statements → find both code paths → compare lock order.

Key points

• Postgres reports row-lock waits as "ShareLock on transaction"; the queries shown are the blocked ones.
• InnoDB's LATEST DETECTED DEADLOCK shows held and awaited locks, and which transaction was rolled back.
• Turn on log_lock_waits (Postgres) and innodb_print_all_deadlocks (MySQL).
• The usual fix is a deterministic lock order across all code paths.