SQL Server 死锁概念和分析

周翔

锁升级　　:为了提高效率，锁会从一种粒度升级到另一种粒度。一个锁大概需要96B空间，如果锁太多会占用太多资源。如果一个操作需要对很多资源加锁，SQL会自动对这个资源的上级加相同的锁,即锁升级

　　死锁的概念
　　死锁就是两个或多个会话(SPID)相互请求对方持有的锁资源，导致循环等待的情况。

　　死锁的检查和分析
　　在Profiler中使用Deadlock Graph分析死锁
　　
　　捕获到死锁图

　　也可以使用ErrorLog来记录死锁

• 打开trace flag
  
  
  
  • dbcc traceon(1204,-1)
    
  • dbcc traceon(1222,-1)
    
  
  
• 清空errorLog(不一定需要清空，只为减少log数据)
  
  
  
  • dbcc errorlog或者  exec sp_cycle_errorlog
    
  
  
• 打开errorlog文件或者执行 xp_readerrorlog
  
• 分析死锁图,如下图：
  

　　根据errorlog内容画出死锁图，其中：

• NODE:代表每个死锁节点
  
• Key/RID/Object：被锁定的资源
  
• Grant List:拥有该资源的锁的SPID
  
• Reuested List:请求该资源的SPID
  
• SPID:会话ID
  
• MODE: 该资源被锁定的模式。参考：锁模式
  
• Input Buf：发生死锁时的SQL语句
  

　　这样就可以画出死锁图:

　　这样便可以分析出死锁的情况，当然如果对errorlog已经比较熟悉，就可以不用画出死锁图了。
　　死锁和索引

• 如果被锁的资源是一个Object,只有object_id，可以通过Object_name()函数获取object的名字。
  
• 如果是RID，可以通过 dbcc page指令去知道哪行数据被锁。
  
• 如果是key，需要通过 %%lockres%%列，或者“dbcc ind指”令和“dbcc page”指令去知道哪个索引键被锁。
  
• 在死锁节点的Key中，显示被锁的key为：
  

　　KEY: 49:72057594189512704 (11036c0fa4d3)，格式为：
　　KEY: db_id:hobt_id(index key hash value)
　　SQL SERVER通过对一个由索引键值生成的hash value进行锁定，来达到对索引锁定的目的。其中(11036c0fa4d3)就是某个key的hash value。
　　如何由hash value得知是哪行数据被锁定？请按以下步骤：
　　如果是聚集索引，可以使用%%lockres%%隐藏列获取lock hash value:

　　如果是非聚集索引，%%lockres%%只显示 RID。如果需要确定数据行对应的索引页中的索引键，需要使用dbcc ind和dbcc page指令进行分析：

• 要查看索引的lock hash value，需要查看索引所在的数据页的内容。
  
• 要知道索引在什么数据页中，需要使用dbcc ind指令：dbcc ind(‘moe_dev’,592773219,3)  “592773219”代表表的object_id,3代表索引的index_id 。可使用dbcc ind指令:
  

　　知道了索引所在数据页的Page_ID,可以使用dbcc page指令查看数据页内容（最后一个参数必须为“3”）:

　　知道了索引所在数据页的Page_ID,可以使用dbcc page指令查看数据页内容（最后一个参数必须为“3”）:

　　知道了key hash value和Heap RID，可以通过下面的SQL语句转换成RID
　　

DECLARE @HeapRid BINARY(8)　　

SET @HeapRid = 0xDB27000001001700　　
SELECT
　　
CONVERT (VARCHAR(5),
　　
CONVERT(INT, SUBSTRING(@HeapRid, 6, 1)
　　
+ SUBSTRING(@HeapRid, 5, 1)))
　　
+ ':'
　　
+ CONVERT(VARCHAR(10),
　　
CONVERT(INT, SUBSTRING(@HeapRid, 4, 1)
　　
+ SUBSTRING(@HeapRid, 3, 1)
　　
+ SUBSTRING(@HeapRid, 2, 1)
　　
+ SUBSTRING(@HeapRid, 1, 1)))
　　
+ ':'
　　
+ CONVERT(VARCHAR(5),
　　
CONVERT(INT, SUBSTRING(@HeapRid, 8, 1)
　　
+ SUBSTRING(@HeapRid, 7, 1)))
　　
AS 'Fileid:Pageid:slot'
　　

　　得到的RID为：

　　通过dbcc page指令就可以查看是哪行被锁定了。
　　参考资料
　　有关死锁的资料和比较好的文章，请看另一篇博文《有关DeadLock的文章列表》
　　
posted on 2017-04-19 11:20 wyman25 阅读(...) 评论(...)  编辑 收藏


刷新评论刷新页面返回顶部


导航

• 
  博客园
  
• 
  首页
  
• 
  新随笔
  
• 
  
• 
  
• 
  管理
  

　　
Powered by:

　　
博客园

　　
Copyright © wyman25