详解Sqlserver死锁检测的方法

万望

死锁（deadlock）指进程之间互相永久阻塞的状态，SQL可以检测到死锁，并选择终止其中一个事务以干预sql server死锁状态。

第一步：首先创建两个测试表，表goods_sort和goods

表goods_sort：创建并写入测试数据。

1. IF EXISTS(SELECT name FROM sysobjects WHERE name='goods_sort' AND xtype='U')
   
2. DROP TABLE dbo.goods_sort
   
3. --创建商品分类表
   
4. CREATE TABLE dbo.goods_sort(
   
5. iSortID int NOT NULL
   
6. CONSTRAINT PK_iSortID PRIMARY KEY
   
7. IDENTITY(1001,1),
   
8. sSortName NVARCHAR(20) NOT NULL
   
9. )
   
10. GO
    
11. INSERT INTO dbo.goods_sort VALUES('服饰')
    
12. INSERT INTO dbo.goods_sort VALUES('女包')
    
13. INSERT INTO dbo.goods_sort VALUES('鞋子')
    
14. INSERT INTO dbo.goods_sort VALUES('首饰')
    
15. INSERT INTO dbo.goods_sort VALUES('美容')
    
16. GO

复制代码

表goods：创建并写入测试数据：

1. IF EXISTS(SELECT name FROM sysobjects WHERE name='goods' AND xtype='U')
   
2. DROP TABLE dbo.goods;
   
3. --创建商品表
   
4. CREATE TABLE dbo.goods(
   
5. iID int NOT NULL
   
6. CONSTRAINT PK_iID PRIMARY KEY
   
7. IDENTITY(1,1),
   
8. iGoodsID varchar(20) NOT NULL,
   
9. sGoodsName nvarchar(100) NOT NULL,
   
10. iGoodTotal int NOT NULL
    
11. CONSTRAINT DF_iGoodTotal DEFAULT(0),
    
12. iPrice int NOT NULL
    
13. CONSTRAINT DF_iPrice DEFAULT(0),
    
14. iPriceTotal int NOT NULL,
    
15. iSortID int NOT NULL,
    
16. tAddDate smalldatetime NOT NULL
    
17. CONSTRAINT DF_tAddDate DEFAULT getdate()
    
18. )
    
19. GO
    
20. INSERT INTO dbo.goods
    
21. (iGoodsID,sGoodsName,iGoodTotal,iPrice,iPriceTotal,iSortID)
    
22. VALUES('YR6001','瘦身羽绒服',20,200,4000,1001)
    
23. INSERT INTO dbo.goods
    
24. (iGoodsID,sGoodsName,iGoodTotal,iPrice,iPriceTotal,iSortID)
    
25. VALUES('YR6002','加厚羽绒服',20,300,6000,1001)
    
26. INSERT INTO dbo.goods
    
27. (iGoodsID,sGoodsName,iGoodTotal,iPrice,iPriceTotal,iSortID)
    
28. VALUES('BB7001','小黄牛皮马鞍包',30,100,3000,1002)
    
29. INSERT INTO dbo.goods
    
30. (iGoodsID,sGoodsName,iGoodTotal,iPrice,iPriceTotal,iSortID)
    
31. VALUES('BB7002','十字绣流苏包',50,150,7500,1002)
    
32. GO
    

复制代码

第二步：创建两个会产生死锁的事务

事务1：

1. SET NOCOUNT ON;
   
2. SET XACT_ABORT ON;
   
3. GO
   
4. --使用TRY-CATCH，使代码发生错误也继续运行
   
5. BEGIN TRY
   
6. BEGIN TRAN
   
7. UPDATE dbo.goods_sort SET sSortName='女鞋' WHERE iSortID=1003;
   
8. WAITFOR DELAY '00:00:05';
   
9. UPDATE dbo.goods SET sGoodsName='胖子羽绒服' WHERE iID=2;
   
10. COMMIT TRAN
    
11. END TRY
    
12. BEGIN CATCH
    
13. IF (XACT_STATE()=-1)
    
14. ROLLBACK TRAN;
    
15. --ERROR_NUMBER()值为1205则表示发生了死锁
    
16. IF (ERROR_NUMBER() = 1205)
    
17. PRINT '事务1发生了死锁'
    
18. --写SQL Server日志或者返回错误给应用程序
    
19. END CATCH
    
20. SELECT iID,sGoodsName FROM dbo.goods WHERE iID=2;
    
21. SELECT iSortID,sSortName FROM dbo.goods_sort WHERE iSortID=1003;
    
22. GO

复制代码

事务二：

1. SET NOCOUNT ON;
   
2. SET XACT_ABORT ON;
   
3. GO
   
4. --使用TRY-CATCH，使代码发生错误也继续运行
   
5. BEGIN TRY
   
6. BEGIN TRAN
   
7. UPDATE dbo.goods SET sGoodsName='瘦子羽绒服' WHERE iID=2;
   
8. WAITFOR DELAY '00:00:05';
   
9. UPDATE dbo.goods_sort SET sSortName='男鞋' WHERE iSortID=1003;
   
10. COMMIT TRAN
    
11. END TRY
    
12. BEGIN CATCH
    
13. IF (XACT_STATE()=-1)
    
14. ROLLBACK TRAN;
    
15. --ERROR_NUMBER()值为1205则表示发生了死锁
    
16. IF (ERROR_NUMBER() = 1205)
    
17. PRINT '事务2发生了死锁'
    
18. --写SQL Server日志或者返回错误给应用程序
    
19. END CATCH
    
20. SELECT iID,sGoodsName FROM dbo.goods WHERE iID=2;
    
21. SELECT iSortID,sSortName FROM dbo.goods_sort WHERE iSortID=1003;
    
22. GO
    

复制代码

然后运行事务1，接着马上运行事务2，这种情况下某一个事务会提示发生了死锁，修改不成功。另外一个事务则完成。

第一点：使用TRY.CATCH让产生异常的事务能继续完成后面的代码。

第二点：使用WAITFOR DELAY产生造成死锁的发生环境。

第三点：使用ERROR_NUMBER()来判断是否发生事务。

第四点：发生死锁，写SQL Server日志或者返回应用程序去写日志。便于检查日志的时候发现存在死锁并做相应的修改。

万望

1. 死锁原理

    根据操作系统中的定义：死锁是指在一组进程中的各个进程均占有不会释放的资源，但因互相申请被其他进程所站用不会释放的资源而处于的一种永久等待状态。

    死锁的四个必要条件：
互斥条件(Mutual exclusion)：资源不能被共享，只能由一个进程使用。
请求与保持条件(Hold and wait)：已经得到资源的进程可以再次申请新的资源。
非剥夺条件(No pre-emption)：已经分配的资源不能从相应的进程中被强制地剥夺。
循环等待条件(Circular wait)：系统中若干进程组成环路，该环路中每个进程都在等待相邻进程正占用的资源。

万望

陈晓龙

万望 发表于 2016-5-3 22:16
1. 死锁原理

    根据操作系统中的定义：死锁是指在一组进程中的各个进程均占有不会释放的资源，但因互 ...

很详细的嘛！

万望