我正在使用具有隔离级别的 Microsoft SQL Server 2005 数据库READ_COMMITTED
and READ_COMMITTED_SNAPSHOT=ON
.
现在我想使用:
SELECT * FROM <tablename> FOR UPDATE
...以便其他数据库连接在尝试访问同一行“FOR UPDATE”时阻塞。
我试过:
SELECT * FROM <tablename> WITH (updlock) WHERE id=1
...但这会阻止所有其他连接,即使选择“1”以外的 id 也是如此。
执行以下操作的正确提示是SELECT FOR UPDATE
众所周知的 Oracle、DB2、MySql?
编辑 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