Coders' Corner Search:
Databases
    InterBase (25)
    MS-SQL (4)
    mysql (24)
    Oracle (1)

 
Exchange Links
 

Links to us

Book on

 

 

 General :: Databases :: MS-SQL
Tricks how to deal with Microsoft SQL Server (MS-SQL).

Articles:

Featured Article

How can I simulate a deadlock for testing purposes?

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