Code to simulate deadlock

Question

I am testing my application I need some code that stable simulates the deadlock on database site (sql script if possible).

Thank you.

ADDED:

Reproducing deadlocks involving only one table

Answer 1

The best way would be to use tables you already have. Create two tables -- table-a, table-b For a test you can even update the same column with the same information so you don't affect any real data.

For instance UPDATE table_a set ID = ID where ID = 100;

Open two sessions to the same database. On one, run

BEGIN TRAN
update table_a set ID=ID where ID = 100;

On two run

BEGIN TRAN
update table_b set ID=ID where ID =100;

Then, copy the update statements to the opposing sessions and run at the same time. In one,

update table_b set ID=ID where ID =100;

In two

update table_a set ID=ID where ID = 100;

I just tried this now and got on MS-SQL

Msg 1205, Level 13, State 56, Line 1
Transaction (Process ID 23) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Answer 2

Use the sp_getapplock system stored procedure to take what ever locks to need on your sample code.

Strictly speaking, this is a Dijkstra semaphore. Still damn useful though

Answer 3

Here's another method similar to the one posted above -->

CREATE TABLE Tbl1 (id INT NOT NULL PRIMARY KEY CLUSTERED, col INT)
CREATE TABLE Tbl2 (id INT NOT NULL PRIMARY KEY CLUSTERED, col INT REFERENCES dbo.Tbl1(id))

Script to be used in Query Window #1

BEGIN TRAN
INSERT dbo.Tbl1 (id, col) VALUES (2, 999)

Script to be used in Query Window #2

BEGIN TRAN
INSERT dbo.Tbl2 (id, col) VALUES (111, 2)

Script to be added to Query Window #1

INSERT dbo.Tbl2 (id, col) VALUES (111, 555)

For additional details on this, refer to http://ajitananthram.wordpress.com/2014/02/23/scripts-to-force-a-deadlock-in-sql-server/