SQL Server 2005 and SELECT and UPDATE locked

Question

I want to perform a update then select the result. I don't want anything to be able to update the row I am updating until after the select has occurred. How would I do this?

My goal is to increment a value of a row and return that incremented value. I have thus far found that I end up with an issue where update (to increment) followed by a select in a situation where two queries happen at near the same time the selects seem to return the same number. So I am guessing that something like update > update > select > select is happening.

I miss labeled this as SQL Server 2005. I am actually working with Server 2000. So the output clause does not work (is not in that version).

BEGIN TRANSACTION
UPDATE Table SET Last=(Last+1) WHERE ID=someid;
SELECT * FROM Table WHERE ID=someid;
COMMIT TRANSACTION

Answer 1

BEGIN TRAN
UPDATE ...
SELECT...
COMMIT 

Should do it even at the default transaction isolation level of read committed.

You could also use the OUTPUT clause to get the row directly back after the update. Example of this

UPDATE <YourTable>
   SET ...
OUTPUT INSERTED.*
 WHERE ...