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
