title: sql transactionscope row level locking
body: <p>Question on locking scope in SQL Server (SQL Azure to be precise). </p>
  
  <p>Scenario </p>
  
  <p>A bunch of records are selected using a select statements.
  We loop through the records 
  Each record is updated within a transactionscope - 
  (each record is independent of the other and there is no need for a table lock) </p>
  
  <p>Am I right in assuming that the above would  result in a row level lock of just that particular record row?</p>
  
  <p>Framing the question within the context of a concrete example. 
  In the below example would each item in itemsToMove be locked one at a time?</p>
  
  <pre><code>var itemsToMove  = ObjectContext.Where(emp =&gt; emp.ExpirationDate &lt; DateTime.Now)                        
  foreach(Item expiredItem in itemsToMove)
  {               
      bool tSuccess = false;
      using (TransactionScope transaction = new TransactionScope())
      {
          try
          {               
              //We push this to another table. In this case Azure Storage.                    
              bool bSuccess = PushToBackup();             
              if(bSuccess)
              {
                  ObjectContext.DeleteObject(expiredItem);
              }
              else 
              {
                  //throw an exception or return 
                  return false;
              }
              ObjectContext.SaveChanges();
  
              transaction.Complete();
              tSuccess = true;
          }
          catch (Exception e)
          {
              return cResults;
          }       
      }
  }
  if (tSuccess)
  {
      ObjectContext.AcceptAllChanges();
  }
  </code></pre>