We have a web page that is used to edit a table (Details) and for some people editing a row can take a while. To avoid concurrent update problems, I write a row in another table (Locks) whenever someone opens a Details row for updating (containing Details
id, who has it locked, start date/time, and end date/time)
If someone tries to open a Details row for edit that is already locked, the page refuses them access and tells them who has it locked.
I unlock (enter an end date/time in the Locks row) whenever the person editing the Details row clicks Update or Cancel, or clicks on any other web page in the menu (I do that in the 'On Load' event of all other pages).
The problem is, sometimes people just "X-out" of the web page so their lock record remains open. (I've already implemented a function to allow others to choose to "steal" the lock, but that's not very elegant.)
I'm already using a "windows.onbeforeunload" Javascript event to catch people who try to leave without saving the record after making changes, but I can't come up with a good way to trigger codebehind to clear the lock...
Some simple solution is the last win approach. Users can edit concurrently the same entity without any lock, the last save wins.
You can also check during the save that other user already modified the entity
since it retrived for editing, in this case you can ask the user to commit or revert to the latest etc.
So there is no lock, and the user can leave the page, it doesn't matter.
DaveFromNH
I'm already using a "windows.onbeforeunload" Javascript event to catch people who try to leave
This is not safe, what happens when the browser crash etc.
Thank you for your response, but in this application it is not ok to let someone work for a while then tell them their changes are lost (because someone else changed it in the interim.)
Also, they are working in controls that are not all causing postbacks so they could put in quite a bit of time filling in details before I could get codebehind control to do the check you suggest. (Right now I only let them into the page if they have been
successful in getting a lock set - I need to keep others out until they're done, and I also need to catch them and release the lock if they leave abruptly...)
The problem is, sometimes people just "X-out" of the web page so their lock record remains open. (I've already implemented a function to allow others to choose to "steal" the lock, but that's not very elegant.)
The problem is you can not employ strictly a client side solution to handle database concurrency which is the real task at hand here. You Javascript attempts are reasonable to add but can't be counted on as you inidicated because of the client's
ability to manipulate the browser in ways we can not capture (i.e. 'x-out' of the application).
You are better off using ADO.NET transactions around your record edits, adds, updates, etc. and then handle exceptions accordingly in the UI to inform the user of a concurrent record update violation. This is a standard and best practice way of dealing with
this need.
Have a look to the following example using ASP.NET. I do not recommend placing any transaction code directly in the code behind the page or in a controller (if using MVC), but rather relegated down to your DAL or similar layer.
If you had to rollback a transaction due to concurrency violation, you could throw an exception back up to the UI, and let the user know their record could not be saved.
So you get similar suggestion like mine from atconway.
Making a single user exlusive lock is always dangerous, even with desktop applications (crashes, power failure etc. this seems trivial but will happen :( )
I think educated users can be quiclky familiar with the commit, revise, commit approach, and will like it better than locks/exclusive checkouts.
If you still want locks, your existing implementation is good, and cannot be really improved.
(One improvement could be(?) a sliding expiration of the lock. The user gets 10 minutes to finish the edit, this time can be extended when there is user activity in the page. Takes some effort to implement, but...)
DaveFromNH
Member
43 Points
60 Posts
Avoiding concurrent updates
Dec 29, 2012 12:57 PM|LINK
We have a web page that is used to edit a table (Details) and for some people editing a row can take a while. To avoid concurrent update problems, I write a row in another table (Locks) whenever someone opens a Details row for updating (containing Details id, who has it locked, start date/time, and end date/time)
If someone tries to open a Details row for edit that is already locked, the page refuses them access and tells them who has it locked.
I unlock (enter an end date/time in the Locks row) whenever the person editing the Details row clicks Update or Cancel, or clicks on any other web page in the menu (I do that in the 'On Load' event of all other pages).
The problem is, sometimes people just "X-out" of the web page so their lock record remains open. (I've already implemented a function to allow others to choose to "steal" the lock, but that's not very elegant.)
I'm already using a "windows.onbeforeunload" Javascript event to catch people who try to leave without saving the record after making changes, but I can't come up with a good way to trigger codebehind to clear the lock...
Suggestions?
stmarti
Contributor
4963 Points
1036 Posts
Re: Avoiding concurrent updates
Dec 29, 2012 06:26 PM|LINK
Some simple solution is the last win approach. Users can edit concurrently the same entity without any lock, the last save wins.
You can also check during the save that other user already modified the entity since it retrived for editing, in this case you can ask the user to commit or revert to the latest etc.
So there is no lock, and the user can leave the page, it doesn't matter.
This is not safe, what happens when the browser crash etc.
DaveFromNH
Member
43 Points
60 Posts
Re: Avoiding concurrent updates
Dec 31, 2012 04:42 PM|LINK
Thank you for your response, but in this application it is not ok to let someone work for a while then tell them their changes are lost (because someone else changed it in the interim.)
Also, they are working in controls that are not all causing postbacks so they could put in quite a bit of time filling in details before I could get codebehind control to do the check you suggest. (Right now I only let them into the page if they have been successful in getting a lock set - I need to keep others out until they're done, and I also need to catch them and release the lock if they leave abruptly...)
atconway
All-Star
16846 Points
2756 Posts
Re: Avoiding concurrent updates
Jan 09, 2013 01:12 PM|LINK
The problem is you can not employ strictly a client side solution to handle database concurrency which is the real task at hand here. You Javascript attempts are reasonable to add but can't be counted on as you inidicated because of the client's ability to manipulate the browser in ways we can not capture (i.e. 'x-out' of the application).
You are better off using ADO.NET transactions around your record edits, adds, updates, etc. and then handle exceptions accordingly in the UI to inform the user of a concurrent record update violation. This is a standard and best practice way of dealing with this need.
Have a look to the following example using ASP.NET. I do not recommend placing any transaction code directly in the code behind the page or in a controller (if using MVC), but rather relegated down to your DAL or similar layer.
Making SQL transaction in DB using ASP.NET 2.0 and C#:
http://www.aspnettutorials.com/tutorials/database/sql-transaction-csharp.aspx
Here is the MSDN documentation in using transactions:
Local Transactions (ADO.NET):
http://msdn.microsoft.com/en-us/library/2k2hy99x(v=vs.100).aspx
If you had to rollback a transaction due to concurrency violation, you could throw an exception back up to the UI, and let the user know their record could not be saved.
Hope this helps!
stmarti
Contributor
4963 Points
1036 Posts
Re: Avoiding concurrent updates
Jan 09, 2013 01:43 PM|LINK
So you get similar suggestion like mine from atconway.
Making a single user exlusive lock is always dangerous, even with desktop applications (crashes, power failure etc. this seems trivial but will happen :( )
I think educated users can be quiclky familiar with the commit, revise, commit approach, and will like it better than locks/exclusive checkouts.
If you still want locks, your existing implementation is good, and cannot be really improved.
(One improvement could be(?) a sliding expiration of the lock. The user gets 10 minutes to finish the edit, this time can be extended when there is user activity in the page. Takes some effort to implement, but...)