SQL Server死锁总结

转载自：http://www.supesoft.com/ArticleDisp.asp?ID=4861

1. 死锁原理  

    根据操作系统中的定义：死锁是指在一组进程中的各个进程均占有不会释放的资源，但因互相申请被其他进程所站用不会释放的资源而处于的一种永久等待状态。

    死锁的四个必要条件：
互斥条件 (Mutual exclusion) ：资源不能被共享，只能由一个进程使用。
请求与保持条件 (Hold and wait) ：已经得到资源的进程可以再次申请新的资源。
非剥夺条件 (No pre-emption) ：已经分配的资源不能从相应的进程中被强制地剥夺。
循环等待条件 (Circular wait) ：系统中若干进程组成环路，该环路中每个进程都在等待相邻进程正占用的资源。

对应到 SQL Server 中，当在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁；这些资源可能是：单行 (RID ，堆中的单行 ) 、索引中的键 (KEY ，行锁 ) 、页 (PAG ， 8KB) 、区结构 (EXT ，连续的 8 页 ) 、堆或 B 树 (HOBT) 、表 (TAB ，包括数据和索引 ) 、文件 (File ，数据库文件 ) 、应用程序专用资源 (APP) 、元数据 (METADATA) 、分配单元 (Allocation_Unit) 、整个数据库 (DB) 。 一个死锁示例如下图所示：

    说明： T1 、 T2 表示两个任务； R1 和 R2 表示两个资源；由资源指向任务的箭头 ( 如 R1->T1 ， R2->T2) 表示该资源被改任务所持有；由任务指向资源的箭头 ( 如 T1->S2 ， T2->S1) 表示该任务正在请求对应目标资源；
    其满足上面死锁的四个必要条件：
(1). 互斥：资源 S1 和 S2 不能被共享，同一时间只能由一个任务使用；
(2). 请求与保持条件： T1 持有 S1 的同时，请求 S2 ； T2 持有 S2 的同时请求 S1 ；
(3). 非剥夺条件： T1 无法从 T2 上剥夺 S2 ， T2 也无法从 T1 上剥夺 S1 ；
(4). 循环等待条件：上图中的箭头构成环路，存在循环等待。

2. 死锁排查  

(1). 使用 SQL Server 的系统存储过程 sp_who 和 sp_lock ，可以查看当前数据库中的锁情况；进而根据 objectID(@objID)(SQL Server 2005)/ object_name(@objID)(Sql Server 2000) 可以查看哪个资源被锁，用 dbcc ld(@blk) ，可以查看最后一条发生给 SQL Server 的 Sql 语句；

CREATE   Table  #Who(spid  int ,
    ecid  int ,
    status  nvarchar ( 50 ),
    loginname  nvarchar ( 50 ),
    hostname  nvarchar ( 50 ),
    blk  int ,
    dbname  nvarchar ( 50 ),
    cmd  nvarchar ( 50 ),
    request_ID  int );

CREATE   Table  #Lock(spid  int ,
    dpid  int ,
    objid  int ,
    indld  int ,
     [ Type ]   nvarchar ( 20 ),
    Resource  nvarchar ( 50 ),
    Mode  nvarchar ( 10 ),
    Status  nvarchar ( 10 )
);

INSERT   INTO  #Who
     EXEC  sp_who active   -- 看哪个引起的阻塞，blk 
INSERT   INTO  #Lock
     EXEC  sp_lock   -- 看锁住了那个资源id，objid 

DECLARE   @DBName   nvarchar ( 20 );
SET   @DBName = ' NameOfDataBase '

SELECT  #Who. *   FROM  #Who  WHERE  dbname = @DBName
SELECT  #Lock. *   FROM  #Lock
     JOIN  #Who
         ON  #Who.spid = #Lock.spid
             AND  dbname = @DBName ;

-- 最后发送到SQL Server的语句
DECLARE  crsr  Cursor   FOR
     SELECT  blk  FROM  #Who  WHERE  dbname = @DBName   AND  blk <> 0 ;
DECLARE   @blk   int ;
open  crsr;
FETCH   NEXT   FROM  crsr  INTO   @blk ;
WHILE  ( @@FETCH_STATUS   =   0 )
BEGIN ;
     dbcc  inputbuffer( @blk );
     FETCH   NEXT   FROM  crsr  INTO   @blk ;
END ;
close  crsr;
DEALLOCATE  crsr;

-- 锁定的资源
SELECT  #Who.spid,hostname,objid, [ type ] ,mode, object_name (objid)  as  objName  FROM  #Lock
     JOIN  #Who
         ON  #Who.spid = #Lock.spid
             AND  dbname = @DBName
     WHERE  objid <> 0 ;

DROP   Table  #Who;
DROP   Table  #Lock;

