www.bifa9999com模拟死锁及案例分析

草稿箱二十篇随笔没有发布,零零散散记录着曾经以为还不错的知识点。稍作整理发布,方便以后查看。2015-11-26
18:04 整理,未发布

在两个或多个SQL
Server进程中,每一个进程锁定了其他进程试图锁定的资源,就会出现死锁,例如,
进程process1对table1持有1个排它锁(X),同时process1对table2请求1个排它锁(X),
进程process2对table2持有1个排它锁(X),同时process2对table1请求1个排它锁(X)

1、模拟死锁

首先创建测试数据,然后开启必要的跟踪,最后执行两个语句模拟死锁。

类似这种情况,就会出现死锁,除非当某个外部进程断开死锁,否则死锁中的两个事务都将无限期等待下去。

1.1、创建测试数据

创建测试数据表、创建索引

www.bifa9999com 1www.bifa9999com 2

create table testklup
(
clskey int not null,
nlskey int not null,
cont1  int not null,
cont2  char(3000)
)

create unique clustered index inx_cls on testklup(clskey)

create unique nonclustered index inx_nlcs  on testklup(nlskey) include(cont1)

insert into testklup select 1,1,100,'aaa'
insert into testklup select 2,2,200,'bbb'
insert into testklup select 3,3,300,'ccc'

View Code

在测试之前,先开启监视死锁的开关1222,让SQL
Server遇到死锁时,在Errorlog文件里打印出死锁的详细内容。

DBCC TRACEON(1222,-1)

也可以同时使用SQL
Trace来捕捉和死锁相关的信息。主要选择事件Locks->Daedlock graph

Microsoft SQL Server 数据库引擎死锁监视器定期检查陷入死锁的任务。

1.2、开启死锁会话

开启一个会话进行修改

www.bifa9999com 3www.bifa9999com 4

----模拟高频update操作
declare @i int
set @i = 100
while 1=1
begin
    update testklup set cont1 = @i where clskey = 1  
    set @i = @i+1
end

View Code

开启另外一个会话进行查询

www.bifa9999com 5www.bifa9999com 6

----模拟高频select操作
declare @cont2 char(3000)
while 1=1
begin
    select @cont2=cont2 from testklup where nlskey=1
end

View Code

两条语句一起运行,无须多长时间就会有其中一个连接遇到死锁的错误:
www.bifa9999com 7

如果监视器检测到循环依赖关系,将选择其中一个任务作为牺牲品(通常是选择占资源比较小的进程作为牺牲品),然后终止其事务并提示错误1205

2、分析死锁

在开启必要的跟踪并捕获到死锁后,就可以对其进行分析。

这里我们通过SQL Server
Profiler来监视分析死锁的发生过程,那样我们就会深刻理解死锁的成因。

2.1、1222标志

这时在Errorlog文件里,就会看到下面的输出

www.bifa9999com 8www.bifa9999com 9

spid16s     deadlock-list
spid16s      deadlock victim=process5f3000
spid16s       process-list
spid16s        process id=process5f3000 taskpriority=0 logused=0 waitresource=KEY: 8:72057594040025088 (8194443284a0) waittime=3300 ownerId=16991311 transactionname=SELECT lasttranstarted=2016-09-28T15:07:26.720 XDES=0xe291448 lockMode=S schedulerid=4 kpid=5748 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2016-09-28T15:07:26.717 lastbatchcompleted=2016-09-28T15:02:29.293 clientapp=Microsoft SQL Server Management Studio - 查询 hostname=LIXUANYAO hostpid=7584 loginname=LIXUANYAO\Administrator isolationlevel=read committed (2) xactid=16991311 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
spid16s         executionStack
spid16s          frame procname=adhoc line=5 stmtstart=134 stmtend=232 sqlhandle=0x02000000c3289f0a3b1699a92a155bf6611d1a82d2dbd79a
spid16s     select @cont2=cont2 from testklup where nlskey=1     
spid16s         inputbuf
spid16s     ----模拟高频select操作
spid16s     declare @cont2 char(3000)
spid16s     while 1=1
spid16s     begin
spid16s         select @cont2=cont2 from testklup where nlskey=1
spid16s     end    
spid16s        process id=process5f3558 taskpriority=0 logused=228 waitresource=KEY: 8:72057594040090624 (8194443284a0) waittime=3300 ownerId=16991312 transactionname=UPDATE lasttranstarted=2016-09-28T15:07:26.720 XDES=0xe291860 lockMode=X schedulerid=4 kpid=7240 status=suspended spid=55 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2016-09-28T15:07:24.843 lastbatchcompleted=2016-09-28T15:07:23.603 clientapp=Microsoft SQL Server Management Studio - 查询 hostname=LIXUANYAO hostpid=7584 loginname=LIXUANYAO\Administrator isolationlevel=read committed (2) xactid=16991312 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
spid16s         executionStack
spid16s          frame procname=adhoc line=6 stmtstart=140 stmtend=248 sqlhandle=0x0200000087695c3ac89c30f0ca649915b3e2cb1c22db1292
spid16s     update testklup set cont1 = @i where clskey = 1     
spid16s         inputbuf
spid16s     ----模拟高频update操作
spid16s     declare @i int
spid16s     set @i = 100
spid16s     while 1=1
spid16s     begin
spid16s         update testklup set cont1 = @i where clskey = 1  
spid16s         set @i = @i+1
spid16s     end    
spid16s       resource-list
spid16s        keylock hobtid=72057594040025088 dbid=8 objectname=Test.dbo.testklup indexname=inx_cls id=locka2bc5c0 mode=X associatedObjectId=72057594040025088
spid16s         owner-list
spid16s          owner id=process5f3558 mode=X
spid16s         waiter-list
spid16s          waiter id=process5f3000 mode=S requestType=wait
spid16s        keylock hobtid=72057594040090624 dbid=8 objectname=Test.dbo.testklup indexname=inx_nlcs id=locka2b8d80 mode=S associatedObjectId=72057594040090624
spid16s         owner-list
spid16s          owner id=process5f3000 mode=S
spid16s         waiter-list
spid16s          waiter id=process5f3558 mode=X requestType=wait

