title: how to prevent deadlocks occurring in this SQL
body: <p>Firstly, I don't need to 100% prevent the deadlocks, but anything I can do to reduce them would be nice.</p>
  
  <p>I have two tables <code>Source</code> and <code>Dest</code>. Source has a load of unique values in them, I need to request a new value from <code>Source</code> and in doing so, move it into <code>Dest</code>.</p>
  
  <p>I have the following sql:</p>
  
  <pre><code>begin tran
      declare @value
      select top 1 @value = [value] from [source]
      delete from [Source] where [value]=@value
      insert into [Dest] ([Value]) values (@value)
      select @value
  commit tran
  </code></pre>
  
  <p>this occasionally throws deadlocks when multiple users get the same <code>value</code> row. How can I prevent/reduce this?</p>
  
  <p>Im using SQL Server 2008</p>
  
  <p>As an aside, there are other columns in <code>Source</code> and <code>Dest</code> that I am reading from/writing to. This is a simplification for brevity.</p>
  
  <p>Thanks</p>