[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Transaction locking

Posted on 2006-04-14
Medium Priority
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

ID: 16455680
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

ID: 16456143

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

Plucka earned 332 total points
ID: 16458243

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.

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 35

Assisted Solution

mrichmon earned 332 total points
ID: 16458257
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

RCorfman earned 336 total points
ID: 16462409
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.

Author Comment

ID: 16468801
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/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…
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Integration Management Part 2
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses
Course of the Month20 days, 1 hour left to enroll

873 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