InnoDB Locking and Deadlocks in MySQL Explained

"Deadlock found when trying to get lock; try restarting transaction" is one of the most common MySQL errors in busy apps. It's not a bug in MySQL; it's two transactions waiting on each other.

How a deadlock happens

Transaction A                         Transaction B
UPDATE accounts ... WHERE id = 1      UPDATE accounts ... WHERE id = 2
(locks row 1)                         (locks row 2)
UPDATE accounts ... WHERE id = 2      UPDATE accounts ... WHERE id = 1
(waits for B)                         (waits for A)  -> deadlock

InnoDB detects the cycle immediately and rolls back one transaction (the "victim") so the other can finish.

InnoDB lock basics

• Writes (UPDATE, DELETE) and SELECT ... FOR UPDATE take exclusive row locks on the index records they scan.
• Under the default REPEATABLE READ isolation level, range scans also take gap locks to prevent new rows appearing in the range.
• Without a suitable index, an UPDATE ... WHERE can scan and lock far more rows than it changes.

See what happened

SHOW ENGINE INNODB STATUS\G   -- "LATEST DETECTED DEADLOCK" section

Set innodb_print_all_deadlocks = ON to log every deadlock to the error log.

Prevention patterns

1. Lock in a consistent order: always update rows sorted by primary key.
2. Keep transactions short: no network calls or user waits inside them.
3. Index your WHERE clauses so statements lock only the rows they need.
4. Use atomic single statements instead of read-then-write where possible.
5. Consider READ COMMITTED, which uses far fewer gap locks, if your logic doesn't rely on repeatable reads.

-- Transfer money: lock both rows in id order, regardless of direction
START TRANSACTION;
SELECT id, balance FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

Always retry

Deadlocks can't be eliminated entirely under concurrency. Treat error 1213 (deadlock) and 1205 (lock wait timeout) as retryable: roll back and run the whole transaction again, with a small random delay.