7

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?

ren
  • 3,843
  • 9
  • 50
  • 95
  • Can you supply more details of your specific case? This is a very general question and there may already be good patterns for what you are trying to do. – Martin Smith Sep 15 '11 at 12:34
  • well, same procedure is called ~simultaneously and each must obtain unique counter (i.e. it reads counter and increments it). And counter must be incremented gradually (no random values) – ren Sep 15 '11 at 14:43
  • 1
    You can increment a counter in a single update statement you don't need a separate read. `UPDATE Foo SET counter = counter + @increment` but maybe I haven't understood what you are trying to do. – Martin Smith Sep 15 '11 at 14:46
  • I need to know the old value first. But this gives an idea of this trick: `select oldvalue update set value = oldvalue+1 where value = oldvalue` if nothing was updated try whole thing again. – ren Sep 15 '11 at 15:14
  • 4
    For that you can use `SET @variable = value, value = value+1` which sets the variable to the pre-update value of the column (as documented in BOL for the `UPDATE` statement) – Martin Smith Sep 15 '11 at 15:21

1 Answers1

6

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845