Pessimistic record locking in a Web App

Posted on 2005-04-28
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 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
    LVL 1

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Adobe Dreamweaver CC 2015

    Adobe Dreamweaver Creative Cloud is used by web designers and front-end developers and allows you to visualize your site in real-time as you code. This course covers exam objectives for the Adobe Certified Associate (ACA) certification.

    Suggested Solutions

    The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    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…
    The viewer will learn how to dynamically set the form action using jQuery.

    737 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

    20 Experts available now in Live!

    Get 1:1 Help Now