Simulate deadlock with more than 2 processes

How to Simulate deadlock with more than 2 processes in SQL Server test environment? Can someone help me with the code? Just to clarify my question again, I don't want deadlock simulation with only 2 processes. I know how to produce deadlock with 2 processes but am unable to produce it with more than two processes.

If you know how to simulate it with 2, you know how to simulate it with 3, or with N. You have to build a cyclic graph:

• 2 nodes: A->B->A (process A waits on B, process B waits on A)

• 3 nodes: A->B->C->A (process A waits on B, B waits on C, C waits on A)

• ...

• N nodes: P1->P2->...Pn->P1

To construct the Px->Py primitive (Process X waits on Process Y) use your favorite blocking mechanism. For example, row locking on a specific key P1->P2->P3->P1:

CREATE TABLE rows (key INT NOT NULL PRIMARY KEY);

1) P1, from session 1:

BEGIN TRANSACTION
INSERT INTO rows (key) VALUES (1);

2) P2, from session 2:

BEGIN TRANSACTION
INSERT INTO rows (key) VALUES (2);

3) Have P1 wait on P2. From session 1:

SELECT key FROM rows WHERE key = 2;

4) Continue with P3, session 3:

BEGIN TRANSACTION
INSERT INTO rows (key) VALUES (3);

5) Add the P2->P3 wait, in session 2:

SELECT key FROM rows WHERE key = 3;

6) To complete the cycle, add the P3->P1 wait, in session 3:

SELECT key FROM rows WHERE key = 1;

As the wait graph created a cycle, the 3 processes are now deadlocked. In a short while the engine deadlock detection mechanism (which periodically walks the wait graphs looking for cycles) detects this cycle and breaks it by choosing a victim and aborting it's transaction then raising error 1205 in the victim session.

To extend to N nodes, repeat steps 4) and 5) as necessary.

StackExchange Database Administrators Q#139479, answer score: 19