使用 SQL Server 选择进行更新

Question

我使用的 Microsoft SQL Server 2005 数据库的隔离级别为 READ_COMMITTED 和 READ_COMMITTED_SNAPSHOT=ON。

现在我想使用：

SELECT * FROM <tablename> FOR UPDATE

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

我尝试过：

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

...但这会阻止所有其他连接，即使选择“1”以外的 id 也是如此。

对于 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- 05：

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

mechanism              | SELECT on different row blocks | SELECT on same row blocks
-----------------------+--------------------------------+--------------------------
ROWLOCK                | no                             | no
updlock, rowlock       | yes                            | yes
xlock,rowlock          | yes                            | yes
repeatableread         | no                             | no
DBCC TRACEON (1211,-1) | yes                            | yes
rowlock,xlock,holdlock | yes                            | yes
updlock,holdlock       | yes                            | yes
UPDLOCK,READPAST       | no                             | no

I'm looking for        | no                             | yes

Answer 1

最近我遇到了 死锁问题，因为 Sql Server 锁定的次数超过必要（页）。你真的无法做任何事情来反对它。现在我们正在捕获死锁异常...我希望我有 Oracle。

编辑：
同时我们正在使用快照隔离，它解决了许多但不是全部问题。不幸的是，为了能够使用快照隔离，必须得到数据库服务器的允许，这可能会在客户站点造成不必要的问题。现在，我们不仅捕获死锁异常（当然，这仍然可能发生），而且还捕获快照并发问题，以从后台进程重复事务（用户无法重复）。但这仍然比以前表现得好得多。

Answer 2

我有类似的问题，我只想锁定 1 行。
据我所知，使用 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 的行，因此这些行将被锁定。

Answer 3

您不能同时进行快照隔离和阻止读取。快照隔离的目的是防止阻塞读取。

Answer 4

完整的答案可以深入研究 DBMS 的内部结构。这取决于查询引擎（执行 SQL 优化器生成的查询计划）如何运行。

然而，一种可能的解释（至少适用于某些 DBMS 的某些版本 - 不一定适用于 MS SQL Server）是 ID 列上没有索引，因此任何尝试使用 'WHERE id = 进行查询的进程?' 最终会对表进行顺序扫描，并且该顺序扫描会命中您的进程应用的锁。如果 DBMS 默认应用页级锁定，您也可能会遇到问题；锁定一行会锁定整个页以及该页上的所有行。

有一些方法可以揭穿这是麻烦的根源。查看查询计划；研究指标；尝试使用 ID 为 1000000 而不是 1 的 SELECT 来查看其他进程是否仍然被阻止。

Answer 5

也许使 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;

Answer 6

尝试（updlock、rowlock）

Answer 7

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

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 了解更多信息

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

Answer 9

创建一个假更新来强制执行行锁。

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

如果这不能锁定你的行，天知道什么会锁定你的行。

在此“UPDATE”之后，您可以执行SELECT (ROWLOCK) 和后续更新。

Answer 10

问题 - 这种情况是否被证明是锁升级的结果（即，如果您使用探查器跟踪锁升级事件，这肯定是导致阻塞的原因）？如果是这样，有一个完整的解释和一个（相当极端的）解决方法，通过在实例级别启用跟踪标志来防止锁升级。请参阅 http://support.microsoft.com/kb/323630 跟踪标志 1211

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

如果您故意锁定一行并长时间保持锁定，那么使用事务的内部锁定机制并不是最好的方法（至少在 SQL Server 中）。 SQL Server 中的所有优化都是针对短事务的 - 进入、更新、退出。这就是锁升级的首要原因。

因此，如果目的是长时间“签出”一行，最好使用包含值的列和简单的更新语句来将行标记为锁定或未锁定，而不是事务锁定。

Answer 11

应用程序锁是一种以自定义粒度滚动您自己的锁定的方法，同时避免“有用的”锁升级。请参阅 sp_getapplock。

Answer 12

尝试使用：

SELECT * FROM <tablename> WITH ROWLOCK XLOCK HOLDLOCK

这应该使锁具有独占性，并在事务持续时间内保持它。

Answer 13

根据这篇文章，解决方案是使用WITH(REPEATABLEREAD) 提示。

Answer 14

重新访问所有查询，也许您有一些查询在没有 ROWLOCK/FOR UPDATE 提示的情况下从您具有 SELECT FOR UPDATE 的同一个表中进行选择。

MSSQL 经常将这些行锁升级为页级锁（甚至是表级锁，如果您正在查询的字段没有索引），请参阅此 说明。既然您要求更新，我可以假设您需要交易级别（例如财务、库存等）的稳健性。因此该网站上的建议不适用于您的问题。这只是 MSSQL 升级锁的一个见解。

如果您已经在使用 MSSQL 2005（及更高版本），它们是基于 MVCC 的，我认为使用 ROWLOCK/UPDLOCK 提示的行级锁定应该没有问题。但是，如果您已经在使用 MSSQL 2005 及更高版本，请尝试检查一些查询，这些查询查询您想要 FOR UPDATE 的同一个表，如果它们通过检查 WHERE 子句上的字段（如果有索引）来升级锁定。

附注
我正在使用 PostgreSQL，它也使用 MVCC 有 FOR UPDATE，我没有遇到同样的问题。锁升级是 MVCC 解决的问题，因此如果 MSSQL 2005 仍然使用 WHERE 子句（字段上没有索引）升级表上的锁，我会感到惊讶。如果 MSSQL 2005 仍然存在这种情况（锁升级），请尝试检查 WHERE 子句中的字段是否有索引。

免责声明：我最后一次使用的 MSSQL 仅是 2000 版本。

Answer 15

您必须在提交时处理异常并重复事务。

Answer 16

我以完全不同的方式解决了行锁问题。我意识到 sql server 无法以令人满意的方式管理这样的锁。我选择从编程的角度通过使用互斥锁来解决这个问题... waitForLock ... releaseLock ...

Answer 17

你试过READPAST吗？

当将表视为队列时，我将 UPDLOCK 和 READPAST 一起使用。

Answer 18

首先尝试对此行进行简单的更新（而不真正更改任何数据）怎么样？之后，您可以继续处理该行，就像选择更新一样。

UPDATE dbo.Customer SET FieldForLock = FieldForLock WHERE CustomerID = @CustomerID
/* do whatever you want */

编辑：当然，您应该将其包装在事务中

编辑2：另一种解决方案是使用 SERIALIZABLE 隔离级别