Data Locking In a Web Application?

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

Comments

Same as bugzilla?

[Derek Pomery]

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:Same as bugzilla?

[nahdude812]

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.

Optimistic concurrency

[Shimmer]

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.

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:

1. User X fetches version 1 of Record A.
2. User Y fetches version 1 of Record A.
3. User X modifies her copy of Record A and attempts to save the change.
4. System checks whether incoming version (1) matches database version (1). It does, so the save proceeds and the version number on the record is updated to 2.
5. User Y modifies his copy of Record A and attempts to save the change.
6. System checks whether incoming version (1) matches database version (2). It does not, so User Y is notified that he cannot save his changes.
7. User Y fetches version 2 of Record A and tries again.

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