Take the 2-minute tour ×  Let's say you have the following code
(please ignore that it's awful):  DECLARE @id int SELECT @id =
id + 1 FROM TableA; UPDATE TableA SET id = @id; --TableA must
have only one row, apparently! -- @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 answe