--测试示例：CREATE TABLE mytest ( id INT, name VARCHAR(20), info VARCHAR(20), ) INSERT INTO mytest VALUES(1,'kk',null),(2,'mm',null) --【现在测试只有非聚集索引的】CREATE NONCLUSTERED INDEX IX_mytest_id ON DBO.mytest(id) --打开跟踪标志DBCC TRACEON(1222,-1)
--分别打开个窗口，先执行事务窗口【1】,再执行事务窗口【2】--事务窗口【1】 BEGIN TRAN PRINT @@SPID update dbo.mytest set info='A' where id =1 waitfor delay '00:00:10' update dbo.mytest set info='B' where id =2 ROLLBACK TRAN --事务窗口【2】 BEGIN TRAN PRINT @@SPID update dbo.mytest set info='C' where id =2 select * from dbo.mytest where id =1ROLLBACK TRAN
--session=61 成为死锁牺牲品
--打开SqlServer日志，几个地方可以看到锁定的资源信息。
这个RID具体是哪行数据在争用导致死锁？？
--找到主要信息waitresource=RID: 7:1:786:0这是一个堆表,db_id=7,fileId=1,pageId=786,Slot = 0--【方法一】查看数据页的信息DBCC TRACEON(3604)DBCC PAGE(TEST,1,786,3)
PAGE: (1:786)
Slot 0 Offset 0x1008Length 19
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 19
Memory Dump @0x62DAD008
00000000: 30000800 01000000 04000802 00110013 ?0...............
00000010: 006b6b???????????????????????????????.kk
Slot 0 Column 67108865 Offset 0x0 Length 0 Length (physical) 0
DROPPED = NULL
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 1
Slot 0 Column 2 Offset 0x11 Length 2 Length (physical) 2
name = kk
Slot 0 Column 3 Offset 0x0 Length 0 Length (physical) 0
info =[NULL]
Slot 1 Offset 0x101eLength 19
Record Type =PRIMARY_RECORD Record Attributes= NULL_BITMAP VARIABLE_COLUMNS
Record Size = 19
Memory Dump @0x62DAD01E
00000000: 30000800 02000000 04000802 00110013 ?0...............
00000010: 006d6d???????????????????????????????.mm
Slot 1 Column 67108865Offset 0x0 Length 0 Length (physical) 0
DROPPED = NULL
Slot 1 Column 1 Offset 0x4Length 4 Length (physical) 4
id = 2
Slot 1 Column 2 Offset0x11 Length 2 Length (physical) 2
name = mm
Slot 1 Column 3 Offset 0x0Length 0 Length (physical) 0
info = [NULL]
DBCC 执行完毕。如果DBCC 输出了错误信息，请与系统管理员联系。
可以看到Slot = 0的数据内容为mytest(1,'kk',null)
可以确定，是这一行数据导致死锁。从t-sql脚本看是id=1导致的死锁。
--【方法二】更简单！直接select查询锁资源SELECT * FROM [test].[dbo].mytest where %%lockres%% = '1:786:0'
--【现在创建聚集索引的情况】CREATE CLUSTERED INDEX IX_mytest ON DBO.mytest(name) --CREATE NONCLUSTERED INDEX IX_mytest_id ON DBO.mytest(id)
waitresource=KEY: 7:72057594050445312 (a791659675d9)上面可以得出的信息：db_id=7;hobt_id=72057594050445312;keyhashvalue=(a791659675d9)这下dbcc page()就就很难找了，虽然可以确定是哪个索引，但是确定不了具体锁资源SELECT * FROM SYS.partitions WHERE hobt_id=72057594050445312 SELECT * FROM SYS.indexes WHERE OBJECT_ID= 251147940 AND index_id=1
--查看方法和上面rid查找的一样SELECT * FROM [test].[dbo].mytest where %%lockres%% = '(a791659675d9)'id name info2 mm NULL--牺牲品是在等他id=2的行失败了。--如果还想看看是哪个数据页，还是有办法查看的！SELECT %%physloc%%,* FROM [test].[dbo].mytest where %%lockres%% = '(a791659675d9)'SELECT sys.fn_physlocformatter(0xEE02000001000100)--或者select * from dbo.mytest cross apply sys.fn_PhysLocCracker(%%physloc%%)%%physloc%%能找到数据行的物理地址，函数fn_physlocformatter再将地址解析为(file:page:slot)的格式。在使用DBCC PAGE()查看
其实确定死锁的具体资源也没什么用，更重要还是确定产生死锁的对象及脚本。
通常减少事务死亡的一些办法去解决：
按同一顺序访问对象。
避免事务中的用户交互。
保持事务简短并处于一个批处理中。
使用较低的隔离级别。
使用基于行版本控制的隔离级别。
使用绑定连接。
如果以上没法更改，试着其他的方法：
1 检查脚本是否有优化的空间进行优化
2 确定表中是否有聚集索引，创建聚集索引
3 是否有其他是索引，强制使用有利的索引
4 事务中的语句尽量短、处理少，不要执行太多语句以至时间太长（类似waitfor delay）
5 尽量不要在一个事务中重复的读取和更改相同的数据，能一次读写完最好