Definition
- Circular wait – transaction A holds row 1 and waits for row 2 while transaction B holds row 2 and waits for row 1; neither can proceed.
- Automatic detection – InnoDB maintains a wait-for graph, detects the cycle, and immediately rolls back one victim (the cheapest to undo).
- Application retry – the victim receives error 1213 (SQLState 40001) and must re-run the whole transaction with bounded retries and randomized backoff.
- Reduce frequency – use consistent lock ordering (always lock the lower id first), keep transactions short, and index predicates so fewer rows are locked.
- Never claim impossible – deadlocks are a normal, expected outcome under concurrency; robust systems treat them as a retryable condition.
Two National Bank of India clerks each grab one of two ledgers the other needs: clerk A holds the savings ledger and reaches for the loan ledger, while clerk B holds the loan ledger and reaches for the savings ledger. A supervisor spots this standoff, tells one clerk to drop everything and start over, which frees a ledger so the other finishes. The clerk who was sent back simply tries the whole task again.
Understanding the Concepts
What exactly happens during a deadlock?
A deadlock arises from a specific sequence under row locking. Transaction A acquires an exclusive lock on row 1 and then requests row 2. Transaction B has already acquired row 2 and then requests row 1. Both wait forever unless the engine intervenes.
-- Transaction A: transfer from account 1001 to 1002
BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1001; -- locks row 1001
UPDATE accounts SET balance = balance + 5000 WHERE id = 1002; -- WAITS for B
-- Transaction B: transfer from account 1002 to 1001
BEGIN;
UPDATE accounts SET balance = balance - 3000 WHERE id = 1002; -- locks row 1002
UPDATE accounts SET balance = balance + 3000 WHERE id = 1001; -- WAITS for A
-- DEADLOCK: A waits for B, B waits for A
How does InnoDB detect and resolve the deadlock?
InnoDB maintains a wait-for graph and runs automatic deadlock detection. When it finds a cycle, it chooses a victim (typically the transaction that has done the least work) and rolls it back immediately.
Wait-For Graph:
A ──waits for──▶ B
B ──waits for──▶ A
│
▼
Cycle detected!
Victim: B (fewer rows modified)
B is rolled back, error 1213 returned
A acquires the freed lock and proceeds
-- InnoDB returns this error to the victim:
-- ERROR 1213 (40001): Deadlock found when trying to get lock;
-- try restarting transaction
Why must the application retry?
Because the victim's transaction is rolled back, any work it did is lost. The application must catch the error and re-run the entire transaction.
package com.bank.service;
import java.sql.*;
public class TransferService {
private static final int MAX_RETRIES = 3;
public void transfer(Connection conn, long fromId, long toId,
java.math.BigDecimal amount) throws SQLException {
for (int attempt = 1; attempt <= MAX_RETRIES; attempt++) {
try {
conn.setAutoCommit(false);
debit(conn, fromId, amount);
credit(conn, toId, amount);
conn.commit();
return; // success
} catch (SQLException e) {
conn.rollback();
if (e.getSQLState().equals("40001") && attempt < MAX_RETRIES) {
// Deadlock: retry with randomized backoff
sleepRandom(50, 200);
continue;
}
throw e; // non-deadlock error or retries exhausted
}
}
}
private void sleepRandom(int minMs, int maxMs) {
try {
Thread.sleep(minMs + (long)(Math.random() * (maxMs - minMs)));
} catch (InterruptedException e) {
Thread.currentThread().interrupt();
}
}
private void debit(Connection conn, long id, java.math.BigDecimal amt)
throws SQLException {
try (PreparedStatement ps = conn.prepareStatement(
"UPDATE accounts SET balance = balance - ? WHERE id = ?")) {
ps.setBigDecimal(1, amt);
ps.setLong(2, id);
ps.executeUpdate();
}
}
private void credit(Connection conn, long id, java.math.BigDecimal amt)
throws SQLException {
try (PreparedStatement ps = conn.prepareStatement(
"UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
ps.setBigDecimal(1, amt);
ps.setLong(2, id);
ps.executeUpdate();
}
}
}
How does consistent lock ordering prevent deadlocks?
If every transaction always locks the lower account id first, the circular wait cannot form.
package com.bank.service;
import java.math.BigDecimal;
import java.sql.*;
public class SafeTransferService {
public void transfer(Connection conn, long fromId, long toId,
BigDecimal amount) throws SQLException {
// Always lock the lower ID first to prevent deadlock
long firstId = Math.min(fromId, toId);
long secondId = Math.max(fromId, toId);
conn.setAutoCommit(false);
try {
lockAccount(conn, firstId);
lockAccount(conn, secondId);
debit(conn, fromId, amount);
credit(conn, toId, amount);
conn.commit();
} catch (SQLException e) {
conn.rollback();
throw e;
}
}
private void lockAccount(Connection conn, long id) throws SQLException {
try (PreparedStatement ps = conn.prepareStatement(
"SELECT id FROM accounts WHERE id = ? FOR UPDATE")) {
ps.setLong(1, id);
ps.executeQuery();
}
}
}
How do short transactions reduce deadlocks?
Keeping transactions short minimizes the window in which locks are held, reducing the chance of overlap.
-- Wrong: long transaction with unnecessary work between locks
BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1001;
-- ... call external API here (holds lock for seconds!) ...
UPDATE accounts SET balance = balance + 5000 WHERE id = 1002;
COMMIT;
-- Correct: do external work OUTSIDE the transaction
-- 1. Call external API first
-- 2. THEN start the short transaction
BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1001;
UPDATE accounts SET balance = balance + 5000 WHERE id = 1002;
COMMIT;
How does proper indexing reduce deadlocks?
Good indexes ensure a statement locks only the few rows it needs via an index seek instead of scanning and locking many rows.
-- Without index: Seq Scan locks many rows
UPDATE transactions SET status = 'PROCESSED'
WHERE account_id = 1001 AND status = 'PENDING';
-- Seq Scan locks every row it examines
-- With index: only target rows are locked
CREATE INDEX idx_txn_acct_status ON transactions (account_id, status);
-- Now locks only the matching PENDING rows for account 1001
How is a deadlock different from simple lock contention?
Simple contention is one transaction waiting for another to finish. A deadlock is a mutual, unbreakable standoff.
| Feature | Lock Contention | Deadlock |
|---|---|---|
| Pattern | A waits for B | A waits for B AND B waits for A |
| Resolution | B commits, A proceeds | Engine rolls back one victim |
| Application action | Just wait | Must retry |
| Frequency | Normal, expected | Less frequent but still expected |
What should a senior engineer know?
The senior framing is a two-part answer: design to make deadlocks rare and design to survive the ones that still occur.
- Consistent lock ordering is the single most effective prevention.
- Short transactions reduce the overlap window.
- Proper indexing locks fewer rows.
- Lower isolation levels (where correctness allows) reduce gap and next-key locks.
- The retry pattern must be bounded, with randomized backoff.
- Monitor deadlock frequency:
SHOW ENGINE INNODB STATUSshows the last deadlock.
-- MySQL: check the last deadlock
SHOW ENGINE INNODB STATUS;
-- Look for the LATEST DETECTED DEADLOCK section
Visual Overview
Transaction A Transaction B
───────────── ─────────────
LOCK row 1001 (X) ✓ LOCK row 1002 (X) ✓
│ │
▼ ▼
REQUEST row 1002 ──── WAIT ──── REQUEST row 1001
│ │
└────────── DEADLOCK ────────────┘
│
▼
InnoDB detects cycle
in wait-for graph
│
▼
Victim (B) rolled back
Error 1213 returned
│
▼
A acquires freed lock
A completes
B retries
Key Takeaway
A deadlock is a circular wait between transactions: A holds row 1 and requests row 2, while B holds row 2 and requests row 1, forming a cycle in the wait-for graph that neither can escape. InnoDB handles it automatically by maintaining a wait-for graph, detecting the cycle, and immediately rolling back one transaction as the victim, returning error 1213 so the surviving transaction completes.
Because the victim is rolled back, the application must be ready to retry. The correct pattern is to catch the deadlock error (SQLState 40001), re-run the whole transaction a small bounded number of times with randomized backoff, which is safe only because the transaction is written to be fully re-runnable.
To reduce deadlocks, consistent lock ordering is the most effective technique: if every transfer always locks the lower account id first, the circular wait cannot form. Short transactions minimize the window locks are held, and proper indexing ensures only the needed rows are locked.
At the National Bank of India the transfer service deadlocked when opposing transfers locked the same two accounts in opposite order. Rajesh fixed it by making every transfer lock the lower id first and wrapping the transfer in a bounded retry, so any residual deadlock is transparently re-attempted.
Remember one sentence: a deadlock is a circular lock wait that InnoDB resolves by rolling back a victim, so the application must catch the error and retry, and you reduce deadlocks with consistent lock ordering, short transactions, and good indexing.
---