Sql Server 检测死锁的SQL语句及死锁成因

首先创建一个标量值函数DigLock，用来递归检测SqlServer中的每一个会话是否存在加锁循环，如果该函数最终返回1则表示检测到了加锁循环 （也就是说检测到了死锁），如果最终返回0则表示没有检测到加锁循环。

1 CREATE FUNCTION [dbo].[DigLock] 2 (

3 @spid int,

4 @orginSpid int 5 )

6 RETURNS bit 7 AS 8 BEGIN 9 declare @blockedSpid int=null;

10

11 select @blockedSpid=spid from sysprocesses where blocked<>0 and blocked=@spid12

13 if @blockedSpid=@orginSpid14 return 1;--检测到了死锁15

16 if @blockedSpid is not null17 begin18 return dbo.DigLock(@blockedSpid,@orginSpid);

19 end20

21 return 0;--未检测到死锁22 END

然后定义一个视图V_DeadLock_Process，调用上面创建的函数，如果查询出了结果说明当前Sql Server中存在死锁

按 Ctrl+C 复制代码

按 Ctrl+C 复制代码

查询视图V_DeadLock_Process，如果当前Sql Server中存在死锁的话就会显示查询到了记录

Select * from [dbo].[V_DeadLock_Process]

上图显示，53号会话锁住了54号会话，54号会话又锁住了53号会话，所以当前Sql Server中存在死锁。

然后可以使用DBCC INPUTBUFFER语句传入上面视图查询到的会话spid，找到造成死锁的Sql语句

DBCC INPUTBUFFER (53)--输入会话spid，可查询该会话正在执行的Sql语句，从而知道发生死锁的会话执行了什么Sql语句

SQL SERVER中的两种常见死锁及解决思路

  作者及来源： Bright Zhang - 博客园    收藏到→_→：

摘要: SQL SERVER中的两种常见死锁及解决思路

"SQL SERVER中的两种常见死锁及解决思路"：

关键词：sql server 中 两种 常见 死锁 解决 思路

在sql server中，死锁都与一种锁有关，那就是排它锁（x锁）。由于在同一时间对同一个数据库资源只能有一个数据库进程可以拥有排它锁。因此，一旦多个进程都需要获取某个或者同一个数据库资源的排它访问权，而又被对方所阻止的时候，死锁就会出现。

第一种就是最经典的race condition思路，两个数据库进程，a和b，则a进程中修改数据表t1（假设id=100），再修改数据表t2（假设id=200）；而在进程b中修改数据表t2（id=200），然后再修改数据库表t1（id=100），当两个进程在并发的情况下，就会出现a尝试获取t2的排他锁或意向排他锁，b尝试获取t1的排他锁或意向排他锁的情况，由于a已经占有了t1的排他锁，b占有了t2的排他锁，因此，进程a和进程b一直处于僵持地步，从而造成了死锁。

脚本演示:

进程1：

begin tran

update customer set name='test' where id=2

waitfor delay '00:00:20';

update bill set remark=remark+':test' where id=2;

commit tran

进程2：

begin tran

update bill set remark=remark+':test' where id=2;

waitfor delay '00:00:20';

update customer set name='test' where id=2;

commit tran

两个进程同时执行，数据库发现存在死锁，选择一个牺牲品（victim），并直接将其kill掉：

msg 1205, level 13, state 51, line 6

