Sql Server 死锁的监控分析解决思路
1 背景
1.1 报警情况
最近整理笔记,打算全部迁移到evernote。整理到锁这一部分,里边刚好有个自己记录下来的案例,重新整理分享下给大家。
某日中午,收到报警短信,db死锁异常,单分钟死锁120个。
死锁的xml文件如下:
<deadlock-list> <deadlock victim="process810b00cf8"> <process-list> <process id="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="microsoft sql server management studio - 查询" hostname="test-server" hostpid="1433" loginname="xinysu" isolationlevel="read committed (2)" xactid="3396587959" currentdb="13" locktimeout="4294967295" clientoption1="671098976" clientoption2="390200"> <executionstack> <frame procname="adhoc" line="7" stmtstart="214" stmtend="484" sqlhandle="0x020000003acf4f010561e479685209fb09a7fd15239977c60000000000000000000000000000000000000000"> update financereceiptnorule set nowseqvalue=@returnnum,isrunning='0',lastwritetime=getdate() where isrunning='1' and seqcode=@seqcode </frame> </executionstack> <inputbuf> 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 </inputbuf> </process> <process id="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="microsoft sql server management studio - 查询" hostname="test-server" hostpid="1433" loginname="xinysu" isolationlevel="read committed (2)" xactid="3396522828" currentdb="13" locktimeout="4294967295" clientoption1="671090784" clientoption2="390200"> <executionstack> <frame procname="adhoc" line="6" stmtstart="210" stmtend="400" sqlhandle="0x020000001b4f23368af7bba99098c10dec46585804f1b4ce0000000000000000000000000000000000000000"> update dbo.financereceiptnorule set [isrunning]='1' where seqcode=@seqcode and isrunning='0' </frame> </executionstack> <inputbuf> 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 </inputbuf> </process> </process-list> <resource-list> <ridlock fileid="1" pageid="1541136" dbid="13" objectname="fin_test.dbo.financereceiptnorule" id="lock51e8a3980" mode="x" associatedobjectid="72057594040025088"> <owner-list> <owner id="process18fd5d8cf8" mode="x" /> </owner-list> <waiter-list> <waiter id="process810b00cf8" mode="u" requesttype="wait" /> </waiter-list> </ridlock> <keylock hobtid="72057594040090624" dbid="13" objectname="fin_test.dbo.financereceiptnorule" indexname="pk_financereceiptnorule" id="lock7b2c6bc80" mode="u" associatedobjectid="72057594040090624"> <owner-list> <owner id="process810b00cf8" mode="u" /> </owner-list> <waiter-list> <waiter id="process18fd5d8cf8" mode="u" requesttype="wait" /> </waiter-list> </keylock> </resource-list> </deadlock> </deadlock-list>
表格结构跟模拟数据如下:
--涉及表格: 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,点击<工具>,选择 <sql server profiler>,如下图。
登录到需要监控的db实例,填写相应的跟踪属性,首先是<常规>页面,如下图。这里注意2个方面,第一,选择 <tsql-locks>模板,这个模板即可以用来监控死锁,也可以拿来观察 锁申请与释放情况,非常详细,有事没事可以多拿来看select update delete等语句对锁的申请及释放情况;第二,监控结果存储,建议可以存放到某个表格中去,方便定期分析与统计。
接着填写<事件选择>项,只需要选择 <deadlock graph> events,其他都不需要打勾,最后点击运行就可以开始监控了。
可以用一个万年常用的例子来检查是否监控正常,开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:
- 根据主键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逐个找行数据的锁申请
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,同时也希望多多支持!