4

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.

Win
  • 61,100
  • 13
  • 102
  • 181
  • If you want to lock records while editing, consider dBase. Better check if your aproach is really necessary, it is not the normal practice. – H H Jul 07 '11 at 15:05
  • If it's not long running, you can use a TransactionScope. – Jeff Jul 07 '11 at 15:12
  • This doesn't sound like the kind of thing you want to use database/transaction locks on - ""(it sometime takes long)"". I would recommend you adding a column to the table in question - IsLocked, and updating that as required. It's likely a more complicated will be required though (what if a process locks a record and then the process is somehow terminated). To counter this, consider a second table that keeps track of your locks (Lock Id, Process Id, Expiry) and then adding the Lock Id to whichever record you want to remain untouched? – Smudge202 Jul 07 '11 at 16:03
  • @Smudge202 - I added IsLock column. I think the problem is, when threads from different applications read the same record (almost) exact same time, lock doesn't work anymore. – Win Jul 07 '11 at 16:21
  • 2
    Ah, I see now. 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](http://msdn.microsoft.com/en-us/library/ms187373.aspx)). 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 – Smudge202 Jul 07 '11 at 16:32
  • @Smudge202 - Thank you! I'll definitely use store procedure as you suggested, if I don't find any solution with Entity Famework. – Win Jul 07 '11 at 16:43
  • @Smudge: put your comment as an answer so it can be accepted and question is not unanswered. – Ladislav Mrnka Jul 12 '11 at 07:49
  • @Ladislav for some reason I didn't get the notification to winmyans response. Thanks for the prompt. – Smudge202 Jul 12 '11 at 08:05

1 Answers1

5

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

Smudge202
  • 4,689
  • 2
  • 26
  • 44