mysql with updlock,SELECT FOR UPDATE with SQL Server

可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):

问题:

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 FOR UPDATE

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

I tried: SELECT * FROM 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

回答1:

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.

回答2:

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.

回答3:

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

回答4:

Try (updlock, rowlock)

回答5:

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.

回答6:

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] 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;

回答7:

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

回答8:

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 WITH (XLOCK,READPAST) WHERE RowId = @SomeId -- Do SOMETHING UPDATE SET =@somevalue WHERE RowId=@SomeId COMMIT

回答9:

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

回答10:

Create a fake update to enforce the rowlock. UPDATE (ROWLOCK) SET = 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.

回答11:

Try using: SELECT * FROM WITH ROWLOCK XLOCK HOLDLOCK

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

回答12:

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

回答13:

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.

回答14:

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

回答15:

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.

回答16:

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

回答17:

Have you tried READPAST?

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

回答18:

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