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

问题描述

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

现在我想使用:

SELECT * FROM <表名>更新

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

我试过:

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

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

对于 Oracle、DB2、MySql 而言，执行 SELECT FOR UPDATE 的正确提示是什么?

编辑 2009-10-03:

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

CREATE TABLE 示例(Id BIGINT NOT NULL，TransactionId BIGINT，终端 BIGINT，状态 SMALLINT );ALTER TABLE 示例 ADD CONSTRAINT index108 PRIMARY KEY ( Id )CREATE INDEX I108_FkTerminal ON 示例(终端)CREATE INDEX I108_Key ON 示例( TransactionId )

很多并行进程都这样做SELECT:

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

编辑 2009-10-05:

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

<前>机制|在不同的行块上选择 |在同一行块上选择-----------------------+------------------------------+------------------------------罗洛克 |没有|不updlock, rowlock |是 |是的xlock,rowlock |是 |是的可重复阅读 |没有|不DBCC TRACEON (1211,-1) |是 |是的rowlock,xlock,holdlock |是 |是的updlock,holdlock |是 |是的上锁，读过去 |没有|不我在找|没有|是的

解决方案

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

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

I'm using a Microsoft SQL Server 2005 database with isolation level READ_COMMITTED and 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".

I tried:

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

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

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

EDIT 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 * FROM example o WITH (updlock) WHERE o.TransactionId = ?

EDIT 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.

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.

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