locked
Avoiding concurrent updates RRS feed

  • Question

  • User1283837345 posted

    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?

    Saturday, December 29, 2012 8:57 AM

Answers

  • User-952121411 posted

    DaveFromNH

    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.

    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! Smile

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 9, 2013 9:12 AM

All replies

  • User1070236303 posted

    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.

    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.

    Saturday, December 29, 2012 2:26 PM
  • User1283837345 posted

    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...)

     

     

    Monday, December 31, 2012 12:42 PM
  • User-952121411 posted

    DaveFromNH

    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.

    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! Smile

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 9, 2013 9:12 AM
  • User1070236303 posted

    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...)

    Wednesday, January 9, 2013 9:43 AM