sql transactionscope row level locking

Question

Question on locking scope in SQL Server (SQL Azure to be precise).

Scenario

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)

Am I right in assuming that the above would result in a row level lock of just that particular record row?

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?

var itemsToMove  = ObjectContext.Where(emp => emp.ExpirationDate < 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();
}

Answer 1

Provided that there isn't any outer / wrapper transaction calling your code, each call to transaction.Complete() should commit and release any locks.

Just a couple of quick caveats

• SQL will not necessarily default to row level locking - it may use page level or higher locks (recommend that you leave SQL to its own devices, however)
• Note that the default isolation level of a new TransactionScope() is read serializable. This might be too pessimistic for your scenario.