你的位置:首页 > 数据库

[数据库]使用SQL检测死锁


第一步:首先创建两个测试表,表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)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 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())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 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 TRANEND TRYBEGIN CATCH IF (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 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 TRANEND TRYBEGIN CATCH IF (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日志或者返回应用程序去写日志。便于检查日志的时候发现存在死锁并做相应的修改。