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."
Same as bugzilla? (Score:5, Informative)
Same as bugzilla does. Just use a timestamp or counter on the records so you can tell when an edit occurred while you were editing
Then you can review the edit.
If you want, you can use XHR (maybe with a slow load response for performance depending on the number of users) to notify that an edit happened.
Re:The euphemism treadmill (Score:1, Informative)
I think you have that backwards.
http://www.merriam-webster.com/dictionary/taint
This book: (Score:3, Informative)
The way this is generally handled... (Score:4, Informative)
You make sure that edits are handled in a form on a web page with a submit button. The user gets to fiddle all the bits they want on the web page, then they hit the submit button. At that point the web app goes and locks the stuff it needs to do to update the database to reflect the user's changes. It then applies those changes, then commits them, thereby releasing all the locks.
If two users might potentially be editing the same records, keep an SHA-256 hash of the original data around as a hidden form field. Then when the update proceeds, check the data to make sure the SHA-256 hash matches the data you fetched when you generated the form page (helpfully put into a hidden form field). If the hash doesn't match, tell the person who did the submit that some fields may have changed and somehow present them with what those changes might be.
Re:Same as bugzilla? (Score:4, Informative)
Exactly, this is how I have done it in every web application I have developed. If someone updates the data while someone else is editing then they will get a message saying someone did an edit. They then get a chance to review the new data and modify their edit if needed.
NOTE: It is critical that the user not lose their edit. Save that data even if you don't actually do the update. There is nothing more annoying than spending 15 minutes carefully putting in a bunch of data just to have it lost due to someone else editing the same record. Let the user review what happened and then modify (or not) their own data they were putting in.
Re:The way this is generally handled... (Score:2, Informative)
Storing the hash of the original data client side is bad from a security perspective. A malicious user could manipulate the hash as they sought fit. I'd keep the hash in a server side session specific variable. I realize the damage that could be done seems small, but I wouldn't trust *anything* - especially a critical part of your locking mechanism - to a variable that could be manipulated client side.
Optimistic concurrency (Score:5, Informative)
Slashdot is hardly the right venue to get a good answer to this question (how the hell did it end up in the Hardware category?), but I've dealt with this a zillion times, so I'll give a pointer to what is very likely the correct answer: optimistic locking [wikipedia.org].
Hard locks are probably not what you want in a stateless web app. (E.g. What happens if someone locks a record and then is hit by a bus?) Instead, here's how it works:
This is also known in the vernacular as "second save loses". It may sound too harsh, but it is much better than "first save loses and user isn't notified", which is what you get if you have no currency checking at all. And it's also much more web friendly that your old desktop app (which uses an approach that is technically called "pessimistic locking").
Re:Same as bugzilla? (Score:3, Informative)
On top of this you could actually send AJAX requests while editing to see if someone is requesting the data. Carefully, considering performance.
An other option is to check-out, check-in with a session. In this case of course you need to make sure if a checked-out file's session is still alive.
Re:Same as bugzilla? (Score:4, Informative)
For the record this is called: http://en.wikipedia.org/wiki/Optimistic_locking [wikipedia.org]
Re:Optimistic concurrency (Score:4, Informative)
What shimmer says is exactly what you should do with 2 possible additions. Often people leave themselves in a web page for an hour and then start to make edits. So when the user makes the first edit, use ajax to see if there was already an edit done in the meantime so they know before they make lots of changes.
Also you should consider using sequences instead of checking if the data changed. Both are good ideas in certain situations. For example with a table that is only edited once every few months, I use a sequence on the whole table. For a table that is changed 100 times per day by 3 different users, either do row based sequences or check to see if the 'from' part of the changes match the database.
Confluence (Score:5, Informative)
Look at Confluence by Atlassian. When you edit a page they track the edit action. When another user goes to edit the page they are warned that "John Doe is currently editing this page, last edit at date/time". They also do polling via AJAX so if you're working on a page and another user starts actually editing it you see a message on the page "Jane Doe started editing this page". They also save page drafts scoped to the user to help people resolve edit conflicts. It seems to balance things well with not explicitly forcing locks but actively letting users know when they are heading for a conflict.
CouchDB (Score:4, Informative)
Use Optimistic Locking (Score:5, Informative)
Instead do optimistic locking... Assume there are no conflicting edits (or that they are at least rare). Then version each row (with a monotonically increasing number for example). At the beginning of the transaction also retrieve the version, and upon save verify that the version did not change - if it has changed there was a conflicting edit in the meanwhile and the current save should be prevented (you could then get fancy and retrieve the current version of the row from the database and show it to the user, etc).
One can actually show that if the rate of collisions is low optimistic locking even performs better, whereas in scenarios where the contention is high (a significant fraction of transaction result in a conflict) pessimistic database locks performs better.
Uh, lots of frameworks do this for free... (Score:1, Informative)
Wow guys. Getting Optimistic locking for free is basically *why* you do stuff in frameworks like Spring+Hibernate, Ruby on Rails, or .Net you know.
Re:Optimistic concurrency (Score:3, Informative)
There is one gap in this. If steps 3 and 5 happen at the same time, then steps 4 and 6 happen at the same time, and both User X and User Y could pass the "System checks whether incoming version matches database version" check. Some locking is still required, otherwise it will look to both Users as if they "won".
The way I do it (Score:3, Informative)
Although I don't know what your implementation, or even what server-side language or database you use... I'll comment.
- For the sake of simplicity, add 2 columns to the table you want to be able to lock. Call them `lk` (lock) and `lkts` (lock timestamp).
- When a user is currently editing the row/document/whatever it is, have an icon of a lock or something to display in the list if someone is currently editing it.
- When generating the list of 'documents'(/whatever it is), check `lkts` and compare it to the current time. If it is stale (5 mins old), clear it and allow people to edit it. Always allow users to view the data.
- When a user clicks on the 'Edit' button, change the `lk` column to 1 and `lkts` to the current timestamp (UNIX_TIMESTAMP under sane DB's)
- Use RPC or XML-RPC to save the document periodically (every 60 seconds or so). Every save, update the `lkts` with a fresh timestamp.
- When the user clicks "Save and Exit" or "Save and Continue", have it submit the form the old fashioned way, save the data, and set `lk` and `lkts` to 0.
- Use Javascript to detect how long of a period of time passes for of no-activity. If it goes on past, say, 10 minutes, submit the form (thereby clearing `lk` and `lkts` and allowing other users in to edit)
This is bottom-of-the-barrel designed for simplicity. No security or anything in mind, but simply something that will work even after a browser crash or someone leaves the computer with a 'document' open and walks away.
Disclaimer: Just worked 14 hours. Very tired, don't want to go into any more detail. If this makes absolutely no sense to anybody, please discard this message.
The Only Choice is... (Score:5, Informative)
Optimistic Concurrency
Both the curse and the blessing of web applications. Most of the work is offloaded to the browser, thus not bogging down the database servers with keeping a ton of row level locks in memory, or even worse, page level locks.
For the programmers POV you use some back end language, php, java, ruby, python, it matters not, write a program, it launchs, connects to a database, ( no matter how much middle-ware you slap in ) sends it a query, gets the data, returns it for presentation, consideration and subsequent modification ( or not! ) by the user and then the program ends. You are no longer connected to the database, heck your browser is no longer connected to the server!
Some have mentioned AJAX <sigh...> AJAX is nothing but bundling together a few different bits of tech to do ONE thing, make a call to the server without refreshing the page. No matter how you slice it and dice it, thats all it does, it makes a call through the web server, to launch a program written in one of the afore mentioned languages and it follows the same set of steps, through either the post method or the get method and nothing has changed!
So you need a scheme to know if you can write to a record without overwriting someone else changes.
The only real choice is to use a timestamp value, all databases support them, usually down to the millisecond of accuracy. It is a simple process which you can make more complicated as you desire. As many have mentioned, you read the record making sure you get the timestamp of the last update. That timestamp gets sent to the browser along with the data. When the user clicks save the stored procedure that does the actual update then compares the timestamp you are sending with the one on the current record as in "select for update ...." and if the one you are sending along does not match the one on the current record, then your update loses and the stored procedure reports that back and then you deal with the user feedback in any way you see fit. Typically this is done by sending back the record in is new state and telling the user, "sorry, but you have to star over.".
Now having said that there is nothing to say that you cannot be imaginative with a bit of javascript or something like that, or even with the php array_diff() function or an equivalent in some other language then insert some fields above or below the the data that was previously changed to at least have the conflicting data shown in both forms eg: what it is NOW and what they wanted it to BE.
Handled this on a web interface (Score:2, Informative)
What we finally did is lock the editing page, so that if someone else had it opened you were not allowed to update it until they removed the lock on that page.
Or the user could over ride the page lock if they felt pretty sure that the other user was not using it for editing ( Maybe they just had it open on their desktop).
In a table we put the page, user identification, and timestamp when the lock was created.
So whenever the page was opened, it checked the table to see if it was locked. If it was locked, then it displayed header showing who had it locked and how long they have had it.
We generally only have 3 to 4 users that may open a page for editing and they soon learn that if you are going to edit something after it has been sitting for some time to update the page.
We should probably update this with ajax so that at least the header of the page tells the user someone else has taken the lock.
But currently happens though is that the page won't update if it doesn't have a lock and the user has to go back if and start over if someone stole the lock. So far I haven't heard of it happening, because they usually open or update right before they start editing so they know they have the lock.
But handling it in this manner has greatly reduced our problems.
Yeah, it's amazing how if you think it could happen it will. And most of our problems, I think, were caused by users opening the same page on multiple computers and then instead of closing the page, they were updating the page with the old information.
Re:Optimistic concurrency (Score:4, Informative)
This sounds a lot like a RDBMS... (Score:3, Informative)
Locking is a solved problem in most Database Management Systems. I think you are worried about the wrong layer of your application. Web and Application code is most often agnostic to how records are retrieved, updated, and locked for concurrency. For reference, look up the ACID [opensourcearmenia.com] properties of a typical RDBMS.
Re:Its doable, ajax helps (Score:1, Informative)
I agree, there is no reason to go with an inferior solution just because it's perceived as simpler. Lock the document when it is opened for editing, refresh the lock timestamp periodically (as long as the user is actually changing the document), release the lock when the user saves the document or when it times out (from user closing the window/tab, browser crashing, inactivity).
Of course version control is a great feature but from my experience users want, and need, simplicity. They want to relate to one version of a file, not an ever growing tree of different versions. Thus, the best implementation of this in my mind is exclusive locking coupled with saving the document as a new entry in the database with an increased version number.
Re:Same as bugzilla? (Score:5, Informative)
When a client wanted to know while they were working on a record that someone else had it open (they truly wanted the record locked while one user had it up on the screen), we used a LOCKED_BY and LOCKED_UNTIL field on each relevant record. While editing, records are read-only if LOCKED_UNTIL is in the future and LOCKED_BY is not the current user.
On the edit page, an AJAX call is made on a 10 second interval which updates LOCKED_UNTIL to be +30 seconds (this way even if there are network issues of some sort, three consecutive status updates need to fail in a row). If the browser is closed or the computer blue screens, etc, after 30 seconds the record unlocks itself. When you save the record, LOCKED_BY is nulled, and LOCKED_UNTIL is set to the epoch.
We also employed a version ID so that if all else fails and your client for some reason stops keeping the record locked (eg you suspend your laptop and come back to it later), when you submit your edits; if anyone else had made edits while your client was unable to keep the record locked, you're still given an indication that another user updated the record. The interval update checks the version ID too (a single SQL statement with PostgreSQL's excellent UPDATE RETURNING syntax) and warns the client if somehow someone else updated the version without this client having been able to maintain the lock - as soon as the next update interval succeeds the user gets notice.
The ajax call was basically something like:
UPDATE tbl_something
SET locked_by = (current_user), locked_until = (time+30)
WHERE record_id = (record_id)
AND locked_by = (current_user)
RETURNING
locked_by, version_id
Double check that locked_by is still the current user and version_id is still the known version of this record.
Re:Same as bugzilla? (Score:3, Informative)
This is a very good solution but it can still paralyze you if someone:
* Opens an important record
* Gets distracted
* Leaves for vacation for 2 weeks
Now their PC is locked with the page open and constantly polling, the record is locked forever, and people are angry. This can be solved with a message like
"This record is locked. _Take Control of this record_" - Clicking it would up-end the equasion - in order to keep control the other user has to click a "I'm still editing" link within a minute. This would solve not only the vacation disaster, but the lunch annoyance.
Depending on your resources and how large a "Record" is, you may consider getting more granular as well. You could lock by field, or form section, to capture use cases where for example "I'll type up the client notes, you update their contact info to fix any errors you find, and we'll call them about this as soon as possible."