CODEWITHSUNDEEP
sqlsql-servermultithreadingsql-server-2008
Omu
Omu

Reputation: 71288

Thread safe sql transaction, how to lock a specific row during a transaction?

I have a procedure like this:

create procedure Checkout
@Foo nvarchar(20),
@cost float
as
begin transaction

declare @Bar nvarchar(20);
select @Bar = Bar from oFoo where Foo = @Foo;

update Foo set gold = gold - @cost where name = @Foo;
update Bar set gold = gold + @cost where name = @Bar;

delete from oFoo where @Foo = Foo;

commit transaction

I need to lock the row with Foo = @Foo from oFoo table during this transaction so that nobody could read/edit/delete it, anybody knows how to do that ?

I am using Microsoft SQL Server 2008

Upvotes: 8

Views: 15934

Answers (3)

martinr
martinr

Reputation: 3812

See: http://www.mssqlcity.com/Articles/Adm/SQL70Locks.htm

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

Note particularly on the MSDN page:

READ COMMITTED

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

Upvotes: 1

liggett78
liggett78

Reputation: 11358

If you want nobody to update/delete the row, I would go with the UPDLOCK on the SELECT statement. This is an indication that you will update the same row shortly, e.g.

select @Bar = Bar from oFoo WITH (UPDLOCK) where Foo = @Foo;

Now if you want the situation where nobody should be able to read the value as well, I'd use the ROWLOCK (+HOLDLOCK XLOCK to make it exclusive and to hold until the end of the transaction).

You can do TABLOCK(X), but this will lock the whole table for exclusive access by that one transaction. Even if somebody comes along and wants to execute your procedure on a different row (e.g. with another @Foo value) they will be locked out until the previous transaction completed.

Note: you can simulate different scenarios with this script:

CREATE TABLE ##temp (a int, b int)
INSERT INTO ##temp VALUES (0, 0)

client #1

BEGIN TRAN
SELECT * FROM ##temp WITH (HOLDLOCK XLOCK ROWLOCK) WHERE a = 0
waitfor delay '0:00:05'
update ##temp set a = 1 where a = 0
select * from ##temp
commit tran

client #2:

begin tran
select * from ##temp where a = 0 or a = 1
commit tran

Upvotes: 11

kevchadders
kevchadders

Reputation: 8335

(Based on SQL Server)

I think when it comes to Table hints you need to experiment (TABLOCK, TABLOCKX), and see which fits best for you. Also be aware that the query optimizer may ignore the hints. Table-level hints will be ignored if the table is not chosen by the query optimizer and used in the subsequent query plan.

Another useful article on the subject, though a little old as its based around SQL Server 2000 is SQL Server 2000 Table Locking Hints

Upvotes: 1

Related Questions