Retrying deadlocks

On PostgreSQL the transient error you retry is SQLSTATE 40001. On MySQL it's errno 1213 — a deadlock victim. Different mechanism, same contract: the database rolled your transaction back not because it was wrong, but because it collided with another one. Run it again and it will very likely succeed.

The manual is unambiguous (How to Minimize and Handle Deadlocks): "Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again."

The helper is a dozen lines:

/** Re-run `fn` when it fails with a transient InnoDB error: 1213 (deadlock victim). */
export async function withRetry<T>(fn: () => Promise<T>, attempts = 5): Promise<T> {
  for (let attempt = 1; ; attempt++) {
    try {
      return await fn();
    } catch (e: any) {
      if (e.code !== "1213" || attempt === attempts) throw e;
    }
  }
}

And here it is earning its keep. The scenario forces the classic opposite-order deadlock on the first attempt, hands the 1213 to withRetry, and proves the second attempt succeeds:

let attempt = 0;
await withRetry(async () => {
  attempt++;
  await B`BEGIN`;
  await B`UPDATE accounts SET balance = balance - 25 WHERE id = 2`;

  if (attempt === 1) {
    // Force the deadlock once: A runs the opposite transfer, locking in reverse order.
    t.note("B holds bob. A locks alice, then wants bob — and blocks. B then wants alice: a cycle.");
    await A`BEGIN`;
    await A`UPDATE accounts SET balance = balance - 10 WHERE id = 1`;
    const pending = await A.blocked`UPDATE accounts SET balance = balance + 10 WHERE id = 2`;
    const err = await B.fails`UPDATE accounts SET balance = balance + 25 WHERE id = 1`;
    eq(err.code, "1213");
    await pending.success(); // B's rollback freed bob — A's transfer completes
    await A`COMMIT`;
    throw err; // hand the 1213 to withRetry, exactly as a driver would
  }

  t.note("Attempt 2 is a brand-new transaction. A is done — B's transfer sails through.");
  await B`UPDATE accounts SET balance = balance + 25 WHERE id = 1`;
  await B`COMMIT`;
});
eq(attempt, 2);

const [alice] = await A`SELECT balance FROM accounts WHERE id = 1`;
const [bob] = await A`SELECT balance FROM accounts WHERE id = 2`;
eq(alice!.balance, 115); // 100 - 10 (A) + 25 (B)
eq(bob!.balance, 85); //   100 + 10 (A) - 25 (B)

B> BEGIN;
Query OK

B> UPDATE accounts SET balance = balance - 25 WHERE id = 2;
Query OK, 1 row affected

B holds bob. A locks alice, then wants bob — and blocks. B then wants alice: a cycle.

A> BEGIN;
Query OK

A> UPDATE accounts SET balance = balance - 10 WHERE id = 1;
Query OK, 1 row affected

A> UPDATE accounts SET balance = balance + 10 WHERE id = 2;
⏳ A is waiting for a lock…

B> UPDATE accounts SET balance = balance + 25 WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

⏵ A resumes:
Query OK, 1 row affected

A> COMMIT;
Query OK

B> BEGIN;
Query OK

B> UPDATE accounts SET balance = balance - 25 WHERE id = 2;
Query OK, 1 row affected

Attempt 2 is a brand-new transaction. A is done — B's transfer sails through.

B> UPDATE accounts SET balance = balance + 25 WHERE id = 1;
Query OK, 1 row affected

B> COMMIT;
Query OK

A> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     115 
(1 row)

A> SELECT balance FROM accounts WHERE id = 2;
 balance 
---------
      85 
(1 row)

Verified against MySQL 8.4.10 · Run it yourself · Scenario source

What must be inside the retry

The same rule as PostgreSQL's: re-run the whole transaction, including the application logic — every read, every decision, every computed value. The first attempt's reads are void, and reusing any of them re-introduces the stale-read bug you're recovering from. In the scenario, attempt 2 re-runs both UPDATEs from the top.

Two MySQL-specific cautions come with this. First, don't retry errno 1205 the way you retry 1213: a lock-wait timeout rolls back only the statement, so your transaction stays open and keeps holding its locks. Blindly re-running the whole function on 1205 double-applies whatever already succeeded, so roll back first, then retry.

Second, SERIALIZABLE multiplies deadlocks. InnoDB's SERIALIZABLE detects conflicts via locks, so the errors your retry loop meets at that level are these same 1213s — budget for more of them.

The mental model is short: 1213 means "collided", not "failed", so you retry the whole transaction with fresh reads and all. Cap the attempts and log exhaustion, because a hot row can starve a naive infinite loop. And keep 1205 in its own lane — ROLLBACK first, since a statement-level rollback leaves the transaction open, then retry.

Further reading

• MySQL docs: Deadlock Detection and Rollback
• MySQL docs: How to Minimize and Handle Deadlocks
• The PostgreSQL counterpart: retrying 40001