1

First I'd like to describe the mechanism of a locking solution I'd like to implement. Basically an item can be opened in read or write mode. However if an user opens the item in write mode, no other user should be able to open it in edit mode. The item means a case in a customer service application.

In order to to this I came up with the following: The table will contain a flag which indicates if an item is checked out for edit, and an 'end time', while this flag is valid. The default value for it is 3 minutes, if no user interaction happens during this time, the flag can be ignored next time when an user tries to open the same item.

On the UI side, I use jQuery to monitor if an user is active. If he or she is, a periodic AJAX call extends his or her time frame so he or she can continue working on the item. When the user saves the item, the flag will be removed. The end time is necessary to handle situations when the browser crashes or when the user goes to drink a coffee and leaves the item open for an hour.

So, the question. :) If an user opens the item in edit mode first I have to read the flag & time values for the time item, and if I find these valid (flag is not set, or set but not valid because of the time) and I have to update them with new values. What kind of transaction level should I use for this in EF, if any? Or should I write stored procedures to handle the select & update in a transaction? If so, what kind of locking method should I use?

Improve this question
0

3 Answers 3

Reset to default
4

You are describing pessimistic locking, there is really no debate on that. There are detailed instructions on what you want to do in the excellent MVC/EF tutorial http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application There’s a chapter early on about pessimistic.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I see. But in this case 'stuck' locks should be cleaned by a mechanism after a time, shouldn't they? Also, is there any problem with the solution I described, beside that locking should not be done on application level? I see my solution as a more elastic one compared to DB locks (although I don't have that much knowledge in DB locks; I guess the behaviour I described could also be done by using them too).
+1, I'm throwing my weight behind Rick's expertise. It didn't seem right to do pessimistic locking from the app. Besides, the solution I posted could fail if you had more than 1 static MyClassThatManagesConcurrency (in a different class loader, on a different machine).
I've just read this article and this it summarizes pretty well what I'd like to achieve: ibm.com/developerworks/websphere/techjournal/0603_ilechko/… (pessimistic session locking section). Regarding to my question - correct me if I'm wrong - it can be solved by handling race conditions with one of the approaches, suggested in Ladislav's answer.
Ladislav also described the two possible ways to handle concurrency: optimistic and optimistic. The solution with the timestamp is the optimistic locking: you don't lock the row...but you are able to verify if someone else updated it while you was reading it. The second solution...is your lock. if possible, in a web environment optmistic concurrency is the best solution because you avoid "blocking a row" while waiting for an update...that might never arrive...because the user might have left the session.
Sorry, I mistyped my previous post. I meant: "Ladislav also described the two possible ways to handle concurrency: optimistic and pessimistic"...hope..it was understandable anyway..
3

Optimistic locking is still OK in this case. You can use timestamp / rowversion and your flag together. The flag will be used to handle your application logic - only single user can edit the record and the timestamp will be used to avoid race condition when setting the flag because only single thread will be able to read the record and write it back. If any other thread tries to read the record concurrently and saves it after the first thread it will get concurrency exception.

If you don't want to use timestamp different transaction isolation level will not help you because isolation level doesn't force queries to lock records. You must manually write SQL query and use UPDLOCK hint to lock the record by querying and after that execute update. You can do this in stored procedure.

Improve this answer

Comments

1

The answer below is not a good way to implement pessimistic concurrency. You should not implement this at the application level. The RDBMS have better tools for this.

If you are locking a row in the db, this is by definition pessimistic.

Since you are controlling the pessimistic concurrency at the application level, I don't think it matters which transaction scope EF uses. EF will automatically start a db-level transaction when you SaveChanges.

To prevent multiple threads from executing the lock / unlock from your app, you can lock the section of code that queries & updates like so:

public static object _lock = new object(); public class MyClassThatManagesConcurrency { public void MyMethodThatManagesConcurrency() { lock(_lock) { // query for the data // determine if item should be unlocked // dbContext.SaveChanges(); } } }

With the above, no 2 threads will ever execute code inside the lock section at the same time. However, I am not sure why this is necessary. If all you are doing is reading the object and unlocking it when time has expired, and 2 threads enter the method at the same time, either way, the item will become unlocked.

On the other hand, if your db row for this object has a timestamp column (not a datetime column but a columng for versioning rows), and 2 threads enter the method at the same time, the second will receive a concurrency exception. But unless you have are versioning rows at the db level, I don't think you need to do any locking.

Reply to comment

Ok I get it now, you are right. But you are still locking at the application level, which means it should not matter which db transaction ef chooses. To prevent 2 users from unlocking the same object, use the C# lock block I posted above.

Improve this answer

2 Comments

Timestamp would be okay if I were using optimistic locking as far as I know. In this case I could use the built-in functionality of EF to handle the concurrent updates. However I'd like to prevent the situation when two person would start to work on the same case. (because in this case both of them would figure out a solution, start writing a reply (from the app) and after the work they'd get boo, someone else already solved this.) Regarding the concurrency control name, I've quoted it from the EF4 in Action book. :) (the halfway solution: pessimistic/optimistic concurrency control)
You are describing pessimistic locking, there is really no debate on that. You shouldn't implement locking yourself, let the DBMS do that.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.