← SQL roadmap

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.

ExpertSenior · 5–8 yearsProduction concurrency

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?
Practise SQL with the AI mentor

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.