How to simulate Deadlock in SQL Server, pretty simple

1. Create a table

CREATE TABLE MyDeadlockTable
(
REF INT PRIMARY KEY NOT NULL,
NAME VARCHAR(255)
)
GO

2. Populate a table with the record
INSERT INTO MyDeadlockTable(REF, NAME) VALUES(1, ‘Record 1’)
GO
INSERT INTO MyDeadlockTable(REF, NAME) VALUES(2, ‘Record 2’)
GO

3. On the first window let’s run this
BEGIN TRAN
UPDATE MyDeadlockTable set NAME=NAME WHERE REF = 1;
WAITFOR DELAY ’00:00:15′
UPDATE MyDeadlockTable set NAME=NAME WHERE REF = 2;
COMMIT

4. Open a 2nd window let’s run this
BEGIN TRAN
UPDATE MyDeadlockTable SET NAME=NAME WHERE REF = 2;
WAITFOR DELAY ’00:00:15′
UPDATE MyDeadlockTable SET NAME=NAME WHERE REF = 1;
COMMIT