is sql server transaction atomic

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 no two stored procedures read the same value. In other words read and update should be atomic. This code does this? If not how do I do it?

Answer 1

Yes they are atomic but that does not mean that you will get the behaviour that you want here! The property you need to look at is isolation.

To achieve the exclusion that you require you would need to make the SELECT operation on the single value mutually exclusive. You can do this by requesting an Update lock (make sure the WHERE predicate can be found through an index to avoid locking unnecessary extra rows)

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

Note this lock will be held until your transaction commits however not released at the end of the critical section but that is always going to be the case if you have updated the value anyway.