(2). 使用 SQL Server Profiler 分析死锁 : 将 Deadlock graph 事件类添加到跟踪。此事件类使用死锁涉及到的进程和对象的 XML 数据填充跟踪中的 TextData 数据列。 SQL Server 事件探查器   可以将 XML 文档提取到死锁 XML (.xdl) 文件中，以后可在 SQL Server Management Studio 中查看该文件。

3. 避免死锁  

    上面 1 中列出了死锁的四个必要条件，我们只要想办法破其中的任意一个或多个条件，就可以避免死锁发生，一般有以下几种方法 (FROM Sql Server 2005 联机丛书 ) ：
(1). 按同一顺序访问对象。 ( 注：避免出现循环 )
(2). 避免事务中的用户交互。 ( 注：减少持有资源的时间，较少锁竞争 )
(3). 保持事务简短并处于一个批处理中。 ( 注：同 (2) ，减少持有资源的时间 )
(4). 使用较低的隔离级别。 ( 注：使用较低的隔离级别（例如已提交读）比使用较高的隔离级别（例如可序列化）持有共享锁的时间更短，减少锁竞争 )
(5). 使用基于行版本控制的隔离级别 ： 2005 中支持快照事务隔离和指定 READ_COMMITTED 隔离级别的事务使用行版本控制，可以将读与写操作之间发生的死锁几率降至最低：
SET ALLOW_SNAPSHOT_ISOLATION ON -- 事务可以指定 SNAPSHOT 事务隔离级别 ;
SET READ_COMMITTED_SNAPSHOT ON -- 指定 READ_COMMITTED 隔离级别的事务将使用行版本控制而不是锁定。默认情况下 ( 没有开启此选项，没有加 with nolock 提示 ) ， SELECT 语句会对请求的资源加 S 锁 ( 共享锁 ) ；而开启了此选项后， SELECT 不会对请求的资源加 S 锁。
注意： 设置 READ_COMMITTED_SNAPSHOT 选项时，数据库中只允许存在执行 ALTER DATABASE 命令的连接。在 ALTER DATABASE 完成之前，数据库中决不能有其他打开的连接。数据库不必一定要处于单用户模式中。
(6). 使用绑定连接 。 ( 注：绑定会话有利于在同一台服务器上的多个会话之间协调操作。绑定会话允许一个或多个会话共享相同的事务和锁 ( 但每个回话保留其自己的事务隔离级别 ) ，并可以使用同一数据，而不会有锁冲突。可以从同一个应用程序内的多个会话中创建绑定会话，也可以从包含不同会话的多个应用程序中创建绑定会话。在一个会话中开启事务 (begin tran) 后，调用 exec sp_getbindtoken @Token out; 来取得 Token ，然后传入另一个会话并执行 EXEC sp_bindsession @Token 来进行绑定 ( 最后的示例中演示了绑定连接 ) 。

4. 死锁处理方法：  

(1). 根据 2 中提供的 sql ，查看那个 spid 处于 wait 状态，然后用 kill spid 来干掉 ( 即破坏死锁的第四个必要条件 : 循环等待 ) ；当然这只是一种临时解决方案，我们总不能在遇到死锁就在用户的生产环境上排查死锁、 Kill sp ，我们应该考虑如何去避免死锁。

(2). 使用 SET LOCK_TIMEOUT timeout_period( 单位为毫秒 ) 来设定锁请求超时 。默认情况下，数据库没有超时期限 (timeout_period 值为 -1 ，可以用 SELECT @@LOCK_TIMEOUT 来查看该值，即无限期等待 ) 。当请求锁超过 timeout_period 时，将返回错误。 timeout_period 值为 0 时表示根本不等待，一遇到锁就返回消息。设置锁请求超时，破环了死锁的第二个必要条件 ( 请求与保持条件 ) 。

(3). SQL Server 内部有一个锁监视器线程执行死锁检查 ，锁监视器对特定线程启动死锁搜索时，会标识线程正在等待的资源；然后查找特定资源的所有者，并递归地继续执行对那些线程的死锁搜索，直到找到一个构成死锁条件的循环。检测到死锁后，数据库引擎   选择运行回滚开销最小的事务的会话作为死锁牺牲品，返回 1205 错误，回滚死锁牺牲品的事务并释放该事务持有的所有锁，使其他线程的事务可以请求资源并继续运行。

5. 两个死锁示例及解决方法  

5.1 SQL 死锁  

(1). 测试用的基础数据：

CREATE   TABLE  Lock1(C1  int   default ( 0 ));
CREATE   TABLE  Lock2(C1  int   default ( 0 ));
INSERT   INTO  Lock1  VALUES ( 1 );
INSERT   INTO  Lock2  VALUES ( 1 );

(2). 开两个查询窗口，分别执行下面两段 sql

-- Query 1
Begin   Tran
   Update  Lock1  Set  C1 = C1 + 1 ;
   WaitFor  Delay  ' 00:01:00 ' ;
   SELECT   *   FROM  Lock2
Rollback   Tran ;

-- Query 2
Begin   Tran
   Update  Lock2  Set  C1 = C1 + 1 ;
   WaitFor  Delay  ' 00:01:00 ' ;
   SELECT   *   FROM  Lock1
Rollback   Tran ;

上面的 SQL 中有一句 WaitFor Delay '00:01:00' ，用于等待 1 分钟，以方便查看锁的情况。

(3). 查看锁情况

在执行上面的 WaitFor 语句期间，执行第二节中提供的语句来查看锁信息：

Query1 中，持有 Lock1 中第一行 ( 表中只有一行数据 ) 的行排他锁 (RID:X) ，并持有该行所在页的意向更新锁 (PAG:IX) 、该表的意向更新锁 (TAB:IX) ； Query2 中，持有 Lock2 中第一行 ( 表中只有一行数据 ) 的行排他锁 (RID:X) ，并持有该行所在页的意向更新锁 (PAG:IX) 、该表的意向更新锁 (TAB:IX) ；

执行完 Waitfor ， Query1 查询 Lock2 ，请求在资源上加 S 锁，但该行已经被 Query2 加上了 X 锁； Query2 查询 Lock1 ，请求在资源上加 S 锁，但该行已经被 Query1 加上了 X 锁；于是两个查询持有资源并互不相让，构成死锁。

(4). 解决办法

a). SQL Server 自动选择一条 SQL 作死锁牺牲品 ：运行完上面的两个查询后，我们会发现有一条 SQL 能正常执行完毕，而另一个 SQL 则报如下错误：

