Transaction locking

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

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.

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.

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jduawaAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.