Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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
add comment

3 Answers

up vote 19 down vote accepted

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.
share|improve this answer
    
it works. thanks. –  garik Jan 6 '11 at 12:31
    
@igor, if it worked, you should mark this as the answer. –  jcolebrand Jan 6 '11 at 15:27
add comment

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

share|improve this answer
    
i remember this old good stored proc :). –  garik Jan 6 '11 at 12:32
    
sp_getapplock won't throw an error. It will either wait for the timeout to expire, or (if there is no timeout), return -3 (msdn.microsoft.com/en-us/library/ms189823.aspx) –  Ian Boyd Aug 9 '11 at 15:46
    
Strictly speaking, this is a (Dijkstra) semaphore en.wikipedia.org/wiki/Semaphore_(programming) Still damn useful though –  gbn Aug 19 '11 at 12:57
add comment

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/

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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