在使用SELECT-UPDATE模式时管理并发性

Question

假设你有以下代码（请忽略它的糟糕程度）：

BEGIN TRAN;
DECLARE @id int
SELECT @id = id + 1 FROM TableA;
UPDATE TableA SET id = @id; --TableA must have only one row, apparently!
COMMIT TRAN;
-- @id is returned to the client or used somewhere else

在我看来，这样做并不能正确地处理并发。仅仅因为你有一个事务，并不意味着在你执行更新语句之前，其他人不会读取相同的值。 现在，将代码保持不变（我意识到最好使用单个语句或者更好地使用自增/标识列），有哪些确保它能正确处理并发并防止出现允许两个客户端获取相同id值的竞争条件的方法？ 我很确定在SELECT语句中添加WITH (UPDLOCK, HOLDLOCK)可以解决问题。可串行化事务隔离级别似乎也可以工作，因为它拒绝其他人在事务结束之前读取你所读取的内容（更新：这是错误的，请参考Martin的回答）。这是真的吗？它们都能很好地工作吗？有没有一种更优选的方法？ 想象一下，做一些比更新ID更合理的操作——基于读取的计算需要进行更新。可能涉及到多个表，其中一些你会写入，而其他一些则不会。在这种情况下，最佳实践是什么？ 写完这个问题后，我觉得锁提示更好，因为这样你只会锁定你需要的表，但我很感谢任何人的意见。 顺便说一句，不，我不知道最佳答案，真的想要更好地理解！ :)

- ErikE

---

仅供澄清：您是想防止两个客户端读取相同的值，还是防止发出基于过时数据的“更新”操作？如果是后者，您可以使用“rowversion”列来检查要更新的行是否自读取以来就没有被更改过。 - a1ex07

我们不希望第二个客户在第一个客户将旧的 ID 值更新为新值之前获取该值。它应该被阻塞。 - ErikE

Answer 1

只是针对SERIALIZABLE隔离级别方面进行说明。是的，这样做可以工作，但存在死锁风险。

两个事务都能够并发读取行。它们不会相互阻塞，因为它们将根据表结构采取对象S锁或索引RangeS-S锁，这些锁是兼容的。但是，当它们尝试获取更新所需的锁（分别为对象IX锁或索引RangeS-U）时，它们将相互阻塞，从而导致死锁。

而使用显式的UPDLOCK提示将使读取操作串行化，从而避免了死锁风险。

- Martin Smith

---

+1 但是：对于堆表，即使使用更新锁定，仍然可能发生转换死锁：http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/11/some-heap-tables-may-be-more-prone-to-deadlocks-than-identical-tables-with-clustered-indexes.aspx - A-K

奇怪的，@alex。我想这可能与引擎在实际进行UPDLOCK之前尝试找到要锁定的内容时出现了竞争条件有关... - ErikE

@ErikE - 在Alex的文章中，转换死锁是在堆本身上从IX转换为X。有趣的是，没有任何行符合条件，因此永远不会获取行锁。不确定为什么需要获取X锁。 - Martin Smith

这个回答的六行比我读过的许多其他回答更有用。 - George Menoutis

Answer 2

我认为对你来说最好的方法是暴露你的模块以承受高并发，亲自去尝试一下。有时候仅使用UPDLOCK就足够了，不需要使用HOLDLOCK。有时候sp_getapplock也非常有效。在这里我不会做任何笼统的陈述 - 有时候增加一个索引、触发器或索引视图可能会改变结果。我们需要对代码进行压力测试，并且根据具体情况自己去验证。 我写了几个压力测试的例子这里。 编辑：为了更好地了解内部情况，你可以阅读Kalen Delaney的书籍。然而，书籍可能和其他文档一样不同步。此外，有太多的组合要考虑：六种隔离级别，多种类型的锁，聚簇/非聚簇索引等等。这是很多组合。而且，SQL Server是闭源的，所以我们不能下载源代码、调试等等 - 那将是知识的终极来源。其他任何内容都可能不完整或在下一个版本或服务包发布后过时。 所以，你不应该在没有进行自己的压力测试之前决定对你的系统有效的东西。无论你读到什么，它可以帮助你理解正在发生的事情，但你必须证明你所阅读的建议对你有效。我不认为有人可以为你做到这一点。

- A-K

Answer 3

在这种特殊情况下，将UPDLOCK锁添加到SELECT确实可以防止异常情况发生。添加HOLDLOCK不是必需的，因为更新锁将在事务期间一直保持，但我承认过去可能由于习惯而包含它（可能是不好的习惯）。

想象一下，做一些比ID更新更合法的操作，基于读取的计算需要进行更新。可能涉及许多表格，其中一些你将进行写入，其他的则不会。在这种情况下，最佳实践是什么？

并没有最佳实践。并发控制的选择必须基于应用程序的要求。某些应用程序/事务需要执行得像它们对数据库有独占所有权一样，以避免任何代价上的异常和不准确。其他应用程序/事务可以容忍彼此的一定程度的干扰。

检索网络商店中产品的带有标记的库存水平（<5，10+，50+，100+）= 脏读取（不准确并不重要）。 在网店结账时检查和减少库存水平 = 可重复读取（我们必须在出售之前拥有库存，我们不能出现负库存水平）。 在银行之间转移现金到我的活期和储蓄账户 = 序列化（不要错误计算或错放我的现金！）。

- Mark Storey-Smith