Transaction locking

Posted on 2006-04-14
Last Modified: 2013-12-24
I am building an application in which multiple users could potentially edit the same record...I am wondering the best way to prevent this from happening.  Usera will click a link and bring up a form...that form already now what record in the DB it will need to update when the form is submitted.  While the form is updated i need to not allow others to update that same record.  When user a opens the form i can set a column in the DB that the record is open there by preventing others from modifying it, but what if the user hits the back button or closes the browser then that record should no longer be locked.  Hopefully this make sense
Question by:jduawa
    LVL 35

    Expert Comment

    It does make sense.

    But that is a standard problem.  You can't avoid it.  The best you can do is a work around such as removing any locks that are older than 30 minutes or an hour or whatever time you want.  Then if the person does not have a lock when they save - you tell them a timeout happened.

    That is the only way to clear locks for editing - by guessing.

    Otherwise you can instead of locking do a complex test to see if it changed in the meantime and only allow save if it did not change.  But this is very complex to do.
    LVL 10

    Expert Comment


    if your records have an updateTime field, you can use that to see if the record has been updated since this user has retreived it. simply gab the update time when the user starts updating, send it to the page as a hidden field, then compare it to the current record before updating. if the time stamp from the users page doesnt match whats in the db, throw an error.
    LVL 18

    Assisted Solution


    Here's a method similar to advisory locking, that is easy to implement.

    When you read the record, keep the query.

    When they submit the form, do the query again and compare to the original.

    If something is different, dont save, let the user know, sorry the record has been modified. Then take them to the form again whe reloaded record.

    Otherwise your good to go.

    Another way, similar to mrichom's method is to have another table, called locks or similar it contains 5 fields.

    1. lockId
    2. tableName
    3. tableRecordId
    4. dateTimeModified
    5. userModified

    Each update / insert to any table, update this table.

    Then similar to above, you get the dateTimeModified at the top of the form, then before saving, make sure it's still the same, much easier comparison. Then you also have the benefit of being able to display something like this.

    Sorry, this record was modified by Bill Gates at 10:45:12 PM

    It also means you don't ever need to add dateTimeModified or userModifed to every other table.

    LVL 35

    Assisted Solution

    Yes those methods do work.  They are the more complex methods I was mentioneing in place of locking.  The sad fact is that you can't get this effect with locks - it requires other measuers like saving the data, query etc. or checking timestamps, etc.

    Locking just can't work for this situation on the web.  Not without guessing involved.

    All good alternative suggestions mentioned so far.
    LVL 16

    Accepted Solution

    You can refine the query check method too. I often store the original values in hidden fields on the form... Then, you can do the update statement and include criteria...

    where colA=OrigColAValue and colB=OrigColBValue
    You would only do this check on the columns that the user is actually updating. This allows for concurrent update of the same record by different users, but only if they are updating different columns. You may need to refine this more if there are other business relations, like a total, or something else that is dependent on multiple columns.

    As already mentioned, if the update doesn't take (no rows updated), then you report back to the user that somebody else already did an update... this is the 'First Update Wins' Method, and I believe it is the most common way to do this.... don't try to lock the records while they are displayed by the user, you'll only run into more problems trying to keep the locks straight.

    I worked on one application where the original designers had an update flag that they set to yes when the user brought  the record up, then cleared it when they did the update, and they were forever trying to come up with ways to unlock records that had gotten left with the flag set because the user just closed the application.  The last update date/time flags work, but you run into a different set of problems, especially on a really busy system where you really can have many updates occuring at virtually the same time.  

    I think the cleanest model is the first update wins model where you store the original values and check them during the update process... if the original values had changed, display an error.
    LVL 1

    Author Comment

    All good methods, I will have to try and implement them...I suppose in most application where you compare the fileds that could have been updated to the originals and the second user actually needed to make a change then they would just have to repoen that record to get "new" original values and at that time they can make changes they would need to...Thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    A web service ( is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
    When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now