详解SQL死锁检测的方法_Sql Server

sql server中的死锁是指进程之间互相永久阻塞的状态，下文就将为您介绍如何检测sql server死锁，希望对您有所帮助。

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

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

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

									IF EXISTS(SELECT name FROM sysobjects WHERE name='goods_sort' AND xtype='U')

									DROP TABLE dbo.goods_sort

									--创建商品分类表

									CREATE TABLE dbo.goods_sort(

									iSortID int NOT NULL

									CONSTRAINT PK_iSortID PRIMARY KEY

									IDENTITY(1001,1),

									sSortName NVARCHAR(20) NOT NULL

									)

									GO

									INSERT INTO dbo.goods_sort VALUES('服饰')

									INSERT INTO dbo.goods_sort VALUES('女包')

									INSERT INTO dbo.goods_sort VALUES('鞋子')

									INSERT INTO dbo.goods_sort VALUES('首饰')

									INSERT INTO dbo.goods_sort VALUES('美容')

									GO

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

									IF EXISTS(SELECT name FROM sysobjects WHERE name='goods' AND xtype='U')

									DROP TABLE dbo.goods;

									--创建商品表

									CREATE TABLE dbo.goods(

									iID int NOT NULL

									CONSTRAINT PK_iID PRIMARY KEY

									IDENTITY(1,1),

									iGoodsID varchar(20) NOT NULL,

									sGoodsName nvarchar(100) NOT NULL,

									iGoodTotal int NOT NULL

									CONSTRAINT DF_iGoodTotal DEFAULT(0),

									iPrice int NOT NULL

									CONSTRAINT DF_iPrice DEFAULT(0),

									iPriceTotal int NOT NULL,

									iSortID int NOT NULL,

									tAddDate smalldatetime NOT NULL

									CONSTRAINT DF_tAddDate DEFAULT getdate()

									)

									GO

									INSERT INTO dbo.goods

									(iGoodsID,sGoodsName,iGoodTotal,iPrice,iPriceTotal,iSortID)

									VALUES('YR6001','瘦身羽绒服',20,200,4000,1001)

									INSERT INTO dbo.goods

									(iGoodsID,sGoodsName,iGoodTotal,iPrice,iPriceTotal,iSortID)

									VALUES('YR6002','加厚羽绒服',20,300,6000,1001)

									INSERT INTO dbo.goods

									(iGoodsID,sGoodsName,iGoodTotal,iPrice,iPriceTotal,iSortID)

									VALUES('BB7001','小黄牛皮马鞍包',30,100,3000,1002)

									INSERT INTO dbo.goods

									(iGoodsID,sGoodsName,iGoodTotal,iPrice,iPriceTotal,iSortID)

									VALUES('BB7002','十字绣流苏包',50,150,7500,1002)

									GO

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

事务1：

									SET NOCOUNT ON;

									SET XACT_ABORT ON;

									GO

									--使用TRY-CATCH，使代码发生错误也继续运行

									BEGIN TRY

									BEGIN TRAN

									UPDATE dbo.goods_sort SET sSortName='女鞋' WHERE iSortID=1003;

									WAITFOR DELAY '00:00:05';

									UPDATE dbo.goods SET sGoodsName='胖子羽绒服' WHERE iID=2;

									COMMIT TRAN

									END TRY

									BEGIN CATCH

									IF (XACT_STATE()=-1)

									ROLLBACK TRAN;

									--ERROR_NUMBER()值为1205则表示发生了死锁

									IF (ERROR_NUMBER() = 1205)

									PRINT '事务1发生了死锁'

									--写SQL Server日志或者返回错误给应用程序

									END CATCH

									SELECT iID,sGoodsName FROM dbo.goods WHERE iID=2;

									SELECT iSortID,sSortName FROM dbo.goods_sort WHERE iSortID=1003;

									GO

事务2：

									SET NOCOUNT ON;

									SET XACT_ABORT ON;

									GO

									--使用TRY-CATCH，使代码发生错误也继续运行

									BEGIN TRY

									BEGIN TRAN

									UPDATE dbo.goods SET sGoodsName='瘦子羽绒服' WHERE iID=2;

									WAITFOR DELAY '00:00:05';

									UPDATE dbo.goods_sort SET sSortName='男鞋' WHERE iSortID=1003;

									COMMIT TRAN

									END TRY

									BEGIN CATCH

									IF (XACT_STATE()=-1)

									ROLLBACK TRAN;

									--ERROR_NUMBER()值为1205则表示发生了死锁

									IF (ERROR_NUMBER() = 1205)

									PRINT '事务2发生了死锁'

									--写SQL Server日志或者返回错误给应用程序

									END CATCH

									SELECT iID,sGoodsName FROM dbo.goods WHERE iID=2;

									SELECT iSortID,sSortName FROM dbo.goods_sort WHERE iSortID=1003;

									GO