title: Manual semaphore in SQL Server
body: <p>I am trying to put a time-constrained semaphore into a sql database. The lock shall provide mutual exclusion on write access to a data table, but concerning only a certain FK value (email), which is why I can't use table locks or similar. And since I don't trust the sql connection to hold or the lock to be released in all cases (of course we try, but better safe than sorry), the lock shall time out after 3 minutes if it is not renewed or released in between.</p>
  
  <p>Up to now, I have the following SQL statement:</p>
  
  <pre><code>UPDATE users SET lockTime = GETDATE() OUTPUT DELETED.LockTime WHERE email = @mail
  </code></pre>
  
  <p>The problem: If the user is in the table, this will always update the lockTime and return the old lockTime. So, if an orphaned lock is in effect and the query is run in 2-minute intervals, the lock will never be released. (For testing all the logic around it, this is okay, but not for production.)</p>
  
  <p>The lockTime should only be updated if it is older than, say, 3 minutes. So I tried:</p>
  
  <pre><code>UPDATE users SET lockTime = GETDATE() OUTPUT DELETED.LockTime 
  WHERE email = @mail AND DATEADD(lockTime,'3 minutes')&lt;GETDATE()
  </code></pre>
  
  <p>But this can't even be put into testing code, since I can't distinguish the cases "email is not in DB" and "lock is younger than 3 minutes".</p>
  
  <p>So, where am I thinking wrong?</p>