title: is sql server transaction atomic
body: <p>so I have a stored procedure (sql server 2008 r2) something like this</p>
  
  <pre><code>BEGIN TRAN
  BEGIN TRY
  
  
     //critical section
      select value        
      update value
         //end of critical section
  
  
      COMMIT
  END TRY
  BEGIN CATCH
      IF @@TRANCOUNT &gt; 0
          ROLLBACK
  END CATCH
  </code></pre>
  
  <p>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?</p>