1 背景
最近整理笔记,打算全部迁移到EVERNOTE。整理到锁这一部分,里边刚好有个自己记录下来的案例,重新整理分享下给大家。
某日中午,收到报警短信,DB死锁异常,单分钟死锁120个。
死锁的xml文件如下:
表格结构跟模拟数据如下:
ALTER TABLE [dbo].[FinanceReceiptNoRule] ADD CONSTRAINT [pk_FinanceReceiptNoRule] PRIMARY KEY NONCLUSTERED
(
[SeqCode] ASC
)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,SORT_IN_TEMPDB = OFF,IGNORE_DUP_KEY = OFF,ONLINE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
1.2 如何监控
捕获死锁有多种方式可以捕获,这里介绍2种:sql SERVER Profiler工具跟Extended Events。Profiler相对比较耗资源,但是由于只监控死锁这一项,所以性能影响不是很大,其可视化界面较易上手;Extended Events耗费资源较少,实时记录到倒数第二个死锁,同时需要sql语句来分析查询记录文件。
如何使用 Profiler监控?
打开 SSMS,点击<工具>,选择
登录到需要监控的DB实例,填写相应的跟踪属性,首先是<常规>页面,如下图。这里注意2个方面,第一,选择
接着填写<事件选择>项,只需要选择
可以用一个万年常用的例子来检查是否监控正常,开3个查询窗口,按照以下顺序执行则会发生资源占用及申请互斥导致死锁,执行完第5步,等待1-3s则发生死锁。脚本提供如下:
INSERT INTO Test_DL(id,name) select 1,'a';
INSERT INTO Test_DL(id,name) select 2,'b';
--session2 2 2 2 2 2 2 2 2 2
BEGIN TRANSACTION
UPDATE Test_DL SET Name='a-test' WHERE ID=1
--session3 3 3 3 3 3 3 3 3 3
BEGIN TRANSACTION
UPDATE Test_DL SET Name='b-test' WHERE ID=2
--session2 2 2 2 2 2 2 2 2 2
SELECT * FROM Test_DL WHERE ID=2
--session3 3 3 3 3 3 3 3 3 3
SELECT * FROM Test_DL WHERE ID=1
模拟死锁sql
监控到的死锁界面如下:
如何使用Extended Events监控?
建立扩展事件监控的脚本如下:(扩展事件很赞,2012版支持可视化操作,感兴趣的可以上 MSDN了解:https://msdn.microsoft.com/zh-cn/library/bb630282.aspx,本文就不分析语法等知识点了)
查询sql如下,这里需要注意:查询是基于buffer还是基于filer分析,一般buffer存储的个数都是有限的,比如上文我们只分配了4M存储,file分析则是完整的,但是要看保留的文件个数。这里我们给出buffer的查询sql如下,file的查询大家感兴趣的可以动手写下。
SELECT
dateadd(hour,+6,tb.col.value('@timestamp[1]','varchar(max)')) TimePoint,tb.col.value('(data/value/deadlock/process-list/process/executionStack/frame)[1]','VARCHAR(MAX)') statement_parameter_k,tb.col.value('(data/value/deadlock/process-list/process/executionStack/frame)[2]','VARCHAR(MAX)') statement_k,tb.col.value('(data/value/deadlock/process-list/process/executionStack/frame)[3]','VARCHAR(MAX)') statement_parameter,tb.col.value('(data/value/deadlock/process-list/process/executionStack/frame)[4]','VARCHAR(MAX)') [statement],tb.col.value('(data/value/deadlock/process-list/process/@waitresource)[1]','VARCHAR(MAX)') waitresource_k,tb.col.value('(data/value/deadlock/process-list/process/@waitresource)[2]','VARCHAR(MAX)') waitresource,tb.col.value('(data/value/deadlock/process-list/process/@isolationlevel)[1]','VARCHAR(MAX)') isolationlevel_k,tb.col.value('(data/value/deadlock/process-list/process/@isolationlevel)[2]','VARCHAR(MAX)') isolationlevel,tb.col.value('(data/value/deadlock/process-list/process/@waittime)[1]','VARCHAR(MAX)') waittime_k,tb.col.value('(data/value/deadlock/process-list/process/@waittime)[2]','VARCHAR(MAX)') waittime,tb.col.value('(data/value/deadlock/process-list/process/@clientapp)[1]','VARCHAR(MAX)') clientapp_k,tb.col.value('(data/value/deadlock/process-list/process/@clientapp)[2]','VARCHAR(MAX)') clientapp,tb.col.value('(data/value/deadlock/process-list/process/@hostname)[1]','VARCHAR(MAX)') hostname_k,tb.col.value('(data/value/deadlock/process-list/process/@hostname)[2]','VARCHAR(MAX)') hostname
FROM @deadlock_xml.nodes('//event') as tb(col)
这个sql可以查询的出非常详细的资源争夺情况,如果想要有效的使用扩展事件,建议大家详细查看下官网的xml语法(sql SERVER对xml的支持也是棒棒哒,期待2016版中的json支持)
是不是很清晰,一目了然,有了这个就可以去分析拉!
2 分析
根据xml文件内容或者扩展事件的监控内容,都可以整理为以下信息(开头的那个死锁分析):
查看事务1及事务2的执行计划如下:
结合表格及执行计划,可以大致推测死锁过程:
会话1:
- 根据主键SeqCode查找到键值所在的 索引页 Index_Page,找到该页上面的 keyhashvalue 键值行 Index_key,对Index_Page持有IU锁,对Index_key持有U锁;
- 由于该表是堆表,bookmark lookup是通过 RID查找 ,即通过行标识符查找,找到RID所对应的行数据所在的 数据页 Data_Page,然后在该页面上找到RID指向槽号上的行数据,对该行数据持有U锁;
- 这个时候,已经查找到了需要更新的行数据,可以把数据页 Data_Page上的IU锁 升级为IX锁,RID指向的行数据 从U锁升级为X锁,升级结束后,释放索引页跟键值行上面的 IU锁及U锁。
- 则此时,会话1 持有 Data_Page 上的IX锁、RID行上的 X锁.
这个过程中,刚好会话2进行这样的锁申请:
- 找出事务2中持有锁资源是哪个索引,可以根据sys.partitions 可以查看到72057594038910976是主键pk_FinanceReceiptNoRule,主键列是:SeqCode。
- 根据主键SeqCode查找到键值所在的 索引页 Index_Page,找到该页上面的 键值行 Index_key,对Index_Page持有IU锁,对Index_key持有U锁;
- 由于该表是堆表,bookmark lookup是通过 RID查找 ,即通过行标识符查找,找到RID所对应的行数据所在的 数据页 Data_Page,然后在该页面上找到RID指向槽号上的行数据,准备该行数据持有U锁,但是发现RID行上被会话1持有了X锁,导致其申请 U锁 Timeout。
- 则此时 会话2 持有 Index_Page上的IU锁、Index_key上的U锁、Data_Page上的IU锁,请求 RID行的 U锁。
假设这个时候,会话1 中又执行了一次update操作(同一个事务中):
根据主键SeqCode查找到键值所在的 索引页
Index_Page,找到该页上面的 键值行 Index_key,对Index_Page持有IU锁,准备对Index_key持有U锁,但是发现 Index_key被会话2持有了U锁。
那么这个时候死锁就产生了(详见下图):
- 会话1 持有 Data_Page 上的IX锁、RID行上的 X锁,申请 Index_key 的U锁(等待会话2释放)
- 会话2 持有 Index_Page上的IU锁、Index_key上的U锁、Data_Page上的IU锁,请求 RID行的 U锁(等待会话1释放)
3 解决
想法子除去RID查找,直接index就找到数据,就不会发生这个死锁,也就是,在主键上面重新建立聚集索引,丢弃原先的非聚集索引主键。因为这样排除了RID的U锁申请与持有,直接是保持X锁 直至事务结束,同时可以直接根据主键来修改键值所在的数据页,减少的RID查询行的时间。
修改后的执行计划如下:
其锁申请释放的流程如下(详见截图):