使用 SQL Server 选择更新

SELECT FOR UPDATE with SQL Server

提问by tangens

I'm using a Microsoft SQL Server 2005 database with isolation level READ_COMMITTEDand READ_COMMITTED_SNAPSHOT=ON.

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

Now I want to use:

现在我想使用：

SELECT * FROM <tablename> FOR UPDATE

...so that other database connections block when trying to access the same row "FOR UPDATE".

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

I tried:

我试过：

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

...but this blocks all other connections even for selecting an id other than "1".

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

Which is the correct hint to do a SELECT FOR UPDATEas known for Oracle, DB2, MySql?

哪个是SELECT FOR UPDATE对 Oracle、DB2、MySql执行已知操作的正确提示？

EDIT 2009-10-03:

编辑 2009-10-03：

These are the statements to create the table and the index:

这些是创建表和索引的语句：

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 )

A lot of parallel processes do this SELECT:

许多并行进程这样做SELECT：

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

EDIT 2009-10-05:

编辑 2009-10-05：

For a better overview I've written down all tried solutions in the following table:

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

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

采纳答案by Stefan Steinegger

Recently I had a deadlock problembecause Sql Server locks more then necessary (page). You can't really do anything against it. Now we are catching deadlock exceptions... and I wish I had Oracle instead.

最近我遇到了一个死锁问题，因为 Sql Server 锁比必要的多（页面）。你真的不能做任何反对它的事情。现在我们正在捕获死锁异常......我希望我有 Oracle 代替。

Edit: We are using snapshot isolation meanwhile, which solves many, but not all of the problems. Unfortunately, to be able to use snapshot isolation it must be allowed by the database server, which may cause unnecessary problems at customers site. Now we are not only catching deadlock exceptions (which still can occur, of course) but also snapshot concurrency problems to repeat transactions from background processes (which cannot be repeated by the user). But this still performs much better than before.

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

回答by ManuelConde

I have a similar problem, I want to lock only 1 row. As far as I know, with UPDLOCKoption, SQLSERVER locks all the rows that it needs to read in order to get the row. So, if you don't define a index to direct access to the row, all the preceded rows will be locked. In your example:

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

Asume that you have a table named TBL with an idfield. You want to lock the row with id=10. You need to define a index for the field id (or any other fields that are involved in you select):

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

CREATE INDEX TBLINDEX ON TBL ( id )

And then, your query to lock ONLY the rows that you read is:

然后，您只锁定您读取的行的查询是：

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

If you don't use the INDEX(TBLINDEX) option, SQLSERVER needs to read all rows from the beginning of the table to find your row with id=10, so those rows will be locked.

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

回答by Christian Hayter

You cannot have snapshot isolation and blocking reads at the same time. The purpose of snapshot isolation is to preventblocking reads.

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

回答by BlueMonkMN

Try (updlock, rowlock)

尝试（updlock，rowlock）

回答by Jonathan Leffler

The full answer could delve into the internals of the DBMS. It depends on how the query engine (which executes the query plan generated by the SQL optimizer) operates.

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

However, one possible explanation (applicable to at least some versions of some DBMS - not necessarily to MS SQL Server) is that there is no index on the ID column, so any process trying to work a query with 'WHERE id = ?' in it ends up doing a sequential scan of the table, and that sequential scan hits the lock which your process applied. You can also run into problems if the DBMS applies page-level locking by default; locking one row locks the entire page and all the rows on that page.

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

There are some ways you could debunk this as the source of trouble. Look at the query plan; study the indexes; try your SELECT with ID of 1000000 instead of 1 and see whether other processes are still blocked.

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

回答by Michael Buen

perhaps making mvcc permanent could solve it (as opposed to specific batch only: SET TRANSACTION ISOLATION LEVEL SNAPSHOT):

也许使 mvcc 永久可以解决它（与仅特定批次相反：SET TRANSACTION ISOLATION LEVEL SNAPSHOT）：

ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

[EDIT: October 14]

[编辑：10 月 14 日]

After reading this: Better concurrency in Oracle than SQL Server?and this: http://msdn.microsoft.com/en-us/library/ms175095.aspx

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

When the READ_COMMITTED_SNAPSHOT database option is set ON, the mechanisms used to support the option are activated immediately. When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. There must be no other open connection in the database until ALTER DATABASE is complete. The database does not have to be in single-user mode.

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

i've come to conclusion that you need to set two flags in order to activate mssql's MVCC permanently on a given database:

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

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

回答by Feu

Create a fake update to enforce the rowlock.

创建虚假更新以强制执行行锁。

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

If that's not locking your row, god knows what will.

如果那没有锁定您的行，天知道会发生什么。

After this "UPDATE" you can do your SELECT (ROWLOCK)and subsequent updates.

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

回答by TFD

OK, a single select wil by default use "Read Committed" transaction isolation which locks and therefore stops writes to that set. You can change the transaction isolation level with

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

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

These are explained in detail in SQL Server BOL

这些在 SQL Server BOL 中有详细说明

Your next problem is that by default SQL Server 2K5 will escalate the locks if you have more than ~2500 locks or use more than 40% of 'normal' memory in the lock transaction. The escalation goes to page, then table lock

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

You can switch this escalation off by setting "trace flag" 1211t, see BOL for more information

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

回答by ewoo

I'm assuming you don't want any other session to be able to read the row while this specific query is running...

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

Wrapping your SELECT in a transaction while using WITH (XLOCK,READPAST) locking hint will get the results you want. Just make sure those other concurrent reads are NOT using WITH (NOLOCK). READPAST allows other sessions to perform the same SELECT but on other rows.

在使用 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

回答by onupdatecascade

Question - is this case proven to be the result of lock escalation (i.e. if you trace with profiler for lock escalation events, is that definitely what is happening to cause the blocking)? If so, there is a full explanation and a (rather extreme) workaround by enabling a trace flag at the instance level to prevent lock escalation. See http://support.microsoft.com/kb/323630trace flag 1211

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

But, that will likely have unintended side effects.

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

If you are deliberately locking a row and keeping it locked for an extended period, then using the internal locking mechanism for transactions isn't the best method (in SQL Server at least). All the optimization in SQL Server is geared toward short transactions - get in, make an update, get out. That's the reason for lock escalation in the first place.

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

So if the intent is to "check out" a row for a prolonged period, instead of transactional locking it's best to use a column with values and a plain ol' update statement to flag the rows as locked or not.

因此，如果目的是长时间“签出”一行，最好使用带有值的列和普通的 ol' update 语句来标记行是否已锁定，而不是事务锁定。