title: Attempting to lock a row in SQL with a 'Lock' column
body: <p>I'm trying to lock a row in a database, with a guid that identifies the holder of the lock, and an expiry date that specifies how long the lock is valid for (in case the process crashes, and fails to release the lock). Here's what I managed to come up with so far:</p>
  
  <pre><code>UPDATE Foo
  SET
      Lock = @Lock,
      LockExpiry = DATEADD(MILLISECOND, @Duration, GETDATE())
  WHERE Id = (
      SELECT TOP 1 Id
      FROM Foo
      WHERE (Lock IS NULL OR LockExpiry &lt; GETDATE())
      ORDER BY LockExpiry
  );
  
  SELECT TOP 1
  FROM Foo
  WHERE (Lock = @Lock AND LockExpiry &gt; GETDATE())
  </code></pre>
  
  <p>However, I've added some logging to the project, and it appears that the locks are sometimes overwritten by other processes. I have a suspicion there could be a race condition here:</p>
  
  <ul>
  <li>Thread A selects the top Foo waiting to be updated, and gets Foo #3</li>
  <li>Thread B selects the top Foo waiting to be updated, and gets Foo #3</li>
  <li>Thread B sets Foo #3's lock id to some guid 'xxxx'</li>
  <li>Thread B selects the top Foo that it holds a lock on, and returns Foo #3</li>
  <li>Thread A sets Foo #3's lock id to some guid 'yyyy'</li>
  <li>Thread A selects the top Foo that it holds a lock on, and returns Foo #3</li>
  </ul>
  
  <p>How can I tell if this is happening? And if it is possible, how can I prevent it?</p>
  
  <p><strong>Edit</strong></p>
  
  <p>Also, is there an easier way of doing this? I need to lock the row, and have the lock persist across multiple transactions, possibly even multiple connections.</p>