×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Data Locking In a Web Application?

timothy posted more than 4 years ago | from the get-offa-my-cloud dept.

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

cancel ×
This is a preview of your comment

No Comment Title Entered

Anonymous Coward 1 minute ago

No Comment Entered

283 comments

Duct Tape (5, Funny)

Anonymous Coward | more than 4 years ago | (#29536219)

Lots and lots of Duct Tape.

The euphemism treadmill (-1, Offtopic)

seanadams.com (463190) | more than 4 years ago | (#29536227)

don't want to taint the responses

It cracks me up how "taint" originally was a noun referring to the perineum - the region between the ass and balls/muff, and now all of a sudden everyone just uses it to mean "to soil". It's so fascinating, how language evolves...

Re:The euphemism treadmill (1, Informative)

Anonymous Coward | more than 4 years ago | (#29536239)

I think you have that backwards.

http://www.merriam-webster.com/dictionary/taint

Re:The euphemism treadmill (4, Funny)

Anonymous Coward | more than 4 years ago | (#29536259)

So that's what the song "Tainted Love" is really about! Who knew.

Re:The euphemism treadmill (1)

grub (11606) | more than 4 years ago | (#29536363)

Bahahaha, almost had brandy on my keyboard.
Good one, thanks.

Re:The euphemism treadmill (0, Offtopic)

Rophuine (946411) | more than 4 years ago | (#29536465)

"'tain't [it ain't] the pussy, 'tain't the asshole". This is a modern slang usage.

Next time, check your references before you promulgate such tripe.

Re:The euphemism treadmill (0, Offtopic)

Jurily (900488) | more than 4 years ago | (#29536567)

It cracks me up how "taint" originally was a noun referring to the perineum - the region between the ass and balls/muff, and now all of a sudden everyone just uses it to mean "to soil". It's so fascinating, how language evolves...

BTW soil, guess where the earth chakra is.

Re:The euphemism treadmill (0, Offtopic)

chthonicdaemon (670385) | more than 4 years ago | (#29536707)

Taint (n) has its origins in Middle English (as a verb in the sense [convict, prove guilty] ): partly from Old French teint "tinged," based on Latin tingere "to dye, tinge"; partly a shortening of attaint, according to my Mac dictioary. Shows you how newer senses of words can be believed to be "original".

Same as bugzilla? (5, Informative)

Derek Pomery (2028) | more than 4 years ago | (#29536235)

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? (4, Informative)

Anonymous Coward | more than 4 years ago | (#29536289)

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:Same as bugzilla? (5, Insightful)

Dexx (34621) | more than 4 years ago | (#29536627)

To ensure the edit isn't lost, we handle this by kicking the user back to the form with a message. You could go one step further and get the modified record from the DB, then highlight the field in question and give the user the option to keep/override. You could make it more intelligent by detecting the collision, analysing the difference, then committing if no fields conflict. Depends on the business logic, I guess.

Re:Same as bugzilla? (1)

gmrath (751453) | more than 4 years ago | (#29536651)

I deal with a browser-based CMMS system at work that has this fault. One would hope for the cost of the purchase of software, hardware and the annual maintenance (not cheap) that the developers would make sure the system has something as basic as record locking, notification that the record is in use, or both. After all, it's a front end of a relatively sophisticated database. And databases have been around a while and for the most part are thoroughly understood. The first package we had: V2.0; the current package V4.0. Guess what? No record locking. And no luck with the vendor. They are very nice and seem to be attentive to bug reports, but still no record locking. The CMMS system is better than most for our application and is easy to learn and use. It's this one thing that's the pisser. We've learned to save work very, very frequently. And you're right, there's nothing quite as annoying as losing 15 minutes or more of work. Just gone. Just a information box saying, in effect, "So Sorry, record in use, entries not saved". Really wish you were on that dev team.

Re:Same as bugzilla? (2, Informative)

dindi (78034) | more than 4 years ago | (#29536393)

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? (1)

dgatwood (11270) | more than 4 years ago | (#29536865)

The trivial solution is to set a time stamp and user ID every time somebody goes in to modify it. If the time stamp is within the last thirty minutes, display a warning that "John Doe is editing this file. You should check with him/her before making changes to make sure you don't collide." That's good enough for 99% of these sorts of things.

But yeah, rolling back and showing both versions in a side-by-side view when collisions occur is a nicer user experience if you're dealing with a lot of users. Using diff and patch to generate a suggested merging of the two new revisions with changes highlighted in yellow and conflicts shown in red is nicer still.

Re:Same as bugzilla? (0)

Anonymous Coward | more than 4 years ago | (#29536413)

What's amazing to me is that this idea isn't blindingly obvious until you've had it explained to you clearly. I remember when I was a new developer and someone showed me this the first time. I couldn't believe I had been so dumb. I couldn't believe I had never thought of it myself.

That's why I believe getting out and talking to and learning from other developers is absolutely critical.

Re:Same as bugzilla? (1)

Casandro (751346) | more than 4 years ago | (#29536805)

True, the way to go is simply to store every change and usually display the newest version of a record. This also allows you to make an "undo" function which your users will appreciate very much.

This book: (2, Informative)

walmass (67905) | more than 4 years ago | (#29536253)

on Google Books [google.com]. You are welcome

Re:This book: (1)

Firehed (942385) | more than 4 years ago | (#29536741)

A handy resource (Google has full O'reilly books? When did that happen?), but I don't think it's quite what the OP had in mind. It sounds like the issue is more of the checkin/checkout/merge nature, rather than table-level locking. In which case keep it simple (at least to start) - when the first user starts editing, some sort of "in use by $userId" flag is set, and everyone else gets read-only access. When that user saves the document (or closes/cancels), remove the flag. Some sort of live editing (a la Google Docs) or diff/merge functionality might be good for v2 for a nicer experience.

Adjust as needed to fit your actual requirements, but don't overcomplicate it.

Re:This book: (1)

geminidomino (614729) | more than 4 years ago | (#29536775)

when the first user starts editing, some sort of "in use by $userId" flag is set, and everyone else gets read-only access.

I've been looking at a way to do something just like this myself on a LAMP app. The problem I have is that I am aware that Users Are Idiots(TM) and do fun things like stop halfway through an edit to piss off for two hours, and I haven't found anyway to have the app force an "unlock" when the session expires...

So far the only obvious solution (cronjob checking) is a non-starter, for various implementation reasons.

Re:This book: (1)

plastbox (1577037) | more than 4 years ago | (#29536923)

One really simple way of doing it is (as a previous poster suggested) having the client send an AJAX call signifying that the document is still open. So, when I click "Edit" on "shopping_list.txt" it's database entry gets it's "edit_by" set to my userID and "ts_editing" field set to unix_timestamp() (for mysql or time() for php). The edit-page sends an AJAX request every.. say.. 30 seconds to a file that updates the timestamp.

Now, when you list the available files, you will see that I am editing "shopping_list.txt" because it's current "ts_editing" is larger than (unix_timestamp() - 60). At most 1 minute after I leave the editing page, either by saving or by closing the window, the document will become available for editing again.

If you are uncomfortable with JavaScript, check out jquery [jquery.com]. In fact, check it out, even if you consider your js skills to be badass. The code for updating the editing-timestamp can be as simple as follows:

function updatelock()
{
$.get("updatelock.php");
}

$(document).ready(function() {
setInterval("updatelock()", 30000);
});

Assuming you set the flags mentioned earlier upon opening the file for edit, updatelock.php really only needs to do a
UPDATE `files` SET ts_editing = UNIX_TIMESTAMP() WHERE edit_by = $_SESSION['userid'] AND ts_editing > UNIX_TIMESTAMP() - 60
(updating the timestamp on anything you temporarily own that hasn't timed out) or something like that. =P

More detail please (1)

MichaelSmith (789609) | more than 4 years ago | (#29536255)

I the web version uses a database then your locking should work the same way. If it uses flat files or something you can create lock files along with the data. But IMHO lock files are a PITA.I suppose its locks which are a pain....

The way this is generally handled... (4, Informative)

Omnifarious (11933) | more than 4 years ago | (#29536271)

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:The way this is generally handled... (5, Interesting)

palegray.net (1195047) | more than 4 years ago | (#29536305)

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.

Re:The way this is generally handled... (1)

Omnifarious (11933) | more than 4 years ago | (#29536361)

I think that would likely be a lot nicer from a user's perspective. :-)

But it would also take more work and have the locking mechanism rely on Javascript. You could still use my method as a backup for when Javascript didn't work for whatever reason.

Re:The way this is generally handled... (1)

palegray.net (1195047) | more than 4 years ago | (#29536765)

I largely agree, although for an internal application I think JavaScript can be an assumed capability. It's actually not a lot of work; it could probably be implemented in a couple of days with some investigation into jquery [jquery.com] and some minor DB changes ;).

Re:The way this is generally handled... (1)

Omnifarious (11933) | more than 4 years ago | (#29536501)

Oh, I just thought of a reason why the AJAX idea would be even harder than I originally thought. I bet the web app doesn't even use cookies or sessions at all. That makes that kind of thing a lot harder to implement.

Re:The way this is generally handled... (1)

palegray.net (1195047) | more than 4 years ago | (#29536773)

Well, bolt on some session handling :). That piece should be separate from primary application logic anyhow, so it shouldn't be too difficult to put in place.

Re:The way this is generally handled... (1)

rta (559125) | more than 4 years ago | (#29536513)

I like this solution better than the optimistic locking usually seen in web apps, but it's a matter of the specific use-case, of course. This is a good method when there is a) significant chance of collision and/or b) editing takes a lot of effort or is high cost.

pessimistic locking (even with js automation, timeouts and overrides as you describe) has the downside of of high complexity/cost of implementation, and as the guy above says you still need versioning or other system as backup if this is at all an "important" system since you can't (shouldn't) trust that the web browser will do the right thing.

bugzilla's done ok w/ just collision detection for a good 10 or 15 years :-)

Re:The way this is generally handled... (1)

palegray.net (1195047) | more than 4 years ago | (#29536795)

Honestly, it shouldn't be difficult to add this to any existing web application. I think it's a prerequisite for any situation where there is a chance of collisions (as you pointed out), unless the users are technically savvy enough to be presented with a diff of the values and allowed to make a decision based on the available data. In my view, versions should still be maintained as part of a comprehensive logging system (regardless of which approach is taken). This isn't difficult either; it's a simple matter of tracking the version data in a separate write-only table.

Re:The way this is generally handled... (2, Interesting)

spiffmastercow (1001386) | more than 4 years ago | (#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.

Re:The way this is generally handled... (2, Informative)

omkhar (167195) | more than 4 years ago | (#29536307)

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.

Re:The way this is generally handled... (1)

Omnifarious (11933) | more than 4 years ago | (#29536335)

Can you explain to me how a malicious person's manipulation of the hash value could damage anything? How would they know what to change it to? I suppose they could just hash the form fields and hope, but that's easily defeated by adding in a server side session variable as salt.

Also, while this isn't exactly the best practice, the question made it clear that it was a fairly small internal web app. So worrying about malicious users on that scale is likely not an issue.

Re:The way this is generally handled... (1)

palegray.net (1195047) | more than 4 years ago | (#29536359)

Agreed. The GP might be right in theory, but it probably doesn't matter in practice. Some people feel a compulsion to pick nits, I suppose.

Re:The way this is generally handled... (1)

omkhar (167195) | more than 4 years ago | (#29536411)

Can you explain to me how a malicious person's manipulation of the hash value could damage anything? How would they know what to change it to?

Any input taken should be scrutinized for injection, overflows etc. Another input from "out there" another set of variable to scrub. A sloppily coded hash/verification could be a vector for SQL injection for example.

I suppose they could just hash the form fields and hope, but that's easily defeated by adding in a server side session variable as salt.

If you have a session to begin with... just store it server side

Also, while this isn't exactly the best practice, the question made it clear that it was a fairly small internal web app. So worrying about malicious users on that scale is likely not an issue

assumptions regarding the scope, confidentiality, integrity or availability requirements weren't part of my answer. Only that from a security perspective, anytime you have another piece of information that's user submitted, requires a thorough check/scrub/sanitization prior to being processed.

Re:The way this is generally handled... (0)

Anonymous Coward | more than 4 years ago | (#29536451)

Use properly parametrized stored procedures/prepared statements and you don't need to worry about SQL injection.

Re:The way this is generally handled... (1)

Omnifarious (11933) | more than 4 years ago | (#29536485)

A hash value is 43 bytes of base64 encoded stuff. It's trivial to validate that it's still 64 bytes of base64 encoded stuff when you get it back. And verifying that it's the right set of base64 encoded stuff is fairly easy too. If it matches the result of computing the hash of what's in the database, the database hasn't changed since the form was generated. If it has, the database hash changed. The only possible attack is fiddling the hash value to be the hash of the data that now is in the database, which would require a willing accomplice who could tell you what the new values are because that's how they set them. And the result of that would be that their values are stomped on.

Also, no SQL injection is possible because the hash value itself isn't stored in the database so there's no reason to include it in a database query in any way.

So, I don't think there is an attack that could work by fiddling the hash value sent back in a hidden form field.

And, doing so gives you extra security against X-site scripting attacks involving someone having a POST that goes against the web app URL from a completely different page. They are certain to not be able to get the hash value correct and the user isn't going to be tossed back from their attempt to post with a confusing message about the database having been updated since they filled out the form.

For extra security against X-site scripting attacks you can make the hash be a MAC instead and have the key be some secret server side data from the server side session data structure. But that then starts requiring you to actually have sessions, which it doesn't sound like the original poster has.

Just because someone is using data in hidden form fields for something important does not automatically mean that they are doing something insecure. This analysis I just posted was actually done in my head before I made the original post.

Re:The way this is generally handled... (1)

Mathinker (909784) | more than 4 years ago | (#29536751)

For extra security against X-site scripting attacks you can make the hash be a MAC instead and have the key be some secret server side data from the server side session data structure. But that then starts requiring you to actually have sessions, which it doesn't sound like the original poster has.

Am I missing something here, or didn't you skip a step in the security hierarchy? A MAC with a fixed secret server side key would still be more secure than a vanilla hash (it would limit the attacker to replay attacks), yet not need sessions, no?

Or was everyone saying "hash" when they meant "fixed-key MAC" (since there really is no reason I can see not to implement a fixed key MAC, here, instead)?

Re:The way this is generally handled... (1)

Omnifarious (11933) | more than 4 years ago | (#29536841)

Oh, you're right. I didn't think of that. It would be more secure, and that's a good idea. And you're right, given that idea there is no good reason not to implement it using a MAC instead of a hash.

I still don't think the plain old hash is all that insecure though.

Re:The way this is generally handled... (1)

omkhar (167195) | more than 4 years ago | (#29536771)

Last post on this topic - clearly you and I have a different understanding of security. No HTTP POST/GET variable are typed - you can throw whatever you want in them. Lazy assumptions about length won't help you either. Point is, there is an extra set of data to parse. Whenever there is data to parse, there is the potential for an exploit. See my solution above, and lets move on.

Re:The way this is generally handled... (0)

Anonymous Coward | more than 4 years ago | (#29536381)

This is the method I would suggest too. The first part doesn't seem to really matter, it is really the hash comparing that prevents double editing. You could do the check with XHR as well.
The OP makes it sound as if edit overwrites are fairly infrequent ("time to time") so exclusive locking may be a little overboard.

Might be nice if you offered the user an option to merge their changes with the existing ones in the event of a collision.

Re:The way this is generally handled... (2, Insightful)

nanospook (521118) | more than 4 years ago | (#29536399)

Some interesting ideas here.. Especially the AJAX idea.. However, consider this. Any scheme that involves telling the user "after the fact" that the record has changed is "wasting" the user's/companies time and money, resulting in rework. If your scheme tells you ahead of time that so and so has the lock and you can't, then you save the user's efforts. Of course, I'm speaking generically in that some data entry systems might be ok with multiple edits of the same records.

Hibernate In Action (even if not using Java) (2, Interesting)

Zarf (5735) | more than 4 years ago | (#29536285)

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:Hibernate In Action (even if not using Java) (0)

Anonymous Coward | more than 4 years ago | (#29536325)

I agree wholeheartedly. Optimistic Locking is the way to go (here's another wikipedia article: http://en.wikipedia.org/wiki/Optimistic_concurrency_control). Reliance on pessimistic locking techniques, will only get you into trouble in a stateless, web browser world.

Answer is optimistic locking (0)

Anonymous Coward | more than 4 years ago | (#29536295)

optimistic locking: http://en.wikipedia.org/wiki/Optimistic_concurrency_control

Re:Answer is optimistic locking (0)

Anonymous Coward | more than 4 years ago | (#29536339)

Exactly. That and versioning AKA Wikipedia where conflicts have to be resolved.

Its doable, ajax helps (0)

Anonymous Coward | more than 4 years ago | (#29536299)

The standard technique I've use before:

onChange handlers on each input field.
On first change, send an AJAX request to the server to lock the data
If lock succeeds, do nothing
If lock fails, alert the user to that fact and disable all inputs on that record.
On submit, release lock.
On navigate away (next transaction by that user) release the lock
Auto expire the lock after some reasonable period of time in case the user A) went to lunch or B) navigated to www.cnn.com

Re:Its doable, ajax helps (-1, Flamebait)

Anonymous Coward | more than 4 years ago | (#29536327)

Holy hell. That is exactly the kind of bullshit that you can avoid by learning about Optimistic Locking. Haven't you ever fucking heard of CVS? SVN? Bugzilla? What the fuck. I bet it works but fuck that's complex. What happens if a browser crashes between first and second lock? What happens if a browser bug never releases the lock? What the FUCK happens if some kid with a copy of Fire-fucking-Bug starts diddling with your FUCKING javascript.

FUCKING quit your goddam job and hand it over to someone who knows how to do it and go the FUCK to school you flaming MORON!!!

Re:Its doable, ajax helps (0)

Anonymous Coward | more than 4 years ago | (#29536531)

I love the smell of Slashdot in the morning.

Lock the same as any other app... (0)

Anonymous Coward | more than 4 years ago | (#29536331)

The only thing is that it is much harder to determine if the client is still there & active, so you should release the lock.

With ajax widgets & cookie state it's not that difficult to determine if the client is still there.

use a hash/timestamp (1)

psy (88244) | more than 4 years ago | (#29536333)

Create a timestamp/random hash and store it against each record, then include it in your update query.

UPDATE table SET
      data1 = @Data,
      hash = NewHash()
WHERE ID = @ID
AND Hash = @Hash

Every save, change the hash to a new value.

If someone has changed the record and another person goes to save it, the hash wont match and 0 records will be updated. This can then be captured in your web application.

If 0 records updated - display error saying "user has already changed record, please reload page"
If 1 records updated - display success.

Re:use a hash/timestamp (0)

Anonymous Coward | more than 4 years ago | (#29536499)

I must admit that hash idea is rather surprising to me. I can confidently say I would use an incrementing version number myself and would never think to do a hash. The probability of hash collision is probably infinitesimal. The probability of collision between two copies of an incrementing version number is zero. There is a probability two timestamps conflicting if your users update sub-second from each other. If we worked together I would probably argue that the incrementing version number is the simplest solution so it is correct.

If you were my boss and anyway senior to me I would let you use your hashes but I wouldn't worry too much about it since it's likely we work in a tiny shop on tiny apps that have tiny numbers of users and we have tiny salaries. And I would start quietly looking for a new job.

Optimistic concurrency (5, Informative)

Shimmer (3036) | more than 4 years ago | (#29536367)

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:

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

Re:Optimistic concurrency (4, Informative)

gr7 (933549) | more than 4 years ago | (#29536457)

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.

Re:Optimistic concurrency (3, Insightful)

Anonymous Coward | more than 4 years ago | (#29536537)

Slashdot is hardly the right venue to get a good answer to this question

Actually slashdot is really good at this kind of stuff, there was a few dozen relevant, on-topic, well-written replies soon after the question was posted.

On the other hand, political discussions ... embarrassing.

Re:Optimistic concurrency (4, Insightful)

hibiki_r (649814) | more than 4 years ago | (#29536563)

+5 Is not enough for the value of the parent post. Optimistic Locking is the right answer in 99% of the cases. The issue then becomes how you want to deal with re-submitted of changes. If the entities to be saved are small and very atomic, asking the user to retype, making sure their changes are still sensible on the modified record makes sense. If your records are very large and/or very complex, then you might consider using some business knowledge to see if changes to the record can be grouped logically, and maybe even committed individually: If someone changed data for X shipment of a purchase order, while someone else changed Y, then the changes don't really have to conflict.

But whatever you do, build it around optimistic locking: Don't try to lock a record because somebody just has it open somewhere on a remote location. That path leads to madness.

Re:Optimistic concurrency (1)

Tablizer (95088) | more than 4 years ago | (#29536565)

Hard locks are probably not what you want in a stateless web app.

Hard locks can suck anyhow. I once worked in a place where every now and then somebody would forget to close a form screen and run off or swap to another app, leaving it locked. Unfortunately, the system didn't track who had it open, so a message was sent to the front desk, and EVERYONE in the building would hear something like this on the overhead paging speakers: "Whoever is locking customer 1234, please close your screen or call X." I heard about 3 of those a week.

Even if the system did track who locked it, it would still require a fair amount of investigation to resolve. Not the best technology approach to shared data unless everyone's in the same room.

Re:Optimistic concurrency (3, Funny)

ArcadeNut (85398) | more than 4 years ago | (#29536579)

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

Ok, so if Slashdot isn't the right venue to get an answer, should he ignore your answer?

Re:Optimistic concurrency (2, Informative)

MagicM (85041) | more than 4 years ago | (#29536613)

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

Re:Optimistic concurrency (4, Informative)

argodk (640595) | more than 4 years ago | (#29536735)

Absolutely correct, but that just means that there has to be server-side locks for the commitment phase (4-6), it doesn't impact the client-side. This has an implication for performance of the commitment phase, but luckily, database vendors have been struggling with efficient implementation of commit for years, so using the transaction features of whatever database is used for storage should resolve most of those problems (i.e. check and update the version number in the database in a single transaction).

Re:Optimistic concurrency (1)

gullevek (174152) | more than 4 years ago | (#29536819)

this can be solved with a timestamp at which the user loaded the data, first come, first servers. second one gets notice the data has been changed.

heh (1)

shentino (1139071) | more than 4 years ago | (#29536379)

Interesting.

First management shoots you down on a technical point by a foot-stomping shove of fiat, and then turns around later when they get bit in the butt by the users and blames you for obeying them.

Re:heh (3, Insightful)

IntlHarvester (11985) | more than 4 years ago | (#29536581)

Actually, I don't blame them. The first instinct of people coming from a client-server background is to introduce to some form of record locking. Since this isn't "in the box" with web app frameworks, it makes sense to push back on the feature until you have user feedback or other analysis that it's actually required. Otherwise you are spending valuable time coding/debugging a feature that will rarely ever be used.

Re:heh (0)

Anonymous Coward | more than 4 years ago | (#29536749)

Since this isn't "in the box" with web app frameworks

Woah look what's in the box: Grails Guide: Pessimistic & Optimistic Locking [grails.org] ... that's a built in Hibernate feature common to all Java frameworks... for some reason I thought *all* frameworks had that baked in. Look Ruby on Rails [rubyonrails.org] has this too proving how AWESOME it is.

What *lame* frameworks don't have this?

Re:heh (1)

IntlHarvester (11985) | more than 4 years ago | (#29536827)

Yeah, this is one of those posts I wish there was an edit button for :P Anyway, the UI isn't really in the box, just the more trivial database stuff.

depends on the desried semantics (0)

Anonymous Coward | more than 4 years ago | (#29536387)

Because http is stateless, it might be a bit of a challenge to take advantage of database locks since they only last inside a transaction, and you probably don't want a transaction to extend possibly indefinitely (ie, if the person shuts down their pc, goes home, dies in their sleep, and their pc is disappears for a week).

Depending on the desired semantics, you may need to implement your own locking mechanism.
When Person A requests a set of records with intent of modifying some of them, all the records in the set are marked as being modifiably only by Person A.
When person B tries to pull down a set of records that overlap, any records that overlap with Person's set are marked as "being modified by Person A, you can't touch".
That way, they at least know which records are not stable. Pretty trivial to do this in a database, you just need another table whose column are the rowids of the table in question and the id of the person currently editing the table. Compliance has to be enforced by your application rather than something lower level.

Re:depends on the desried semantics (0)

Anonymous Coward | more than 4 years ago | (#29536523)

"http is stateless" blah blah. So are ethernet frames. In fact other than for fragmentation, so is IP, but it doesn't stop anyone from implementing state on top. The problem is an application layer problem, not presentation or transport.

Re:depends on the desried semantics (1)

timmarhy (659436) | more than 4 years ago | (#29536831)

The above poster is the most level headed and sane on here so far, but i'm cringing at some of the suggestions on here. if you do ANYTHING, do this - get a decent fucking DBA who can guide you through these issues. solving this at the application layer is asking for disaster, this HAS to be done in the database.

if this was me, my solution would be as follows

1. on initiating your transaction, create a named sql object with the name being unquie based on the client's session (take your pick on how you do this, but this solves your state issues). in a decent sql server you can set a timeout on these query objects, so that if the session is closed it won't bog the system down (i don't know exactly what you plan on using so it's hard to say).

2. take advantage of the databases build in locking - since your using your named object that persists within the database this is now easy and more importantly FAST. what i'm cringing about on here the most is the endless suggestions that require extra columns and requerying of the database from the webapp.

like i said without knowing the database your using it's bit of a shot in the dark, but conceptually this is what you should be doing. one other thing i'd do also is create these queries from within an sql function, so that you don't need to give gobal create permissions or anything of that sort. it'll mean the function will have the permissions and not the clients, letting you lock things down nicely.

LAN apps vs. Web apps (0)

Anonymous Coward | more than 4 years ago | (#29536389)

You cannot lock a Web client the same way you used to do with LAN clients. Otherwise your entire application will grind to a stop with locks all over the place. The proper way to do it is let each client commit their work to a transaction history table. Either the sum or the last transaction is the current info. Think of it as an accounting ledger.

google optimistic locking (0)

Anonymous Coward | more than 4 years ago | (#29536397)

you probably want optimistic locking. have a quick read about this - and then decide if you are using timestamps, update id's or whatever suits your situation.

2 words (0)

Anonymous Coward | more than 4 years ago | (#29536407)

Optimistic Locking.

Check some frameworks... (0)

Anonymous Coward | more than 4 years ago | (#29536453)

...because It's been done in quite a few already. If you're using an ORM, chances are it already supports something.

Otherswise, if you need to do it outside if a transaction in a single request, you basically have two options:
1) Add locked_by (the user id) and locked_at (a timestamp) fields to the database, and populate them when someone gets a lock.
2) Use a lock_version (just an integer) column and wrap your select in a transaction to also increment the column. Check the record you have against the record in the database (again, in a transaction) to make sure it hasn't changed since.

The way Rails' ActiveRecord does lock_version is a good example to look at because it's relatively simple, so have a look.

Confluence (4, Informative)

goofy183 (451746) | more than 4 years ago | (#29536471)

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.

Three Options (0)

Anonymous Coward | more than 4 years ago | (#29536575)

simple = time stamp. everyone understands it.
elegant = ajax to show each user what the other user is doing in real time.
complicated but feasible = distributed version control system - take a look at those algorithms (HG). the last user to save might have to do some merge operation.

File locking? (0)

Ash-Fox (726320) | more than 4 years ago | (#29536477)

File locking with the contents of the user uid inside it in a /tmp type path?

If you can write to it, it's not locked. Make sure the lock you use doesn't prevent reading.

CouchDB (4, Informative)

deweller (266610) | more than 4 years ago | (#29536515)

Check out CouchDB [apache.org]. It is built around the concepts of distributed (and even offline) databases and handles conflict resolution. It employs optimistic locking.

Use Optimistic Locking (4, Informative)

linuxhansl (764171) | more than 4 years ago | (#29536517)

Don't take out a database lock (also referred to as pessimistic lock sometimes). Web transactions tend to be long lived and there's typically no easy way to know when the user just abandoned the edit (and hence you would not really know when it save to release the lock, unless it is by timeout or explicit release by the user).

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.

The full-fledge solution (0)

Anonymous Coward | more than 4 years ago | (#29536519)

I did this once on a web application, and I was fairly happy with the results. Each time a user opened a record for editing, the user's username and a timestamp were written to two fields I appended to the table. The system also had a global record lock timeout (20 minutes in our case). If a 2nd user attempted to open the record for editing, the app would check to see if someone else had tried to open the record in the last 20 minutes, and if so, the 2nd user would be given a message that the record is locked, who locked it, and then present the user with a *clearly* read-only version of the record (just HTML text instead of HTML form elements). It quickly became clear that this worked for the users extremely well, but it was not easy to implement in our environment (ASP). It was also not an easy "drop in" scheme, and required extensive modification of the source code for each web page / database table that we wanted to add it to.

Re:The full-fledge solution (1)

Simon80 (874052) | more than 4 years ago | (#29536625)

There are many comments posted before yours that recommend optimistic locking, which is better than the pessimistic locking you just suggested. I also suggest that to be complete, an application should also attempt to merge the new version of the data with the change that the user is trying to make. For examples of merge algorithm implementations, the developer should look at a distributed version control system, like Git, Mercurial, or Bazaar.

It was also not an easy "drop in" scheme, and required extensive modification of the source code for each web page / database table that we wanted to add it to.

I don't envy the crazy amounts of code duplication that you clearly had to deal with on a regular basis.

there are two parts to this (1)

MagicM (85041) | more than 4 years ago | (#29536585)

The method I'm most familiar with consists of 2 parts:

Each record[1] has a last_updated value associated with it.

Part 1:
When a user loads the record for editing, it also loads the last_updated value. Upon submitting, the last_updated value is compared against the stored value and if they don't match this is considered a concurrency error. Exactly what happens depends on the nature of the record, but usually a message is shown to the effect of "the record was changed by someone else, please reload it and retry your edit."
This is necessary to avoid problems when user B updates data between the time user A loads the data and submits their updates.

Part 2:
At the exact moment when the submitted last_updated value is checked against the stored value, if the values do match then the stored value is locked for editing in the database.[2] For example via a "select for update" statement. Then the input is validated, the submitted data is stored, the last_updated value is updated, and all updates are committed, thereby releasing the lock.
This is necessary to avoid problems when user A and user B submit their updates at almost the exact same time.

[1] "record" could be a logical record that consists of multiple physical records in the database. You'd need one last_update value per logical record.
[2] In some cases you need to lock multiple locks when multiple separate records affect each other. Exactly when and how this needs to happen is left as an exercise to the reader.

ModifiedOn (1)

the-matt-mobile (621817) | more than 4 years ago | (#29536589)

Put a datetime or timestamp field in each table called ModifiedOn. Every time the record is changed, update that date. Then, in your data access layer (you have one of those, right?), do not allow an update to a record if the ModfiedOn date has changed since you originally pulled the record. If the date was the same for all records being updated in the transaction, then no one touched them. It's called optimistic locking. Later, you can add more featureful locking on top of this with change resolution/merging, etc. But, this is a failsafe starting point to ensure data integrity.

If you don't already know, get off the project. (1)

kuzb (724081) | more than 4 years ago | (#29536591)

Anyone who doesn't understand the basics behind record locking/concurrency control and how to implement it shouldn't be involved in a multi-user concurrent application in the first place. This is really weak as far as slashdot articles go.

Uh, lots of frameworks do this for free... (1, Informative)

Anonymous Coward | more than 4 years ago | (#29536601)

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.

Timestamp and timer (0)

Anonymous Coward | more than 4 years ago | (#29536605)

I personally use a timed lock, and use an ajax poll (on a timer) to update the timestamp for the duration of the edit session. I keep the timeout short, but not too short... that way if the user leaves, the edit is discarded and so is the lock (after a short time). Once the edit is done, the lock is removed of course.

help the users by giving them info but not limits (3, Funny)

roman_mir (125474) | more than 4 years ago | (#29536631)

I just made a console a month ago that handled this problem as follows:

Records that need to be processed are in 'pending' (unconfirmed actually) status, once any user clicks to select the record, it is timestamped and the user is 'locking' it. Actually the user is assigned to it and all other users see that this record is 'locked' by the first user who selected it.

However, now anyone can open the details of the record and do the following: they will see a button 'Take the lock away from $user$', so they can take the lock away! But there is history of who took whose lock, so the problem will be solved outside of the applicaction if they take each other locks away.

If the user locked a record he has a choice of 'save', 'save and release lock', 'release lock' buttons on the record details.

The users are allowed taking the lock away from each other so the lock resolution is pushed into the real world and out of the app.

The way I do it (2, Informative)

corychristison (951993) | more than 4 years ago | (#29536667)

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... (4, Informative)

FlyingGuy (989135) | more than 4 years ago | (#29536679)

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 (2, Informative)

JumpDrive (1437895) | more than 4 years ago | (#29536691)

We ran into the same problem.
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.

Way more informtion (3, Insightful)

mindstrm (20013) | more than 4 years ago | (#29536709)

I think we'd need way more information to come up wiht a good solution - this is an overall application architecture problem, not just a locking problem.

What are the use cases? what kind of app is it? what is it that you are trying to lock, exactly?

Do your own damn job. (0, Troll)

HomerJ (11142) | more than 4 years ago | (#29536715)

They aren't paying you money to ask a bunch of guys on Slashdot. Do it your damn self or find another job that you're actually qualified to do without submitting an "Ask Slashdot" anonymously to do it.

Do as ticketmaster does... (3, Interesting)

Lord Byron II (671689) | more than 4 years ago | (#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.

This is user requirements, not implementation (4, Insightful)

viking80 (697716) | more than 4 years ago | (#29536777)

This is more a question of requirements than implementation. If your users want wikipedia style optimistic locking, just do that. If your users want hard locking with a timeout, do that. Just like your online bank does.

If users ask for hard locks without timeout, ask them what their real requirements are.

Carbon paper triplicate forms (0)

Anonymous Coward | more than 4 years ago | (#29536801)

Changes have to be submitted in hard copy form.

Carbon copies, real typewriter stuck copies, none of this pansy "3x copies" laser printer crap.

The original for me, the 2nd copy for the 86 file and the 3rd barely legible copy as your receipt.

We'll let you know in two weeks, maybe, if your changes are good.

This sounds a lot like a RDBMS... (3, Informative)

drfreak (303147) | more than 4 years ago | (#29536803)

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.

Replication conflict (1, Redundant)

mysidia (191772) | more than 4 years ago | (#29536833)

Time to move your application to a Lotus Notes DB. At least that platform has handling for it.

Either design your web app so two users won't step on each other, or design in a way of dealing with it... such as comparing a user's changes from the original form, and submitting those, instead of "overwriting all other changes"

Or depending on the app... treat it like any version control system, keep both sets of conflicting changes, and let the users resolve them a bit later, after your app informs them of the conflict.

3 sided change detection (0)

Anonymous Coward | more than 4 years ago | (#29536839)

We use knowledge from three sources for change detection:

1. Data posted from the user
2. Current state of data
3. Initial state of form when initially loaded

#3 is done by setting crc of field values and storing the CRC values in cookies but theres no shortage of available methods including just remembering initial values and sending them in the post form.

The general goal is that when you save the form only fields that you have personally changed are updated in the database. Your program should not update all fields. You need a well behaved data management layer.

If user A changes field 1 and user B changes field 2 there should be no conflict even if user B saves the form with stale data after user A made their change. Use diff between #3 and #1 to determine conflicts.

If you are posting to an RDBMS recommend starting a transaction, updating the row in some nonsensical way that guarantees a write lock on the row. Read current state from the database (#2) and run through diffs of #1,2,3 to find any applicable conflicts. If there are no conflicts update the row and commit the transaction.

If all system access follows the update before read semantic you get guaranteed behavior regardless of the read isolation semantics of the underlying RDBMS.

Load More Comments
Slashdot Account

Need an Account?

Forgot your password?

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>
Sign up for Slashdot Newsletters
Create a Slashdot Account

Loading...