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 SELECTing 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)