For this deadlock simulation we need to have 2 tables, each one of them with at least one record:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AA]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AA](
[A] [int] NULL
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BB]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BB](
[B] [int] NULL
) ON [PRIMARY]
END
GO
INSERT INTO AA (A) VALUES (1);
GO
INSERT INTO BB (B) VALUES (2);
GO
Now we will open two sessions:
|
Session #1 |
Session #2 |
|
BEGIN TRANSACTION; Update AA Set A = 3; WAITFOR DELAY '00:00:03'; Update BB Set B = 4; ROLLBACK TRANSACTION; |
BEGIN TRANSACTION; Update BB Set B = 4; WAITFOR DELAY '00:00:03'; Update AA Set A = 3; ROLLBACK TRANSACTION; |
Start execute Session #1 and immediately execute Session #2.
The 2 sessions are waiting for each other: Session #1 lock AA, Session #2 lock BB.
This is a deadlock.
One of them will be chosen (By SQL Server) as a deadlock victim and will be "killed".
