how to create deadlock situation in SQL Server
--Create demo database and table to create deadlock
CREATE DATABASE DeadlockDemo;
GO
USE DeadlockDemo;
GO
CREATE TABLE TableA (
ID INT PRIMARY KEY,
Value NVARCHAR(100)
);
CREATE TABLE TableB (
ID INT PRIMARY KEY,
Value NVARCHAR(100)
);
-- Insert sample rows
INSERT INTO TableA VALUES (1, 'RowA1'), (2, 'RowA2');
INSERT INTO TableB VALUES (1, 'RowB1'), (2, 'RowB2');
GO
--Session -1
USE DeadlockDemo;
BEGIN TRAN;
-- Lock row in TableA
UPDATE TableA SET Value = 'RowA1-Updated' WHERE ID = 1;
-- Pause for 5 seconds so Session 2 can run
WAITFOR DELAY '00:00:05';
-- Try to update TableB (this will conflict with Session 2)
UPDATE TableB SET Value = 'RowB1-Updated' WHERE ID = 1;
COMMIT TRAN;
--Session -2
USE DeadlockDemo;
BEGIN TRAN;
-- Lock row in TableB
UPDATE TableB SET Value = 'RowB1-Updated' WHERE ID = 1;
-- Pause for 5 seconds so Session 1 can run
WAITFOR DELAY '00:00:05';
-- Try to update TableA (this will conflict with Session 1)
UPDATE TableA SET Value = 'RowA1-Updated' WHERE ID = 1;
COMMIT TRAN;