Transaction And Lock--由Lookup导致的死锁情况

存在这样情况：
1.表TB1有一列建立索引
2.事务A对表进行更新，先获取对表的X锁以更新基本表中数据，然后对索引申请X锁以更新索引数据。
3.事务B对表进行更新，先获取索引上S锁以使用索引进行Loopup来查询数据，然后申请表的X锁以更新基本表数据

由于事务A和事务B申请到一部分锁资源同时需要对方的锁资源来完成操作，由于锁的不可剥夺性导致死锁产生

--使用DBCC 来打开追踪死锁
DBCC TRACEON(1222,-1)

--创建测试表
CREATE TABLE TB0001
(
 C1 INT NOT NULL,
 C2 INT NOT NULL,
 C3 INT NOT NULL
)
--向测试表中填充数据,执行多次将表中数据填充上1W
INSERT INTO TB0001(C1,C2,C3)
SELECT C.object_id,C.column_id,C.column_id FROM sys.all_columns C

--在测试表上建立索引
CREATE INDEX IX_C2 ON TB0001
(
C2 DESC
)

---打开一连接执行以模拟事务A操作
WHILE(1=1)
BEGIN
UPDATE dbo.TB0001
SET C2=C2+1
END

---打开一连接执行以模拟事务B操作
--查询中强制使用索引，以模拟RID lookup
WHILE(1=1)
BEGIN
UPDATE dbo.TB0001
SET C1=C1+2
WHERE C2 IN
(
SELECT C2 FROM TB0001 WITH(INDEX=IX_C2 )
WHERE C3=5
)
END

 

等到死锁发生：
Msg 1205, Level 13, State 45, Line 3
Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

查看SQL Server日志

 
解决此类死锁的有效办法：
1.减少每次修改数据的行数,以减少事务的执行时间，从而降低事务发生的可能性
2.在一些情况下使用Include索引来减少lookup操作