SQL SERVER:一个由SELECT和DELETE引起的死锁
0
悬赏园豆：20
[已解决问题]
解决于 2016-04-28 17:45
Hi，各位。最近我被一个由Select和Delete引起的死锁问题困扰，希望能得到大家的帮助。
死锁涉及到的表:
MOE_PendRloc(
SourceSystem varchar(10) NULL,
SourceRef varchar(15) NULL,
DepartDate datetime NULL,
OwnerTeam char(3) NULL,
OwnerStaff varchar(10) NULL,
NoOfTkt int NULL,
Passengers nvarchar(500) NULL,
CreateOn datetime NULL,
CreateBy varchar(10) NULL,
UpdateOn datetime NULL,
UpdateBy varchar(10) NULL
)
涉及到的索引:
NONCLUSTERED INDEX [IX_MOE_PendRloc_SrcRef_Team_System] ON [dbo].[MOE_PendRloc]
(
[SourceRef] ASC,
[OwnerTeam] ASC,
[SourceSystem] ASC
)
涉及到该表的业务逻辑（整个业务逻辑运行在一个READ COMMITTED的事务中）:
先执Select操作：SELECT top 1 * FROM MOE_PendRloc WHERE [SourceRef]=‘AJPKCI’ AND[SourceSystem]=‘IUR’ AND [OwnerTeam]=‘HWT’
如果查询得到数据，则执行Delete操作:
DELETE FROM MOE_PendRloc WHERE[SourceRef]='AJPKCI' AND[OwnerTeam]='HWT'
如果查询没有得到数据，则执行Insert操作:
INSERT INTO MOE_PendRloc (SourceRef,SourceSystem,OwnerTeam,...) Values(‘AJPKCI’,‘IUR’,‘HWT’...)
ErrorLog中的Deadlock Graph:
Node:1
RID: 49:1:10204:3 CleanCnt:3 Mode:X Flags: 0x2
Grant List 2:
Owner:0x80169420 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:155 ECID:0 XactLockInfo: 0xBEF98F3C
SPID: 155 ECID: 0 Statement Type: DELETE Line #: 1
Input Buf: Language Event: (@_SourceRef varchar(15),@_OwnerTeam char(3))DELETE FROM MOE_PendRloc WHERE [SourceRef]=@_SourceRef AND [OwnerTeam]=@_OwnerTeam
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0xBF574FA0 Mode: S SPID:73 BatchID:0 ECID:0 TaskProxy:(0xBEED0378) Value:0x80154280 Cost:(0/0)
NULL
Node:2
KEY: 49:72057594189512704 (8d031e91b408) CleanCnt:2 Mode:U Flags: 0x0
Grant List 0:
Owner:0x80166140 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:73 ECID:0 XactLockInfo: 0xBF574FC4
SPID: 73 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: (@_SourceRef varchar(6),@_SourceSystem varchar(3),@_OwnerTeam char(3))SELECT top 1 * FROM MOE_PendRloc WHERE [SourceRef]=@_SourceRef AND [SourceSystem]=@_SourceSystem AND [OwnerTeam]=@_OwnerTeam ;
Grant List 1:
Grant List 2:
Requested By:
ResType:LockOwner Stype:'OR'Xdes:0xBEF98F18 Mode: X SPID:155 BatchID:0 ECID:0 TaskProxy:(0xBF5CA378) Value:0x80159360 Cost:(0/92)
NULL
发生死锁时，MOE_PendRloc表中有若干条WHERE [SourceRef]=‘AJPKCI’ AND [SourceSystem]=‘IUR’ AND [OwnerTeam]=‘HWT’ 的记录（我为了重现死锁，使用多线程负载测试进行请求，因此会同时有多条相似的记录）
使用 DBCC IND和DBCC PAGE命令查看相关索引页情况：
粉色的记录就是本次死锁中被Node 2加S锁的索引键，而其中HEAP RID 0xDC27000001000300 是指向 RID: 1:10204:3,也就是 Node1中被加上X锁的RID
我对该死锁的推测是(由于我对锁不十分理解,所以不是很确定)：
SPID 155 delete语句从粉色框的索引键(加S锁)，找到该行RID，并将行加上X锁并删除之，删除行后需要将索引键一并删除，所以回过头来对索引键请求X锁。而就在删除行时，SPID 73查找到索引键并对索引键加上S锁(SPID 73和SPID 155的S锁兼容)，但当进行RID查找到行时，发现行上已经有SPID155的X锁，所以等待。而SPID155对索引键请求X锁时，发现已经被SPID73加了S锁，所以也在等待。于是出现了死锁。
以上是我的猜测，但我不明白的是：
是否Delete操作真会在索引查找时先对索引键加S锁，然后进行RID查找到数据行并加X锁删除，再调过头对索引键加X锁然后删除该索引键。我查了很多资料，没有找到这个说法。
个人感觉这个理解应该是错的，应该是找到索引键后就直接加X锁，找到数据行也是直接加X锁。但如果是直接加X锁，为什么该索引键还会被SPID73加上了S锁？
不好意思只有24豆了
最佳答案
2
SELECT top 1 * FROM MOE_PendRloc with(updlock)...
ps:这个你都不用想那么深，你第一句一个s锁，后面两个条件都是x锁，x锁又排他，两个同时进来都可以拿到s锁，后面到x锁就变成两边互相等对方释放s锁。
收获园豆：10
其他回答(2)
0
说的好高深.不太懂.但我之前也遇到类似的情况.是不是你表里有多个索引?或者一个操作会影响到多个索引.
收获园豆：5
是有另外一个索引，不过本次业务逻辑的数据查询不会涉及到那个索引，死锁中也没有出现和那个索引有关的信息。
@柯柏文: 我之前也是这样的.后来把所有的索引整理一遍就好了.你试试把另外个索引删了看还会不会.
要是还会死锁的话就不太懂了..
0
SELECT top 1 * FROM MOE_PendRloc WHERE [SourceRef]=‘AJPKCI’ AND[SourceSystem]=‘IUR’ AND [OwnerTeam]=‘HWT’
如果我记得没有错 这一句表上是有锁的 这一句标明不要锁
收获园豆：5
之前我也建议在select时加nolock，但后来发现这个业务逻辑不允许有脏读数据，所以不能加nolock了事。
@柯柏文: 不能有脏数据也没事 这里既然规约不让删除 那用 悲观锁呢？
@柯柏文: 首先确定下问题 select 有没有锁表 先 测试下 是不是select造成的死锁
如果是 考虑用其他手段实现 例如 悲观锁（在数据上加时间戳）