none
Pessimistic concurrency in LINQ to Entity? RRS feed

  • Question

  •  

    Hi guys,

     

    How can I use updlock with LINQ to Entity?  For example I would like to select a row and lock it till I complete the transaction.  See example below.

     

    E.g.

     

    Transaction_1

    begin tran

     

    select * from table1 with (udplock) where ID = 1

    ..

    ..

    ..

     

    commit

     

    Transaction_2

    begin tran

     

    select * from table1 with (udplock) where ID = 1

     

    commit

     

    Transaction_1 will run first follow by Transaction_2. With select updlock statement in Transaction_1, Transaction_2 select statement have to wait till Transaction_1 complete it's transaction.  How can I achieve this type of locking in LINQ to Entity?

     

    Thanks in advance.

    Friday, July 18, 2008 4:02 AM

Answers

  • Jason,

    I see your sample does a dummy update on Employees in order to acquire a lock on the row on that particular table. I guess you choose not to use the RepeatableReads isolation level I suggested because you did not want to lock rows on all tables, but just on Employees.

    I think it is an interesting hack. It costs you an extra roundtrip to the database, but I don’t think you will have problems as long as you do this always upfront (when you still don’t have real changes to save) and the business logic methods operate on the same transaction.

    If you want to do something more generic (i.e. create a method that locks any entity), you may want to take a look at the ObjectStateEntry.SetModified() method.

    I am curious, what is the reason having this pessimistic locking is a requirement for you?

    Thanks,
    Diego

    Monday, July 28, 2008 4:12 PM
    Moderator

All replies

  •  

    EF doesn't support pessimistic concurrency so you can't place a lock on a database.

     

    Depending on what your reason for the nolock is, you might want to look at using IsolationLevels on the queries. The ReadCommitted option will prevent the read for accessing any data that is in the process of being updated on the database but not yet committed.  This may be very different than what your goal is, however.

     

     

     

    Friday, July 18, 2008 1:19 PM
  • As Julie said, Entity Framework queries do not give you granular control of locks, but you can achieve the same effects with transaction of the appropriate isolation level.

    This is a short piece of code that exercises transaction with isolation level of REPETEABLE READS:

    Code Snippet

    using (var scope =

        new TransactionScope(TransactionScopeOption.Required,

        new TransactionOptions() { IsolationLevel = IsolationLevel.RepeatableRead }))

    {

        using (var context = new NorthwindEntities())

        {

            // uncomment next line to prevent distributed transaction promotion

            // on SQL Server previous to 2008

            // contex.Connection.Open();

            var firstCategory = context.Categories.First();

            var secondCategory =

                context.Categories

                .Where(c => c.CategoryID != firstCategory.CategoryID)

                .First();

            var product =

                context.Products

                .Where(p => p.Categories.CategoryID == firstCategory.CategoryID)

                .First();

            product.Categories = secondCategory;

            context.SaveChanges();

            scope.Complete();

        }

    }

     

    Hope this helps,
    Diego

    Sunday, July 20, 2008 1:41 AM
    Moderator
  • Hi guys,

     

    I have do some tested and notice I manage to perform pessimistic locking here.  But I'm not sure if this is the correct way or if I have introduce another problem or what so ever.  What do you guys think?

     

     

    private static void LockEmployeeRecord()

    {

    using (TransactionScope scope = new TransactionScope())

    {

    using (EmployeeEntities Emp = new EmployeeEntities ())

    {

    var query = (from e in Emp.EmployeeTable

    where e.ID == 1

    select e).First();

     

    query.FirstName = query.FirstName; //perform a dummy update

    Emp.SaveChanges(); //.SaveChanges() will then lock this row of record until it exit LockEmployeeRecord function

    }

     

    FunctionONE();  //perform any business logics

    FunctionTWO(); //perform any business logics

    FunctionTHREE(); //perform any business logics

     

    scope.Complete();

    }

    }

    Monday, July 28, 2008 3:08 PM
  • Jason,

    I see your sample does a dummy update on Employees in order to acquire a lock on the row on that particular table. I guess you choose not to use the RepeatableReads isolation level I suggested because you did not want to lock rows on all tables, but just on Employees.

    I think it is an interesting hack. It costs you an extra roundtrip to the database, but I don’t think you will have problems as long as you do this always upfront (when you still don’t have real changes to save) and the business logic methods operate on the same transaction.

    If you want to do something more generic (i.e. create a method that locks any entity), you may want to take a look at the ObjectStateEntry.SetModified() method.

    I am curious, what is the reason having this pessimistic locking is a requirement for you?

    Thanks,
    Diego

    Monday, July 28, 2008 4:12 PM
    Moderator
  • Hi Diego,

     

    I will look at the suggested method later.

     

    Yes, using pessimistic in certain cases is part of my requirement.  This is because we do have some tables which keep count on quantity or serial number generation.  These tables are access by hundreds of station at one time.  So to ensure those transaction are getting the latest last quantity or last used serial number, I want to put a lock on that particular row of record.  At least this is how we do it in SP level.

     

    Thanks Diego.

     

     

    Cheers,

    Jason

     

    Tuesday, July 29, 2008 1:01 AM
  • Hi jason

    I have a pessimistic concurrency problem. Let me explain you what a need.

    When a user try to fetch a record in order to modify, he has to check if this record is not locked before by another user. So we don't want to fetch the record if it is locked.
    It is not a good idea to leave more users working on the same entity and decide a the end who will win. This is the requirement.
    You mention in brackets that you have a method that locks any entity. It would be great if you can send me this method or give me any hint.
    Thank you

    Francois Blochaou
    Thursday, May 14, 2009 7:57 AM