您现在的位置是：网站首页> 编程资料编程资料
Sql Server 死锁的监控分析解决思路_MsSql_
2023-05-26
313人已围观
简介 Sql Server 死锁的监控分析解决思路_MsSql_
1 背景
1.1 报警情况
最近整理笔记，打算全部迁移到EVERNOTE。整理到锁这一部分，里边刚好有个自己记录下来的案例，重新整理分享下给大家。
某日中午，收到报警短信，DB死锁异常，单分钟死锁120个。
死锁的xml文件如下：
UPDATE FinanceReceiptNoRule SET NowSeqValue=@ReturnNum,ISRUNNING='0',LastWriteTime=GETDATE() WHERE IsRunning='1' AND SeqCode=@SeqCode declare @SeqCode varchar(60) declare @ReturnNum bigint set @SeqCode='CGJS20160106' while(1=1) begin UPDATE FinanceReceiptNoRule SET NowSeqValue=@ReturnNum,ISRUNNING='0',LastWriteTime=GETDATE() WHERE IsRunning='1' AND SeqCode=@SeqCode end Update dbo.FinanceReceiptNoRule Set [IsRunning]='1' where SeqCode=@SeqCode and IsRunning='0' declare @SeqCode varchar(60) declare @ReturnNum bigint set @SeqCode='CGJS20160106' while(1=1) begin Update dbo.FinanceReceiptNoRule Set [IsRunning]='1' where SeqCode=@SeqCode and IsRunning='0' end
表格结构跟模拟数据如下：
--涉及表格： CREATE TABLE [dbo].[FinanceReceiptNoRule]( [SeqCode] [varchar](60) NOT NULL, [NowSeqValue] [bigint] NULL, [SeqDate] [varchar](14) NOT NULL, [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-08 05:05:49.163' AS DateTime), N'TEST') GO INSERT [dbo].[FinanceReceiptNoRule] ([SeqCode], [NowSeqValue], [SeqDate], [IsRunning], [LastWriteTime], [Prefix]) VALUES (N'TEST20150109', 1377, N'20150109', N'0', CAST(N'2015-01-09 04:50:26.610' AS DateTime), N'TEST') GO 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则发生死锁。脚本提供如下：
--session 1 CREATE TABLE Test_DL( id int not null primary key , name varchar(100)); 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，本文就不分析语法等知识点了）
CREATE EVENT SESSION [DeadLock] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET filename=N'F:\events\deadlock\deadlock.xel',max_file_size=(20)), ADD TARGET package0.ring_buffer(SET max_events_limit=(100),max_memory=(10240),occurrence_number=(50)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON) GO
查询SQL如下，这里需要注意：查询是基于buffer还是基于filer分析，一般buffer存储的个数都是有限的，比如上文我们只分配了4M存储，file分析则是完整的，但是要看保留的文件个数。这里我们给出buffer的查询SQL如下，file的查询大家感兴趣的可以动手写下。
DECLARE @deadlock_xml XML SELECT @deadlock_xml=( SELECT ( SELECT CONVERT(XML, target_data) FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE s.name = 'deadlock' AND st.target_name = 'ring_buffer' ) AS [x] FOR XML PATH('') , TYPE ) 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、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持；
2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
