SQL Server 死锁概念和分析

锁的概念

• 锁是什么

锁是数据库中在并发操作情形下保护资源的机制。通常(具体要看锁兼容性)只有锁的拥有者才能对被锁的资源进行操作，从而保证数据一致性。

• 锁的概念可分为几部分

1. 锁资源(锁住什么)
2. 锁模式(怎么锁法)
3. 锁持续时间
4. 兼容性
5. 锁的行为(锁转换，锁升级)

 

1.锁的资源

 

2.锁的模式

1. 共享锁:Shared Lock,S Lock. 通常情况下，读取数据时会对数据加上S Lock。
2. 排它锁: Exclusive Lock,X Lock。对数据进行更改(insert update,delete)时加X Lock
3. 更新锁:Update Lock,U Lock(或叫UPD Lock)。通常对数据进行Update操作会加U锁。查找数据时会加U锁，找到后对数据进行更改时在转换为X锁。U锁是为了防止在并发对数据进行更新时出现死锁，因为如果先加S锁再转换为X锁，由于S锁和S锁兼容，但X锁和S锁不兼容，所以有可能出现死锁。
4. 意向锁:Intent Lock(例如：IX,IU,IS)，是指对被锁定的资源的上层资源加锁。意向锁是为了提高锁的效率。例如对行加X锁，会对表加IX锁(意向排他锁)，如果其他线程或事物想对该表加X锁，就不用逐行检查是否有其他所，只需检查是否有IX锁(或其他意向锁)

3.锁在事务中的持续时间

不同的事务隔离级别下，锁有不同的持续时间。(单一个SQL语句也是一个事物，称为“自动提交事务”，用begin tran/commit声明的是显式事务)

1. Read uncommitted: select不会加锁(no lock)，但更新会加U锁并持续到事务介绍
2. Read committed:select加S锁，读完释放。U锁和X锁持续到事务结束
3. Repeatable Read : select加S锁，但读完不释放，和U锁,X锁一样持续到事务结束。
4. Serializable :select会加范围锁，读完不释放，和U锁,X锁一样持续到事务结束。

 

4.锁兼容性

锁兼容性是指不同模式的锁能不能共存。锁兼容性对照图：

图片出自Microsoft

 

5.锁转换和锁升级

1. 锁转换：锁会由一种模式转换为另一种模式。例如delete一条数据，先在数据查找时对数据加上U锁，再在准备删除数据前转换成X锁。
2. 锁升级:为了提高效率，锁会从一种粒度升级到另一种粒度。一个锁大概需要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的文章列表》