How can I Cause a Deadlock in MySQL for Testing Purposes

Question

I want to make my Python library working with MySQLdb be able to detect deadlocks and try again. I believe I've coded a good solution, and now I want to test it.

Any ideas for the simplest queries I could run using MySQLdb to create a deadlock condition would be?

system info:

• MySQL 5.0.19
• Client 5.1.11
• Windows XP
• Python 2.4 / MySQLdb 1.2.1 p2

Answer 1

Here's some pseudocode for how i do it in PHP:

Script 1:

START TRANSACTION;
INSERT INTO table <anything you want>;
SLEEP(5);
UPDATE table SET field = 'foo';
COMMIT;

Script 2:

START TRANSACTION;
UPDATE table SET field = 'foo';
SLEEP(5);
INSERT INTO table <anything you want>;
COMMIT;

Execute script 1 and then immediately execute script 2 in another terminal. You'll get a deadlock if the database table already has some data in it (In other words, it starts deadlocking after the second time you try this).

Note that if mysql won't honor the SLEEP() command, use Python's equivalent in the application itself.

Answer 2

you can always run LOCK TABLE tablename from another session (mysql CLI for instance). That might do the trick.

It will remain locked until you release it or disconnect the session.