数据库死锁 之 三.死锁解读
-- 3 解读死锁的信息
前面的系列文章中已经得到了死锁的相关信息，那目前就需要对这些信息解读，得到关键信息，从而发现堵塞的具体点；
-- 3.1 解读Profiler中的死锁信息图；
1.找到数据库 DB ID： select db_name(5)
2. 找到数据库的文件名字
use 数据库名称 select * from sys.database_files where file_id =1
3. 通过page id 找到具体的对象
#打开DBCC 3604 开关，然后运行DBCC Page （databaseName,fileId,pageId,dumStyle）
DBCC TRACEON(3604) DBCC Page (dotnet_erp60_cs,1,666)
# 带入ObjectId以及IndexId查询 SELECT sc.name as schema_name, so.name as object_name, si.name as index_name FROM sys.objects as so JOIN sys.indexes as si on so.object_id=si.object_id JOIN sys.schemas AS sc on so.schema_id=sc.schema_id WHERE so.object_id =343880492 and si.index_id = 0; GO
通过这里我们就可以解读得到死锁的资源，结合系统操作来分析，从而解决死锁问题；
-- 3.2 解读死锁日志信息；
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
此处的信息解读分为三部分：
node 1,node 2,Victim Resource Owner(牺牲者)
|
|
关键信息 |
解读
|
|
node1
|
PAGE: 5:1:1544
Mode:IX
SPID:62
Event: BEGIN TRANSACTION ;INSERT INTO TestA(Name) VALUES('张三');SELECT name FROM TestB;COMMIT;
|
数据库5 1分区 1544行
锁类型：索引排它锁
进程ID
执行的SQL语句
|
|
node2
|
PAGE: 5:1:664
Mode:IX
SPID:63
Event: BEGIN TRANSACTION ;INSERT INTO TestB(Name) VALUES('张三');SELECT name FROM TestA;COMMIT;
|
同上
|
|
Victim Resource Owner
|
SPID:63
lockmode:S
isolationlevel=read committed
clientapp
executionStack
|
干掉的进程：63
锁类型为 获取共享锁
事务隔离级别：提交读
发起事件的来源
执行的堆信息，包括执行的语句
|
通过三部分的解读，我们也能得到死锁的资源了，结合操作来解决死锁问题；
这个系列的关于死锁的相关分享就到这里了，结尾说下解决思路；这类的死锁类型为共享锁，代表数据表被X锁占用，无法执行共享锁，这种场景多发生在对同一资源的更新和查询上，该场景有两种解决思路：
1.事务默认隔离级别改为快照读
2.查询语句增加with(noloc),去除共享锁
在一个死锁发生时，有如下的一些大概的思路供参考（后续会再陆续开文章来进行分享，具体说明死锁的优化）
1..优化索引,避免全表扫描,减少锁的申请数目.
2.避免事务中的用户交互.
3.使用基于行版本控制的隔离级别.
4.将事务默认隔离级别的已提交读改成快照 SET TRANSACTION ISOLATION LEVEL SNAPSHOT .
首选数据库需要允许开启快照读
alter database 数据库 set ALLOW_SNAPSHOT_ISOLATION on
执行事务的语句中执行事务的隔离级别为允许快照读；当然也可以把数据库默认的隔离级别改为快照读；
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
go
begin TRANSACTION
go
xxx
COMMIT;
# 参考地址：https://www.cnblogs.com/who-else/p/6659564.html
# 查看数据库的默认隔离级别
DBCC Useroptions -- isolation level 这项的值就代表当前的隔离级别
# 设置系统当前隔离级别
SET TRANSACTION ISOLATION LEVEL Read UnCommitted;
其中Read UnCommitted为需要设置的值
5.使用nolock去掉共享锁,但死锁发生在u锁或x锁上，则nolock不起作用
6.升级锁颗粒度(页锁，表锁), 以阻塞还代替死锁
