详解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 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 

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

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

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

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

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

以上内容给大家介绍了sql死锁检测的方法，希望大家喜欢。