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]
http://en.wiktionary.org/wiki/taint
Seems to think the "to soil" meaning came first, independent of the slang meaning referring to your perineum.
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.
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.
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.
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.
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.
I don't envy the crazy amounts of code duplication that you clearly had to deal with on a regular basis.
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.
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?
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 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
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.
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.
If you mod me down, I will become more powerful than you can imagine....
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.
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
... 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!
I agree, there is no reason to go with an inferior solution just because it's perceived as simpler. Lock the document when it is opened for editing, refresh the lock timestamp periodically (as long as the user is actually changing the document), release the lock when the user saves the document or when it times out (from user closing the window/tab, browser crashing, inactivity).
Of course version control is a great feature but from my experience users want, and need, simplicity. They want to relate to one version of a file, not an ever growing tree of different versions. Thus, the best implementation of this in my mind is exclusive locking coupled with saving the document as a new entry in the database with an increased version number.
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.
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
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.
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.
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.
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+
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.
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...
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
That path leads to madness.
Made me laugh!
-kgj