title: SQL server table locking and unlocking issue
body: <p>I have a very interesting problem.</p>
  
  <p>I have a table where I have data, like this:
  DVDSerialNumbers(ID, Create_Date, SerialNumber, CategoryID)</p>
  
  <p>My application generates serial numbers for DVD's and I have a SQL command like this (issued by my application):</p>
  
  <pre><code>ExecuteQuery("select top {0} SerialNumber from DVDSerialNumbers where CategoryID = {1};" &amp; 
  "delete from DVDSerialNumbers where ID in (select top{0} ID from DVDSerialNumber where " &amp; 
   "CategoryID = {1});", n, CategoryID)
  </code></pre>
  
  <p>ExecuteQuery returns the result of my select query but the delete command is executed too.</p>
  
  <p>Basically I get no more than n SerialNumbers which have the given CategoryID and I delete their rows.</p>
  
  <p>However, here I have a concurency problem.
  If the code above runs twice in the same time, it's possible that the results will be the same, however, the idea is to get a given SerialNumber from the table only once.</p>
  
  <p>How can I make instance B to wait for instance A to finish this command? Should I lock the table? Or should I lock some rows? Or is there a better solution?</p>
  
  <p>Thanks in advance for your help.</p>