RE: [PHP-DB] Application-based locking with PHP?

Justin/Matthew, thanks for your replys. After some more research, it looks like MySQL does have some provisions for doing what I need, either thru constructive queries which can emulate row/column leveling locking as seen here: http://www.mysql.com/doc/C/o/Commit-rollback.html Or using cooperative advisory locking as mentioned here: http://www.mysql.com/doc/M/i/Miscellaneous_functions.html Regardless, this has become more of a MySQL list and I will post there from now on. Just wanted to pass on my findings in case anyone else was interested. Thanks for your time. > -----Original Message----- > From: Justin Buist [mailto:jbuist@tridenttechnology.com] > Sent: Monday, September 17, 2001 12:40 PM > To: Barry L. Jeung > Cc: php-db@lists.php.net > Subject: Re: [PHP-DB] Application-based locking with PHP? > > > So long as you keep any referencial integrity from being broken by two > nearly simultaneous updates I don't really see much of a > problem. If two > users update a trouble ticket what's the big issue? Perhaps a > notification could be displayed on the update confirmation > page letting > one of the user's know that somebody else updated it while > they also did. > > Take a look at bugzilla -- it chugs along just fine it seems > w/out any bug > locking mechanism. > > Probably not the ansewr you were looking for, however if you're still > hellbent on getting a real locking mechanism in place you may > wish to take > a look at SysV semaphores at > http://www.php.net/manual/en/ref.sem.php. > > <Overkill solution> > If you're familiar with using them in C you should be able to > pick right > up on it; if not then you may wish to consult manpages of whatever OS > you're using (I assume *nix) for the C counterparts: > > man 2 semget > man 2 semctl > man 2 semop > man 5 ipc > man 3 ftok > > Even if you use them, which should be more trustworthy than > DB locking, > you end up with the problem of unlocking it after the user has exited. > Perhaps you could use the shared memory functions to insert a > timestamp at > which the semaphore expires, store that stamp in session > variable, then > before the update compare the session variable with the value > in shared > memory. If they don't match they've lost their lock because > they took too > long to update and somebody else now has the semaphore and is > in control > of the update. Rather than lock on the ablitity to update > the DB though > you have to lock on the ability to look at and update the > shared memory > segment. I don't think PHP has an application-wide variable scope, so > shared memory is probably your only option. And hey, if > you're dealing > with SysV semaphores anyway you might as well just do the > whole thing in a > SysV IPC style. :) > </Overkill Solution> > > If it's really -really- important, that's the only truly > solid option I > see. If it's not this important the I'd dump it, let people > muddle with > the same ticket at once and let them sort their differences out later. > > Justin Buist > Trident Technology, Inc. > 4700 60th St. SW, Suite 102 > Grand Rapids, MI 49512 > Ph. 616.554.2700 > Fx. 616.554.3331 > Mo. 616.291.2612 > > On Mon, 17 Sep 2001, Barry L. Jeung 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. > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > > For additional commands, e-mail: php-db-help@lists.php.net > > To contact the list administrators, e-mail: > php-list-admin@lists.php.net > > > >