Re: Application-based locking with PHP?

Hello Barry, On 17-Sep-01 16:00:15, you wrote: >Just wondering if anyone has tried doing quasi application based locking >with PHP? My scenario is this. I'm constructing a database for my >company to help keep track of trouble tickets, etc with a web-based >frontend. I'm using PHP (obviously =]) for inputing/retrieving data and >have a slight predicament. Since MySQL does table-level locking, if I >put lock statements in my queries, it would cause two problems. One >being the entire table being locked and two if the user just closes the >webpage without exiting properly, the table would remain locked >indefinately. So I'm trying to think of alternatives, one of which is >using global variables to store table/record id's, and then evaluating >the queries based on that information. So if John selects ticket # 5 and >might be updating it, then when Bob attempts to select ticket #5, then >the $id and $table variables would be checked against the variables >stored when john placed his query, and bob would get a message saying >that record #5 is locked or in-use by John and to try later. Is this >feasible or is there a better way to do this? Thanks for your time. The way to do it right is to use transactions. If you really insist on using MySQL, there are several extensions that provide a new table types that support transactions. The Berkeley DB tables are the ones that are officially supported by MySQL developers. You may want to try Metabase which is a PHP database abstraction package that can also create and maintain database schemas from a description of the tables, fields, indexes and sequences defined in specific XML file. It can create MySQL tables suitable for use with BDB transactions. Then, at run-time it is like any other database, you just use functions to disable automatic commit so you can commit and rollback a transaction whenever you want. If your script for some reason exits with a transaction in progress, Metabase is able to automatically rollback the transaction right before your script ends, so you don't lock your database indefinetly. Metabase is freely available here: http://phpclasses.UpperDesign.com/browse.html/package/20 Regards, Manuel Lemos Web Programming Components using PHP Classes. Look at: http://phpclasses.UpperDesign.com/?user=mlemos@acm.org -- E-mail: mlemos@acm.org URL: http://www.mlemos.e-na.net/ PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp --