删除然后批量插入时出现SQL死锁 [英] SQL deadlock on delete then bulk insert

问题描述

我遇到无法解决的SQL Server死锁问题.

I have an issue with a deadlock in SQL Server that I haven't been able to resolve.

基本上，我有大量的并发连接(来自许多机器)，这些并发连接正在执行事务，在这些事务中，它们首先删除一系列条目，然后使用批量插入重新插入相同范围内的条目.

Basically I have a large number of concurrent connections (from many machines) that are executing transactions where they first delete a range of entries and then re-insert entries within the same range with a bulk insert.

本质上，交易看起来像这样

Essentially, the transaction looks like this

BEGIN TRANSACTION T1
DELETE FROM [TableName] WITH( XLOCK HOLDLOCK ) WHERE [Id]=@Id AND [SubId]=@SubId

INSERT BULK [TableName] (
[Id] Int
, [SubId] Int
, [Text] VarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS
) WITH(CHECK_CONSTRAINTS, FIRE_TRIGGERS)

COMMIT TRANSACTION T1

批量插入仅在同一事务中插入与删除的ID和SubId相匹配的项目.此外，这些Id和SubId条目不应重叠.

The bulk insert only inserts items matching the Id and SubId of the deletion in the same transaction. Furthermore, these Id and SubId entries should never overlap.

当我有足够多的这种形式的并发事务时，我开始看到这些语句之间有大量死锁.

When I have enough concurrent transaction of this form, I start to see a significant number of deadlocks between these statements.

我添加了锁定提示XLOCK HOLDLOCK来尝试解决此问题，但它们似乎并没有帮助.

I added the locking hints XLOCK HOLDLOCK to attempt to deal with the issue, but they don't seem to be helpling.

此错误的规范死锁图显示:

The canonical deadlock graph for this error shows:

连接1:

• 在PK_TableName上保留RangeX-X
• 将IX Page锁定在桌子上
• 请求在表格上锁定X页面

连接2:

• 将IX Page锁定在桌子上
• 请求桌子上的RangeX-X锁

为了确保这些死锁不会发生，我需要做什么.

What do I need to do in order to ensure that these deadlocks don't occur.

我一直在阅读RangeX-X锁，但我不确定我是否完全了解这些锁的状态.除了将整个表格锁定在这里，我还有其他选择吗?

I have been doing some reading on the RangeX-X locks and I'm not sure I fully understand what is going on with these. Do I have any options short of locking the entire table here?

推荐答案

紧接着Sam Saffron的回答:

Following on from Sam Saffron's answer:

• 如果@ ID7 @ SubID不一致，请考虑使用READPAST提示跳过所有持有的锁
• 考虑SERIALIZABLE并删除XLOCK，HOLDLOCK
• 为批量插入使用单独的临时表，然后从中复制

这篇关于删除然后批量插入时出现SQL死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！