What's the best way to lock a record while it is being updated?
If I need to 开发者_如何学运维SELECT
a value from a table column (happens to be the primary key column) based on a relatively complex WHERE
clause in the stored procedure, and I then want to update that record without any other concurrent stored procedures SELECT
ing the same record, is it as simple as just using a transaction? Or do I also need to up the isolation to Repeatable Read?
It looks like this:
Alter Procedure Blah
As
Declare @targetval int
update table1 set field9 = 1, @targetval = field1 where field1 = (
SELECT TOP 1 field1
FROM table1 t
WHERE
(t.field2 = 'this') AND (t.field3 = 'that') AND (t.field4 = 'yep') AND (t.field9 <> 1))
return
I then get my targetval in my program so that I can do work on it, and meanwhile I don't have to worry about other worker threads grabbing the same targetval.
I'm talking SQL 2000, SQL 2005, and SQL 2008 here.
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)
精彩评论