这就是上面第四节中介绍的锁监视器 干活了。

b). 按同一顺序访问对象： 颠倒任意一条 SQL 中的 Update 与 SELECT 语句的顺序。例如修改第二条 SQL 成如下：

-- Query2
Begin   Tran
   SELECT   *   FROM  Lock1 -- 在Lock1上申请S锁
   WaitFor  Delay  ' 00:01:00 ' ;
   Update  Lock2  Set  C1 = C1 + 1 ; -- Lock2:RID:X
Rollback   Tran ;

当然这样修改也是有代价的，这会导致第一条 SQL 执行完毕之前，第二条 SQL 一直处于阻塞状态。单独执行 Query1 或 Query2 需要约 1 分钟，但如果开始执行 Query1 时，马上同时执行 Query2 ，则 Query2 需要 2 分钟才能执行完；这种按顺序请求资源从一定程度上降低了并发性。

c). SELECT 语句加 With(NoLock) 提示 ：默认情况下 SELECT 语句会对查询到的资源加 S 锁 ( 共享锁 ) ， S 锁与 X 锁 ( 排他锁 ) 不兼容；但加上 With(NoLock) 后， SELECT 不对查询到的资源加锁 ( 或者加 Sch-S 锁， Sch-S 锁可以与任何锁兼容 ) ；从而可以是这两条 SQL 可以并发地访问同一资源。当然，此方法适合解决读与写并发死锁的情况，但加With(NoLock)可能会导致脏读。

d). 使用较低的隔离级别。 SQL Server 2000 支持四种事务处理隔离级别 (TIL) ，分别为： READ UNCOMMITTED 、 READ COMMITTED 、 REPEATABLE READ 、 SERIALIZABLE ； SQL Server 2005 中增加了 SNAPSHOT TIL 。默认情况下， SQL Server 使用 READ COMMITTED TIL ，我们可以在上面的两条 SQL 前都加上一句 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ，来降低 TIL 以避免死锁 ；事实上，运行在 READ UNCOMMITTED TIL 的事务，其中的 SELECT 语句不对结果资源加锁或加 Sch-S 锁，而不会加 S 锁；但还有一点需要注意的是： READ UNCOMMITTED TIL 允许脏读 ，虽然加上了降低 TIL 的语句后，上面两条 SQL 在执行过程中不会报错，但执行结果是一个返回 1 ，一个返回 2 ，即读到了脏数据，也许这并不是我们所期望的。

e). 在 SQL 前加 SET LOCK_TIMEOUT timeout_period ，当请求锁超过设定的 timeout_period 时间后，就会终止当前 SQL 的执行，牺牲自己，成全别人。

f). 使用基于行版本控制的隔离级别 (SQL Server 2005 支持 ) ：开启下面的选项后， SELECT 不会对请求的资源加 S 锁，不加锁或者加 Sch-S 锁，从而将读与写操作之间发生的死锁几率降至最低；而且不会发生脏读。 啊

       g). 使用绑定连接 ( 使用方法见下一个示例。 )</