Forgot your password?
typodupeerror
Databases Programming Hardware

Data Locking In a Web Application? 283

Posted by timothy
from the get-offa-my-cloud dept.
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."
This discussion has been archived. No new comments can be posted.

Data Locking In a Web Application?

Comments Filter:
  • Duct Tape (Score:5, Funny)

    by Anonymous Coward on Thursday September 24, 2009 @10:49PM (#29536219)

    Lots and lots of Duct Tape.

  • Same as bugzilla? (Score:5, Informative)

    by Derek Pomery (2028) on Thursday September 24, 2009 @10:51PM (#29536235)

    Same as bugzilla does. Just use a timestamp or counter on the records so you can tell when an edit occurred while you were editing
    Then you can review the edit.
    If you want, you can use XHR (maybe with a slow load response for performance depending on the number of users) to notify that an edit happened.

    • Re:Same as bugzilla? (Score:4, Informative)

      by Anonymous Coward on Thursday September 24, 2009 @10:58PM (#29536289)

      Exactly, this is how I have done it in every web application I have developed. If someone updates the data while someone else is editing then they will get a message saying someone did an edit. They then get a chance to review the new data and modify their edit if needed.

      NOTE: It is critical that the user not lose their edit. Save that data even if you don't actually do the update. There is nothing more annoying than spending 15 minutes carefully putting in a bunch of data just to have it lost due to someone else editing the same record. Let the user review what happened and then modify (or not) their own data they were putting in.

      • by Dexx (34621) on Friday September 25, 2009 @12:01AM (#29536627) Homepage

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

        • Re:Same as bugzilla? (Score:5, Informative)

          by nahdude812 (88157) * on Friday September 25, 2009 @07:59AM (#29538045) Homepage

          When a client wanted to know while they were working on a record that someone else had it open (they truly wanted the record locked while one user had it up on the screen), we used a LOCKED_BY and LOCKED_UNTIL field on each relevant record. While editing, records are read-only if LOCKED_UNTIL is in the future and LOCKED_BY is not the current user.

          On the edit page, an AJAX call is made on a 10 second interval which updates LOCKED_UNTIL to be +30 seconds (this way even if there are network issues of some sort, three consecutive status updates need to fail in a row). If the browser is closed or the computer blue screens, etc, after 30 seconds the record unlocks itself. When you save the record, LOCKED_BY is nulled, and LOCKED_UNTIL is set to the epoch.

          We also employed a version ID so that if all else fails and your client for some reason stops keeping the record locked (eg you suspend your laptop and come back to it later), when you submit your edits; if anyone else had made edits while your client was unable to keep the record locked, you're still given an indication that another user updated the record. The interval update checks the version ID too (a single SQL statement with PostgreSQL's excellent UPDATE RETURNING syntax) and warns the client if somehow someone else updated the version without this client having been able to maintain the lock - as soon as the next update interval succeeds the user gets notice.

          The ajax call was basically something like:
          UPDATE tbl_something
          SET locked_by = (current_user), locked_until = (time+30)
          WHERE record_id = (record_id)
          AND locked_by = (current_user)
          RETURNING
          locked_by, version_id

          Double check that locked_by is still the current user and version_id is still the known version of this record.

          • Re: (Score:3, Insightful)

            by KillerBob (217953)

            Mod parent up.

            That's basically what I was going to suggest, and I think it's more in line with what the clients of the OP are asking for... in Paradox, when a record was locked it was tagged read-only until it was unlocked (or the lock expired). This way, when you're using a multi-user database access program, one user can open/edit a record, and other users can access the information within, but nobody else can modify it. So if you implement it the way parent is saying, you'll end up with a system that's m

            • Re: (Score:3, Interesting)

              by TooMuchToDo (882796)

              The way that other people are suggesting, honestly, would just annoy me. I'd be *really* pissed if I opened a document, spent half an hour working on it, then committed my changes only to find out that somebody else had been spending time working on it as well. That adds up to a lot of wasted time. If, however, I were given an indication that somebody else was editing it, I could work on a different record without wasting my time.

              I think Google Docs shows an excellent way of handling this. It uses AJAX to tell everyone on that page, spreadsheet, etc. who else is viewing/collaborating on it, and changes are reflected in real time. You don't have to go so far as to reflect changes in real-time, but just seeing who else is working on the doc would be helpful so you could IM them to collaborate and prevent collisions.

          • Re: (Score:2, Insightful)

            Beautifully simple! If I may add: try to keep all this functionality inside your Data Access Layer. That will really make things simpler in the presentation layer (your web pages).
          • Re: (Score:3, Informative)

            by SoopahMan (706062)

            This is a very good solution but it can still paralyze you if someone:
            * Opens an important record
            * Gets distracted
            * Leaves for vacation for 2 weeks

            Now their PC is locked with the page open and constantly polling, the record is locked forever, and people are angry. This can be solved with a message like
            "This record is locked. _Take Control of this record_" - Clicking it would up-end the equasion - in order to keep control the other user has to click a "I'm still editing" link within a minute. This would solv

    • Re: (Score:3, Informative)

      by dindi (78034)

      On top of this you could actually send AJAX requests while editing to see if someone is requesting the data. Carefully, considering performance.

      An other option is to check-out, check-in with a session. In this case of course you need to make sure if a checked-out file's session is still alive.

      • by dgatwood (11270)

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

        But yeah, rolling back and showing both versions in a side-by-side view when collisions occur is a nicer user experience if you're dealing with a lot of users. Using

    • Re:Same as bugzilla? (Score:4, Informative)

      by Zarf (5735) on Thursday September 24, 2009 @11:20PM (#29536421) Journal

      For the record this is called: http://en.wikipedia.org/wiki/Optimistic_locking [wikipedia.org]

    • by Casandro (751346)

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

    • 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/ig

  • This book: (Score:3, Informative)

    by walmass (67905) on Thursday September 24, 2009 @10:53PM (#29536253)
    on Google Books [google.com]. You are welcome
    • by Firehed (942385)

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

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

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

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

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

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

    • by palegray.net (1195047) <philip@paradis.palegray@net> on Thursday September 24, 2009 @11:01PM (#29536305) Homepage Journal
      My company has an internal app that approaches locking in a different manner. When you start updating a record, it uses an AJAX routine to set a lock on the record being updated. As long as you're still on that page, you "have the lock" and other users are notified of this if they attempt to edit the record. Once your changes are submitted, the lock is released automatically. It's possible to "steal" a lock in our model; this may not work for everyone. If you didn't want to allow this, you could incorporate a timeout for locks, whereby the original user would be notified that the lock had expired due to inactivity.
      • Re: (Score:3, Interesting)

        How do you make sure the lock gets released when the page closes? I once investigated this, but determined that I would have to either a.) set a timeout on the lock and have the page update the lock every x seconds, or b.) use the page close event and hope that the user's browser doesn't close unexpectedly.
        • Obviously the first has to be used (as GP said, AJAX is used). But you don't have to look into proprietary web apps, dokuwiki does that too.

    • Re: (Score:2, Informative)

      by omkhar (167195)

      Storing the hash of the original data client side is bad from a security perspective. A malicious user could manipulate the hash as they sought fit. I'd keep the hash in a server side session specific variable. I realize the damage that could be done seems small, but I wouldn't trust *anything* - especially a critical part of your locking mechanism - to a variable that could be manipulated client side.

      • I fail to see how it matters for this particular locking purpose. Please explain what are the gains of scenario 1 gain over scenario 2 from a malicious user perspective:

        1. Mallory makes some changes; another user makes other changes to the same document and saves it before Mallory does. Priory to posting her changes back, Mallory retrieves the new hash, and changes the form value accordingly, thus cleverly escaping your locking mechanism
        2. Mallory makes some changes; another user makes other changes to the same
    • Re: (Score:2, Insightful)

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

  • 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.wik [wikipedia.org]

  • by Shimmer (3036) <brianberns@gmail.com> on Thursday September 24, 2009 @11:12PM (#29536367) Homepage Journal

    Slashdot is hardly the right venue to get a good answer to this question (how the hell did it end up in the Hardware category?), but I've dealt with this a zillion times, so I'll give a pointer to what is very likely the correct answer: optimistic locking [wikipedia.org].

    Hard locks are probably not what you want in a stateless web app. (E.g. What happens if someone locks a record and then is hit by a bus?) Instead, here's how it works:

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

    This is also known in the vernacular as "second save loses". It may sound too harsh, but it is much better than "first save loses and user isn't notified", which is what you get if you have no currency checking at all. And it's also much more web friendly that your old desktop app (which uses an approach that is technically called "pessimistic locking").

    • by gr7 (933549) on Thursday September 24, 2009 @11:26PM (#29536457)

      What shimmer says is exactly what you should do with 2 possible additions. Often people leave themselves in a web page for an hour and then start to make edits. So when the user makes the first edit, use ajax to see if there was already an edit done in the meantime so they know before they make lots of changes.

      Also you should consider using sequences instead of checking if the data changed. Both are good ideas in certain situations. For example with a table that is only edited once every few months, I use a sequence on the whole table. For a table that is changed 100 times per day by 3 different users, either do row based sequences or check to see if the 'from' part of the changes match the database.

    • Re: (Score:3, Insightful)

      by Anonymous Coward

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

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

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

    • by hibiki_r (649814) on Thursday September 24, 2009 @11:47PM (#29536563)

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

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

    • by ArcadeNut (85398) on Thursday September 24, 2009 @11:50PM (#29536579) Homepage

      Slashdot is hardly the right venue to get a good answer to this question (how the hell did it end up in the Hardware category?)

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

    • Re: (Score:3, Informative)

      by MagicM (85041)

      There is one gap in this. If steps 3 and 5 happen at the same time, then steps 4 and 6 happen at the same time, and both User X and User Y could pass the "System checks whether incoming version matches database version" check. Some locking is still required, otherwise it will look to both Users as if they "won".

      • by argodk (640595) on Friday September 25, 2009 @12:29AM (#29536735) Homepage
        Absolutely correct, but that just means that there has to be server-side locks for the commitment phase (4-6), it doesn't impact the client-side. This has an implication for performance of the commitment phase, but luckily, database vendors have been struggling with efficient implementation of commit for years, so using the transaction features of whatever database is used for storage should resolve most of those problems (i.e. check and update the version number in the database in a single transaction).
      • by gullevek (174152)

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

      • You really have to wonder what's going on behind the scenes in some of the database-backed apps that we interact with daily. There are plenty of PHP monkeys that concat SQL to parameters. But there are plenty of others that have just never thought about locking. Or have it wrong. There are subtle concurrency bugs all over the place - the database usually handles it well enough that many developers just never catch on.

        • by aj50 (789101)

          If your users don't do edits on the same data (e.g. a forum) or your webapp isn't heavily used (e.g. internal app for a small company), you can have plenty of concurrency bugs all through your code which never get triggered.

      • by aj50 (789101)

        At some level, where users can read and write data, locking is always required.

        In the most basic case, a user must never retrieve a half written record, therefore the database must ensure that a read either occurs before or after a write, never mid-way through.

        (Note that the database may make some optimizations here as serialization of operations only has to be maintained from the clients point of view. Internally, the database may be updating the record and indexes at the same time that data is being read

    • by cerberusss (660701) on Friday September 25, 2009 @03:42AM (#29537165) Homepage Journal

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

      There's a Firefox extension for that.

      In our company, users' pulses are tethered to the USB bus. The Firefox extension can then use this information. People spend hours in our time accounting system, which has a pessimistic locking scheme. The Firefox extension sends an 'unlock' when the user's pulse stops for whatever reason. We've had buses driving users over, we've had rabid squirrels, a janitor going postal, exploding Sony laptops and a manager doing the 'Godfather-baseball-bat-routine' on an unsuspecting employee. Our time accounting system runs great, we've never had a stray lock.

      • by aj50 (789101)

        All well and good until Firefox (or the whole OS) crashes.

        (Yes, I get the joke, I just take a sick pleasure in pulling it apart)

      • >> In our company, users' pulses are tethered to the USB bus.

        Is this a read-only interface, or can you write a string of zeroes to it?

    • by dkf (304284)

      We use the approach described in a Wiki implementation and it works pretty well. However, I suggest a refinement...

      6. System checks whether incoming version (1) matches database version (2). It does not, so User Y is notified that he cannot save his changes.
      7. User Y fetches version 2 of Record A and tries again.

      If this happens, you should consider trying to do a merge of the two sets of changes; if the merge succeeds (e.g., because the edits are of different parts of the page) then you can commit the merged version instead of throwing it back in User Y's face. The technology for such merges is pretty well known; it's been used in software version control systems like CVS, SVN and GIT for many years.

    • by danger42 (302987)

      (E.g. What happens if someone locks a record and then is hit by a bus?)

      Well, the first thing that happens is the company investigates why there was a bus in their call center.

    • I think your response is a damn good example of the fact that is the right venue to get a good answer to this question.

      • by Shimmer (3036)

        Thanks, but there are lots of important architectural considerations that are necessarily left out such a discussion. It's not much different from asking for legal advice - you might get a lawyer to answer, but don't take that answer as professional legal advice.

    • by Rary (566291)

      Optimistic locking often makes sense if a user will only spend a short period of time on a record before saving.

      However, if I'm going to spend 20 minutes on a record, the last thing I want is to get to the end of that and finally be informed that someone else has been working on the same record this whole time, and now it's up to me to deal with merging the changes. If I'm going to commit that much time to a record, I want to be told beforehand that someone else is working on it. This is an opportunity for

  • by shentino (1139071)

    Interesting.

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

    • Re:heh (Score:4, Insightful)

      by IntlHarvester (11985) * on Thursday September 24, 2009 @11:50PM (#29536581) Journal

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

  • Confluence (Score:5, Informative)

    by goofy183 (451746) <eric.dalquist@noSpAM.gmail.com> on Thursday September 24, 2009 @11:28PM (#29536471) Homepage

    Look at Confluence by Atlassian. When you edit a page they track the edit action. When another user goes to edit the page they are warned that "John Doe is currently editing this page, last edit at date/time". They also do polling via AJAX so if you're working on a page and another user starts actually editing it you see a message on the page "Jane Doe started editing this page". They also save page drafts scoped to the user to help people resolve edit conflicts. It seems to balance things well with not explicitly forcing locks but actively letting users know when they are heading for a conflict.

  • CouchDB (Score:4, Informative)

    by deweller (266610) on Thursday September 24, 2009 @11:35PM (#29536515) Homepage
    Check out CouchDB [apache.org]. It is built around the concepts of distributed (and even offline) databases and handles conflict resolution. It employs optimistic locking.
  • by linuxhansl (764171) on Thursday September 24, 2009 @11:36PM (#29536517)
    Don't take out a database lock (also referred to as pessimistic lock sometimes). Web transactions tend to be long lived and there's typically no easy way to know when the user just abandoned the edit (and hence you would not really know when it save to release the lock, unless it is by timeout or explicit release by the user).

    Instead do optimistic locking... Assume there are no conflicting edits (or that they are at least rare). Then version each row (with a monotonically increasing number for example). At the beginning of the transaction also retrieve the version, and upon save verify that the version did not change - if it has changed there was a conflicting edit in the meanwhile and the current save should be prevented (you could then get fancy and retrieve the current version of the row from the database and show it to the user, etc).

    One can actually show that if the rate of collisions is low optimistic locking even performs better, whereas in scenarios where the contention is high (a significant fraction of transaction result in a conflict) pessimistic database locks performs better.

  • The 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 r

  • 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/m
  • 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.
  • by roman_mir (125474) on Friday September 25, 2009 @12:02AM (#29536631) Homepage Journal

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

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

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

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

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

  • The way I do it (Score:3, Informative)

    by corychristison (951993) on Friday September 25, 2009 @12:12AM (#29536667)

    Although I don't know what your implementation, or even what server-side language or database you use... I'll comment.

    - For the sake of simplicity, add 2 columns to the table you want to be able to lock. Call them `lk` (lock) and `lkts` (lock timestamp).
    - When a user is currently editing the row/document/whatever it is, have an icon of a lock or something to display in the list if someone is currently editing it.
    - When generating the list of 'documents'(/whatever it is), check `lkts` and compare it to the current time. If it is stale (5 mins old), clear it and allow people to edit it. Always allow users to view the data.
    - When a user clicks on the 'Edit' button, change the `lk` column to 1 and `lkts` to the current timestamp (UNIX_TIMESTAMP under sane DB's)
    - Use RPC or XML-RPC to save the document periodically (every 60 seconds or so). Every save, update the `lkts` with a fresh timestamp.
    - When the user clicks "Save and Exit" or "Save and Continue", have it submit the form the old fashioned way, save the data, and set `lk` and `lkts` to 0.
    - Use Javascript to detect how long of a period of time passes for of no-activity. If it goes on past, say, 10 minutes, submit the form (thereby clearing `lk` and `lkts` and allowing other users in to edit)

    This is bottom-of-the-barrel designed for simplicity. No security or anything in mind, but simply something that will work even after a browser crash or someone leaves the computer with a 'document' open and walks away.

    Disclaimer: Just worked 14 hours. Very tired, don't want to go into any more detail. If this makes absolutely no sense to anybody, please discard this message.

  • by FlyingGuy (989135) <flyingguy.gmail@com> on Friday September 25, 2009 @12:15AM (#29536679)

    Optimistic Concurrency

    Both the curse and the blessing of web applications. Most of the work is offloaded to the browser, thus not bogging down the database servers with keeping a ton of row level locks in memory, or even worse, page level locks.

    For the programmers POV you use some back end language, php, java, ruby, python, it matters not, write a program, it launchs, connects to a database, ( no matter how much middle-ware you slap in ) sends it a query, gets the data, returns it for presentation, consideration and subsequent modification ( or not! ) by the user and then the program ends. You are no longer connected to the database, heck your browser is no longer connected to the server!

    Some have mentioned AJAX <sigh...> AJAX is nothing but bundling together a few different bits of tech to do ONE thing, make a call to the server without refreshing the page. No matter how you slice it and dice it, thats all it does, it makes a call through the web server, to launch a program written in one of the afore mentioned languages and it follows the same set of steps, through either the post method or the get method and nothing has changed!

    So you need a scheme to know if you can write to a record without overwriting someone else changes.

    The only real choice is to use a timestamp value, all databases support them, usually down to the millisecond of accuracy. It is a simple process which you can make more complicated as you desire. As many have mentioned, you read the record making sure you get the timestamp of the last update. That timestamp gets sent to the browser along with the data. When the user clicks save the stored procedure that does the actual update then compares the timestamp you are sending with the one on the current record as in "select for update ...." and if the one you are sending along does not match the one on the current record, then your update loses and the stored procedure reports that back and then you deal with the user feedback in any way you see fit. Typically this is done by sending back the record in is new state and telling the user, "sorry, but you have to star over.".

    Now having said that there is nothing to say that you cannot be imaginative with a bit of javascript or something like that, or even with the php array_diff() function or an equivalent in some other language then insert some fields above or below the the data that was previously changed to at least have the conflicting data shown in both forms eg: what it is NOW and what they wanted it to BE.

  • 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
  • by mindstrm (20013) on Friday September 25, 2009 @12:25AM (#29536709)

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

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

  • by Lord Byron II (671689) on Friday September 25, 2009 @12:28AM (#29536729)

    When you are ordering tickets through TicketMaster.com, they hold the seat assignment for you for 10 minutes. If you don't complete the transaction within that time frame, the tickets become public again.

    In your database setting, the user Alice wants to edit the customer Carol's record. The application gives Alice a lock on Carol's record for five minutes. If user Bob tries to edit Carol's record within the five minute window, he gets a message telling him to wait for 3:42 while Alice finishes her edit. When Alice is finished, the lock is released and if she doesn't finish in five minutes, the lock is released anyway and her edits are lost.

    You could also add the ability for the user to set the lock time, within a reasonable window, say 5-15 minutes. Also, consider adding the ability for the user to renew the lock.

    BTW - Paradox is still around? I haven't used it since 1993 or so. Wow.

  • by viking80 (697716) on Friday September 25, 2009 @12:36AM (#29536777) Journal

    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.

    • by grey1 (103890)

      just a small build on this point - it's about what the end-user community needs, not wants.

      Sometimes you'll have to work with them to explain why an alternative is better overall, when they are sure that their option is what they want (=need, as far as they are concerned! and they've designed the app for you! with diagrams...)

  • by drfreak (303147) <dtarsky&gmail,com> on Friday September 25, 2009 @12:41AM (#29536803)

    Locking is a solved problem in most Database Management Systems. I think you are worried about the wrong layer of your application. Web and Application code is most often agnostic to how records are retrieved, updated, and locked for concurrency. For reference, look up the ACID [opensourcearmenia.com] properties of a typical RDBMS.

    • by yolto (178256)

      Problem is that you need to inform the user of locked records and give them options on how to handle them, requiring you to inject some awareness and handling of locking into your application layer.

  • by DamnStupidElf (649844) <Fingolfin@linuxmail.org> on Friday September 25, 2009 @01:25AM (#29536953)
    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...
  • This is how I do it (Score:2, Interesting)

    by frambris (525874)
    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
  • by GWBasic (900357)

    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

  • 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 walki

  • 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, c

  • 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

  • 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

  • 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 ge
  • 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, a

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

  • Here's what to do. (Score:3, Insightful)

    by Animats (122034) on Friday September 25, 2009 @12:44PM (#29540937) Homepage

    OK. Here's what to do.

    1. Demonstrate how Wikipedia locks. Edit something in the Wikipedia sandbox from two adjacent computers, demonstrate what an "edit conflict" looks like, and show how you resolve it.
    2. Demonstrate how a hard-lock version control system, like Microsoft Visual SourceSafe, locks. Show what happens when you try to check out something on one machine that's already locked on another machine. Point out what happens if someone leaves something checked out.
    3. Get your management to decide which approach they want.
    4. Implement.

Administration: An ingenious abstraction in politics, designed to receive the kicks and cuffs due to the premier or president. -- Ambrose Bierce

Working...