sql-server - 使用 DELETE - OUTPUT - INSERT 更新身份

Question

我需要在非常具体的场景中更新身份列(大多数时候身份将被单独保留)。当我确实需要更新它时，我只需要给它一个新值，所以我尝试使用 DELETE + INSERT 组合。
目前我有一个看起来像这样的工作查询:

DELETE Test_Id
OUTPUT DELETED.Data, 
       DELETED.Moredata 
INTO Test_id 
WHERE  Id = 13 

(这只是一个例子，真正的查询稍微复杂一些。)
一位同事提出了一个重要的观点。她问这是否会导致死锁，因为我们正在同一张表中写入和读取。虽然在示例中它工作正常(六行)，但在具有数万行的真实场景中这可能不起作用。

这是一个真正的问题吗？如果是这样，有什么办法可以避免吗？

我设置了一个 SQL Fiddle example .
谢谢!

Answer 1

我的第一个想法是，可以。也许它仍然是可能的，但是在这个简化版本的声明中，很难陷入僵局。您正在选择一行，可能为其获取了行级锁，而且删除和插入所需的锁是在彼此之后非常快速地获取的。

我对一个包含一百万行的表进行了一些测试，在 6 个不同的连接上并行执行该语句 500 万次。没有遇到任何死锁。

但是添加 reallive 查询，一个带有索引和外键的表，您可能会成为赢家。我有一个类似的陈述确实导致了僵局。

我遇到过类似语句的死锁错误。

UPDATE A
SET x=0
OUTPUT INSERTED.ID, 'a' INTO B

因此，要完成此语句，mssql 需要为表 A 的更新获取锁，为表 B 的插入获取锁，并为表 A 获取共享(读取)锁，以验证表 B 必须对表 A 的外键。

最后但并非最不重要的一点是，mssql 决定在这个导致语句自身死锁的特定查询上使用并行性是明智的。为了解决这个问题，我只是在语句上设置“MAXDOP 1”查询提示以防止并行。

然而，对于防止死锁没有明确的答案。正如他们经常对 mssql 所说的那样，这取决于情况。您可以使用 TABLOCKX 表提示进行独占。这将防止死锁，但由于其他原因可能不希望这样做。