I am testing my application I need some code that stable simulates the deadlock on database site (sql script if possible).
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 only takes a minute to sign up.Sign up to join this community
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;
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.
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
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/