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.