本篇文章给大家分享的是有关Sql Server中怎么监控死锁，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

死锁的xml文件如下：

<deadlock-list><deadlockvictim="process810b00cf8"><process-list><processid="process810b00cf8"taskpriority="0"logused="0"waitresource="RID:13:1:1541136:62"waittime="7682"ownerId="3396587959"transactionname="UPDATE"lasttranstarted="2016-01-08T12:03:51.067"XDES="0xa99746d08"lockMode="U"schedulerid="41"kpid="17308"status="suspended"spid="108"sbid="0"ecid="0"priority="0"trancount="2"lastbatchstarted="2016-01-08T12:03:51.067"lastbatchcompleted="2016-01-08T12:03:51.067"lastattention="1900-01-01T00:00:00.067"clientapp="MicrosoftSQLServerManagementStudio-查询"hostname="test-server"hostpid="1433"loginname="xinysu"isolationlevel="readcommitted(2)"xactid="3396587959"currentdb="13"lockTimeout="4294967295"clientoption1="671098976"clientoption2="390200"><executionStack><frameprocname="adhoc"line="7"stmtstart="214"stmtend="484"sqlhandle="0x020000003acf4f010561e479685209fb09a7fd15239977c60000000000000000000000000000000000000000">UPDATEFinanceReceiptNoRuleSETNowSeqValue=@ReturnNum,ISRUNNING='0',LastWriteTime=GETDATE()WHEREIsRunning='1'ANDSeqCode=@SeqCode</frame></executionStack><inputbuf>declare@SeqCodevarchar(60)declare@ReturnNumbigintset@SeqCode='CGJS20160106'while(1=1)beginUPDATEFinanceReceiptNoRuleSETNowSeqValue=@ReturnNum,ISRUNNING='0',LastWriteTime=GETDATE()WHEREIsRunning='1'ANDSeqCode=@SeqCodeend</inputbuf></process><processid="process18fd5d8cf8"taskpriority="0"logused="248"waitresource="KEY:13:72057594040090624(b3ade7c5980c)"waittime="4"ownerId="3396522828"transactionname="user_transaction"lasttranstarted="2016-01-08T12:03:05.310"XDES="0x18c1db63a8"lockMode="U"schedulerid="57"kpid="16448"status="suspended"spid="161"sbid="0"ecid="0"priority="0"trancount="2"lastbatchstarted="2016-01-08T12:03:58.737"lastbatchcompleted="2016-01-08T12:03:33.847"lastattention="2016-01-08T12:03:33.850"clientapp="MicrosoftSQLServerManagementStudio-查询"hostname="test-server"hostpid="1433"loginname="xinysu"isolationlevel="readcommitted(2)"xactid="3396522828"currentdb="13"lockTimeout="4294967295"clientoption1="671090784"clientoption2="390200"><executionStack><frameprocname="adhoc"line="6"stmtstart="210"stmtend="400"sqlhandle="0x020000001b4f23368af7bba99098c10dec46585804f1b4ce0000000000000000000000000000000000000000">Updatedbo.FinanceReceiptNoRuleSet[IsRunning]='1'whereSeqCode=@SeqCodeandIsRunning='0'</frame></executionStack><inputbuf>declare@SeqCodevarchar(60)declare@ReturnNumbigintset@SeqCode='CGJS20160106'while(1=1)beginUpdatedbo.FinanceReceiptNoRuleSet[IsRunning]='1'whereSeqCode=@SeqCodeandIsRunning='0'end</inputbuf></process></process-list><resource-list><ridlockfileid="1"pageid="1541136"dbid="13"objectname="fin_test.dbo.FinanceReceiptNoRule"id="lock51e8a3980"mode="X"associatedObjectId="72057594040025088"><owner-list><ownerid="process18fd5d8cf8"mode="X"/></owner-list><waiter-list><waiterid="process810b00cf8"mode="U"requestType="wait"/></waiter-list></ridlock><keylockhobtid="72057594040090624"dbid="13"objectname="fin_test.dbo.FinanceReceiptNoRule"indexname="PK_FINANCERECEIPTNORULE"id="lock7b2c6bc80"mode="U"associatedObjectId="72057594040090624"><owner-list><ownerid="process810b00cf8"mode="U"/></owner-list><waiter-list><waiterid="process18fd5d8cf8"mode="U"requestType="wait"/></waiter-list></keylock></resource-list></deadlock></deadlock-list>

表格结构跟模拟数据如下：

