模拟死锁

模拟死锁

 -- Batch #1

   CREATE DATABASE deadlocktest

   GO

   USE deadlocktest

   SET NOCOUNT ON

   DBCC TRACEON (1222, -1)

   -- 在SQL2005中，增加了一个新的dbcc参数，就是1222，原来在2000下，我们知道，可以执行dbcc    

   -- traceon(1204,3605,-1)看到所有的死锁信息。SqlServer 2005中，对于1204进行了增强，这就是1222。

   GO   

   

   IF OBJECT_ID ('t1') IS NOT NULL DROP TABLE t1

   IF OBJECT_ID ('p1') IS NOT NULL DROP PROC p1

   IF OBJECT_ID ('p2') IS NOT NULL DROP PROC p2

   GO

   CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 char(5000))

   GO

   DECLARE @x int

   SET @x = 1

   WHILE (@x <= 1000) BEGIN

            INSERT INTO t1 VALUES (@x*2, @x*2, @x*2, @x*2)

            SET @x = @x + 1

   END

   GO

 

   CREATE CLUSTERED INDEX cidx ON t1 (c1)

   CREATE NONCLUSTERED INDEX idx1 ON t1 (c2)

   GO

   CREATE PROC p1 @p1 int AS SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1

   GO

   CREATE PROC p2 @p1 int AS

            UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1

            UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1

   GO

   

   

    -- Batch #2

   USE deadlocktest

   SET NOCOUNT ON

   WHILE (1=1) EXEC p2 4

   GO

   

     -- Batch #3

   USE deadlocktest

   SET NOCOUNT ON

   CREATE TABLE #t1 (c2 int, c3 int)

   GO

   WHILE (1=1) BEGIN

             INSERT INTO #t1 EXEC p1 4

             TRUNCATE TABLE #t1

   END

   GO