How To Experiment Dead Locks in MySQL?

If you want to have some experience with dead locks, you can create two windows running two mysql transactions in two sessions at the REPEATABLE READ transaction isolation level. Then run some UPDATE statements as shown in the tutorial exercise below:

(session 1)

mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE fyi_links SET notes='Lock1' where id=101;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(Exclusive lock (X) placed on row id=101)

Switch to session 2:

(session 2)

mysql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE fyi_links SET notes='Lock2' where id=110;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(Exclusive lock (X) placed on row id=110)

mysql> UPDATE fyi_links SET notes='Good1' where id=101;

(Blocked to wait for exclusive lock (X) on row id=101)

Switch to session 1:

(session 1)

mysql> UPDATE fyi_links SET notes='Good2' where id=110;

(Tried to wait for exclusive lock (X) row id=110)

ERROR 1213 (40001): Deadlock found when trying to get lock; 
   try restarting transaction

(Transaction terminated with a rollback)

Switch to session 2:

(session 2)

(Exclusive lock (X) on row id=101 released)
(Block is removed on this transaction is removed)

Query OK, 1 row affected (10.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

⇒ Error: Deadlock Found When Trying to Get Lock in MySQL

⇐ What Is a Dead Lock in MySQL

⇑ Transaction Management: Commit or Rollback in MySQL

⇑⇑ MySQL Database Tutorials