Forgot your password?
typodupeerror
Databases Programming Hardware

Data Locking In a Web Application? 283

Posted by timothy
from the get-offa-my-cloud dept.
An anonymous reader writes "We recently developed a multi-user application and deployed it to our users. This is a web-based application that used to be a Windows application which was written in Delphi using Paradox databases for the client database. In the Windows application, we used the ability in Paradox to lock records which would prevent users from editing the same data. However, in the web application we did not add in a locking facility for the data due to its disconnected nature (at least that's how I was shot down). Now our users are asking to have the locking back, as they are stepping on each others' edits from time to time. I have been assigned to look at best practices for web application locking of data, and figured I would post the question here to see what others have done or to get some pointers to locations for best practices on doing locking with in a web application. I have an idea of how to do this, but don't want to taint the responses so I'll leave it off for the time being."
This discussion has been archived. No new comments can be posted.

Data Locking In a Web Application?

Comments Filter:
  • by Zarf (5735) on Thursday September 24, 2009 @09:57PM (#29536285) Journal

    is a great book about this. Even if you don't use Hibernate or Java. If you hate Java just take the parts of the book dealing with Java and burn them. The rest of the book has awesome discussion of database design for the web... and those parts are worth the purchase price by themselves.

    "Hibernate in Action" covers "Optimistic Locking" which is a simple technique. Just put a versionNumber column in every table and never let anyone insert any version number less than the one in the database... http://en.wikipedia.org/wiki/Optimistic_locking [wikipedia.org] ... if you have another scheme... even if it is smarter and better than this in every conceivable way DO NOT DO IT without FIRST getting Optimistic Locking working.

  • My company has an internal app that approaches locking in a different manner. When you start updating a record, it uses an AJAX routine to set a lock on the record being updated. As long as you're still on that page, you "have the lock" and other users are notified of this if they attempt to edit the record. Once your changes are submitted, the lock is released automatically. It's possible to "steal" a lock in our model; this may not work for everyone. If you didn't want to allow this, you could incorporate a timeout for locks, whereby the original user would be notified that the lock had expired due to inactivity.
  • by Lord Byron II (671689) on Thursday September 24, 2009 @11:28PM (#29536729)

    When you are ordering tickets through TicketMaster.com, they hold the seat assignment for you for 10 minutes. If you don't complete the transaction within that time frame, the tickets become public again.

    In your database setting, the user Alice wants to edit the customer Carol's record. The application gives Alice a lock on Carol's record for five minutes. If user Bob tries to edit Carol's record within the five minute window, he gets a message telling him to wait for 3:42 while Alice finishes her edit. When Alice is finished, the lock is released and if she doesn't finish in five minutes, the lock is released anyway and her edits are lost.

    You could also add the ability for the user to set the lock time, within a reasonable window, say 5-15 minutes. Also, consider adding the ability for the user to renew the lock.

    BTW - Paradox is still around? I haven't used it since 1993 or so. Wow.

  • by spiffmastercow (1001386) on Friday September 25, 2009 @12:02AM (#29536881)
    How do you make sure the lock gets released when the page closes? I once investigated this, but determined that I would have to either a.) set a timeout on the lock and have the page update the lock every x seconds, or b.) use the page close event and hope that the user's browser doesn't close unexpectedly.
  • This is how I do it (Score:2, Interesting)

    by frambris (525874) on Friday September 25, 2009 @02:25AM (#29537107) Homepage
    I have a separate locks-table with lock_id, id of the other record and timestamp. When a user brings up the record it is read only. He have to click Edit to begin editing and thus requiring a lock. This is where the lock table is checked. If there are no locks a lock entry is inserted and its lock_id is propagated to the edit form. Upon saving one checks that it is the same lock id, saves and removes lock. If a user tries to edit a locked record one can give him the option of either wait, or override if the users credentials allow it. He then get a new lock id. The other user either get an error message when saving stating that another one override his edit or some AJAX controller periodically checks the lock and takes action if it disappears. If the user just closes the edit form, forgets about it one can expire the lock by deleting old locks.
  • by molecular (311632) on Friday September 25, 2009 @06:34AM (#29537953)

    This is how we did it in an in-house AJAX app for a big corporation.

    There would be a temp-table for every editable data-table in the db, that has the same structure plus a ID_User field. When a users starts editing a data-set, the data is copied into that temp-table. Other users trying to edit that data will get a view-only version of the page and info on who is editing the data. On "submit" the data is copied back to the "real" table, the lock thereby released.

    Should a user decide to abandon his session, crash his browser or anything like that, he will have his unsaved edits back on next login. He will even be redirected to the edit page automatically after login, if there are pending edits.

    A user can "steal" his own lock (actually session, that is) when he tries to log in with a fresh session (e.g. from another workstation at a colleague's). The old session will bail out automatically saying "session hijacked" (on any next ajax action) and the fresh session will get loaded with the old one's data.

    Other users can only steal locks after a timeout (to avoid problems with people going on vacation with locked data on them)

    ----

    This eactually worked pretty well from a user's perspective, because as opposed to with optimistic locking there would never be a situation where a user would have done some edits in vain or have extra work rechecking it.

    From a developer's perspective it was a pain in the ass (maintain 2 almost identical tables, maintain checkout/checkin-code everytime).

    From support's point of view, it wasn't too bad. Seldomly people would call and demand release of a lock because someone just went to have coffee and he had to edit the data real quick or managment would kill him on the spot because the data would be frozen in like 7 minutes to generate reports.

  • Re:Same as bugzilla? (Score:3, Interesting)

    by TooMuchToDo (882796) on Friday September 25, 2009 @09:39AM (#29539475)

    The way that other people are suggesting, honestly, would just annoy me. I'd be *really* pissed if I opened a document, spent half an hour working on it, then committed my changes only to find out that somebody else had been spending time working on it as well. That adds up to a lot of wasted time. If, however, I were given an indication that somebody else was editing it, I could work on a different record without wasting my time.

    I think Google Docs shows an excellent way of handling this. It uses AJAX to tell everyone on that page, spreadsheet, etc. who else is viewing/collaborating on it, and changes are reflected in real time. You don't have to go so far as to reflect changes in real-time, but just seeing who else is working on the doc would be helpful so you could IM them to collaborate and prevent collisions.

"Don't worry about people stealing your ideas. If your ideas are any good, you'll have to ram them down people's throats." -- Howard Aiken

Working...