Pessimistic record locking in a Web App
Posted on 2005-04-28
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.