[PHP-DB] Mysql: LOCK TABLES
Hi, I've got question on using LOCK TABLES with MySQL and PHP. I don't have a great understanding of LOCK TABLES, but I'll lay out my situation: I've got a table and a PHP function to rebuild several columns in this table. I need to read these columns from the database (all of them) and recalculate the proper values, then UPDATE the rows with the new values. So, two queries, a read query, then a write query. This is a recurisve function in PHP, so it can't be done in one MySQL query unfortunately. I need to keep the values of these columns from changing in-between the read and write queries. As I understand it this is exactly what a WRITE lock is for. Now, to my question. What does my PHP function see if it goes to lock a table, and fails? Does the query itself fail, requiring me to Loop+sleep my application until it doesn't? Or does mysqli_query() not return until it has successfully locked the table? If it's one of these, can I force it to act like the other? Another thing, This table could possibly get *huge* in the future, if so I would probably need to loop through the results of the read query and RUN the UPDATEs as soon as my PHP app knows it to save on memory usage. Thanks in advance, Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql: LOCK TABLES
At 09:01 PM 5/1/2005, Chris wrote: Hi, I've got question on using LOCK TABLES with MySQL and PHP. I don't have a great understanding of LOCK TABLES, but I'll lay out my situation: I've got a table and a PHP function to rebuild several columns in this table. I need to read these columns from the database (all of them) and recalculate the proper values, then UPDATE the rows with the new values. So, two queries, a read query, then a write query. This is a recurisve function in PHP, so it can't be done in one MySQL query unfortunately. I need to keep the values of these columns from changing in-between the read and write queries. As I understand it this is exactly what a WRITE lock is for. Now, to my question. What does my PHP function see if it goes to lock a table, and fails? Does the query itself fail, requiring me to Loop+sleep my application until it doesn't? Or does mysqli_query() not return until it has successfully locked the table? If it's one of these, can I force it to act like the other? Another thing, This table could possibly get *huge* in the future, if so I would probably need to loop through the results of the read query and RUN the UPDATEs as soon as my PHP app knows it to save on memory usage. Thanks in advance, Chris Chris, So your user LOCKS the table and then power fails, browser crashes, someone else tries to run same function ... etc. MySQL is so fast on indexed queries, are you certain this is information that has to be calculated and stored, rather than fetched and displayed? Of course you've not described the data or the calculation, so my question may be out of line. Generally speaking, though, it's not a good idea to lock either rows or tables in Internet apps. Regards - Miles Thompson -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Mysql: LOCK TABLES
Miles Thompson wrote: At 09:01 PM 5/1/2005, Chris wrote: Hi, I've got question on using LOCK TABLES with MySQL and PHP. I don't have a great understanding of LOCK TABLES, but I'll lay out my situation: I've got a table and a PHP function to rebuild several columns in this table. I need to read these columns from the database (all of them) and recalculate the proper values, then UPDATE the rows with the new values. So, two queries, a read query, then a write query. This is a recurisve function in PHP, so it can't be done in one MySQL query unfortunately. I need to keep the values of these columns from changing in-between the read and write queries. As I understand it this is exactly what a WRITE lock is for. Now, to my question. What does my PHP function see if it goes to lock a table, and fails? Does the query itself fail, requiring me to Loop+sleep my application until it doesn't? Or does mysqli_query() not return until it has successfully locked the table? If it's one of these, can I force it to act like the other? Another thing, This table could possibly get *huge* in the future, if so I would probably need to loop through the results of the read query and RUN the UPDATEs as soon as my PHP app knows it to save on memory usage. Thanks in advance, Chris Chris, So your user LOCKS the table and then power fails, browser crashes, someone else tries to run same function ... etc. MySQL is so fast on indexed queries, are you certain this is information that has to be calculated and stored, rather than fetched and displayed? Of course you've not described the data or the calculation, so my question may be out of line. Generally speaking, though, it's not a good idea to lock either rows or tables in Internet apps. Regards - Miles Thompson This is a Modified Pre-Order Tree traversal.. So I have a Left value, a Right value and a Parent ID value. The Left and Right numbers are mutually unique and used to define a parent-child relationship (and order as well). The function is a 'corruption recovery' function. If the left-right values have duplicates or gaps certain aspects of my app don't work properly. So I need to be able to 'Rebuild' the tree. This rebuilding is recursive and, as far as I know, impossible to do in a query. So I need to read the tree from the database as best I can, then reassign valid values to the 3 fields. If, by chance, it *is* possible to do it in one query, I'd jump on it. As far as I could tell from the MySQL docs, The LOCK would unlock when the connection dropped (if the user hits cancel, or server crashes it would drop); If the server drops uncleanly I've probably got a lot more things to worry about than a tree structure being invalid Chris I -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
