Managing concurrency when using SELECT-UPDATE pattern Let's say you have the following code (please ignore that it's awful):

BEGIN TRAN;
DECLARE @id int
SELECT @id = id + 1 FROM TableA;
UPDATE TableA SET id = @id; --TableA must have only one row, apparently!
COMMIT TRAN;
-- @id is returned to the client or used somewhere else


To my eye, this is NOT managing concurrency properly. Just because you have a transaction doesn't mean someone else won't read the same value that you did before you get to your update statement.

Now, leaving the code as is (I realize this is better handled as a single statement or even better using an autoincrement/identity column) what are sure ways to make it handle concurrency properly and prevent race conditions that allow two clients to get the same id value?

I'm pretty sure that adding a WITH (UPDLOCK, HOLDLOCK) to the SELECT will do the trick. The SERIALIZABLE transaction isolation level would seems to work as well since it denies anyone else to read what you did until the tran is over (UPDATE: this is false. See Martin's answer). Is that true? Will they both work equally well? Is one preferred over the other?

Imagine doing something more legitimate than an ID update--some calculation based on a read that you need to update. There could be many tables involved, some of which you'll write to and others you won't. What is the best practice here?

Having written this question, I think the lock hints are better because then you are only locking the tables you need, but I'd appreciate anyone's input.

P.S. And no, I don't know the best answer and really do want to get a better understanding! :)
Just addressing the SERIALIZABLE isolation level aspect. Yes this will work but with deadlock risk.

Two transactions will both be able to read the row concurrently. They will not block each other as they will either take an object S lock or index RangeS-S locks dependant on table structure and these locks are compatible. But they will block each other when attempting to acquire the locks needed for the update (object IX lock or index RangeS-U respectively) which will lead to deadlock.

The use of an explicit UPDLOCK hint instead will serialize the reads thus avoiding the deadlock risk.