Atom SQL transaction

Question

so i have a stored procedure (sql server 2008 r2) something like this

BEGIN TRAN BEGIN TRY //critical section select value update value //end of critical section COMMIT END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK END CATCH 

I want the two stored procedures not to read the same value. In other words, reading and updating should be atomic. Does this code do this? If not, how to do it?

Answer 1

Yes, they are atomic, but that does not mean that you get the behavior you want here! The property you need to pay attention to is isolation.

To achieve the required exception, you need to make the SELECT operation on a single value mutually exclusive . You can do this by requesting an Update lock (make sure that the WHERE predicate can be found using the index to avoid blocking unnecessary extra rows)

 SELECT * FROM foo WITH(ROWLOCK,UPDLOCK) WHERE bar='baz' 

Please note that this lock will be held until your transaction is completed, but released at the end of the critical section, but this will always be the case if you update the value anyway.