使用SQL Server的SELECT FOR UPDATE语句

Question

我正在使用一个隔离级别为READ_COMMITTED且READ_COMMITTED_SNAPSHOT=ON的Microsoft SQL Server 2005数据库。

现在我想使用：

SELECT * FROM <tablename> FOR UPDATE

...以便当其他数据库连接尝试访问同一行“FOR UPDATE”时会被阻塞。

我尝试过：

SELECT * FROM <tablename> WITH (updlock) WHERE id=1

...但这会阻塞所有其他连接，即使选择的ID不是“1”。

在Oracle、DB2、MySQL中执行SELECT FOR UPDATE的正确提示是什么？

编辑2009-10-03：

以下是创建表和索引的语句：

CREATE TABLE example ( Id BIGINT NOT NULL, TransactionId BIGINT, 
    Terminal BIGINT, Status SMALLINT );
ALTER TABLE example ADD CONSTRAINT index108 PRIMARY KEY ( Id )
CREATE INDEX I108_FkTerminal ON example ( Terminal )
CREATE INDEX I108_Key ON example ( TransactionId )

很多并行进程执行此SELECT查询：

SELECT * FROM example o WITH (updlock) WHERE o.TransactionId = ?

编辑于2009年10月5日：

为了更好地概述，我已经在下表中列出了所有尝试过的解决方案：

机制                   | 在不同行块上执行SELECT   | 在同一行块上执行SELECT
-----------------------+---------------------------+--------------------------
ROWLOCK                | 否                         | 否
updlock, rowlock       | 是                         | 是
xlock,rowlock          | 是                         | 是
repeatableread         | 否                         | 否
DBCC TRACEON (1211,-1) | 是                         | 是
rowlock,xlock,holdlock | 是                         | 是
updlock,holdlock       | 是                         | 是
UPDLOCK,READPAST       | 否                         | 否
我正在寻找             | 否                         | 是

- tangens

---

你试图做什么需要这样的锁定？通常最好使用适当的查询来解决问题，而不是依赖于服务器的“功能”。 - TFD

请提供您正在使用的查询以及表的DDL，包括任何键和索引。 - RBarryYoung

作为一种解决方法，您可以尝试先对此行进行简单的更新（而不实际更改任何数据）。之后，您可以像选择要更新的行一样继续进行。 - Vladimir

@Lenin，是的，SQL Server 2000至少不允许在事务中的另一个会话修改同一行时对该行进行SELECT。然而，Oracle至少11g版本允许这样做。它允许SELECT。 - bjan

你没有提到启用ALLOW_SNAPSHOT_ISOLATION。你尝试过与READ_COMMITTED_SNAPSHOT一起使用吗？ - László van den Hoek

Answer 1

最近我遇到了死锁问题，因为Sql Server锁定的内容比必要的（页面）多。你无法真正对此做出任何反应。现在我们正在捕捉死锁异常...而我希望我有Oracle。

编辑： 与此同时，我们正在使用快照隔离，这解决了许多问题，但并非所有问题。不幸的是，为了能够使用快照隔离，必须允许数据库服务器，这可能会在客户端引起不必要的问题。现在，我们不仅捕获死锁异常（当然还可能发生），还捕获快照并发问题，以重复后台进程中的事务（用户无法重复）。但这比以前效果要好得多。

- Stefan Steinegger

---

很抱歉，这是事实。我找不到获取可用的“SELECT FOR UPDATE”的方法。我的解决方法现在是放弃“SELECT FOR UPDATE”，改为执行简单的、非阻塞的“SELECT”，并通过更新计数器（“UPDATE WHERE id =？and updateCount =？”）检查并发的“UPDATES”。 - tangens

SQL 2008 提供了两个新的乐观隔离级别，类似于 Oracle 提供的。 - Chris Bednarski

@ChrisBednarski 我在 SQL Server 2008 R2 中使用了 WITH(ROWLOCK)，但它仍然锁定了多行!!! 你能提供一些解释吗？ - bjan

@bjan：查找快照隔离。这里有一些信息：http://msdn.microsoft.com/zh-cn/library/tcbchxcb(v=vs.80).aspx - Chris Bednarski

@ChrisBednarski 感谢您提供的链接，这两个命令使情况发生了翻天覆地的变化：ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON; ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON; 还请参考 MichaelBuen 在这个问题中的答案。 - László van den Hoek

抱歉，尽管我不喜欢SQLServer，但这是错误的。您可以通过定义与where子句完全匹配的索引来实现行级锁定。陷阱：如果where子句包含主键列，则还必须执行此操作。我发现sqlserver不关心通过应用主键进行唯一匹配（hibernate，PK+discriminator = pagelock），因此必须定义匹配的索引。 - gorefest

