Update Delayed on Mysql and PHP
Wouldn't it be nice if we could use UPDATE DELAYED in mysql just like we do use INSERT DELAYED? Unfortunately, the work around mysql created for this is to insert a record into the events table in order to separate UPDATE statement into another thread. There is a large overhead to inserting to then execute an update.
If you are using PHP with mysql, there is another work around. PHP has a function called, register_shutdown_function, which will execute a function during the shutdown of processing a script file. So if we add an UPDATE LOW_PRIORITY sql statement wrapped in a function that is passed to register_shutdown_function, we can allow the user to receive the page without delay while the UPDATE statement waits until all locks on the table has been release before proceeding.
function update_delayed()
{
$sql = "UPDATE LOW_PRIORITY table_name SET col1 = 'something'";
mysql_query($sql, $conn);
}
register_shutdown_function('update_delayed');
I wonder if there are even better ways to handle this problem.
April 15th, 2010 - 10:26
I think this is a great idea and I’m gonna try it out. Tired of seeing a bunch of stuff which doesn’t really even matter to the user, on my show processlist
April 2nd, 2009 - 10:52
No, this is to mimic a Mysql query queue by using apache threads as a queue. Presumably, when the script processing is shutting down, all your output to the browser has been sent. But the script will run the update_delayed function which will wait until mysql sees that the table is no longer being locked by any other higher priority queries. Apache will then keep that process until that happens or if timeout setting has reached. Therefore, it is also crucial to set_time_limit on your script to something higher than your maximum expected congestion time. If not, your script might timeout while waiting for mysql and your update will never happen.
This is certainly a hack. It would be much nicer if mysql implemented UPDATE DELAYED.
April 2nd, 2009 - 03:29
… and this doesn’t wait MySQL to return a result (i.e. behaves exactly the same to PHP as “insert delayed”? If so, that’s awfully clever.