Testing Deadlock Conditions

  • EddieN1

    Old Hand

    Points: 352

    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: 11955

    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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply