SQL Server死锁总结-CSDN博客

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时表示根本不等待，一遇到锁就返回消息。设置锁请求超时，破环了死锁的第二个必要条件(请求与保持条件)。

 
  服务器: 消息  
  1222 
  ，级别  
  16 
  ，状态  
  50 
  ，行  
  1 
  
已超过了锁请求超时时段。 
 

(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 
  ); 
 