Pessimistic locking in EF code first

Question

I'd like to lock specified row(s) in my table exclusively, so no reads no updates allowed until the actual transaction completes. To do this, I've created a helper class in my database repository:

public void PessimisticMyEntityHandler(Action<IEnumerable<MyEntity>> fieldUpdater, string sql, params object[] parameters)
{
    using (var scope = new System.Transactions.TransactionScope())
    {
        fieldUpdater(DbContext.Set<MyEntity>().SqlQuery(sql, parameters));
        scope.Complete();
    }
}

Here is my test code. Basicly I'm just starting two tasks and both of them tries to lock the row with the Id '1'. My guess was that the second task won't be able to read(and update) the row until the first one finishes its job, but the output window shows that it actually can.

Task.Factory.StartNew(() =>
{
      var dbRepo = new DatabaseRepository();
      dbRepo.PessimisticMyEntityHandler(myEntities =>
      {
                Debug.WriteLine("entered into lock1");

                /* Modify some properties considering the current ones... */
                var myEntity = myEntities.First();
                Thread.Sleep(1500);
                myEntity.MyEntityCode = "abcdefgh";
                dbRepo.Update<MyEntity>(myEntity);

                Debug.WriteLine("leaving lock1");
     }, "SELECT * FROM MyEntities WITH (UPDLOCK, HOLDLOCK) WHERE Id = @param1", new SqlParameter("param1", 1));
});

Task.Factory.StartNew(() =>
{
      Thread.Sleep(500);
      var dbRepo = new DatabaseRepository();
      dbRepo.PessimisticMyEntityHandler(myEntities =>
      {
                Debug.WriteLine("entered into lock2");

                /* Modify some properties considering the current ones... */
                var myEntity = myEntities.First();
                myEntity.MyEntityCode = "xyz";
                dbRepo.Update<MyEntity>(myEntity);

                Debug.WriteLine("leaving lock2");
     }, "SELECT * FROM MyEntities WITH (UPDLOCK, HOLDLOCK) WHERE Id = @param1", new SqlParameter("param1", 1));
});

Output window:

entered into lock1
entered into lock2
leaving lock2
leaving lock1

Answer 1

What you are asking for, involves two main phenomena in DBMS and particularly in SQL Server: Lock and Isolation Level. I do my best to explain them in summery.

You asked about Pessimistic Concurrency. The answer is: it is not supported yet in Entity Framework. In other words, by conventional API of EF you cannot lock a table or some rows for SELECT like what for example Oracle does via SELECT FOR UPDATE. Though you can achieve this by writing a native SQL command to select some rows or the entire table with an Exclusive lock and maintain this lock until the end of the transaction. This way other threads not only cannot update the selected rows, but also cannot select them. They get blocked until you release the lock. This is what I do in my projects and though somehow risky, it works fine.

So In summery: Lock for select: NO by EF / Yes by native SQL

Lock for Update:

When you modify rows in DB, the modified rows gain some sort of lock. The kind of lock is determined by the Isolation Level of the running Transaction. The default of Isolation Level in SQL Server is Read Committed which means that all the rows that are modified in current transaction gain Shared lock. This lock is compatible with SELECT but not with UPDATE or DELETE. It means that when you modify a row in your transaction, by default it is guaranteed that no other parallel threads can infer and change them until you end the transaction either by COMMIT or ROLLBACK.

• To understand the locks in SQL Server see: http://technet.microsoft.com/en-us/library/aa213039(v=sql.80).aspx
• To understand transaction isolation level see: http://technet.microsoft.com/en-us/library/ms189122(v=sql.105).aspx

.

Update:

Table hints UPDLOCK and HOLDLOCK may be ignored by query optimizer or other DBMS modules since they are just hint :-). The only combination of table hints that is surely enforced is (XLOCK, PAGLOCK).

Example: SELECT * FROM MyTable WITH (XLOCK, PAGLOCK)

As I said, manual locking is risky. Use it with maximum level of consideration.