sql server中的死锁是指进程之间互相永久阻塞的状态，下文就将为您介绍如何检测sql server死锁，希望对您有所帮助。
死锁（deadlock）指进程之间互相永久阻塞的状态，SQL可以检测到死锁，并选择终止其中一个事务以干预sql server死锁状态。
第一步：首先创建两个测试表，表goods_sort和goods
表goods_sort：创建并写入测试数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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：创建并写入测试数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
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：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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
|
然后运行事务1，接着马上运行事务2，这种情况下某一个事务会提示发生了死锁，修改不成功。另外一个事务则完成。
第一点：使用TRY.CATCH让产生异常的事务能继续完成后面的代码。
第二点：使用WAITFOR DELAY产生造成死锁的发生环境。
第三点：使用ERROR_NUMBER()来判断是否发生事务。
第四点：发生死锁，写SQL Server日志或者返回应用程序去写日志。便于检查日志的时候发现存在死锁并做相应的修改。
以上内容给大家介绍了SQL死锁检测的方法，希望大家喜欢。