How can I simulate a deadlock for testing purposes?

Databases
InterBase (28)
MS-SQL (5)
mysql (35)
Oracle (1)

Exchange Links

About this site

How can I simulate a deadlock for testing purposes?

This article has not been rated yet. After reading, feel free to leave comments and rate it.

Question:

How can I simulate a deadlock for testing purposes?

Answer:

Below is a quick recipe for a dead lock. Two transactions, one first updating table 1, then 2 and the other one doing it in reverse order.

Both transactions wait in the middle for 20 seconds to give you some time to execute them 'simulaneously'.

When you run the two in transactions in two windows 'at the same time', you'll only have to wait ~20 seconds, and one of the windows will experience a dead lock.

CREATE TABLE t1 (i int);
CREATE TABLE t2 (i int);

INSERT t1 SELECT 1;
INSERT t2 SELECT 9;


/* in one window enter: */
BEGIN TRAN
UPDATE t1 SET i = 11 WHERE i = 1
WAITFOR DELAY '00:00:20'
UPDATE t2 SET i = 99 WHERE i = 9
COMMIT

/* in a second window (another transaction) enter: */
BEGIN TRAN
UPDATE t2 SET i = 99 WHERE i = 9
WAITFOR DELAY '00:00:20'
UPDATE t1 SET i = 11 WHERE i = 1
COMMIT

Comments:

2008-03-18, 02:01:16

anonymous from Iran

thanks for your advices,i should try it,then i say my poinion.

Email address (not necessary): Rate as	Hide my email when showing my comment. Please notify me once a day about new comments on this topic. Please provide a valid email address if you select this option.

It seems that you are from Washington, US . Info/ Feedback on this	Show city and country Show country only Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:
Add a picture: Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity. The owner of this web site reserves the right to delete such material.