transaction (process id xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. rerun the transaction.

打开1222 trace的error log，我们发现数据库服务记录了此次死锁的最为详细的信息

此文来自: 马开东博客 转载请注明出处 网址：
http://www.makaidong.com

，我摘取最后一段：

2011-01-28 23:12:59.82 spid16s       resource-list

2011-01-28 23:12:59.82 spid16s        keylock hobtid=72057594042515456 dbid=6 objectname=test.dbo.customer indexname=pk_customer id=lock4203a80 mode=x associatedobjectid=72057594042515456

2011-01-28 23:12:59.82 spid16s         owner-list

2011-01-28 23:12:59.82 spid16s          owner id=process398d48 mode=x

2011-01-28 23:12:59.82 spid16s         waiter-list

2011-01-28 23:12:59.82 spid16s          waiter id=process399108 mode=x requesttype=wait

2011-01-28 23:12:59.82 spid16s        keylock hobtid=72057594043236352 dbid=6 objectname=test.dbo.bill indexname=pk_bill id=lock4205200 mode=x associatedobjectid=72057594043236352

2011-01-28 23:12:59.82 spid16s         owner-list

2011-01-28 23:12:59.82 spid16s          owner id=process399108 mode=x

2011-01-28 23:12:59.82 spid16s         waiter-list

2011-01-28 23:12:59.82 spid16s          waiter id=process398d48 mode=x requesttype=wait

从这一段日志中我们可以看到，资源列表中有两个资源，每个资源都处于排它锁状态，同时每个进程的请求类型都为等待，也就是等待对方释放对自己所需资源的排它锁。

第二种死锁是由数据库底层在锁的转换时出现僵持情况造成的。例如，两个进程在各自的事务中都获取了表t中某行(id=300)的共享锁，而都需要对该行做修改，那么两个进程都要获取该行的意向排他锁，由于两个进程都拥有该行的共享锁，因此两个进程出现争端，从而产生死锁。对于这种死锁，数据库选择一个牺牲品并终止它，从而来解决死锁问题。

脚本演示

两个或多个进程同时执行如下脚本：

begin tran

select * from customer where id=2;

waitfor delay '00:00:05';

update customer set name=name+'a' where id=2;

commit tran

这样两个进程就出现了死锁，数据库提供仲裁，选择一个牺牲品来自动解除死锁：

msg 1205, level 13, state 51, line 8

transaction (process id xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. rerun the transaction.

打开1222 trace的error log，我仍旧摘取最后一段：

2011-01-28 23:52:48.52 spid14s       resource-list

2011-01-28 23:52:48.52 spid14s        keylock hobtid=72057594042515456 dbid=6 objectname=test.dbo.customer indexname=pk_customer id=lock420de80 mode=s associatedobjectid=72057594042515456

2011-01-28 23:52:48.52 spid14s         owner-list

2011-01-28 23:52:48.52 spid14s          owner id=process38ad48 mode=s

2011-01-28 23:52:48.52 spid14s          owner id=process398f28 mode=s

2011-01-28 23:52:48.52 spid14s         waiter-list

2011-01-28 23:52:48.52 spid14s          waiter id=process38ad48 mode=x requesttype=convert

2011-01-28 23:52:48.52 spid14s          waiter id=process398f28 mode=x requesttype=convert

从两种死锁的错误日志来看，我们可以发现有点差别，

此文来自: 马开东博客 转载请注明出处 网址：
http://www.makaidong.com

第一种死锁的requesttype为wait，而第二种死锁的requesttype为convert。

因为两种死锁产生的情形是不同的，第一种死锁是相互锁定对方需要的资源、阻止对方获取所需资源的排他访问权所造成的。第二种死锁是共同拥有同一资源的共享访问权，都在要求获取排它访问权而造成的。

从第一种死锁产生的情况看，在比较复杂的业务逻辑中，访问数据库的顺序一定要协调好，如果出现混乱，那么极有可能出现这种不必要的麻烦。

而第二种死锁似乎我们对此无能为力，因为在处理从共享锁到排它锁转换的过程由数据库来操纵。而最讨厌的是，经常会从event view中能够看到此类死锁的身影，查遍了很多地方都找不到原因。

我们仔细观察我在模拟这种死锁的sql脚本中，我在select语句之后增加了waitfor语句等待5秒钟，这是最为关键的地方，如果我去掉等待，那么我用手动是几乎不可能模拟出由共享锁升级到排它锁的死锁的，如果我再去掉select语句，那么就绝对不会有此类死锁了，一次更新就是一个更新锁(u锁），对同一个数据库资源，sql server是不会允许多于一个进程在申请同一个数据库资源时存在交叉。那么同样的道理，之所以出现这种死锁，就是由于让多于一个进程拥有了同一个数据库资源的共享锁所导致的。我不能说我的这种理解非常恰当，但是从这种死锁所产生的场景来看，只要避免共享锁过早被占，就能够解决此类死锁。

避免共享锁过早被占，其实还可以解决另外一个问题，那就是不可重复读的问题。因为共享锁过早被占，因为这在不同的进程中，数据库资源被过早的检索出来，这样就会导致不同进程的操作被覆盖，而不是累加。

那么在开发中，如何做来避免这种死锁呢？通过上面的分析，我的建议是对于数据一致性要求比较高而且操作比较频繁、复杂的数据库资源上，使用sqltransaction(isolation level=serializable)，它采用的是悲观的锁定策略，在不同的进程中可以确保进程等待，而不会出现共享锁提前被占的情况。

var url = window.location.href;document.write("此文链接："+url+"

");document.write("转载请注明出处："+document.title+"");