Entity Framework - Read Lock on Record

Question

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.

If you want to lock records while editing, consider dBase. Better check if your aproach is really necessary, it is not the normal practice. — Henk Holterman
– Henk Holterman, Commented Jul 7, 2011 at 15:05
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
– Smudge202, Commented Jul 7, 2011 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
– Win, Commented Jul 7, 2011 at 16:21
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). 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
– Smudge202, Commented Jul 7, 2011 at 16:32

Smudge202 · Accepted Answer · 2011-07-12 08:04:23Z

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

Collectives™ on Stack Overflow

Entity Framework - Read Lock on Record

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related