MySQL Deadlock: Practical Example, Detection & Prevention

What Is a Deadlock

A deadlock occurs when two or more transactions each hold resources the other needs, causing all of them to wait indefinitely.

Reproducing a Deadlock

First, create a test table t_test with an auto-increment id primary key and a quantity column:

CREATE TABLE `t_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quantity` int(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Insert two rows:

INSERT INTO `t_test` VALUES ('1', '1');
INSERT INTO `t_test` VALUES ('2', '2');

Two transactions update the same rows but in opposite order. Transaction 1 updates id=1 then id=2; Transaction 2 updates id=2 then id=1. To make the deadlock deterministic, a SLEEP(10) is added after the first update in each transaction:

# Transaction 1
START TRANSACTION;
UPDATE t_test SET quantity=101 WHERE id = 1;
SELECT SLEEP(10) FROM dual;
UPDATE t_test SET quantity=102 WHERE id = 2;
COMMIT;

# Transaction 2
START TRANSACTION;
UPDATE t_test SET quantity=201 WHERE id = 2;
SELECT SLEEP(10) FROM dual;
UPDATE t_test SET quantity=202 WHERE id = 1;
COMMIT;

Running both concurrently produces a deadlock. MySQL detects it and rolls back one transaction (usually the smaller one). The other commits successfully. In the example, Transaction 2 is rolled back, Transaction 1 commits, leaving quantity=101 for id=1 and quantity=102 for id=2.

Deadlock Detection and Analysis

MySQL enables deadlock detection by default via the innodb_deadlock_detect option. When a deadlock is found, InnoDB rolls back one or more transactions to break the cycle, preferring to roll back the smaller transaction. To inspect the latest deadlock, run: SHOW ENGINE INNODB STATUS; The output includes a LATEST DETECTED DEADLOCK section showing both transactions, their locks, and the wait-for graph. The log excerpt in the article shows:

Transaction 1 (trx id 350231) holds a record lock on id=2 and waits for a lock on id=1.

Transaction 2 (trx id 350232) holds a record lock on id=1 and waits for a lock on id=2.

InnoDB chooses to roll back Transaction 2 ( WE ROLL BACK TRANSACTION (2)).

The detailed lock information includes space ID, page number, index name, lock mode ( X locks rec but not gap), and the physical record hex values.

Prevention Strategies

To minimize deadlocks, the article recommends:

Keep transactions as small and short as possible.

Choose an appropriate transaction isolation level.

Set a reasonable lock wait timeout ( innodb_lock_wait_timeout).

Ensure a consistent order of operations across transactions (e.g., always update rows in ascending id order).

Create proper indexes to reduce the number of rows locked.

Conclusion

Deadlocks are a common runtime error in MySQL. Understanding how they arise, how InnoDB detects and resolves them, and how to design transactions to avoid them is essential for backend developers.