Finding Problem Queries with SQL Profiler - Professional SQL Server 2005 Performance Tuning - page 111

To simulate a deadlock event, you will create a simple testing database with a single table. The source
code can be downloaded from the publisher's web site.
CREATE DATABASE TestingDeadlockDB
GO
USE TestingDeadlockDB
CREATE TABLE tbl_deadlock (id int IDENTITY(1,1), Comments VARCHAR(30))
INSERT INTO tbl_deadlock values ('Simulating Deadlock Event')
INSERT INTO tbl_deadlock values ('Simulating Deadlock Event')
CREATE UNIQUE NONCLUSTERED INDEX [CI_id] ON [dbo].[tbl_deadlock]
( [id] ASC )ON [PRIMARY]
GO
Next, you will use a simple script to trigger a deadlock. The purpose is to show that the Profiler is now
fully capable of capturing deadlock events and providing useful information. Open a new query window
(in the following example, you refer to it as connection 1) and type in the following code:
-- Open a new SQL Query Window from SQL Management Studio (connection 1)
USE TestingDeadlockDB
SET NOCOUNT ON
SELECT @@SPID AS SPID
BEGIN TRAN
UPDATE tbl_deadlock
SET Comments = 'Simulating Deadlock Event 2.'
WHERE id = 2
WAITFOR DELAY '00:0:20'
UPDATE tbl_deadlock
SET Comments = 'Simulating Deadlock Event 1.'
WHERE id = 1
There are two T-SQL UPDATE statements in one transaction. The first UPDATE modifies the data row
with id=2 and waits for 10 seconds; the second UPDATE modifies the data row with id=1 . Do not execute
the code yet.
Open another query window (you refer to it as connection 2) and type in the following code. The code
logic is similar to the previous code except it modifies the data row with id=1 first and then the data row
with id=2 . Do not execute the code yet.
-- Open another SQL Query Window from SQL Management Studio (connection 2)
USE TestingDeadlockDB
SET NOCOUNT ON
SELECT @@SPID AS SPID
BEGIN TRAN
UPDATE tbl_deadlock
SET Comments = 'Simulating Deadlock Event 1.'
WHERE id = 1