Deadlocks

Deadlocks

Deadlocks: Detection, Diagnosis, and Avoidance

A deadlock is a cycle of waits: A holds a lock B needs while waiting for one B holds. Two carts that contain the same two books, updated in opposite order, are enough:

Two checkouts lock the same rows in opposite orderSQL

Session A> START TRANSACTION;
Session A> UPDATE products SET stock = stock - 1 WHERE id = 1;
Session A: Query OK, 1 row affected
Session B> START TRANSACTION;
Session B> UPDATE products SET stock = stock - 1 WHERE id = 2;
Session B: Query OK, 1 row affected
Session A> UPDATE products SET stock = stock - 1 WHERE id = 2;
Session A: (waiting)
Session B> UPDATE products SET stock = stock - 1 WHERE id = 1;
Session A: Query OK, 1 row affected
Session B: ERROR 1213 (40001) at line 3: Deadlock found when trying to get lock; try restarting transaction
Session A> COMMIT;

InnoDB's detector (innodb_deadlock_detect, on by default) found the cycle at once, chose a victim, preferring the transaction that changed fewest rows, and rolled back all of it, releasing A. The right response to SQLSTATE 40001 is to retry the whole transaction, as Laravel 2,157 's DB::transaction($callback, 3) does (Transactions and Locks). The InnoDB monitor keeps the latest deadlock:

Extracting the latest deadlock from SHOW ENGINE INNODB STATUSShell

mysql -E -e 'SHOW ENGINE INNODB STATUS' \
  | grep -E '^(\*\*\* |UPDATE |RECORD LOCKS)' \
  | sed -E 's/space id .* (index \w+) of table (\S+) trx id [0-9]+/\1 \2/'

Output

*** (1) TRANSACTION:
UPDATE products SET stock = stock - 1 WHERE id = 2
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS index PRIMARY `shop`.`products` lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS index PRIMARY `shop`.`products` lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
UPDATE products SET stock = stock - 1 WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS index PRIMARY `shop`.`products` lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS index PRIMARY `shop`.`products` lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

It names both statements, the index and the lock modes; innodb_print_all_deadlocks logs every deadlock to the error log. To avoid deadlocks, lock rows in one order (sort a cart by product ID), keep transactions short, index the columns your UPDATEs filter on, and consider READ COMMITTED. None of this rules deadlocks out, so every write path needs the retry.

MENU