title: Avoiding duplicates on concurrent reads off the same database table
body: <p>Avoiding duplicates on concurrent reads off the same database table </p>
  
  <p>We have a table that contains a list of tasks </p>
  
  <pre><code>Table RecordsTable  
      RecordID
      RecordName
      ...
      ...
      IsProcessed
  </code></pre>
  
  <p>Multiple worker machines read off the table and once the task is processed mark IsProcessed as true.</p>
  
  <p>So if we want the following code to work without duplicates </p>
  
  <p>Pseudocode in C#</p>
  
  <pre><code>//get first 10 records that are not processed based on some other conditions
  var recordSet = objectontext.recordstable.Where(...).Where(c =&gt; c.IsProcessed == false).Take(10);
  //loop through the recordset in a transaction 
  foreach(record singleRecord in recordSet)
  {
      bool result = ProcessRecord();
      //Mark isProcessed as true 
      if(result)
          singleRecord.IsProcessed = true;
      objectContext.Savechanges();
  }
  </code></pre>
  
  <p>We want to avoid duplicate processing of records (since the ProcessRecords() contain mailers and such). <strong>If we wrap the entire code above in 
  a transaction</strong> does it mean that two calls from two different workers would result in non-duplicate records?</p>
  
  <p>If workerA first issues the call to the table it gets,</p>
  
  <pre><code>var recordSetWorkerA = objectontext.recordstable.Where(somecondition...).Where(c =&gt; c.IsProcessed == false).Take(10);
  </code></pre>
  
  <p>If workerB issues a call after worker A already is in the transaction would the following statment fail to execute because trying to read locked rows
   or move to the next 10 records?</p>
  
  <pre><code>var recordSetWorkerB = objectontext.recordstable.Where(somecondition...).Where(c =&gt; c.IsProcessed == false).Take(10);
  </code></pre>
  
  <p>Is there any pattern we should be looking at. </p>