--涉及表格：CREATETABLE[dbo].[FinanceReceiptNoRule]([SeqCode][varchar](60)NOTNULL,[NowSeqValue][bigint]NULL,[SeqDate][varchar](14)NOTNULL,[IsRunning][varchar](1)NULL,[LastWriteTime][datetime]NULL,[Prefix][varchar](4)NULL)ON[PRIMARY]GO--数据模拟INSERT[dbo].[FinanceReceiptNoRule]([SeqCode],[NowSeqValue],[SeqDate],[IsRunning],[LastWriteTime],[Prefix])VALUES(N'TEST20150108',1469,N'20150108',N'0',CAST(N'2015-01-0805:05:49.163'ASDateTime),N'TEST')GOINSERT[dbo].[FinanceReceiptNoRule]([SeqCode],[NowSeqValue],[SeqDate],[IsRunning],[LastWriteTime],[Prefix])VALUES(N'TEST20150109',1377,N'20150109',N'0',CAST(N'2015-01-0904:50:26.610'ASDateTime),N'TEST')GOALTERTABLE[dbo].[FinanceReceiptNoRule]ADDCONSTRAINT[pk_FinanceReceiptNoRule]PRIMARYKEYNONCLUSTERED([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，点击<工具>，选择 <SQL Server Profiler>，如下图。

登录到需要监控的DB实例，填写相应的跟踪属性，首先是<常规>页面,如下图。这里注意2个方面，第一，选择 <TSQL-Locks>模板，这个模板即可以用来监控死锁，也可以拿来观察 锁申请与释放情况，非常详细，有事没事可以多拿来看SELECT UPDATE DELETE等语句对锁的申请及释放情况；第二，监控结果存储，建议可以存放到某个表格中去，方便定期分析与统计。

接着填写<事件选择>项，只需要选择 <deadlock graph> Events，其他都不需要打勾，最后点击运行就可以开始监控了。

可以用一个万年常用的例子来检查是否监控正常，开3个查询窗口，按照以下顺序执行则会发生资源占用及申请互斥导致死锁，执行完第5步，等待1-3s则发生死锁。脚本提供如下：

--session1CREATETABLETest_DL(idintnotnullprimarykey,namevarchar(100));INSERTINTOTest_DL(id,name)select1,'a';INSERTINTOTest_DL(id,name)select2,'b';--session2222222222BEGINTRANSACTIONUPDATETest_DLSETName='a-test'WHEREID=1--session3333333333BEGINTRANSACTIONUPDATETest_DLSETName='b-test'WHEREID=2--session2222222222SELECT*FROMTest_DLWHEREID=2--session3333333333SELECT*FROMTest_DLWHEREID=1模拟死锁SQL

监控到的死锁界面如下：

如何使用Extended Events监控？

建立扩展事件监控的脚本如下：（扩展事件很赞，2012版支持可视化操作，感兴趣的可以上 MSDN了解:https://msdn.microsoft.com/zh-cn/library/bb630282.aspx，本文就不分析语法等知识点了）

CREATEEVENTSESSION[DeadLock]ONSERVERADDEVENTsqlserver.xml_deadlock_reportADDTARGETpackage0.event_file(SETfilename=N'F:\events\deadlock\deadlock.xel',max_file_size=(20)),ADDTARGETpackage0.ring_buffer(SETmax_events_limit=(100),max_memory=(10240),occurrence_number=(50))WITH(MAX_MEMORY=4096KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30SECONDS,MAX_EVENT_SIZE=0KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)GO

查询SQL如下，这里需要注意：查询是基于buffer还是基于filer分析，一般buffer存储的个数都是有限的，比如上文我们只分配了4M存储，file分析则是完整的，但是要看保留的文件个数。这里我们给出buffer的查询SQL如下，file的查询大家感兴趣的可以动手写下。

DECLARE@deadlock_xmlXMLSELECT@deadlock_xml=(SELECT(SELECTCONVERT(XML,target_data)FROMsys.dm_xe_session_targetsstJOINsys.dm_xe_sessionssONs.address=st.event_session_addressWHEREs.name='deadlock'ANDst.target_name='ring_buffer')AS[x]FORXMLPATH(''),TYPE)SELECTdateadd(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)')hostnameFROM@deadlock_xml.nodes('//event')astb(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查询行的时间。

根据主键SeqCode查找到键值所在的 索引页 Index_Page，找到该页上面的 keyhashvalue 键值行 Index_key，对Index_Page持有IU锁，对Index_key持有U锁； 由于该表已经是聚集索引表，主键所在的页上包含 行数据，则可以直接 对Index_Page持有IU锁升级为IX锁，对Index_key持有U锁升级为X锁，避免了RID逐个找行数据的锁申请

以上就是Sql Server中怎么监控死锁，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。