SQL Server中同一个事务中对同一个表先后进行修改和删除导致死锁的解决方案
执行SQL
两个会话执行的SQL如下,逐条执行(即非两个语句一起执行)。
begin tran
update t_wf_mapstatemqstore set FSTATUS='0' where FACTINSTID='PUR_PurchaseOrder_266879'
delete from t_wf_mapstatemqstore where FACTINSTID='PUR_PurchaseOrder_266879'
rollback
两个会话修改同一条记录,按正常的理解,update操作会放置排他锁,从而导致其他会话阻塞,不应该发生死锁才对。但实际上地方确发生了。
死锁信息
从上图可以得到下面信息:
会话63执行delete语句,对FACTINSTID='PUR_PurchaseOrder_266879'记录拥有X锁,其需要的资源被会话375持有;会话375执行update语句,对FACTINSTID='PUR_PurchaseOrder_266879'记录拥有U锁,其需要的资源被会话63持有。从而引起死锁。
按正常理解,两个会话访问表的顺序一致,并且修改和删除的是同一行记录,理论上应该会产生阻塞而非死锁。
但有一种情况,建立了多个索引的情况下,是可能由于索引的交叉引起死锁的,接下来看看是否属于这种情形。
检查索引定义情况
可以看到,出了主键外,还有两个索引,而上面的语句中,使用的为FACTINSTID对应的唯一索引。删除会触发对主键的删除(由于主键是聚集索引,数据存储按主键顺序存储),而主键删除时同时会导致其他索引结构的更新。
查看锁资源对应的信息
l 锁资源信息
KEY: 8:72059079959511040 (8423a987d5eb) CleanCnt:2 Mode:X Flags: 0x1
KEY: 8:72059079959642112 (4bccc93b42dd) CleanCnt:2 Mode:U Flags: 0x1
l 查看其对应的值
select partition_id,object_name(object_id),index_id from sys.partitions where object_id=object_id('t_wf_mapstatemqstore')
l 查看index_id对应的索引名称
可以看到的确是发生在两个索引上。
解决方案
通过上面的分析,可以明确,导致问题的原因在于,语句使用了唯一键进行数据修改和删除,而删除操作引发了主键数据的删除,从而导致主键以及其他索引结构的更新,从而导致死锁。
所以,如果我们以主键的作为条件,进行数据的查找和更新,这个时候锁只会放在主键上,从而避免由于其他主键上持有锁而发生死锁。
所以,将上述的SQL语句改写为下面的方式,死锁问题得以解决。
update t_wf_mapstatemqstore set FSTATUS='0' where fid in(select fid from t_wf_mapstatemqstore where FACTINSTID='PUR_PurchaseOrder_266879')
delete from t_wf_mapstatemqstore where fid in(select fid from t_wf_mapstatemqstore where FACTINSTID='PUR_PurchaseOrder_266879')
结论
即使在代码中,对表操作的顺序一致,但也会由于一个表上,持有多个索引,从而引起死锁。所以,为了避免死锁:
l 避免一字段被建立为多个索引
对一个表在一个事务中,同时有更新和删除时(语句分开执行),需要采用主键进行条件过滤
高手膜拜下
SQL Server中同一个事务中对同一个表先后进行修改和删除导致死锁的解决方案
本文2024-09-16 18:38:24发表“云星空知识”栏目。
本文链接:https://wenku.my7c.com/article/kingdee-k3cloud-23434.html