Slashdot Mirror
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."
Lots and lots of Duct Tape.
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.
-- perl -e'print pack"H*","6e656d6f406d38792e6f7267"'
I think you have that backwards.
http://www.merriam-webster.com/dictionary/taint
on Google Books. You are welcome
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....
http://michaelsmith.id.au
So that's what the song "Tainted Love" is really about! Who knew.
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.
Need a Python, C++, Unix, Linux develop
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 ... 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.
[signature]
optimistic locking: http://en.wikipedia.org/wiki/Optimistic_concurrency_control
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
http://en.wiktionary.org/wiki/taint
Seems to think the "to soil" meaning came first, independent of the slang meaning referring to your perineum.
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.
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.
Bahahaha, almost had brandy on my keyboard.
Good one, thanks.
Trolling is a art,
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:
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").
The most rabid believers in American Exceptionalism are the exact same people whose policies are destroying it.
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.
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.
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.
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.
Optimistic Locking.
...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.
"'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.
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.
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.
Change is certain; progress is not obligatory.
Check out CouchDB. It is built around the concepts of distributed (and even offline) databases and handles conflict resolution. It employs optimistic locking.
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.
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.
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.
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.
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.
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.
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.
BeauHD. Worst editor since kdawson.
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.
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.
don't forget: "tain't the meat, it's the motion"
http://www.geocities.com/merrystar3/allysongs/ItAintTheMeat.htm
The Cloud - because you don't care if your apps and data are up in the air.
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.
You can't handle the truth.
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.
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.
Hey KID! Yeah you, get the fuck off my lawn!
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.
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".
Languages aren't inherently fast -- implementations are efficient
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?
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.
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.
0x3A28213A 0x6339392C 0x736368xE
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.
don't cut it off www.mgmbill.org
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.
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 properties of a typical RDBMS.
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.
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.
The XMLHttpRequest can be pretty fast when optimized for performance. There is plenty of time to request a lock and pull information about all current editors from the server between user clicking on "Edit" and focusing on the edited information and moving hands from the mouse back to keyboard... ;-) Lot of time to warn/notify/forbid the first edit before it really happens.
Other approach is revisions. User might be informed that there is somebody else editing and the user might choose to request the lock/ignore warning. If ignored then the latest saved
a) wins - overwrites earliest (but thanks the revisions nothing is permanently lost so all overwritten info can be recovered)
b) the DIFF user interface is brought up and user may try to merge all changes that happened in the meantime into his/her revision before really saving (can be automated - depends on type of data and extent of changes, you can use open source diff tool that is provided as a library to many languages including PHP...)
Well, I've got to get back to work. When I stop rowing, the slave ship just goes in circles.
Wikipedia gets by without locking because it keeps multiple versions. If you really want to do locking, just throw a column in your database called "locked_by" and lock a record with "update foo set locked_by=CURRENT_USER where foo.id=WHATEVER and locked_by is null" and make all your updates conditional like "update foo set bar=baz, ..., locked_by=null where foo.id=WHATEVER and (locked_by is null or locked_by = CURRENT_USER)". Databases have atomic transactions for a reason...
Use the primary key and an integer counter (date time stamps are to messy), apply the update with the primary key and counter as the 'where ' clause, if the update fails someone else has edited the record, and the user will need to refresh and try again. Pre-emptive locking is very messy , I would not recommend it. Often it is a kneejerk reaction to bad operational procedures within the production environment. You need to ask: what are multiple users doing trying to edit the same row at the same time? The counter based scheme sounds harsh but in reality it seldom leads to conflicts when the operations are run in a sensible manner.
You never catch me alive
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.
An HTML text input has various Javascript events that you can use to trigger an AJAX call to the server, which you can then use to check if the record is locked. If you use the Prototype.js library to register events and make AJAX calls, it will handle the browser incompatibilities for you.
If you need to push events to the client about when a record gets locked, then things become harder and you'll need to be creative. You can either poll, or use a combination of polling and blocking on the server. (When you block on the server, the server blocks an incoming HTTP request until an event occurs.)
No, I will not work for your startup
Here is my solution:
1 server emails everyone on internet that record (r) on table (t) on database (d) is about to be modified on host (h) by user (u) at time (hh:nn:ss +-GMT)
2 in the email ask each user to reply with keyword (k1) in the body text if they also wish to edit this record otherwise respond with keyword (k2)
3 wait until you have received a reply from every user
no wait a minute, this isn't going to work - the mail might get tagged as spam and never be seen by the recipient. Step 3 would then wait forever and the original editor might get frustrated.
... don't write a web app using Delphi.... and don't write a an app using Paradox.
Having used both circa 1999 and vouch for the awesomeness of both products (at the time), the world has seriously moved on in regards to web development and database platforms.
There are many viable (and cheaper and far more reliable and easier and....) solutions utilising PHP (and their frameworks), Ruby (and its Frameworks), Java and dare I say (wretch) ASP.
Good luck!
It would seem that the server wouldn't read an incoming message until he was ready to read it. If he was in the middle of an edit, he wouldn't be ready to read the next incoming message. That means you're single threading in the bottleneck where the message is read, but you can multi-thread in the edit process underneath it.
Assuming that users want locking the old fashioned way, where only the first person who opens a record can edit it, you just have to be careful how you go about it.
When a user opens a record, the client just has to ask the server every so often who has the lock. If nobody has the lock on that record, the server adds a row to a lock table indicating the username/IP/whatever of the client, and current time. That row is then returned whenever any client asks who has the lock for that record. If the lock belongs to the asking client, the client will recognize the user as himself and allow the edit; if not, the client can tell the user who currently has the lock.
The key here is that the client will make this request every 10 seconds to keep the lock fresh. Every 30 seconds the server will go through the lock table and delete any locks that are over 30 seconds old. That way if a client ever abandons a record (the browser gets closed, crashes, or whatever), it will only be a minute before somebody else can edit the locked record.
dom
In case you don't need to tell the original user when the record was changed by another user, you can just grab the old value in the SELECT, perform the UPDATE by including the old value in the WHERE clause, and check the result: If it didn't work or you don't get the new value when SELECTing the row, it means that someone else has updated this row while you were busy. Here's an example in SQLite: === CREATE TABLE IF NOT EXISTS mytable (id INTEGER PRIMARY KEY, label VARCHAR); INSERT INTO mytable (label) VALUES ("old"); //Some other user updates this row while you're busy
UPDATE mytable SET label="concurrency" WHERE id=1;
UPDATE mytable SET label="new" WHERE id=1 AND label="old"; //In case your DBMS doesn't return a value so you can tell if it went OK or not, //just reread the row:
SELECT label FROM mytable;
concurrency
===
HTH,
Doing record locking isn't really that tough - there are a ton of ways to implement locking. The easiest is to add a lock and locked by column to your tables and set it whenever the user edits and unset it when they save. If you really want to get slick you can implement a lock table and log user edits (lock set for invoice 12231245 by joe.smoker on 01/01/2010 at 12:32:31 released by system_cleanup_process on 01/01/2010 at 15:33:00).
The tough part is unlocking because web users have a strange way of walking away in the middle of edits. As a result, you'll have to have very strong session management so you can run a clean up process when a user times out that includes removing any locks the user may have active.
About the time you get done with implementing locking, managers will want a way to manually override locks or will ask if you can do field level locking. At which point you are up against the, well, with enough time and money you can accomplish anything problem.
Well... if you give a mouse popcorn...
-- $G
When a user starts to edit a page, send in "edit heartbeats" using ajax. The heartbeat msg changes f.e. by creating a hash of the edited text every 30 sec. If the "edit heartbeat" message does not change over time, f.e. 15min, you release the lock, if another user is asking for an edit.
dude, do your business a favor : hire a professional.
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.
The simplest way is to have some form of persistant storage to record when a user grabs a record for edit. Either a table, file or some shared memory like memcached to hold the record id. Then it's a simpler matter to check that before offering the requested record to the user. You could even implement a messaging / notification system to alert a secondary user when the record is checked back in.
Keep a sessions table with logged in users and their last activity time. This gets updated by a common include file. The file also deletes all sessions > you session time out, so the table is self cleaning. When someone attempts to access a record for editing you look at the record and the sessions table. If the record is locked, and the person is still active, you give read only. If its locked and the person's session has expired (they would not have a session in the db as the first thing the page you are on did would have been to update your session and kill theirs if it was >20 minutes inactive) you claim the record for yourself. The edit page has a cancel button, save and save and exit. Cancel removes the lock then moves on to another page, save saves the data and keeps the lock, save and exit saves the data, removes the lock and moves on. No 2 users can edit the same data that way. This can be implemented with a single field in the table containing editable records, a 2 field sessions table and 50 lines of code over all.
Our users wanted locking, so we did it this way:
- Add a record in a lock table when a user accesses an item in edit mode.
- Always delete any previous locks a user has when a new one is added (one lock per user).
- Delete the user's lock records when the user explicitly logs out.
- A job deletes all lock records for expired sessions (every five minutes).
- Users accessing a locked record get a read only version and a notice that another user has it locked (complete with the other user's email and phone).
The stored procedure for locking a record checks that there isn't one already before adding one and does so in a transaction. We rely on the database making a decision about who gets the lock record to make sure that simultaneous access doesn't cause problems, the theory being that the DB has better code for that kind of thing anyway.
In the case where a user was editing something, timed out, got his lock deleted, another user edits it, and then the user comes back and hits submit... this doesn't have a significant chance of happening in our environment, so we didn't worry about it. Background polling from the browser is a possible but unreliable solution. You should never rely on a well behaved client when designing a web app (or any app). A suggestion I saw elsewhere in this story about using a hidden field with a checksum is a good idea which had not occurred to me. Another possible answer is to record each version separately and include the version being updated in a hidden field on the client, then check on the server that the version you're updating is the latest.
I want my Cowboyneal
Why not just log a timestamp of the last edit a user made to that individual record, and allow x minutes from that point to allow another user to edit the record. Same idea as a comment above but if you use a web farm, stay away from sessions as session state is not persisted between different servers in a web farm.
And if you aren't using a webapp framework which deals with this kind of thing transparently to application business logic, then you need a better framework.
Seriously, look into things like JBoss SEAM. I'm not suggesting that any particular core technology is the preferred choice, but that happens to be one which provides a fairly nice implementation of the concept.
Again in J2EE land, the concept is also more directly supported by EJB3 configurations using optimistic locking (the technical term for checking a counter/datestamp when a record to saved). Entity beans attached to an EntityManager which is configured for optlocking will handle the whole process transparently. If a stale record gets persisted, you'll get an appropriate exception at the SSB (application logic) layer and you can take whatever action you need to in the UI.
These are the kinds of reasons everyone should be using well designed frameworks instead of hacking together any non-trivial webapps on their own. It looks easy, until you start to get into the details.
"Malo periculosam, libertatem quam quietam servitutem." -- Jefferson
If a determinate record is being edited, you set the "lockedby" field in the record, and a "lockexpire" field (say, 30 mins). This is set when the user starts editing the page. This could be done in the same table, or in a specific table for locking purposes. This specific table would have the following fields:
table, recordnumber,user,expires
When the user finishes, the data is written and the locks are removed.
If another user tries to edit a record, the software checks if that record is being "locked" by another user, and posts an error message.
To solve the problem of users trying to access the same record at the same time, you simply insert the "locking" queries inside a transaction. Ta-da!
We had a similar problem where we need locking across parent child records and across multiple web pages.
We handled locking as follows:
When a user goes to edit a record we make a copy of the record making the primary key of the parent record the negative value (e.g. key was 27 copy gets -27) and stamping a timestamp on it and the user who is editing. Then we copy all the child records as well with new keys but referencing the negative parent.
This lets us use the database to hold changes between pages without having a large object model hold the data in the application server. We update the date stamp of the lock every intermediate save to the negative record.
When the user finishes there multipage editing session and gracefully saves or cancels we copy back over the original record their changes and delete the negative copy.
If a second user tries to edit they are told it is locked.
If the first user exits ungracefully (e.g. reboots their PC mid session) we allow the same user to start a new edit session without a lock message but they lose their changes... (we could have given them a choice I guess to keep editing where they left off)
If the lock was held for 2 hours without any updates any user can aquire the lock in case someone shut off their computer and left.
A nightly job checks for abandoned records every night.
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.
Hey, let's keep this civil. Give them some shooter game where they can resolve it.
That sounds great, but dealing with the real world is inconveniently expensive. Employee counseling and the restaffing costs due to unusually high employee homicide rates would probably outweigh the savings in programming cost and reliability.
The new door locks and full-time armed guards in the data center would also be expensive.
Computer science has addressed this issue with a variety of algorithms. It boils down to using mutexs (mutual exclusion). Wikipedia (http://en.wikipedia.org/wiki/Mutual_exclusion) has a number of algorithms that you should evaluate. The biggest problem is that once a lock is acquired, how long do you wait before you realize that the lock will never be released?
I use optimistic locking, but I warn the user when someone might be editing the record. We penalize slow users in favor of faster users.
My app gives the user an 'edit' button. When they click it, they get a timestamp inserted in the record, and it is refreshed (thus they see if someone else is editing). If effect, this timestamp acts like a record lock, but if it is >5 minutes old, it is ignored. This means I don't have to have the server clearing old locks.
Anytime I display the data, the clients looks at the timestamp, and if it is 5 minutes old, I color the field yellow. This warns the user that an edit may be in progress. If they choose to hit the edit button, they risk losing their edits.
Downside: User 1 starts his edit, but takes 6 minutes. User 2 starts his edit @ 5.1 minutes (lock has 'expired') and completes before User 1. Thus User 1 loses his edit - and bitches to me.
Upside: When User 1 bitches, I point out that it is his fault that he didn't complete the task in a reasonable time, and the quicker (busier?) user wins. Solution - quit screwing around when you edit, or you might have to do it twice.
Yes, we have a lot of old timestamps sitting around, but the load on the server is light. We can flush those out easy enough at night.
I'm sure someone will point out something I've overlooked, but it seems to work pretty good for us.
Oh, if you hit the "cancel edit" button, we clear the timestamp, allowing others to edit. So in the case of a lock, user 2 can call user 1 and ask them to cancel the edit. I encourage users to communicate as a way to resolve these issues, rather than code a "my way or the highway" solution.
Place nail here >+
the joomla! approuche is a smart one. when someone start editing an article the core block it and its keep blocked until the current editor save it, if the editor get disconected the article remains blocked until the editor reconect and save it or and admin unblock it. it use ajax to automatic save the article from time to time. maybe you can use something like that but you give every one the avility to unblock a register. cheers
I wrote an article for Delphi Developer magazine back in the late 90's on Paradox in client/server and shared network environments. To summarize, it can be done but you really need to move to a database server. Paradox was designed to be a desktop database, and use for anything else is difficult and unreliable, not to mention simply technically inappropriate for the tool.
Database servers are meant to handle that kind of thing for you. I would suggest Firebird or Interbase if you want a more "Borland" (or whatever Borland is now) approach, or look into MySQL or even MS SQL Server Express.
Write a message to a queue on "lock," clearing the message would be an "unlock." Zend_Queue has a good implementation for reference. Locks "timeout" when the message expires.
There's plenty of literature on this, but a quick read of Martin Fowler's Patterns of Enterprise Application Architecture [http://martinfowler.com/eaaCatalog/optimisticOfflineLock.html] should get you going.
Basically, you keep a version_id for your record.
You include this version_id in your form.
When the user submits, you not only get the record by it's primary key, but also by the version_id (ie "... WHERE version_id = x")
You modifiy the fields and then set version_id to x+1
When the second user tries to commit his changed, version id will no longer match (x instead of x+1)
and you can notify him.
Cheers
http://en.wikipedia.org/wiki/Optimistic_concurrency_control
It's offtopic, I know, but that is NOT the original meaning of "taint". Open a dictionary. There's more information in there than simply definitions. "Taint" is from Middle English (ca. 14-16th centuries) meaning "hue" or "dye". It's the same origin as "tint". Its meaning evolved from there to the idea of having a trace of contamination, infection or corruption.
It wasn't until the late 20th or early 21st century that "taint" came to refer to the anatomical region you're referring to: the perineum.
"It is better to keep your mouth closed and let people think you are a fool than to open it and remove all doubt."
- Mark Twain
Put columns in the table in question called "lockedTime" and "lockedBy". Set them to null. When the user gets a record, set them. When they're done editing, set them to null again.
Write a Stored Proc to run at some interval and unlock files that have been locked for whatever you think is "too long" (could be done once a day).
A user can't edit a record that is locked by someone other than himself. You could get by with just a lockedTime, but lockedBy allows the user to get back to the record if they get disconnected for some reason.
I don't know how your system works, but in every one I've seen, individual users don't have their own DB accounts; there is 1 DB account for the system. It's nice to know who has what record locked.
Maybe I'm naive on how Phoenix DB works? I feel like anyone could have figured this out.
What software, framework, persistence package, and db are you using?
Hibernate, for example, has both optimistic and pessimistic locking. Optimistic is done with timestamps or version numbering.
Pessimistic locking is backed by the db, so in some instances it does nothing. On mysql, using their innodb engine, as an example, all threads trying to access a locked record will actually block until the lock is released. The latter is often problematic in web apps, but is sometimes desireable.
Hope that helps, and if you care to give more info, I might be able to give more specific answers.
Set up a GUID field in a table, with a timestamp and a username
1. hash the md5(table+key)
2. check for a lock
3. insert a lock if it doesn't exist.
4. have ajax check, and pop up a div with partial alpha to make the record readable, but an obvious visual cue that it is locked.
OR, add the lock field to the table itself, and you can just do an Update where lock is null, and then check and see if you got the lock.
meh
This is a stateless web-based application, we we're talking long-running transactions. You're not going to hold a db transaction open while the user goes and eats lunch, has a few water-cooler discussions, and updates his fantasy football lineup (or simply abandons the transaction entirely!). Such a solution does not scale well, because you'll have to hold a db connection and a db transaction open for each user of your application who is modifying data until you can guarantee that he is no longer modifying data. Your database will grind to a screeching halt past a few thousand users.
Since you can't scale an app under that model, ACID is not going to protect you. Consider the following scenario:
1. User A reads record 1 and starts making modifications in his browser
2. User B reads record 1 and starts making modifications in her browser
3. User B finishes first and writes record 1 back to the database
4. User A writes record 1 back to the database, obliterating User B's modifications
Note: ACID was not violated here, and the database is still in a consistent state. Unfortunately, the record probably contains incorrect data, because user A didn't know about user B's modifications, and neither user even knows there was a ever a conflict!
The correct answer in 99% of applications is to use some form of Optimistic Locking. In the remaining 1%, where usage patterns simply cause too much contention for the same data, a pessimistic locking approach would be required.
They don't grade fathers, but if your daughter's a stripper, you fucked up. --Chris Rock
'Locking http://en.wikipedia.org/wiki/Semaphores '
Locking the data? seriously? turn in your degree.
The Kruger Dunning explains most post on
Yet it appears that about 90% of the people developing web apps are entirely sans-clue on the whole subject. Most of these apps I suspect never have ANY real design applied to them anyway, just ad-hoc stuff that starts from at best some templating system.
Ah well, I shouldn't complain. I get to go in and clean up the mess (IE tell them what they have is probably worthless) and get paid for it.
"Malo periculosam, libertatem quam quietam servitutem." -- Jefferson
This works for a small set of users who know each other and can communicate offline easily and comfortably.
In a big company though I might not know who $user$ is, and might not care. I just need to do my work and screw that guy, he can start over.
Build a man a fire, he's warm for one night. Set him on fire, and he's warm for the rest of his life.
OK. Here's what to do.
after reading most of the comments I can sum it up:
Optimistic Locking: described here by many readers, easy to implement, also good idea to implement first even when using other scheme. It will piss off users from time to time, though, because they loose data. This is the choice of the (lazy) developer and/or the cheap customer.
Real row-based or even finer locking: Hard to implement right with all the timeouts, user-infos, keeping unfinished edits over sessions,.... This is the choice of the demanding user that doesn't want to loose any work at all, and the wise customer.
Realtime concurrent editing: not really sure about this.
Ramble on, guys.
Except that the management will not like it if people take locks away from each other all the time and interfere with each others work. The only reason to take a lock away is if the work is not getting done by the other user for a period of time that is too long or if there is another meaningful reason that can be explained to the mgmt. I am sure that if someone is being a pita every day, he'll have difficult time explaining himself. The set of users in my case is about 30, they are even located between different buildings, so it's not like they see each other all the time.
You can't handle the truth.
Once worked on an app that was built upon a "last saved wins" rule. But the remarkable thing was that the rule was applied at the the column level, not the row level. So if User1 and User2 both cached local copies of the same row for editing before either saved and they updated mutually exclusive fields on that row, when User2 committed his/her edits (after User1 did), he/she didn't overwrite the updates made by User1. So basically we generated SQL update statements that only included the columns that were actually changed. If they happened to update some of the same fields ... you just got sort of what you'd expect anyway. Maybe we didn't have the same transaction volume you are dealing with and you could make arguments against that scheme on the basis of the fact that updates committed that way under those circumstances (example above) result in a row that is inconsistent with the expectations of both users since neither saw it on their screen as it is on the database after they are both done ... but it worked very well for us.
http://www.amazon.com/Transaction-Processing-Concepts-Techniques-Management/dp/1558601902/ref=sr_1_1?ie=UTF8&s=books&qid=1253900733&sr=8-1
or any decent transaction processing book that uses him as a reference. Now with cloud computing this is more important than ever.
And just because your DB has row level locking doesn't mean you have transactional integrity. Distributed transactions, ISAM databases, Hibernate, XML databases, race conditions, sloppy programming, poor DB design and outages can all conspire against you.
There is no magic bullet.
putting the 'B' in LGBTQ+
Because you really should look into Optimistic Locking. I'll wait awhile to make sure nobody mentions it and then psuedo out an example.
1. User selects a row to edit. Application pulls all the columns for that row and stores them into the users session.
2. User makes modifications to some fields and submits back to the server.
3. Server issues an UPDATE statement and includes all the original column values in the WHERE clause to only update the record if nobody else has modified it since we started.
SELECT id, color, name, description FROM vehicle WHERE id = '100'
id = 100
color = 'red'
name = 'kia'
description = 'cheap'
User changes the color to 'blue' and saves changes, the app might do something like:
UPDATE vehicle SET color='blue' WHERE id=100 AND color='red' AND name='kia' AND description='cheap'
If anyone else modified a field in the row the update would fail and you can handle it however you like.
If it were me, I would attempt(emphasis on attempt) to avoid the locking problem altogether via DB and system design. Sure you will pay the price concerning the size of your data repository and concerning DB administration, but your transactions will be more data safe and user friendly. I know for some apps this would be impossible to eliminate altogether, but something might be gained from minimizing it. Now if you are in the situation where you are not so much concerned about end user experience, then ignore what I just said. :P
Do away with this darn infestation of WEB apps the web is for looking and reading ect not to use as a commercial data network , also by getting the net OFF most machines you get more work less games less botnets ect ect ect..
We have just removed ALL games and internet of ALL computers in the workplace except for one that deals with emails and even that is restricted to heel and back and guess what output has gone thru the roof
It sounds like you have a decent amount of experience writing software. My thought is, why are you treating this product any differently just because it is a "web" product?
A "web" program is just the same as any distributed access program. The only difference from having a Mainframe with terminals, a web server with browser access and a client/server system is the UI (user interface).
Personally I would say that the same locking system you were using before would probably work fine here. I would add either a time-out on the lock or a way of overriding that lock for when a user either walks away for the day or just hits close without saving.
postgresql has short term transactional record locks as well as long term advisory locks. It has the ability to do paradox style locking if you are so inclined...
So, according to the Oxford English Dictionary, the verb form of "taint", in the late 16th century, meant "To affect (esp. in a slight degree); to touch, tinge, imbue slightly (usually with some bad or undesirable quality)". You're comparing this verb to a noun that refers to the perineum. OED has no mention of the perineum in their entry for the noun form. The wikipedia article cited below has an etymology of unknown origin that describes your meaning. I would suggest that the word meant "to soil" long, long before people started using it to describe the perineum.
It is fascinating how language evolves. It's also possible to see that evolution occur, if you're willing to do even the slightest amount of research.
Data concurrency is certainly one of the most difficult problems you can have in a web application - no, it's the most difficult. In fact, there's a 90 % of chances that... it's one more problem you don't need to solve. If you're just an 'enteprise programer' (ie, you don't work al Google, MS or IBM), chances all your approaches to locking are mistaken and wrong, and you are certainly intruducing locking bugs, which resort to classical programming theories... only per register or per table. And if you're a much better than average programmer, you should know: you almost certainly don't need it, at all.
In the remaining 10 % of chances where you do need to acknowledge data concurrency, and you are certain that you can't avoid it because your carefully designed model would somewhat be invalidated by non-malevolant users, I can suggest you a few keywords: optimistic locking, optimistic concurrency, estate synchronization, transactionality.
But the most important advice of all, one you should use as a golden rule with the heaviest weight, is that you should NEVER, I mean NEVER EVER, ask a DBA to help you: DBAs are alle a bunch of ignorant, incapable, malevolant and stupid bunch of overpaid bureaucrats, who will manipulate whatever you ask them as to create a false of need and rescue, but aren't even remotely interested in helping you - they despise programming to begin with.
The application that I'm working on does not lock the records. The business object remembers that initial values it was loaded with. If those have changed, when the user saves, it presents them with a message that someone else has already edited the record. Sure, that may be annoying to the user, but it really doesn't come up all that often.
It's simple SQL:
update client set unique_id = ?, first_name = ?, last_name = ? where unique_id = ? and first_name = ? and last_name = ?; (assuming no nulls allowed)
In the set clause you use the new values, in the where clause you use the old values. If zero records are updated, you have a conflict.
You may be interested in some software that addresses your problem directly and specializes in virtual locking - it's at www.arcs.us
I suggest OP to check Beej's Guide for mandatory and advisory locks.
Slashdot = Sarcasm
The issue is time granularity. This would be a transaction based system with short granularity. Everyone sees the data nearly real-time and nearly simultaneous open multiple editing. No need to "own" or lock the page.
For most people an interaction / update time could be 1/10 second. Computers can move data much faster. Much like a chat room. Each user's display screen would be updated several times a second- everyone would see the data as it currently is. So you would see typing / editing going on by everyone involved. It would be live, real-time (within 1/10 second, let's say) and constantly updated. When you're done, nothing to do- just close the page, walk away, stand-by, whatever. The record is kept closed by default- only opens for each edit character.
I didn't say it would be efficient, but if optimized and write cached it would do what the requester wants.
That path leads to madness.
Made me laugh!
-kgj
In Ruby on Rails, the column name 'lock_version' magically does optimistic locking for you.
Set the default value to zero and always include a lock version value with each save.
You can take it a step beyond by catching the StaleObjectError and returning a nicely worded error message instead of HTTP Error 500.
Ruby on Rails has a big learning curve, but is wonderful organized. I am kinda a disorganized mess and forcing myself to do things the 'Ruby way' actually ends up saving a lot of time and frustration. It also makes me more employable.