View Code

先来看看这些输出是什么意思,完成下表:

参与者 process5f3000 process5f3558
牺牲者  
SPID 57 55
连接背景

Microsoft SQL Server Management Studio – 查询

hostname=LIXUANYAO

hostpid=7584

loginname=LIXUANYAO\Administrator

Microsoft SQL Server Management Studio – 查询

hostname=LIXUANYAO

hostpid=7584

loginname=LIXUANYAO\Administrator

正在申请中资源/类型 KEY: 8:72057594040025088 (8194443284a0)/ Mode:S KEY: 8:72057594040090624 (8194443284a0)/ Mode:X
当前开启了几层事务 0 2
事务隔离级别 read committed (2) read committed (2)
当前正在运行的语句 select @cont2=cont2 from testklup where nlskey=1 update testklup set cont1 = @i where clskey = 1
当前正在运行的批处理

–模拟高频select操作

declare @cont2 char(3000)

while 1=1

begin

    select @cont2=cont2 from testklup where nlskey=1

end

–模拟高频update操作

declare @i int

set @i = 100

while 1=1

begin

    update testklup set cont1 = @i where clskey = 1  

    set @i = @i+1

end

死锁资源 资源2 资源1
资源类型 keylock keylock
具体内容

hobtid=72057594040090624 dbid=8 objectname=Test.dbo.testklup 

indexname=inx_nlcs id=locka2b8d80

hobtid=72057594040025088 dbid=8 objectname=Test.dbo.testklup 

indexname=inx_cls id=locka2bc5c0

持有资源进程/类型 process5f3000(spid57) / mode:S process5f3558(spid55) / mode:X
等待资源进程/类型 process5f3558(spid55) / mode:X process5f3000(spid57) / mode:S

 

2.2、死锁图

在跟踪文件里,可以看到这样的死锁图形:
www.bifa9999com 10
我们可以从跟踪文件中,提取死锁事件数据,将死锁图保存为死锁XML文件(.xdl)。然后针对.xdl文件使用记事本打开,整个死锁的内容一目了然。

www.bifa9999com 11www.bifa9999com 12

