使用SQL Server选择更新 [英] SELECT FOR UPDATE with SQL Server

问题描述

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

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

现在我要使用:

SELECT * FROM <tablename> FOR UPDATE

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

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

我尝试过:

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

...但是，即使选择"1"以外的ID，这也会阻止所有其他连接.

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

哪个是执行SELECT FOR UPDATE的正确提示，如Oracle，DB2，MySql?

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

编辑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 )

许多并行进程会执行SELECT:

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

编辑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

推荐答案

最近，我有一个

Recently I had a deadlock problem because 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.

同时，我们正在使用快照隔离，它可以解决许多但不是全部问题.不幸的是，要使用快照隔离，数据库服务器必须允许它使用，这可能会在客户站点上引起不必要的问题.现在，我们不仅捕获死锁异常(当然仍然会发生)，而且还捕获快照并发问题以从后台进程重复事务(用户无法重复).但这仍然比以前更好.

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.

这篇关于使用SQL Server选择更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！