[PHP-DB] Application-based locking with PHP?
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Application-based locking with PHP?
PostgreSQL does row-level locking... But I'm more familiar with MySQL, as I presume you are... If you want to rely on MySQL's table locking, but are worried about a user aborting the script, see: http://www.php.net/manual/en/ref.misc.php A combination of ignore_user_abort() and connection_aborted() will allow you to determine if the user has aborted, and unlock the table accordingly. -Original Message- From: Barry L. Jeung [mailto:[EMAIL PROTECTED]] Sent: Monday, September 17, 2001 3:00 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Application-based locking with PHP? 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
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:[EMAIL PROTECTED]] Sent: Monday, September 17, 2001 12:40 PM To: Barry L. Jeung Cc: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
