首页 新闻 搜索 专区 学院

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豆了
wyman25的主页 wyman25 | 初学一级 | 园豆:14
提问于:2016-04-25 09:32
< >
分享
最佳答案
2

SELECT top 1 * FROM MOE_PendRloc with(updlock)...

ps:这个你都不用想那么深,你第一句一个s锁,后面两个条件都是x锁,x锁又排他,两个同时进来都可以拿到s锁,后面到x锁就变成两边互相等对方释放s锁。

收获园豆:10
Daniel Cai | 专家六级 |园豆:10374 | 2016-04-25 10:42
其他回答(2)
0

 说的好高深.不太懂.但我之前也遇到类似的情况.是不是你表里有多个索引?或者一个操作会影响到多个索引.

收获园豆:5
吴瑞祥 | 园豆:28841 (高人七级) | 2016-04-25 09:34

是有另外一个索引,不过本次业务逻辑的数据查询不会涉及到那个索引,死锁中也没有出现和那个索引有关的信息。

支持(0) 反对(0) wyman25 | 园豆:14 (初学一级) | 2016-04-25 09:40

@柯柏文: 我之前也是这样的.后来把所有的索引整理一遍就好了.你试试把另外个索引删了看还会不会.

要是还会死锁的话就不太懂了..

支持(0) 反对(0) 吴瑞祥 | 园豆:28841 (高人七级) | 2016-04-25 09:44
0

SELECT top 1 * FROM MOE_PendRloc  WHERE [SourceRef]=‘AJPKCI’ AND[SourceSystem]=‘IUR’ AND [OwnerTeam]=‘HWT’

 

如果我记得没有错 这一句表上是有锁的 这一句标明不要锁

收获园豆:5
小眼睛老鼠 | 园豆:2774 (老鸟四级) | 2016-04-25 11:29

之前我也建议在select时加nolock,但后来发现这个业务逻辑不允许有脏读数据,所以不能加nolock了事。

支持(0) 反对(0) wyman25 | 园豆:14 (初学一级) | 2016-04-25 11:37

@柯柏文: 不能有脏数据也没事 这里既然规约不让删除 那用 悲观锁呢?

支持(0) 反对(0) 小眼睛老鼠 | 园豆:2774 (老鸟四级) | 2016-04-25 11:45

@柯柏文: 首先确定下问题 select 有没有锁表 先 测试下 是不是select造成的死锁 

如果是 考虑用其他手段实现 例如 悲观锁(在数据上加时间戳)

支持(0) 反对(0) 小眼睛老鼠 | 园豆:2774 (老鸟四级) | 2016-04-25 11:46
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册