views:

10897

answers:

13

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
+3  A: 

Try (updlock, rowlock)

BlueMonkMN
Yes, I tried rowlock (without combination with other hints), but then a concurrent SELECT FOR UPDATE of the same row doesn't block.
tangens
OK, I tried (updlock, rowlock), but a second SELECT FOR UPDDATE blocks even when accessing another row.
tangens
(xlock,rowlock) blocks other rows too.
tangens
(repeatableread) doesn't block second select of same row.
tangens
Why do you think that it should Block?
RBarryYoung
Because that's the behaviour I'm looking for. Two threads read a record for update, change some values and do the update. I want the second thread to block until the first finishes his transaction.
tangens
That's pessimistic concurrency. If you want that then why are you specifying optimistic concurrency?
RBarryYoung
I think you are asking why I use "READ_COMMITTED_SNAPSHOT=ON"? Because otherwise a second SELECT on the same row doesn't block even if I say 'WITH (updlock)'.
tangens
+1  A: 

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.

Jonathan Leffler
I have an index (but not an unique one) on the id column.
tangens
So it depends on MS SQL Server; maybe it won't skip over locks on the index. I suggest trying the 'ID = <large-number>' test on a big enough data set that you have multiple pages in use. You might see a difference; you might not. You might try running the 'other processes' at a 'dirty read' isolation level; this should get you past the locks for reading - but it is not a very good solution in general.
Jonathan Leffler
+1 I also think this is an index issue.
Arthur
All the processes do the same read: they select "their" record and update the status at the end of the operation.
tangens
So, the value for ID is the same for each process? Or they each use a different ID? I assume the latter. And the issue is whether the DBMS will allow sufficiently different values of ID to bypass any locking. My speculation is that if the ID values are different enough, you'll get past locks. But not if the index on ID is not used, for example. Have you looked at the query plans yet?
Jonathan Leffler
Sorry, I wasn't able to figure out how to do this yet. And yes, each process uses a different ID.
tangens
I looked at the query plan yes but I saw nothing special.
tangens
+1  A: 

Try using:

SELECT * FROM <tablename> WITH ROWLOCK XLOCK HOLDLOCK

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

RMorrisey
(rowlock,xlock,holdlock) blocks other rows too.
tangens
Is there a way to prevent it from blocking other rows? I don't have a SQL Server instance in front of me at the moment to test it with. Maybe UPDLOCK XLOCK HOLDLOCK?
RMorrisey
(updlock,xlock) are incompatible hints.
tangens
Try just UPDLOCK HOLDLOCK then?
RMorrisey
(updlock,holdlock) blocks other rows too.
tangens
Sorry then, I'm not sure =(
RMorrisey
+2  A: 

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.

Stefan Steinegger
I'm afraid this is true. I found no way to get a working "SELECT FOR UPDATE". My solution now is to give up the "SELECT FOR UPDATE" and do a simple, non blocking "SELECT" and check for concurrent "UPDATES" with an update counter ("UPDATE WHERE id=? and updateCount=?").
tangens
SQL 2008 provides two new optimistic isolation levels, similar to what Oracle offers
Chris Bednarski
+1  A: 

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

Christian Hayter
+1  A: 

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

erikkallen
Nice article, thank you. Now I do understand things a little better. But unfortunately the hint (repeatableread) doesn't block a second select of the same row.
tangens
tangens: Why do yo think that it should?
RBarryYoung
Because that's the behaviour I'm looking for.
tangens
Yes, but why do you think that the settings that you are using should have that particular behavior?
RBarryYoung
I don't think that. I'm looking for a solution that gives me the desired behaviour.
tangens
+1  A: 

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.

Michael Buen
Thanks for the link to the article. I've written a junit test doing exaclty what I need and I can reproduce the missing locks or the escalated locks with just a few statements. So I can guarantee that there is no mix of queries with and without ROWLOCK.
tangens
A: 

Have you tried READPAST?

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

Gratzy
I tried it, but then a second select doesn't find the record. That's not the behaviour I'm looking for. I want the second select to block until the first finishes his transaction.
tangens
+2  A: 

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

TFD
Thank you very much, this sounds like it could be the solution. I'll try it on Monday.
tangens
I tried it, but it didn't work. A second SELECT still blocks when accessing a different row.
tangens
A: 

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: http://stackoverflow.com/questions/1564126/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;

then restart your mssql

Michael Buen
I tried this without success.
tangens
+1  A: 

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

qwertzui
A: 

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
ewoo
A: 

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.

onupdatecascade