SQL Server: Simulate deadlock

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".