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

Alireza · Accepted Answer · 2013-10-10T19:24:47.807

10

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.

edited Oct 10 '13 at 19:24

answered Oct 10 '13 at 18:55

Alireza

10,237
6
43
59

Thanks for the response, but as you can see from my code, I already know that(clearly not everything). I just don't understand why my code isn't working. – laszlokiss88 Oct 10 '13 at 19:08
Could you please provide some example code to show how you do it in your projects? – laszlokiss88 Oct 10 '13 at 19:14
@laszlokiss88 I think in the first task, you should put the line `Thread.Sleep(1500);` right after `var myEntity = myEntities.First();`. You want to prolong this task don't you? – Alireza Oct 10 '13 at 20:01
@laszlokiss88 The code with `(XLOCK, PAGLOCK)` does lock the table. To check you can pick the first task and put a breakpoint right after the line `var myEntity = myEntities.First();` and meanwhile try to select the first row in Management Studio and you'll see the query gets blocked. This way you'll ensure about correctness of the code. But writing an application to correctly report the status of the concurrent tasks is another story. – Alireza Oct 10 '13 at 20:09
Ohh, my bad, the test was wrong. Now, I've created an action that takes the sleep time and it is working with the `XLOCK, ROWLOCK`. If I call this action with sleep time 8 secs and right after that with 1 s, the second waits the first one to complete(because of the row locking). Thanks for your help. – laszlokiss88 Oct 10 '13 at 20:17
2

Minor point: locking hits are never ignored by SQL Server. That's required to correctly craft some locking patterns. – usr Oct 11 '13 at 11:19
1

@usr Tables hints may be ignored. Take a look at Remark section of http://technet.microsoft.com/en-us/library/ms187373.aspx – Alireza Oct 11 '13 at 12:41
It says that under very specific conditions. In general, these "hints" are a guarantee. – usr Oct 11 '13 at 14:10
I think the XLOCK might be ignored, not the UPDLOCK ? – Yiping Nov 27 '16 at 07:36
1

So, any news? How to implement pessimistic locking in EF without using NativeSQL (TSQL)? How to achieve `UPDLOCK` on Select for further `UPDATE`? Or do you have any other ideas for locking on Select for Update? – Piotr Kwiatek May 19 '17 at 07:54

Pessimistic locking in EF code first

1 Answers1

Linked