Testing Deadlock Conditions

• EddieN1

  SSC-Addicted

  Points: 492

  More actions

  February 25, 2015 at 10:31 am

  #315682

  Can someone suggest a way I can intentionally create a Deadlock condition for testing? I'm running SQL Server Express 2012.

• Bill Talada

  SSChampion

  Points: 11954

  More actions

  February 25, 2015 at 10:45 am

  #1780003

  In SSMS, open three sheets and run sheet 0. Then run sheet 1 and 2 within 10 seconds of each other.

  Remember to do rollbacks in sheets 1 and 2 after failure.

  -- sheet 0

  CREATE TABLE t1 (i int);

  CREATE TABLE t2 (i int);

  GO

  INSERT INTO t1 VALUES (1);

  INSERT INTO t2 VALUES (2);

  GO

  SELECT * FROM t1;

  SELECT * FROM t2;

  GO

  -- sheet 1

  BEGIN TRAN

  UPDATE t1 SET i = 10;

  WAITFOR DELAY '00:00:10';

  UPDATE t2 SET i = 20;

  COMMIT TRAN

  -- rollback

  -- sheet 2

  BEGIN TRAN

  UPDATE t2 SET i = 22;

  WAITFOR DELAY '00:00:10';

  UPDATE t1 SET i = 11;

  COMMIT TRAN

  -- rollback