title: How to lock a row in a table before updating it
body: <p>To implement multi threading in SQL Server 2012 for one my update tasks, I need to have different threads select a row from a table (Accounts) and mark that row as processed using an update in a stored procedure. </p>
  
  <p>Something like this:</p>
  
  <pre><code>create procedure ChooseNextAccountToProcess (@Account_ID Int Output)
  
      select top 1 @Account_ID = Account_ID 
      from Accounts 
      order by LastProcessDate Desc
  
      update Accounts 
      set LastProcessDate = getdate() 
      where Account_ID = @Account_ID 
  go
  </code></pre>
  
  <p>The problem with this approach is that two threads might call this stored procedure exactly at the same time and process the same account. My goal is to select an account from accounts table and exclusively lock it before update has chance to update it.</p>
  
  <p>I tried <code>SELECT .... WITH (UPDLOCK)</code> and <code>WITH Exclusive lock</code> but none of these can actually put exclusive lock on the row when I select that row.</p>
  
  <p>Any suggestion?</p>