To use the deadlock analysis script, you first need a deadlock to analyze. Creating a deadlock is pretty easy; just follow the instructions and use the code below.
First, create the environment:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE tempdb; GO IF OBJECT_ID('dbo.Test1') IS NOT NULL DROP TABLE dbo.Test1; IF OBJECT_ID('dbo.Test2') IS NOT NULL DROP TABLE dbo.Test2; CREATE TABLE dbo.Test1 (col1 INT); CREATE TABLE dbo.Test2 (col2 INT); INSERT INTO dbo.Test1 VALUES (1),(2),(3),(4),(5); INSERT INTO dbo.Test2 VALUES (1),(2),(3),(4),(5); GO |
Next, open up two query windows in SSMS. In the first window, run this code:
|
1 2 3 |
USE tempdb; BEGIN TRANSACTION UPDATE dbo.Test1 SET col1 = col1*10 WHERE col1=3; |
In the second window, run this code. Note that this transaction does not complete.
|
1 2 3 4 5 |
USE tempdb; BEGIN TRANSACTION; UPDATE dbo.Test2 SET col2 = col2*20 WHERE col2 = 4; UPDATE dbo.Test1 SET col1 = col1*20 WHERE col1 = 3; COMMIT TRANSACTION; |
Return to the first window, and run this code. Normally, this code will be the deadlock victim.
|
1 2 3 4 |
UPDATE dbo.Test2 SET col2 = col2*10 WHERE col2 = 4; COMMIT TRANSACTION; SELECT * FROM dbo.Test1; SELECT * FROM dbo.Test2; |
And there you have your very own deadlock.
