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
The subquery defines an internal query that can be used inside a SELECT, INSERT, UPDATE and DELETE statement. It is a straightforward method to update the existing table data from other tables. The above query uses a SELECT statement in the SET clause of the UPDATE statement.
Simple cursor in SQL server to update rowsWhen doing a cursor update the CURRENT OF keyword is used to update the current row. If a cursor definition has a query with multiple tables then only the table used in update statement is affected. The output of above query is shown below.
The SELECT FOR UPDATE statement is used to order transactions by controlling concurrent access to one or more rows of a table. It works by locking the rows returned by a selection query, such that other transactions trying to access those rows are forced to wait for the transaction that locked the rows to finish.
in SQL 2008 a new TSQL statement "MERGE" is introduced which performs insert, update, or delete operations on a target table based on the results of a join with a source table. You can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With