TX isolation levels (4) How to produce an artificial deadlock

The last article of this series shows, how to produce an artificial deadlock. We have to make a little modification to the table that we’ve used for the examples before – we need to add a primary key. Without primary key, the whole table will be locked, which makes it impossible to produce a deadlock (that’s also, why deadlocks can’t occur on MyISAM tables, because MyISAM uses table locking instead of row locking).

mysql> alter table tt add primary key(d);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

Starting from a table tt with three rows 1, 2 and 3, do following steps:

CLIENT 1
========

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update tt set d=4 where d=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

CLIENT 2
========

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update tt set d=5 where d=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

CLIENT 1
========

mysql> update tt set d=6 where d=1;
Query OK, 1 row affected (10.28 sec)
Rows matched: 1  Changed: 1  Warnings: 0

CLIENT 2
========

mysql> update tt set d=7 where d=2;
ERROR 1213 (40001): Deadlock found when trying to get lock; 
try restarting transaction

After client 2 tries to get a lock on a row that’s already locked from client 1, a deadlock occurs, because client 1 is already waiting to get a lock on a row that has been changed by client 2.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.