Topic 45 of 52 · Expert
How do you safely retry a transaction after a deadlock?
A deadlock is a cycle of transactions waiting for locks. The database aborts a victim so other transactions can proceed. Retry the whole logical transaction, not merely the last failed statement.
MySQL 8-compatible SELECT syntax; sample queries also execute in SQLite 3. Window functions require MySQL 8+.
Detailed answer
A deadlock is a cycle of transactions waiting for locks. The database aborts a victim so other transactions can proceed. Retry the whole logical transaction, not merely the last failed statement.
Use bounded exponential backoff with jitter and a maximum retry count. Keep transactions short, acquire resources in a consistent order and ensure external side effects are not duplicated.
An idempotency key or transactional outbox separates the committed business operation from email or payment side effects. A client timeout can leave commit status uncertain, so reconcile by the key before retrying.
Internal working
InnoDB detects lock-wait cycles and rolls back a chosen transaction. Snapshot reads and locking reads differ; isolation guarantees and lock modes must be understood for the target engine.
Practical scenario
Two account transfers lock accounts in opposite order. Sort account IDs before locking and record a unique transfer ID in the same transaction.
-- MySQL 8 / InnoDB
START TRANSACTION;
SELECT id, balance FROM accounts WHERE id IN (1,2) ORDER BY id FOR UPDATE;
-- Apply a unique transfer operation here, then COMMIT.Common mistakes
- Retrying only one statement.
- Sending email before the transaction commits.
Key takeaways
- Retry at a transaction boundary.
- Bound retry attempts.
- Make side effects idempotent.
Follow-up challenges
- How do you handle an unknown commit outcome?
- Why can SERIALIZABLE increase retry rates?