Data Locking In a Web Application? 283
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."
Hibernate In Action (even if not using Java) (Score:2, Interesting)
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.
Re:The way this is generally handled... (Score:5, Interesting)
Do as ticketmaster does... (Score:4, Interesting)
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.
Re:The way this is generally handled... (Score:3, Interesting)
This is how I do it (Score:2, Interesting)
give users an edit-lock (Score:2, Interesting)
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)
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.