Entity Framework - Read Lock on Record

问题:

I'm calling same database from different applications using Entity Framework. However, when one application is reading/updating a record, I do not want other applications to read that data.

I tried with the following sample code; however, they are still able to read the record.

Any suggestion OR different approaches will be highly appreciated!

using (Entities context = new Entities(ConnectionString))
{
    DBTable entity;

    do
    {
        entity = context.DBTable
            .Where(item => item.IsLock == false)
            .FirstOrDefault();

        if (entity != null)
        {
            // Map entity to class
            ......


            // Lock the record; it will be unlocked later by a calling method.
            entity.IsLock = true;
            context.SaveChanges();

            count++;
        }

    } while (entity != null && count < 100);
}

Edited: Basically, I read a record and do something (it sometimes take long). Then update the record with success/fail flag (if fail other can do that again). I do not want other applications do the successful task multiple times.

回答1:

Moved from comment to an answer:

If you don't mind fixing this with SQL, create a stored procedure. In the stored procedure do a SELECT on the record WITH UPDLOCK (see here for information on table hints). After the SELECT, update the record to IsLock. This prevents any other process from reading the data whilst you are checking/setting the IsLock field.

Hope that helps