25

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

  • 2
    I don't quite understand the question. Can you reword it a little bit? Obviously the two people below understood you well enough, but I'm not quite following you. Do you mean code that "stably" simulates a deadlock? What will you be doing after there is a deadlock? Do you just want to prove that it can happen? – jcolebrand Jan 5 '11 at 22:00
29

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.
7

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

2

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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.