您的位置:首页 > 数据库 > > 正文

sql server锁原理(Sql Server 死锁的监控分析解决思路)

更多 时间:2022-01-18 01:21:21 类别:数据库 浏览量:409

sql server锁原理

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>,如下图。

    sql server锁原理(Sql Server 死锁的监控分析解决思路)

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

    sql server锁原理(Sql Server 死锁的监控分析解决思路)

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

    sql server锁原理(Sql Server 死锁的监控分析解决思路)

    可以用一个万年常用的例子来检查是否监控正常,开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
    
    
  • sql server锁原理(Sql Server 死锁的监控分析解决思路)

    监控到的死锁界面如下:

    sql server锁原理(Sql Server 死锁的监控分析解决思路) 

    如何使用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支持)

     sql server锁原理(Sql Server 死锁的监控分析解决思路)

    是不是很清晰,一目了然,有了这个就可以去分析拉!

    2 分析

    根据xml文件内容或者扩展事件的监控内容,都可以整理为以下信息(开头的那个死锁分析):

     sql server锁原理(Sql Server 死锁的监控分析解决思路)

    查看事务1及事务2的执行计划如下:

     sql server锁原理(Sql Server 死锁的监控分析解决思路)

    结合表格及执行计划,可以大致推测死锁过程:

    会话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中持有锁资源sql server锁原理(Sql Server 死锁的监控分析解决思路)是哪个索引,可以根据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释放)

     sql server锁原理(Sql Server 死锁的监控分析解决思路)

    3 解决

    想法子除去RID查找,直接index就找到数据,就不会发生这个死锁,也就是,在主键上面重新建立聚集索引,丢弃原先的非聚集索引主键。因为这样排除了RID的U锁申请与持有,直接是保持X锁 直至事务结束,同时可以直接根据主键来修改键值所在的数据页,减少的RID查询行的时间。

    修改后的执行计划如下:

     sql server锁原理(Sql Server 死锁的监控分析解决思路)

    其锁申请释放的流程如下(详见截图):

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

     sql server锁原理(Sql Server 死锁的监控分析解决思路)

    以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,同时也希望多多支持开心学习网!

    标签:sql server 死锁
    您可能感兴趣