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.