Below are the steps to generate a deadlock so that the behaviour of a deadlock can be illustrated:
— 1) Create Objects for Deadlock Example
USE TEMPDB
CREATE TABLE dbo.foo (col1 INT)
INSERT dbo.foo SELECT 1
CREATE TABLE dbo.bar (col1 INT)
INSERT dbo.bar SELECT 1
— 2) Run in first connection
BEGIN TRAN
UPDATE tempdb.dbo.foo SET col1 = 1
— 3) Run in second connection
BEGIN TRAN
UPDATE tempdb.dbo.bar SET col1 = 1
UPDATE tempdb.dbo.foo SET col1 = 1
— 4) Run in first connection
UPDATE tempdb.dbo.bar SET col1 = 1
Connection two will be chosen as the deadlock victim
ie.
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
