詳解SQL死鎖檢測的方法

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 NULLCONSTRAINT PK_iSortID PRIMARY KEYIDENTITY(1001,1),sSortName NVARCHAR(20) NOT NULL)GOINSERT 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 NULLCONSTRAINT PK_iID PRIMARY KEYIDENTITY(1,1),iGoodsID varchar(20) NOT NULL,sGoodsName nvarchar(100) NOT NULL,iGoodTotal int NOT NULLCONSTRAINT DF_iGoodTotal DEFAULT(0),iPrice int NOT NULLCONSTRAINT DF_iPrice DEFAULT(0),iPriceTotal int NOT NULL,iSortID int NOT NULL,tAddDate smalldatetime NOT NULLCONSTRAINT DF_tAddDate DEFAULT getdate())GOINSERT 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 TRYBEGIN TRANUPDATE dbo.goods_sort SET sSortName='女鞋' WHERE iSortID=1003;WAITFOR DELAY '00:00:05';UPDATE dbo.goods SET sGoodsName='胖子羽絨服' WHERE iID=2;COMMIT TRANEND TRYBEGIN CATCHIF (XACT_STATE()=-1)ROLLBACK TRAN;--ERROR_NUMBER()值為1205則表示發生了死鎖IF (ERROR_NUMBER() = 1205)PRINT '事務1發生了死鎖'--寫SQL Server日志或者返回錯誤給應用程序END CATCHSELECT 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 TRYBEGIN TRANUPDATE dbo.goods SET sGoodsName='瘦子羽絨服' WHERE iID=2;WAITFOR DELAY '00:00:05';UPDATE dbo.goods_sort SET sSortName='男鞋' WHERE iSortID=1003;COMMIT TRANEND TRYBEGIN CATCHIF (XACT_STATE()=-1)ROLLBACK TRAN;--ERROR_NUMBER()值為1205則表示發生了死鎖IF (ERROR_NUMBER() = 1205)PRINT '事務2發生了死鎖'--寫SQL Server日志或者返回錯誤給應用程序END CATCHSELECT iID,sGoodsName FROM dbo.goods WHERE iID=2;SELECT iSortID,sSortName FROM dbo.goods_sort WHERE iSortID=1003;GO

然后運行事務1，接著馬上運行事務2，這種情況下某一個事務會提示發生了死鎖，修改不成功。另外一個事務則完成。

第一點：使用TRY.CATCH讓產生異常的事務能繼續完成后面的代碼。

第二點：使用WAITFOR DELAY產生造成死鎖的發生環境。

第三點：使用ERROR_NUMBER()來判斷是否發生事務。

第四點：發生死鎖，寫SQL Server日志或者返回應用程序去寫日志。便于檢查日志的時候發現存在死鎖并做相應的修改。

以上內容給大家介紹了SQL死鎖檢測的方法，希望大家喜歡。