SELECT FOR UPDATE with SQL Server

user_170974 0
Viewed 199514

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
read-committed-snapshot sql-server-2005 tsql sql-server sql
18 Answers

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.

user_12853400

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
user_2189080

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.

user_1399380

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

Constantin_203100
Related