title: How to deal with two transactions updating the same row in SQL Server
body: <p>I have the following situation that I wish to deal with:</p>
  
  <p>A table of values:</p>
  
  <pre><code>id int
  val varchar(20)
  used bit
  flag int
  </code></pre>
  
  <p>I want to find the first row WHERE used = 0 AND flag IS NULL and stick something in 'flag'.  Once that is done any other user will not be able to use that row (because flag is not null)</p>
  
  <p>That's simple enough to do of course:</p>
  
  <pre><code>UPDATE top (1) mytable
  SET flag = someUniqueValue
  WHERE used = 0
    AND flag IS NULL
  </code></pre>
  
  <p>What I want to know is what happens if two users are running the same UPDATE at the same time.  Obviously one will get there first.</p>
  
  <p>I don't know how to go about testing this scenario, so can't find out myself.</p>
  
  <p>Does the second user over-ride the first? (straight away or after the lock is released?)</p>
  
  <p>Does the second user get locked out and get an error? (If so how do I go about detecting the error?)</p>
  
  <p>Any help would be appreciated.</p>