Pessimistic record locking in a Web App

Posted on 2005-04-28
Medium Priority
Last Modified: 2012-05-05
For concurrent users accessing the same record, I usually employ a Pessimistic locking strategy (ie, the first user that opens the record sets the 'locked' flag to 1, and all subsequent users get read only access until user A closes the record).

The problem is, I've only implemented this on Windows Applications, not web apps.  In a windows environment, I can explicitly control everything the user does, and I know when he leaves the app, but in web programming, I don't have that control.

For example, what happens if the user who locked the record, closes his browser without going through the proper logout channels of my web app.  Now all his records remain locked in the system.  I could employ a script that runs on a timer to release locked records after a certain time period, but this seems like a hack.  In addition, how do I know whether the user is sitting at his computer idle but with the record still open, or whether he's closed the browser window and/or been timed out.

As I'm relatively new to web programming, any help with this sort of thing is appreicated.  I don't have a very solid understanding of Sessions and how to manage a user's login period throughout his use of the app, and that might go a long way toward preventing orphaned record locks.

The client has explicitly requested Pessimistic and not Optomistic locking...as I realize in a web environment, simply storing a last_modified timestamp would prevent this issue altogether...but alas, I'm not the boss :(.

I'm using PHP 5.0.3 and MySQL Beta 5.0.3.

Thanks for your help.
Question by:kbach
1 Comment

Accepted Solution

theandrew earned 2000 total points
ID: 13888869
It looks like you are using the right software (php/mysql). There are several ways do do locking in MySQL. Since the records arent editable directly, you have to to requests (SELECT) and updates (UPDATE...).

What i usually do, say you are changing an account balance from $100 to $200, First go get the account balance to display on the page where they can apply transactions. Say they add a transaction for $100, making the balance $200, you have several ways to update that. You could to an 'UPDATE table SET balance = 200', which isnt very good, since a previous person could have changed it already. Another idea is to 'UPDATE table SET balance = balance+100' so if the balance has change since, it will fix it.

You can also lock the table before applying the changes, apply the changes, then unlock it. Also putting a date/time stamp on each line for last update could help you determine if the record was changed since the last update.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
Suggested Courses
Course of the Month16 days, 5 hours left to enroll

850 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