Forum Moderators: coopster
I have a MySQL table that has a column called RunningScriptID with an integer. Every time the script runs, it updates this to be RunningScriptID = RunningScriptID + 1.
Later down in the script, there is an infinite loop that detects if RunningScriptID ever changes, and if it does, then it dies so the next script can run. This means that every time a new instance of the script is started, the old one should die.
I've tested this in Toad by simply starting the script and then changing the RunningScriptID value manually. This works in making the script stop execution.
It won't work, however, when I try to make another script force it to stop. If I open two instances of the script, the first one never stops, even though it should. It appears to be blocking on the update statement that increments RunningScriptID.
Here's some basic pseudocode that shows what I'm trying to do. The issue lies in the 3rd line of code, the update statement. This never actually executes until the first script stops running. Remember, two instances of the script below should run at the same time and cause the first one to stop, but it doesn't.
<?php$sql = new SQL();
$sql->query("LOCK TABLE ImageQueueProcessing WRITE");
$sql->query("UPDATE ImageQueueProcessing SET RunningScriptID = RunningScriptID + 1");
$sql->query("SELECT RunningScriptID FROM ImageQueueProcessing");
$row = $sql->getRow();
$scriptID = $row['RunningScriptID'];
$sql->query("UNLOCK TABLES");
$currentScriptID = $scriptID;
while($currentScriptID == $scriptID)
{
//start time limit over at each loop iteration
set_time_limit(0);
//lock the queue table
$sql->query("LOCK TABLE ImageQueue WRITE");
//the processing of the script goes here
//done, unlock tables and continue
$sql->query("UNLOCK TABLES");
//just in case there is nothing in queue waiting, don't loop very, very fast. 1 second delay
sleep(1);
$sql->query("SELECT RunningScriptID From ImageQueueProcessing");
$row = $sql->getRow();
$currentScriptID = $row['RunningScriptID'];
}
?>
Does MySQL automatically lock tables for the entirety of a PHP script being run?
I actually even commented out all queries that locked tables, and the script still doesn't work. It hangs at the UPDATE statement near the top until the first script finishes.
Can PHP not run two instances of the same script at the same time? I put die() on the top line after running the first script (while it was running). Even though it was on the first line, the script waited forever until the first one finished. After the first one finished, the die() immediately was called and the second script finished as well.
No code was executed on the second script instance while the first was running.
To verify this, I made a second copy of the script called whatever2.php, and after running whatever.php and whatever2.php, the code worked exactly as planned. This is a "hack" however and NOT how I want it to work.
Why does PHP behave this way and is there any way around it?
It can, otherwise you could only have 1 person on your website at any given time ;)
something is funky, not totally sure where to look.
Maybe that IS the way PHP/Apache works yet nobody notices?
I'm at a loss of words. I have a stupid hack with cron running 2 different scripts every other alternating minute.
maybe a logic change could fix the issue
Even running just 2 instances of generating the image slowed the server down more than I like. With 1 million+ monthly page impressions, this is a necessity since many people are viewing the site at the same time.