/** 死锁事件数据是后期提取的,与原死锁图稍有不同 **/
-- 第一部分
<deadlock-list>
    <deadlock victim="process2b6b000">
        -- 第二部分
        <process-list>
            <process id="process2b6b000" taskpriority="0" logused="0" waitresource="KEY: 8:72057594040025088 (8194443284a0)" waittime="636" ownerId="758239" transactionname="SELECT" lasttranstarted="2016-10-26T09:59:00.300" XDES="0x5dba408" lockMode="S" schedulerid="1" kpid="4008" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2016-10-26T09:59:00.300" lastbatchcompleted="2016-10-26T09:58:55.807" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="LIXUANYAO" hostpid="5872" loginname="LIXUANYAO\Administrator" isolationlevel="read committed (2)" xactid="758239" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
                <executionStack>
                    <frame procname="adhoc" line="5" stmtstart="134" stmtend="232" sqlhandle="0x02000000c3289f0a3b1699a92a155bf6611d1a82d2dbd79a">
                        select @cont2=cont2 from testklup where nlskey=1     
                    </frame>
                </executionStack>
                <inputbuf>
                    ----模拟高频select操作
                    declare @cont2 char(3000)
                    while 1=1
                    begin
                        select @cont2=cont2 from testklup where nlskey=1
                    end    
                </inputbuf>
            </process>
            <process id="process2dc2aa8" taskpriority="0" logused="228" waitresource="KEY: 8:72057594040090624 (8194443284a0)" waittime="636" ownerId="758240" transactionname="UPDATE" lasttranstarted="2016-10-26T09:59:00.307" XDES="0x5bf6c08" lockMode="X" schedulerid="4" kpid="5832" status="suspended" spid="55" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-10-26T09:58:59.470" lastbatchcompleted="2016-10-26T09:58:58.073" lastattention="2016-10-26T09:58:58.073" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="LIXUANYAO" hostpid="5872" loginname="LIXUANYAO\Administrator" isolationlevel="read committed (2)" xactid="758240" currentdb="8" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
                <executionStack>
                    <frame procname="adhoc" line="6" stmtstart="140" stmtend="248" sqlhandle="0x0200000087695c3ac89c30f0ca649915b3e2cb1c22db1292">
                        update testklup set cont1 = @i where clskey = 1     
                    </frame>
                </executionStack>
                <inputbuf>
                    ----模拟高频update操作
                    declare @i int
                    set @i = 100
                    while 1=1
                    begin
                        update testklup set cont1 = @i where clskey = 1  
                        set @i = @i+1
                    end    
                </inputbuf>
            </process>
        </process-list>
        -- 第三部分
        <resource-list>
            <keylock hobtid="72057594040025088" dbid="8" objectname="Test.dbo.testklup" indexname="inx_cls" id="locka49e800" mode="X" associatedObjectId="72057594040025088">
                <owner-list>
                    <owner id="process2dc2aa8" mode="X"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process2b6b000" mode="S" requestType="wait"/>
                </waiter-list>
            </keylock>
            <keylock hobtid="72057594040090624" dbid="8" objectname="Test.dbo.testklup" indexname="inx_nlcs" id="locka47ab40" mode="S" associatedObjectId="72057594040090624">
                <owner-list>
                    <owner id="process2b6b000" mode="S"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process2dc2aa8" mode="X" requestType="wait"/>
                </waiter-list>
            </keylock>
        </resource-list>
    </deadlock>
</deadlock-list>

View Code

SPID=57:查询语句使用非聚集索引inx_nlcs查找nlskey=1的记录,持有inx_nlcs上的S锁;由于索引未覆盖列cont2,需通过键查询得到cont2,于是申请inx_cls上的S锁。
SPID=55:更新语句使用聚集索引inx_cls查找clskey=1的记录,持有inx_cls上的X锁;由于非聚集索引inx_nlcs包含列cont1,在更新表中记录后,还需更新非聚集索引中的数值,于是申请inx_ncls上的X锁。

www.bifa9999com 13www.bifa9999com 14

--hobtid
SELECT OBJECT_NAME(p.object_id) AS TableName,
       i.name AS IndexName
FROM sys.partitions AS p
     INNER JOIN sys.indexes AS i ON p.object_id = i.object_id
                                    AND p.index_id = i.index_id
WHERE p.hobt_id = 72057594040090624
--associatedObjectId
SELECT OBJECT_NAME(p.object_id) AS TableName,
       i.name AS IndexName
FROM sys.partitions AS p
     INNER JOIN sys.indexes AS i ON p.object_id = i.object_id
                                    AND p.index_id = i.index_id
WHERE p.partition_id = 72057594040025088

--case when type = 1|3 then container_id = sys.partitions.hobt_id
--case when type = 2 then container_id = sys.partitions.partition_id
SELECT * FROM sys.allocation_units WHERE container_id=72057594040090624
SELECT * FROM sys.allocation_units WHERE container_id=72057594040025088

SELECT %%lockres%% AS keyhashvalue,* FROM testklup WHERE %%lockres%% ='(8194443284a0)'

View Code

1.创建测试表。

3、Deadlocks-Example

SQL
Server上的一个奇怪的Deadlock及其分析方法:https://blogs.msdn.microsoft.com/apgcdsd/2012/02/27/sql-serverdeadlock/
SQL Server Deadlocks by
Example:https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/

