I am testing my application I need some code that stable simulates the deadlock on database site (sql script if possible).
Thank you.
ADDED:
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 communityI am testing my application I need some code that stable simulates the deadlock on database site (sql script if possible).
Thank you.
ADDED:
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.
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
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)
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/