在SQL Server中，是否有可能锁定某一列的值？

Question

我有一个看起来像这样的表格：

Id   GroupId
1      G1
2      G1
3      G2
4      G2
5      G2

任何时候都可以读取所有行（仅限已提交的）。当进行更新时，我希望有一个事务，能够锁定组ID，即在给定时间内只能有一个事务尝试每个GroupId进行更新。

最好仍然可以读取所有已提交的行（即其他事务/普通读取仍然可以读取，不会尝试获取“按组更新锁”）。

我之所以想这样做，是因为更新不能依赖于“过时”的数据。也就是说，在事务中进行一些计算，之后不能由另一个事务编辑ID为1的行或添加具有相同GroupId的新行，即使第一个事务永远不会修改该行本身，它仍将依赖于其值。

另一个“好的要求”是有时我需要相同的要求“跨组”，即更新事务必须同时锁定2个组。（这不是动态组数，而只是2个）

- Ilya Chernomordik

---

你尝试过异步操作吗？ https://learn.microsoft.com/zh-cn/sql/relational-databases/native-client/features/performing-asynchronous-operations?view=sql-server-ver15 也许这样你就不用担心分组了。 - asi

听起来你在描述默认行为，即读已提交（READ COMMITTED）。你遇到了什么问题，为什么认为默认的隔离级别不合适？ - SteveC

@asi GroupId是来自业务领域的一个东西，很不幸我无法避免它。 - Ilya Chernomordik

@SteveC 这只是部分读取提交，一个更新事务并不能满足要求，因为在另一个事务完成后读取“旧”数据并进行更新是不够好的。 - Ilya Chernomordik

Answer 1

以下是一些想法。我不认为它们中的任何一个是完美的 - 我认为你需要给自己一组用例并尝试它们。这是我在应用锁之后尝试的一些情况：

• 将WHERE过滤器作为另一组进行SELECT
• 将WHERE过滤器作为锁定组进行SELECT
• 对具有WHERE子句的表进行更新，作为另一组
• 对未锁定ID（而不是GrpID）的表进行更新
• 对已锁定行的表进行更新（例如，ID为1和2）
• 将数据插入具有该GrpId的表中

我有一种奇怪的感觉，这些都不会是100％的解决方案，但最有可能的答案是第二个（设置事务隔离级别）。它可能会锁定更多不必要的内容，但会给您所需的隔离。

还有一件事要记住：如果您锁定了许多行（例如，有数千行具有您想要的GrpId），则SQL Server可以将锁升级为全表锁定。（我相信临界点是5000个锁，但不确定）。

---

老派的黑客工作

在您的交易开始时，以某种方式更新所有相关行，例如：

BEGIN TRAN

UPDATE YourTable 
  SET GrpId = GrpId
  WHERE GrpId = N'G1';

-- Do other stuff

COMMIT TRAN;

没有别的东西可以使用它们，因为（太棒了！）它们是在事务内写入的。

---

方便-设置隔离级别

请参考https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#isolation-levels-in-the-。

在您的事务之前，将隔离级别设置高，例如SERIALIZABLE。

您可能希望在事务开始时读取所有相关行（例如SELECT Grp FROM YourTable WHERE Grp = N'Grp1'）以防止它们被更新。

---

灵活，但需要大量编码。

使用sp_getapplock和sp_releaseapplock进行资源锁定。

这些用于锁定资源，而不是表格或行。

什么是资源？嗯，任何你想要的东西。在这种情况下，我建议使用'Grp1'、'Grp2'等。它实际上并没有锁定行。相反，你要求（通过sp_getapplock或APPLOCK_TEST）是否可以获取资源锁定。如果可以，继续。如果不行，则停止。

任何引用这些表格的代码都需要进行审查，并可能修改为询问是否允许运行。如果有什么东西不询问就直接运行，那么除了通过你明确指定的任何事务外，实际上并没有真正的锁定它。

你还需要确保错误得到适当处理（例如，仍然释放 app_lock）并且阻塞的进程会重新尝试。

- seanb

---

非常感谢您的答复，那种投机取巧的方法确实是个巧妙的技巧，尽管我想避免使用它 :) 关于您在Serializable隔离级别上所写的内容：我确实会在开始时读取所有行（虽然实际上这也会包括日期和TOP上的WHERE子句），但仅此是不足以保护我已读取的行，因为仍然可以添加具有相同groupid的新行，这将影响我的事务（尽管可能可以使用键范围锁定来帮助解决，但我还没有完全弄清楚如何）。我真的很喜欢最后一种选项，因为它更像是我在代码中会做的事情。 - Ilya Chernomordik

这意味着一次只能进行一个更新到一个逻辑的“资源”，有点类似于C#中的锁关键字。我以前没有使用过这些，但它可能完美地起作用，我需要更多地了解细节 :) - Ilya Chernomordik

非常小心 - 我在SO上没有看到其他最近的帖子建议使用应用程序锁。实现非常简单，你需要编写大量代码来管理它们。我们在其中一个生产环境中非常成功地使用它，但我们的一个开发人员花了大约一个月的时间使其足够好。我认为我上面的答案可能有所帮助，但没有一种方法是完美的：这个折中方案可能不够安全，隔离级别可能过重，而且应用程序锁的工作量太大。我并不羡慕你的处境。 - seanb

编码不是问题，我只希望我能正确理解它的工作原理 :) 我们将看到它是否会按预期正常工作。过多地进行锁定可能也不是什么大问题。非常感谢您提供不同选项的帮助。 - Ilya Chernomordik