Retrying deadlocks
An InnoDB deadlock victim receives errno 1213. The MySQL 8.4 error-handling manual states: "InnoDB rolls back the entire transaction. Retry the entire transaction when this happens." A new attempt can also fail, so bound the attempts rather than promise success.
The helper retries only 1213, with five attempts by default. It assumes each call owns the complete InnoDB transaction and propagates the normalized driver error. It is not a cleanup wrapper for other errors:
/** Re-run a complete InnoDB transaction on normalized error 1213, within the attempt limit. */
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;
}
}
}The first attempt forces an opposite-order deadlock. The victim's second attempt runs both UPDATEs again in a new transaction, after the survivor commits. The scenario asserts two attempts and final balances 115 and 85:
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 affectedB 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 affectedAttempt 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.11 · Run it yourself · Scenario source
What must be inside the retry
Re-run the complete transaction and its decisions from fresh reads, not only the statement that found the deadlock. Keep external effects out of a blindly replayed callback; rollback cannot undo a remote operation. The outbox lesson explains that boundary. Retry exhaustion and non-1213 errors propagate to the caller, which must handle the applicable transaction state. The demonstrated scenario exercises the successful second attempt, not every exhaustion or cleanup path.
Errno 1205 has a different scope. With innodb_rollback_on_timeout=OFF, the default, a row-lock timeout rolls back the waiting statement, not the whole transaction, as documented in the error-handling manual. If you choose a whole-transaction retry, explicitly roll back first. With innodb_rollback_on_timeout=ON, the whole transaction is rolled back. Other errors require their own classification; do not pass all of them through the deadlock rule.
SERIALIZABLE can produce 1213 through conflicting locks. This lesson does not measure its deadlock rate or promise eventual success under persistent contention. Limit attempts, log exhaustion, and choose application backoff separately; the helper shows the bounded replay mechanism without a backoff policy.