在 Microsoft SQL Server Management 
Studio上,新建一个查询,写创建表DealLockTest_1 &
DealLockTest_2两个表:

 www.bifa9999com 15

 脚本:

www.bifa9999com 16www.bifa9999com 17代码

use Test

–创建分析死锁使用到的两个表DealLockTest_1 & DealLockTest_2
go
Set Nocount On    
Go
if object_id(‘DealLockTest_1’) Is Not Null
    Drop Table DealLockTest_1
go
Create Table DealLockTest_1
(
    ID int Identity(1,1) Primary Key,
    Name nvarchar(512)
)

if object_id(‘DealLockTest_2’) Is Not Null
    Drop Table DealLockTest_2
go
Create Table DealLockTest_2
(
    ID int Identity(1,1) Primary Key,
    Name nvarchar(512)
)

Go

–插入一些测试数据
Insert Into DealLockTest_1(Name)
    Select name From sys.all_objects

    
Insert Into DealLockTest_2(Name)
    Select name From sys.all_objects
Go
    

创建好表和插入测试数据后,先执行脚本代码(因为我们不需要跟踪该代码),紧接着,我们就模拟两个会话,一个会话里面包含一个事务。这里我们就新建两个查询,其中第一个会话,是更新DealLockTest_1表后,等待5秒钟,更新DealLocktest_2.

 

www.bifa9999com 18 

www.bifa9999com 19www.bifa9999com 20代码

Use Test
Go

    –第一个会话
    
    Begin Tran
    
    Update DealLockTest_1
        Set Name=N’test1′
        Where ID >0
    
    
    /*这里的Waitfor等待,是为了容易获取死锁的发生*/        
    Waitfor Delay ’00:00:05′
    
        
    Update DealLockTest_2
        Set Name=N’test2′
        Where ID >0        
    
    Commit Tran

Go

代码写好后,我们先不要执行代码,接下来就写第二个会话代码;
第二个会话更新表的顺序,刚好与第一个会话相反,是更新DealLockTest_2表后,等待5秒钟,更新DealLocktest_1.

 

www.bifa9999com 21 

www.bifa9999com 22www.bifa9999com 23代码

Use Test
Go
    –第二个会话
    
    Begin Tran
    
    Update DealLockTest_2
        Set Name=N’test1′
        Where ID >0

    /*这里的Waitfor等待,是为了容易获取死锁的发生*/                
    Waitfor Delay ’00:00:05′            
        
    Update DealLockTest_1
        Set Name=N’test2′
        Where ID >0        
    
    Commit Tran

Go

第二个会话代码,也先不要执行。

 

 

2.启动SQL Server Profiler,创建Trace(跟踪).

 

启动SQL Server Profiler工具(在Microsoft SQL Server Management 
Studio的工具菜单上就发现它),创建一个Trace,Trace属性选择主要是包含:

Deadlock graph
Lock: Deadlock
Lock: Deadlock Chain
RPC:Completed
SP:StmtCompleted
SQL:BatchCompleted
SQL:BatchStarting

www.bifa9999com 24 

 点执行按钮,启动Trace。

 

3.执行测试代码&监视死锁。

 转到 Microsoft SQL Server Management 
Studio界面,执行第一个会话&第二个会话的代码,稍稍等待5秒钟,我们就会发现其中一个会话收到报错消息

 

www.bifa9999com 25 

 

 我们再切换到SQL Server Profiler界面,就能发现SQL Server
Profiler收到执行脚本过程发生死锁的信息。

 

www.bifa9999com 26 

 OK,这里就先停止SQL Server Profiler上的“暂停跟踪” Or
“停止跟踪”按钮,下面我们具体分析死锁发生过程。

 

 

4.分析死锁

 如下图,我们可以看到第一个会话在SPID 54,第二个会话在SPID 55,一旦SQL
Server发现死锁,它就会确定一个优胜者,可成功执行,和另一个作为牺牲品,要回滚。

可以到看到EventClass列中,两条SQL:BatchCompleted事件紧跟在Lock:DealLock后面,其中一条,它就是作为牺牲品,它会被回滚.而另一条SQL:BatchCompleted将会是优胜者,成功执行。

 那么,谁是优胜者,谁是牺牲品呢? 不用着急,通过DealLock
graph事件,所返回来的信息,我们可以知道结果。

 

www.bifa9999com 27 

 

 我们虽然不能明白DealLock
