Locking Row in SQL 2005-2008

Locking Row in SQL 2005-2008

You can use RowLock or other hints but you should be careful..

The HOLDLOCK hint will instruct SQL Server to hold the lock until you commit the transaction. The ROWLOCK hint will lock only this record and not issue a page or table lock.

The lock will also be released if you close your connection or it times out. I'd be VERY careful doing this since it will stop any SELECT statements that hit this row dead in their tracks. SQL Server has numerous locking hints that you can use. You can see them in Books Online when you search on either HOLDLOCK or ROWLOCK.

Sql Server Management Studio: table locked due to select statement

A SELECT is run once, and the shared locks (not exclusive locks!) are released once the data has completed being read. This may coincide precisely with the last row being displayed on the client, but it may occur a few milliseconds before. The fact that the results are open on the client is not known by SQL Server (unless we are talking about "Open Table" / "Edit Top 200 Rows"). I don't think it's possible with a single SELECT statement to lock the table in the way you describe - other readers and writers should be able to operate on the table just fine, though there may be some contention (not exclusive locking) while your SELECT is running.

A way you could simulate this, however, is to do some combination of...

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM dbo.table WITH (TABLOCKX);

...and then go to lunch.

What's the best way to lock a record while it is being updated?

Adding ROWLOCK,UPDLOCK to the sub query should do it.

ALTER PROCEDURE Blah
AS
  DECLARE @targetval INT

  UPDATE table1
  SET    field9 = 1,
         @targetval = field1
  WHERE  field1 = (SELECT TOP 1 field1
                   FROM   table1 t WITH (rowlock, updlock)
                   WHERE  ( t.field2 = 'this' )
                          AND ( t.field3 = 'that' )
                          AND ( t.field4 = 'yep' )
                          AND ( t.field9 <> 1 ))

  RETURN  

Updated

The currently accepted answer to this question does not use updlock. I'm not at all convinced that this will work. As far as I can see from testing in this type of query with a sub query SQL Server will only take S locks for the sub query. Sometimes however the sub query will get optimised out so this approach might appear to work as in Query 2.

Test Script - Setup

CREATE TABLE test_table
(
id int identity(1,1) primary key,
col char(40)
)

INSERT INTO test_table
SELECT NEWID() FROM sys.objects

Query 1

update test_table
set col=NEWID()
where id=(SELECT top (1) id from test_table )

Query 2

update test_table
set col=NEWID()
where id=(SELECT max(id) from test_table)

DB locking during select, update, insert and delete

For SQL Server 2008, you can start with Locking and Row Versioning, and Customizing Locking for an Index.

You can control locking granularity using CREATE INDEX..., ALTER INDEX..., CREATE TABLE..., and ALTER TABLE.... See, for example, the syntax for CREATE INDEX, and search that page for the options ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS.

Sql 2005 Locking for OLTP - Committed or Uncommitted?

That does sound like a bit of a rash decision, however without all the details of your environment it is difficult to say.

You should advise your DBA to consider the use of SQL Server's advanced isolation features, i.e. the use of Row Versioning techniques. This was introduced to SQL Server 2005 to specifically address issues with OLTP database that experience high locking.

The following white paper contains quite complicated subject matter but it is a must read for all exceptional DBA's. It includes example of how to use each of the additional isolation levels, in different types of environments i.e. OLTP, Offloaded Reporting Environment etc.

http://msdn.microsoft.com/en-us/library/ms345124.aspx

In summary it would be both foolish and rash to modify the transaction isolation for all of your T-SQL queries without first developing a solid understanding of how the excessive locking is occuring within your environment.

I hope this helps but please let me know if you require further clarification.

Cheers!

What's the best way to lock a record while it is being updated?

Adding ROWLOCK,UPDLOCK to the sub query should do it.

ALTER PROCEDURE Blah
AS
  DECLARE @targetval INT

  UPDATE table1
  SET    field9 = 1,
         @targetval = field1
  WHERE  field1 = (SELECT TOP 1 field1
                   FROM   table1 t WITH (rowlock, updlock)
                   WHERE  ( t.field2 = 'this' )
                          AND ( t.field3 = 'that' )
                          AND ( t.field4 = 'yep' )
                          AND ( t.field9 <> 1 ))

  RETURN  

Updated

The currently accepted answer to this question does not use updlock. I'm not at all convinced that this will work. As far as I can see from testing in this type of query with a sub query SQL Server will only take S locks for the sub query. Sometimes however the sub query will get optimised out so this approach might appear to work as in Query 2.

Test Script - Setup

CREATE TABLE test_table
(
id int identity(1,1) primary key,
col char(40)
)

INSERT INTO test_table
SELECT NEWID() FROM sys.objects

Query 1

update test_table
set col=NEWID()
where id=(SELECT top (1) id from test_table )

Query 2

update test_table
set col=NEWID()
where id=(SELECT max(id) from test_table)

Peewee row level blocking

Peewee supports using the FOR UPDATE clause, and I think this is probably what you want. It won't prevent other clients from reading, but it will prevent modifications for as long as the transaction holding the lock is open.

Ex:

with db.atomic():
    # lock note.
    note = Note.select().where(Note.id == 123).for_update().get()
    # As long as lock is held no other client can modify note.

---

Related Topics