Answer 2

我有一个类似的问题，我想只锁定一行。据我所知，使用UPDLOCK选项时，SQLSERVER会锁定需要读取的所有行以获取该行。因此，如果您没有定义用于直接访问行的索引，则所有前面的行都将被锁定。

在您的示例中：

假设您有一个名为TBL的表格，其中包含一个id字段。您想要锁定id=10的那一行。您需要为id字段（或任何其他涉及您选择的字段）定义一个索引：

CREATE INDEX TBLINDEX ON TBL ( id )

然后，你可以使用以下查询语句只锁定你所读取的行：

SELECT * FROM TBL WITH (UPDLOCK, INDEX(TBLINDEX)) WHERE id=10.

如果你不使用INDEX(TBLINDEX)选项，SQLSERVER需要从表的开头读取所有行以找到id=10的行，因此那些行将被锁定。

- ManuelConde

---

提供根本原因的话，SQL Server 为什么会“忽略”行锁指令。我在我的应用程序中使用了这种方法，并且确实达到了 tangens 所要求的目标。 - Shmil The Cat

我有一个聚集索引，并且在提示中包含它没有任何区别。updlock+rowlock+holdlock 很好用，一旦我意识到我的存储过程中超出了锁定 SELECT 语句的其他记录（缺少原始选择中存在的 id 条件）。 - MoonStom

Answer 3

您不能同时使用快照隔离和阻塞读。快照隔离的目的是为了防止阻塞读。

- Christian Hayter

Answer 4

也许将MVCC永久化可以解决这个问题（而不是仅针对特定批处理：SET TRANSACTION ISOLATION LEVEL SNAPSHOT）：

ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

[编辑：10月14日]

阅读了这篇文章：Oracle比SQL Server更好的并发性？ 和这篇文章：http://msdn.microsoft.com/en-us/library/ms175095.aspx

当设置READ_COMMITTED_SNAPSHOT数据库选项为ON时，用于支持该选项的机制会立即激活。在设置READ_COMMITTED_SNAPSHOT选项时，只允许执行ALTER DATABASE命令的连接进入数据库。在ALTER DATABASE完成之前，数据库中不得有其他打开的连接。数据库不必处于单用户模式。

我得出结论，在给定的数据库上永久激活mssql的MVCC需要设置两个标志：

ALTER DATABASE yourDbNameHere SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

- Michael Buen

---

在阅读了被接受的答案下@Chris Bednarski的评论后，我得出了与你相同的结论，Michael。这似乎缓解了我们的死锁问题。如果您使用SQL Server Management Studio中的数据库属性对话框，将提示您断开所有客户端连接，而无需重新启动。 - László van den Hoek

Answer 5

完整的答案可能需要深入了解DBMS的内部结构。这取决于查询引擎（执行SQL优化器生成的查询计划的引擎）的操作方式。

然而，一个可能的解释（适用于至少某些版本的某些DBMS - 不一定适用于MS SQL Server）是ID列上没有索引，因此任何试图使用“WHERE id =？”的查询处理过程最终都会对表进行顺序扫描，并且该顺序扫描会触发您的进程施加的锁。如果DBMS默认应用页面级锁定，则也可能遇到问题；锁定一行将锁定整个页面和该页面上的所有行。

有一些方法可以证明这不是麻烦的根源。查看查询计划；研究索引；尝试使用ID为1000000而不是1运行SELECT语句，看看是否仍然阻止其他进程。

- Jonathan Leffler

---

所以这取决于MS SQL Server；也许它不会跳过索引上的锁。我建议在足够大的数据集上尝试“ID = <large-number>”测试，以便您使用多个页面。您可能会看到差异；也可能不是。您可以尝试以“脏读”隔离级别运行“其他进程”；这应该可以帮助您读取锁定 - 但总体而言，这不是一个很好的解决方案。 - Jonathan Leffler

所有的进程都执行相同的读取操作：它们选择“自己”的记录并在操作结束时更新状态。 - tangens

那么，每个进程的ID值都是相同的吗？还是它们每个使用不同的ID？我猜测应该是后者。问题在于DBMS是否允许足够不同的ID值绕过任何锁定。我的推测是，如果ID值足够不同，你就可以通过锁定。但如果例如ID上的索引未被使用，则不能。你已经查看了查询计划吗？ - Jonathan Leffler

很抱歉，我还无法弄清楚如何做到这一点。是的，每个进程都使用不同的ID。 - tangens

