← Back to the library
BackendApplied · 8 min

Concurrent writes fail with "deadlock detected"

“Under load, a few write requests fail with "deadlock detected" or "Deadlock found when trying to get lock"; each one works fine when replayed alone.”

ERROR CODES YOU MAY SEE

A code is a clue. Use its meaning and surrounding evidence to narrow the cause.

40P01PostgreSQL SQLSTATE · standard

The server detected a lock cycle between transactions and aborted this one so the others could proceed (deadlock_detected).

The server log's DETAIL lines name both processes and the statements they ran. Look for two code paths that lock the same rows or tables in a different order. Retrying the whole transaction is safe; retrying only the failed statement is not.

Official reference for 40P01 (opens in new tab)Code definition reviewed
ER_LOCK_DEADLOCKMySQL server errors · platform

Error 1213 (SQLSTATE 40001): "Deadlock found when trying to get lock; try restarting transaction". InnoDB rolled back this transaction as the deadlock victim.

Read LATEST DETECTED DEADLOCK in SHOW ENGINE INNODB STATUS, or enable innodb_print_all_deadlocks to log every one. Missing indexes on UPDATE ... WHERE or SELECT ... FOR UPDATE make InnoDB lock far more rows than intended.

Official reference for ER_LOCK_DEADLOCK (opens in new tab)Code definition reviewed

THE PRINCIPLE

Deadlocks cannot be ruled out entirely under concurrency; design so they are rare, and make the victim's retry safe and automatic.

FIRST MOVES

  1. Get both sides of the cycle: PostgreSQL logs the two processes and statements; in MySQL read LATEST DETECTED DEADLOCK in SHOW ENGINE INNODB STATUS or enable innodb_print_all_deadlocks.
  2. Acquire row and table locks in one global order (for example by primary key) in every code path that touches the same data.
  3. Take the strongest lock you will need first (SELECT ... FOR UPDATE) instead of reading, then upgrading.
  4. Keep transactions short: no network calls or user waits inside, and index the columns in UPDATE ... WHERE and FOR UPDATE so fewer rows are locked.
  5. Retry the entire transaction with jittered backoff on 40P01 (PostgreSQL) or 1213 (MySQL); which transaction becomes the victim is not predictable.

TOOLS: THEN → NOW

Guessing from the application stack traceServer-side deadlock reports: PostgreSQL log DETAIL, InnoDB status and innodb_print_all_deadlocks
Raising lock wait timeoutsConsistent lock ordering plus a bounded whole-transaction retry

PATTERN SNAPSHOT

// Lock both rows in id order, then retry the whole transaction if chosen as victim.
for (let attempt = 1; ; attempt++) {
  try {
    return await db.transaction(async (tx) => {
      await tx.query("SELECT id FROM accounts WHERE id = ANY($1) ORDER BY id FOR UPDATE", [[from, to]]);
      await tx.query("UPDATE accounts SET balance = balance - $2 WHERE id = $1", [from, amount]);
      await tx.query("UPDATE accounts SET balance = balance + $2 WHERE id = $1", [to, amount]);
    });
  } catch (err) {
    // isDeadlock: SQLSTATE 40P01 (Postgres) or errno 1213 / ER_LOCK_DEADLOCK (MySQL)
    if (!isDeadlock(err) || attempt >= 3) throw err;
    await sleep(Math.random() * 50 * attempt);
  }
}

CLOSE THE AI. EXPLAIN THIS.

Two endpoints update the same pair of rows in opposite orders. Why does adding a retry fix the error rate but not the cause, and what change fixes the cause?

HOW IT WORKS UNDERNEATH

SOURCES

Guide reviewed