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
The WHERE clause is used to restrict the number of rows returned from a SELECT query.
The SQL LIMIT clause constrains the number of rows returned by a SELECT statement. For Microsoft databases like SQL Server or MSAccess, you can use the SELECT TOP statement to limit your results, which is Microsoft's proprietary equivalent to the SELECT LIMIT statement.
SELECT statements get shared locks on the rows that satisfy the WHERE clause (but do not prevent inserts into this range). UPDATEs and DELETEs get exclusive locks on a range of rows.
A transaction acquires a table lock when a table is modified in the following DML statements: INSERT , UPDATE , DELETE , SELECT with the FOR UPDATE clause, and LOCK TABLE .
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
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