java sqlserver 死锁_Sql Server 死锁的监控分析解决思路

最新推荐文章于 2024-06-04 10:16:20 发布

weixin_39779032

最新推荐文章于 2024-06-04 10:16:20 发布

阅读量271

点赞数

文章标签： java sqlserver 死锁

本文链接：https://blog.csdn.net/weixin_39779032/article/details/114207768

版权

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]

--数据模拟

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')

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')

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]

1.2 如何监控

捕获死锁有多种方式可以捕获，这里介绍2种：SQL SERVER Profiler工具跟Extended Events。Profiler相对比较耗资源，但是由于只监控死锁这一项，所以性能影响不是很大，其可视化界面较易上手；Extended Events耗费资源较少，实时记录到倒数第二个死锁，同时需要SQL语句来分析查询记录文件。

如何使用 Profiler监控？

打开 SSMS，点击，选择，如下图。

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

接着填写项，只需要选择 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)

查询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逐个找行数据的锁申请

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持脚本之家！

weixin_39779032

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
java sqlserver 死锁_Sql Server 死锁的监控分析解决思路

1 背景1.1 报警情况最近整理笔记，打算全部迁移到EVERNOTE。整理到锁这一部分，里边刚好有个自己记录下来的案例，重新整理分享下给大家。某日中午，收到报警短信，DB死锁异常，单分钟死锁120个。死锁的xml文件如下：UPDATE FinanceReceiptNoRule SET NowSeqValue=@ReturnNum,ISRUNNING='0',LastWriteTime=GETDAT...
复制链接

扫一扫