草稿箱二十篇随笔没有发布,零零散散记录着曾经以为还不错的知识点。稍作整理发布,方便以后查看。2015-11-26 18:04 整理,未发布
1、模拟死锁
首先创建测试数据,然后开启必要的跟踪,最后执行两个语句模拟死锁。
1.1、创建测试数据
创建测试数据表、创建索引
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
create table testklup ( clskey int not null, nlskey int not null, cont1 int not null, cont2 char(3000) ) create unique clustered index inx_cls on testklup(clskey) create unique nonclustered index inx_nlcs on testklup(nlskey) include(cont1) insert into testklup select 1,1,100,'aaa' insert into testklup select 2,2,200,'bbb' insert into testklup select 3,3,300,'ccc'
在测试之前,先开启监视死锁的开关1222,让SQL Server遇到死锁时,在Errorlog文件里打印出死锁的详细内容。
DBCC TRACEON(1222,-1)
也可以同时使用SQL Trace来捕捉和死锁相关的信息。主要选择事件Locks->Daedlock graph
1.2、开启死锁会话
开启一个会话进行修改
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
----模拟高频update操作 declare @i int set @i = 100 while 1=1 begin update testklup set cont1 = @i where clskey = 1 set @i = @i+1 end
开启另外一个会话进行查询
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
----模拟高频select操作 declare @cont2 char(3000) while 1=1 begin select @cont2=cont2 from testklup where nlskey=1 end
两条语句一起运行,无须多长时间就会有其中一个连接遇到死锁的错误:
2、分析死锁
在开启必要的跟踪并捕获到死锁后,就可以对其进行分析。
2.1、1222标志
这时在Errorlog文件里,就会看到下面的输出
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
spid16s deadlock-list spid16s deadlock victim=process5f3000 spid16s process-list spid16s process id=process5f3000 taskpriority=0 logused=0 waitresource=KEY: 8:72057594040025088 (8194443284a0) waittime=3300 ownerId=16991311 transactionname=SELECT lasttranstarted=2016-09-28T15:07:26.720 XDES=0xe291448 lockMode=S schedulerid=4 kpid=5748 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2016-09-28T15:07:26.717 lastbatchcompleted=2016-09-28T15:02:29.293 clientapp=Microsoft SQL Server Management Studio - 查询 hostname=LIXUANYAO hostpid=7584 loginname=LIXUANYAO\Administrator isolationlevel=read committed (2) xactid=16991311 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200 spid16s executionStack spid16s frame procname=adhoc line=5 stmtstart=134 stmtend=232 sqlhandle=0x02000000c3289f0a3b1699a92a155bf6611d1a82d2dbd79a spid16s select @cont2=cont2 from testklup where nlskey=1 spid16s inputbuf spid16s ----模拟高频select操作 spid16s declare @cont2 char(3000) spid16s while 1=1 spid16s begin spid16s select @cont2=cont2 from testklup where nlskey=1 spid16s end spid16s process id=process5f3558 taskpriority=0 logused=228 waitresource=KEY: 8:72057594040090624 (8194443284a0) waittime=3300 ownerId=16991312 transactionname=UPDATE lasttranstarted=2016-09-28T15:07:26.720 XDES=0xe291860 lockMode=X schedulerid=4 kpid=7240 status=suspended spid=55 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2016-09-28T15:07:24.843 lastbatchcompleted=2016-09-28T15:07:23.603 clientapp=Microsoft SQL Server Management Studio - 查询 hostname=LIXUANYAO hostpid=7584 loginname=LIXUANYAO\Administrator isolationlevel=read committed (2) xactid=16991312 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200 spid16s executionStack spid16s frame procname=adhoc line=6 stmtstart=140 stmtend=248 sqlhandle=0x0200000087695c3ac89c30f0ca649915b3e2cb1c22db1292 spid16s update testklup set cont1 = @i where clskey = 1 spid16s inputbuf spid16s ----模拟高频update操作 spid16s declare @i int spid16s set @i = 100 spid16s while 1=1 spid16s begin spid16s update testklup set cont1 = @i where clskey = 1 spid16s set @i = @i+1 spid16s end spid16s resource-list spid16s keylock hobtid=72057594040025088 dbid=8 objectname=Test.dbo.testklup indexname=inx_cls id=locka2bc5c0 mode=X associatedObjectId=72057594040025088 spid16s owner-list spid16s owner id=process5f3558 mode=X spid16s waiter-list spid16s waiter id=process5f3000 mode=S requestType=wait spid16s keylock hobtid=72057594040090624 dbid=8 objectname=Test.dbo.testklup indexname=inx_nlcs id=locka2b8d80 mode=S associatedObjectId=72057594040090624 spid16s owner-list spid16s owner id=process5f3000 mode=S spid16s waiter-list spid16s waiter id=process5f3558 mode=X requestType=wait
先来看看这些输出是什么意思,完成下表:
参与者 | process5f3000 | process5f3558 |
---|---|---|
牺牲者 | √ | |
SPID | 57 | 55 |
连接背景 | Microsoft SQL Server Management Studio - 查询 hostname=LIXUANYAO hostpid=7584 loginname=LIXUANYAO\Administrator | Microsoft SQL Server Management Studio - 查询 hostname=LIXUANYAO hostpid=7584 loginname=LIXUANYAO\Administrator |
正在申请中资源/类型 | KEY: 8:72057594040025088 (8194443284a0)/ Mode:S | KEY: 8:72057594040090624 (8194443284a0)/ Mode:X |
当前开启了几层事务 | 0 | 2 |
事务隔离级别 | read committed (2) | read committed (2) |
当前正在运行的语句 | select @cont2=cont2 from testklup where nlskey=1 | update testklup set cont1 = @i where clskey = 1 |
当前正在运行的批处理 | --模拟高频select操作 declare @cont2 char(3000) while 1=1 begin select @cont2=cont2 from testklup where nlskey=1 end | --模拟高频update操作 declare @i int set @i = 100 while 1=1 begin update testklup set cont1 = @i where clskey = 1 set @i = @i+1 end |
死锁资源 | 资源2 | 资源1 |
资源类型 | keylock | keylock |
具体内容 | hobtid=72057594040090624 dbid=8 objectname=Test.dbo.testklup indexname=inx_nlcs id=locka2b8d80 | hobtid=72057594040025088 dbid=8 objectname=Test.dbo.testklup indexname=inx_cls id=locka2bc5c0 |
持有资源进程/类型 | process5f3000(spid57) / mode:S | process5f3558(spid55) / mode:X |
等待资源进程/类型 | process5f3558(spid55) / mode:X | process5f3000(spid57) / mode:S |
2.2、死锁图
在跟踪文件里,可以看到这样的死锁图形:
我们可以从跟踪文件中,提取死锁事件数据,将死锁图保存为死锁XML文件(.xdl)。然后针对.xdl文件使用记事本打开,整个死锁的内容一目了然。
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
/** 死锁事件数据是后期提取的,与原死锁图稍有不同 **/ -- 第一部分 <deadlock-list> <deadlock victim="process2b6b000"> -- 第二部分 <process-list> <process id="process2b6b000" taskpriority="0" logused="0" waitresource="KEY: 8:72057594040025088 (8194443284a0)" waittime="636" ownerId="758239" transactionname="SELECT" lasttranstarted="2016-10-26T09:59:00.300" XDES="0x5dba408" lockMode="S" schedulerid="1" kpid="4008" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2016-10-26T09:59:00.300" lastbatchcompleted="2016-10-26T09:58:55.807" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="LIXUANYAO" hostpid="5872" loginname="LIXUANYAO\Administrator" isolationlevel="read committed (2)" xactid="758239" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"> <executionStack> <frame procname="adhoc" line="5" stmtstart="134" stmtend="232" sqlhandle="0x02000000c3289f0a3b1699a92a155bf6611d1a82d2dbd79a"> select @cont2=cont2 from testklup where nlskey=1 </frame> </executionStack> <inputbuf> ----模拟高频select操作 declare @cont2 char(3000) while 1=1 begin select @cont2=cont2 from testklup where nlskey=1 end </inputbuf> </process> <process id="process2dc2aa8" taskpriority="0" logused="228" waitresource="KEY: 8:72057594040090624 (8194443284a0)" waittime="636" ownerId="758240" transactionname="UPDATE" lasttranstarted="2016-10-26T09:59:00.307" XDES="0x5bf6c08" lockMode="X" schedulerid="4" kpid="5832" status="suspended" spid="55" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-10-26T09:58:59.470" lastbatchcompleted="2016-10-26T09:58:58.073" lastattention="2016-10-26T09:58:58.073" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="LIXUANYAO" hostpid="5872" loginname="LIXUANYAO\Administrator" isolationlevel="read committed (2)" xactid="758240" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"> <executionStack> <frame procname="adhoc" line="6" stmtstart="140" stmtend="248" sqlhandle="0x0200000087695c3ac89c30f0ca649915b3e2cb1c22db1292"> update testklup set cont1 = @i where clskey = 1 </frame> </executionStack> <inputbuf> ----模拟高频update操作 declare @i int set @i = 100 while 1=1 begin update testklup set cont1 = @i where clskey = 1 set @i = @i+1 end </inputbuf> </process> </process-list> -- 第三部分 <resource-list> <keylock hobtid="72057594040025088" dbid="8" objectname="Test.dbo.testklup" indexname="inx_cls" id="locka49e800" mode="X" associatedObjectId="72057594040025088"> <owner-list> <owner id="process2dc2aa8" mode="X"/> </owner-list> <waiter-list> <waiter id="process2b6b000" mode="S" requestType="wait"/> </waiter-list> </keylock> <keylock hobtid="72057594040090624" dbid="8" objectname="Test.dbo.testklup" indexname="inx_nlcs" id="locka47ab40" mode="S" associatedObjectId="72057594040090624"> <owner-list> <owner id="process2b6b000" mode="S"/> </owner-list> <waiter-list> <waiter id="process2dc2aa8" mode="X" requestType="wait"/> </waiter-list> </keylock> </resource-list> </deadlock> </deadlock-list>
SPID=57:查询语句使用非聚集索引inx_nlcs查找nlskey=1的记录,持有inx_nlcs上的S锁;由于索引未覆盖列cont2,需通过键查询得到cont2,于是申请inx_cls上的S锁。
SPID=55:更新语句使用聚集索引inx_cls查找clskey=1的记录,持有inx_cls上的X锁;由于非聚集索引inx_nlcs包含列cont1,在更新表中记录后,还需更新非聚集索引中的数值,于是申请inx_ncls上的X锁。
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
--hobtid SELECT OBJECT_NAME(p.object_id) AS TableName, i.name AS IndexName FROM sys.partitions AS p INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id WHERE p.hobt_id = 72057594040090624 --associatedObjectId SELECT OBJECT_NAME(p.object_id) AS TableName, i.name AS IndexName FROM sys.partitions AS p INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id WHERE p.partition_id = 72057594040025088 --case when type = 1|3 then container_id = sys.partitions.hobt_id --case when type = 2 then container_id = sys.partitions.partition_id SELECT * FROM sys.allocation_units WHERE container_id=72057594040090624 SELECT * FROM sys.allocation_units WHERE container_id=72057594040025088 SELECT %%lockres%% AS keyhashvalue,* FROM testklup WHERE %%lockres%% ='(8194443284a0)'
3、Deadlocks-Example
SQL Server上的一个奇怪的Deadlock及其分析方法:https://blogs.msdn.microsoft.com/apgcdsd/2012/02/27/sql-serverdeadlock/
SQL Server Deadlocks by Example:https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/