我看了查询计划，但是没有发现什么特别的。 - tangens

Answer 6

尝试使用(updlock,rowlock)。

- BlueMonkMN

---

好的，我尝试了（updlock，rowlock），但是即使访问另一行，第二个SELECT FOR UPDATE也会被阻塞。 - tangens

你为什么认为它应该阻塞？ - RBarryYoung

因为这是我要寻找的行为。两个线程读取一个记录以进行更新，更改一些值并执行更新。我希望第二个线程阻塞，直到第一个线程完成他的事务。 - tangens

这是悲观并发。如果你想要那个，为什么还要指定乐观并发呢？ - RBarryYoung

我想你在问我为什么使用"READ_COMMITTED_SNAPSHOT=ON"吗？因为否则，即使我使用'WITH (updlock)'，同一行的第二个SELECT也不会被阻止。 - tangens

Answer 7

创建一个虚假的更新以强制行锁定。

UPDATE <tablename> (ROWLOCK) SET <somecolumn> = <somecolumn> WHERE id=1

如果这都不能锁定你的行，那真不知道什么能锁定了。

在这个“UPDATE”之后，你可以进行SELECT（ROWLOCK）和随后的更新。

- Feu

---

我想知道这个方法是否比另一种方法表现更差，即只使用带有xlock提示的选择。Rowlock并不可靠，因为SQL Server实际上并没有锁定行，而是锁定了行的6字节哈希值，因此在表中有大量记录时可能会发生冲突。此外，由于rowlock是一个“提示”，它并不能保证不会发生锁升级，尽管您可以通过禁用表上的锁升级来最小化该风险。 - Triynko

Answer 8

我假设您不希望在运行此特定查询时，任何其他会话都能读取该行……

在使用WITH（XLOCK，READPAST）锁提示的同时将SELECT包装在事务中，将获得您想要的结果。只需确保那些其他并发读取未使用WITH（NOLOCK）。 READPAST允许其他会话执行相同的SELECT，但是在其他行上执行。

BEGIN TRAN
  SELECT *
  FROM <tablename> WITH (XLOCK,READPAST) 
  WHERE RowId = @SomeId

  -- Do SOMETHING

  UPDATE <tablename>
  SET <column>=@somevalue
  WHERE RowId=@SomeId
COMMIT

- ewoo

Answer 9

好的，默认情况下，单个选择将使用“读取已提交”事务隔离级别，该级别会锁定并因此停止对该集合的写入。您可以使用以下命令更改事务隔离级别：

Set Transaction Isolation Level { Read Uncommitted | Read Committed | Repeatable Read | Serializable }
Begin Tran
  Select ...
Commit Tran

这些内容在SQL Server BOL中有详细解释。

下一个问题是，默认情况下，如果您有超过~2500个锁或使用了超过锁事务中“正常”内存的40％，SQL Server 2K5将升级锁。升级会到页，然后表锁。

您可以通过设置“跟踪标志”1211t来关闭此升级。有关更多信息，请参阅BOL。

- TFD

---

非常感谢，这听起来可能是解决方案。我会在周一尝试它。 - tangens

我尝试了，但它没有起作用。当访问不同的行时，第二个SELECT仍然会被阻塞。 - tangens

Answer 10

问题 - 这个案例是否已经证明是由于锁升级导致的（即如果您使用分析器跟踪锁升级事件，这绝对是导致阻塞的原因）？如果是，那么有一个完整的解释和（相当极端的）解决方法，即在实例级别启用跟踪标志以防止锁升级。请参见http://support.microsoft.com/kb/323630跟踪标志1211。

但是，这很可能会产生意想不到的副作用。

如果您故意锁定一行并将其锁定一段时间，则在 SQL Server 中使用事务的内部锁定机制并不是最佳方法。 SQL Server 中的所有优化都是针对短事务的 - 进入，进行更新，退出。这就是首次引入锁升级的原因。

因此，如果意图是长时间“检出”一行，则最好使用带有值的列和普通的更新语句来标记行是否已锁定。

- onupdatecascade

---

虽然我同意，但我更倾向于采用乐观锁定方法来创建行版本，就像ORM /持久性层（如Hibernate）中所做的那样，请参见：https://codippa.com/optimistic-locking-in-hibernate-auto-versioning-in-hibernate/。这假定更新通过特定应用程序进行控制的环境，或者多个应用程序采用相同的方法。本文涵盖了大多数情况，包括SQL Server的特定“rowversion”数据类型：http://www.simple-talk.com/sql/t-sql-programming/developing-modifications-that-survive-concurrency/。 - Kamal