Understanding and Demonstrating MySQL Deadlocks with Example Code

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

The article illustrates this with a scenario where Transaction 1 locks row id=1 and requests id=2, while Transaction 2 already holds id=2 and requests id=1, creating a circular wait.

First, a test table is created:

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

Two rows are inserted:

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

Two transactions are then executed with opposite update orders and a SLEEP(10) to force the overlap:

# 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;

MySQL’s InnoDB deadlock detector (enabled by default via innodb_deadlock_detect) automatically rolls back one of the transactions; the status can be inspected with: SHOW ENGINE INNODB STATUS; The output shows the two competing transactions, the locks they hold, and which lock caused the wait, confirming that a deadlock was detected and resolved by rolling back the second transaction.

To reduce the likelihood of deadlocks, the article recommends: keeping transactions short and small, choosing appropriate isolation levels, setting lock‑wait timeouts, ordering operations consistently, and creating suitable indexes.

Understanding these concepts helps developers diagnose and avoid deadlocks when working with MySQL databases.