使用 SQLServer 进行 SELECTFORUPDATE

我正在使用一个2005年的 Microsoft SQL Server 数据库，其隔离级别为 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
201632 次浏览
小开

Try (updlock, rowlock)

小开

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.

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.

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.

小开

Try using:

SELECT * FROM <tablename> WITH ROWLOCK XLOCK HOLDLOCK

This should make the lock exclusive and hold it for the duration of the transaction.

小开
最佳答案

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.

小开

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

小开

According to this article, the solution is to use the WITH(REPEATABLEREAD) hint.

小开

Revisit all your queries, maybe you have some query that select without ROWLOCK/FOR UPDATE hint from the same table you have SELECT FOR UPDATE.


MSSQL often escalates those row locks to page-level locks (even table-level locks, if you don't have index on field you are querying), see this explanation. Since you ask for FOR UPDATE, i could assume that you need transacion-level(e.g. financial, inventory, etc) robustness. So the advice on that site is not applicable to your problem. It's just an insight why MSSQL escalates locks.


If you are already using MSSQL 2005(and up), they are MVCC-based, i think you should have no problem with row-level lock using ROWLOCK/UPDLOCK hint. But if you are already using MSSQL 2005 and up, try to check some of your queries which query the same table you want to FOR UPDATE if they escalate locks by checking the fields on their WHERE clause if they have index.


P.S.
I'm using PostgreSQL, it also uses MVCC have FOR UPDATE, i don't encounter same problem. Lock escalations is what MVCC solves, so i would be surprised if MSSQL 2005 still escalate locks on table with WHERE clauses that doesn't have index on its fields. If that(lock escalation) is still the case for MSSQL 2005, try to check the fields on WHERE clauses if they have index.

Disclaimer: my last use of MSSQL is version 2000 only.

小开

Have you tried READPAST?

I've used UPDLOCK and READPAST together when treating a table like a queue.

小开

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

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

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

小开

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

ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

[EDIT: October 14]

After reading this: Better concurrency in Oracle than SQL Server? and this: 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.

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

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

You have to deal with the exception at commit time and repeat the transaction.

小开

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.

BEGIN TRAN
SELECT *
FROM <tablename> WITH (XLOCK,READPAST)
WHERE RowId = @SomeId


-- Do SOMETHING


UPDATE <tablename>
SET <column>=@somevalue
WHERE RowId=@SomeId
COMMIT
小开

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/323630 trace flag 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.

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.

小开

Application locks are one way to roll your own locking with custom granularity while avoiding "helpful" lock escalation. See sp_getapplock.

小开

I solved the rowlock problem in a completely different way. I realized that sql server was not able to manage such a lock in a satisfying way. I choosed to solve this from a programatically point of view by the use of a mutex... waitForLock... releaseLock...

小开

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.

小开

How about trying to do a simple update on this row first (without really changing any data)? After that you can proceed with the row like in was selected for update.

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

Edit: you should wrap it in a transaction of course

Edit 2: another solution is to use SERIALIZABLE isolation level

小开

I have a similar problem, I want to lock only 1 row. As far as I know, with UPDLOCK option, 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:

Asume that you have a table named TBL with an id field. 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):

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.


提交答案