Simulate deadlock with more than 2 processes

Question

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.

Answer 1

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: P₁->P₂->...P_n->P₁

To construct the P_x->P_y primitive (Process X waits on Process Y) use your favorite blocking mechanism. For example, row locking on a specific key P₁->P₂->P₃->P₁:

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

1) P₁, from session 1:

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

2) P₂, from session 2:

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

3) Have P₁ wait on P₂. From session 1:

SELECT key FROM rows WHERE key = 2;

4) Continue with P₃, session 3:

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

5) Add the P₂->P₃ wait, in session 2:

SELECT key FROM rows WHERE key = 3;

6) To complete the cycle, add the P₃->P₁ 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.