graph图示的含义,但通过图中描述的关系,我们知道一些有用的信息。图中左右两旁椭圆形相当一个处理节点(Process
Node),当鼠标移动到上面的时候,可以看到内部执行的代码,如Insert,UPdate,Delete.有打叉的左边椭圆形就是牺牲者,没有打叉的右边椭圆形是优胜者。中间两个长方形就是一个资源节点(Resource
Node),描述数据库中的对象,如一个表、一行或一个索引。在我们当前的实例中,资源节点描述的是,在聚集索引请求获得排它锁(X)。椭圆形与长方形之间,带箭头的连线表示,处理节点与资源节点的关系,包含描述锁的模式.

 

 接下来我们更详细的看图里面的数据说明。

 先看右边作为优胜者的这椭圆形,我们可以看到内容包含有:

 

服务器进程 ID: 服务器进程标识符
(SPID),即服务器给拥有锁的进程分配的标识符。
服务器批 ID: 服务器批标识符 (SBID)。
执行上下文 ID: 执行上下文标识符 (ECID)。与指定 SPID
相关联的给定线程的执行上下文 ID。ECID = {0,1,2,3, …n},其中 0
始终表示主线程或父线程,并且 {1,2,3, …n} 表示子线程。
死锁优先级: 进程的死锁优先级有关可能值的详细信息,请参阅 SET
DEADLOCK_PRIORITY (Transact-SQL)。
已用日志: 进程所使用的日志空间量。
所有者 ID: 正在使用事务并且当前正在等待锁的进程的事务 ID。
事务描述符: 指向描述事务状态的事务描述符的指针。
 

这些数据描述,对于我们理解死锁,只需要知道其中的一些就够,除非我们在专门SQL
Server机构工作,才可能要深入理解它们。

 www.bifa9999com 28

 

 

 下面我们来看左边作为牺牲品的这椭圆形处理节点,它告诉我们以下信息:

1.它是一个失败的事务。(蓝色的交叉表示)

2.它是作为牺牲品的T-SQL代码。

3.它对右下方的资源节点有一个排它锁(X).

4.它对右上方的资源节点请求 一个排它锁(X).

 

www.bifa9999com 29 

 

 

我们再来看中间两个长方形的资源节点,两个处理节点对它们各自都使用权,来执行它们各自的代码,同时又有对对方使用资源请求的动作,从而发生了资源的竞争。

这也就让我们明白死锁发生的原因。

 

 这里说明下资源节点的一些信息:

 

HoBT:  堆或 B 树。 用于保护没有聚集索引的表中的 B
树(索引)或堆数据页的锁

associated objid: 关联的对象ID,这里只是索引关联的对象ID.

Index name:索引名

 

 

 

 

 

 

www.bifa9999com 30 

 

 

 让我们再对SQL Server Profiler监视到的数据,作一次整理:

 回顾图:

www.bifa9999com 26 

1.在第3行SQL:BatchStarting, SPID 54
(第一个会话启动),在索引PK__DealLock__3214EC274222D4EF获得一个排它锁,再处理等待状态,(因为在这个实例中我设置了Waitfor
Delay ’00:00:05′)

2.在第6行SQL:BatchStarting, SPID 55
(第二个会话启动),在索引PK__DealLock__3214EC2745F365D3获得一个排它锁,再处理等待状态,(因为在这个实例中我设置了Waitfor
Delay ’00:00:05′)

3.两个进程都各自获得一个排它锁(X),几秒过去,它们就开始请求排它锁。

SPID 54
(第一个会话),先对PK__DealLock__3214EC2745F365D3请求一个排它锁(X),但PK__DealLock__3214EC2745F365D3当前已经给SPID
55 (第二个会话)获得。SPID 54要于等待。

同时,

SPID 55
(第二个会话),开始对PK__DealLock__3214EC274222D4EF请求一个排它锁(X),但PK__DealLock__3214EC274222D4EF当前已经给SPID
54 (第一个会话)获得。SPID 55要等待。 

 

这里就出现了进程阻塞,从而发生死锁。

 

4.SQL Server 
检查到这两个进程(第一个&第二个会话)发生死锁,并对占用资源比较少的进程,列入牺牲品名单,将它终止(Kill)。通过左右椭圆形进程节点显示,可以发现已用日志最少的是左边的进程节点。

 

  1. SPID 54 (第一个会话)被回滚(Rollback),SPID 55
    (第二个会话)执行成功。

 

 到这里我们已算完成了,对死锁的监视和分析。

 

:是于其他死锁的定义,死锁模式,死锁避免&预防,等等,不是本文重点,我没有提出,网上太多这方面的文章)

 (完)