数据库死锁 之 二.定位死锁
2.获取死锁的信息
这篇文章接着之前的内容,重现死锁并获取死锁相关的信息,从而帮助我们来解决死锁;
-- 2.1 得到死锁信息
经过不懈的努力,在【啥是死锁】的1.3.1中,我们终于如愿的死锁了,那我们就来分析死锁的信息吧,那下一步我们该怎么做呢?怎么得到死锁的相关信息,从而定位到死锁的原因?
-- 2.1.1 SQL Profiler
通过开启数据的Profiler监听工具获取死锁的相关信息,该方式局限性比较大:
1.如遇到客户的正式环境中,通过Profiler的方式性能消耗较大
2.业务场景过多,死锁场景也不是一定可重现(当然明确了死锁步骤除外)
A.开启Profiler,开启Deadlock监听;
B.运行死锁场景,复现死锁,我们这里就是直接运行我们模式死锁的程序,也就是【啥是死锁】中我们自定义的两个存储过程,代码中实现的重复调用
-- 2.1.2 死锁日志方式获取死锁信息
# A. 开启死锁监控日志
dbcc traceon (1222,-1)
dbcc traceon (1204,-1)
# 关闭死锁监控日志
dbcc traceoff (1222,-1)
dbcc traceoff (1204,-1)
# B. 等待或再次触发死锁,重现死锁
# C. 查看死锁的日志
exec xp_readerrorlog
这个存储过程是有参数的,如果你没有写参数,他默认查询的是sqlserve日志编号为0(当前的日志文档,不是当天,指的是前一一个文件的时间节点到当前时间的所有日志)的日志。
如果你想选择参数,从左往右是一共6个,如下解释
1)日志编号——整型,如下图所示的编码,一次只能指定一个编号 ,不能多个。
2)查找的日志类型——整型,1是sqlserver的日志,2是sqlserver代理日志。
3)要查找的字符串,用双引号包着。——字符串
4)要查找的字符串——字符串,3和4没有区别,也就是你规定两个要查找的字符串,如果你只需要查找一个,第二个写NULL就可以。
5)查询的开始日期——字符串,格式是YYYY-MM-DD hh:mm:ss
6)查询的结束日期——字符串,格式是YYYY-MM-DD hh:mm:ss
直接给一个示例(参数一定要用双引号引着,要不然会报错)
exec master..xp_readerrorlog 0,1,null,null,"2020-05-25 11:33:00","2020-05-25 11:40:00"
如下图就是日志存储位置:
死锁日志如下:
然后通过死锁时间获取死锁相关的具体信息:
# 执行命令 exec master..xp_readerrorlog 0 Deadlock encountered .... Printing deadlock information Wait-for graph NULL Node:1 PAGE: 5:1:1544 CleanCnt:2 Mode:IX Flags: 0x3 Grant List 0: Owner:0x0000000389D192C0 Mode: IX Flg:0x40 Ref:0 Life:02000000 SPID:62 ECID:0 XactLockInfo: 0x0000000392CCB108 SPID: 62 ECID: 0 Statement Type: SELECT Line #: 1 Input Buf: Language Event: BEGIN TRANSACTION ;INSERT INTO TestA(Name) VALUES('张三');SELECT name FROM TestB;COMMIT; Requested by: ResType:LockOwner Stype:'OR'Xdes:0x0000000392CE2570 Mode: S SPID:63 BatchID:0 ECID:0 TaskProxy:(0x0000000376B9A638) Value:0x89d17b40 Cost:(0/228) NULL Node:2 PAGE: 5:1:664 CleanCnt:2 Mode:IX Flags: 0x3 Grant List 2: Owner:0x0000000389D1B000 Mode: IX Flg:0x40 Ref:0 Life:02000000 SPID:63 ECID:0 XactLockInfo: 0x0000000392CE25A8 SPID: 63 ECID: 0 Statement Type: SELECT Line #: 1 Input Buf: Language Event: BEGIN TRANSACTION ;INSERT INTO TestB(Name) VALUES('张三');SELECT name FROM TestA;COMMIT; Requested by: ResType:LockOwner Stype:'OR'Xdes:0x0000000392CCB0D0 Mode: S SPID:62 BatchID:0 ECID:0 TaskProxy:(0x00000003778E2638) Value:0x89d1a980 Cost:(0/228) NULL Victim Resource Owner: ResType:LockOwner Stype:'OR'Xdes:0x0000000392CE2570 Mode: S SPID:63 BatchID:0 ECID:0 TaskProxy:(0x0000000376B9A638) Value:0x89d17b40 Cost:(0/228) deadlock-list deadlock victim=process37cb164e8 process-list process id=process37cb164e8 taskpriority=0 logused=228 waitresource=PAGE: 5:1:1544 waittime=2128 ownerId=1813485 transactionname=user_transaction lasttranstarted=2020-05-25T12:30:46.770 XDES=0x392ce2570 lockMode=S schedulerid=6 kpid=10664 status=suspended spid=63 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2020-05-25T12:30:46.767 lastbatchcompleted=2020-05-25T12:30:46.757 lastattention=1900-01-01T00:00:00.757 clientapp=.Net SqlClient Data Provider hostname=DESKTOP-9LP3PP7 hostpid=10844 loginname=sa isolationlevel=read committed (2) xactid=1813485 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 executionStack frame procname=adhoc line=1 stmtstart=112 stmtend=154 sqlhandle=0x020000002d23e01cc638e358ac37629c45bc6abb5c644b220000000000000000000000000000000000000000 unknown inputbuf BEGIN TRANSACTION ;INSERT INTO TestB(Name) VALUES('张三');SELECT name FROM TestA;COMMIT; process id=process37db3b468 taskpriority=0 logused=228 waitresource=PAGE: 5:1:664 waittime=2198 ownerId=1813486 transactionname=user_transaction lasttranstarted=2020-05-25T12:30:46.803 XDES=0x392ccb0d0 lockMode=S schedulerid=1 kpid=912 status=suspended spid=62 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2020-05-25T12:30:46.803 lastbatchcompleted=2020-05-25T12:30:46.807 lastattention=1900-01-01T00:00:00.807 clientapp=.Net SqlClient Data Provider hostname=DESKTOP-9LP3PP7 hostpid=10844 loginname=sa isolationlevel=read committed (2) xactid=1813486 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 executionStack frame procname=adhoc line=1 stmtstart=112 stmtend=154 sqlhandle=0x02000000d1985a2ff61043a378822d6e246dbe147d5962180000000000000000000000000000000000000000 unknown inputbuf BEGIN TRANSACTION ;INSERT INTO TestA(Name) VALUES('张三');SELECT name FROM TestB;COMMIT; resource-list pagelock fileid=1 pageid=1544 dbid=5 subresource=FULL objectname=数据库.dbo.TestA id=lock3891fe680 mode=IX associatedObjectId=72057594456965120 owner-list owner id=process37db3b468 mode=IX waiter-list waiter id=process37cb164e8 mode=S requestType=wait pagelock fileid=1 pageid=664 dbid=5 subresource=FULL objectname=数据库.dbo.TestB id=lock3956b5080 mode=IX associatedObjectId=72057594457030656 owner-list owner id=process37cb164e8 mode=IX waiter-list waiter id=process37db3b468 mode=S requestType=wait
通过这篇文章,我们已经找到死锁的信息了,那下面我们就要开始解读这些信息,从而指导我们来解决死锁;