Demonstrating MySQL Deadlock with a Practical Example

Many introductory articles describe MySQL deadlocks theoretically, but this piece provides a concrete example to illustrate how a deadlock occurs in practice.

First, a test table t_order is created:

CREATE TABLE `t_order` ( `order_id` int(11) NOT NULL, `order_addr` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Two MySQL sessions are opened. In the first session, the transaction isolation level is set to REPEATABLE READ and an INSERT statement that includes SLEEP(10) is executed, causing the session to hold a lock for ten seconds:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; INSERT INTO t_order (order_id, order_addr) SELECT '12345', SLEEP(10) FROM dual WHERE NOT EXISTS (SELECT order_id FROM t_order WHERE order_id='12345');

In the second session, the same isolation level is set and a similar INSERT is run without the sleep:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; INSERT INTO t_order (order_id, order_addr) SELECT '12345', 234 FROM dual WHERE NOT EXISTS (SELECT order_id FROM t_order WHERE order_id='12345');

After a short wait, the second INSERT succeeds, while the first one fails with the deadlock error:

[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

This demonstration shows that the SLEEP function can be used to deliberately create a lock‑wait situation, making it easier to observe and study MySQL deadlocks, which is especially helpful for debugging code that may encounter such issues.

The method also assists developers when testing Java exception handling for deadlock scenarios, allowing them to reproduce the problem locally.

Recommended reading: MySQL High‑Frequency Interview Questions: When Does an Index Become Invalid?