SELECT FOR UPDATE with SQL Server

Question

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  lt tablename gt  FOR UPDATE      so that other database connections block when trying to access the same row  FOR UPDATE    I tried    SELECT   FROM  lt tablename gt  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

User · Answer

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 · Answer

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

User · Answer

Try  updlock  rowlock

User · Answer

Create a fake update to enforce the rowlock   UPDATE  lt tablename gt   ROWLOCK  SET  lt somecolumn gt     lt somecolumn gt  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

User · Answer

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

User · Answer

According to this article  the solution is to use the WITH REPEATABLEREAD  hint

User · Answer

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

User · Answer

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

User · Answer

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  lt tablename gt  WITH  XLOCK READPAST     WHERE RowId    SomeId    -- Do SOMETHING    UPDATE  lt tablename gt    SET  lt column gt   somevalue   WHERE RowId  SomeId COMMIT

User · Answer

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 · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try using   SELECT   FROM  lt tablename gt  WITH ROWLOCK XLOCK HOLDLOCK   This should make the lock exclusive and hold it for the duration of the transaction

User · Answer

Have you tried READPAST   I ve used UPDLOCK and READPAST together when treating a table like a queue

User · Answer

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

User · Answer

Application locks are one way to roll your own locking with custom granularity while avoiding  helpful  lock escalation  See sp getapplock

User · Answer

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

[sql] SELECT FOR UPDATE with SQL Server

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql

Examples related to read-committed-snapshot