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

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