PHP: Skip MySql Query if it takes longer than a second

Question

I have a php page that needs to write to a database sometimes at the beginning of the script.

During a scheduled mysqldump backup, the page isn't accessible, because it is trying to write to a table that is locked.

During the lock, MySql queues those sql updates, and after the backup is complete, they do get executed as desired. Yet my php page won't display until the backup is complete.

However, since the updates do not return anything to the script, I wish they wouldn't hang up my script during the lock; I hope there's a way I can tell php to just send the sql update to mysql and don't worry about confirmation that the update completed.

I want php to send the query to mysql, and then just continue on to the next line of php if the update doesn't complete in one second.

I'm running php on a windows server, so I understand that some type of threading is not an option.

If there is way to set a timeout on that query (specifically), that might be ideal, but I don't want to renege the sql update altogether if it times out. I still want MySQL to process it after the lock is gone.

What would you recommend (given that removing the sql updates altogether is not an option)?

Not possible. PHP has no control over the actual query call mechanics. PHP is not threaded, regardless of which OS it's running on. — Marc B
– Marc B, Commented Jun 9, 2012 at 5:08
If PHP has some type of timeout function that could encapsulated any other function-call while placing a timeout on the encapsulated function, would that technically be multi-threaded? — Lonnie Best
– Lonnie Best, Commented Jun 9, 2012 at 5:28
No. PHP has no support for threading whatsoever, and most likely will never become multithreaded without a fundamental rewrite of the whole language - e.g. PHP v6. Most of the PHP core libraries are not thread safe. — Marc B
– Marc B, Commented Jun 9, 2012 at 5:32
Are you aware of any solution to where I can specify within the sql statement to return true after one second if the update hasn't completed? — Lonnie Best
– Lonnie Best, Commented Jun 9, 2012 at 5:37
nope. nothing within PHP. Your best bet might be to put in a queueing system - set a flag when the dump starts, any upates which come in while the flag's hot get stuck in a queue, then process then after the flag's disabled. — Marc B
– Marc B, Commented Jun 9, 2012 at 5:43

Ismael Vacco · Accepted Answer · 2012-06-09 05:21:13Z

3

One solution, using PDO, start a connect with timeout, begin a transaction and case you have lock, the timeout will close the connection and will be rollback.

For timeout in PDO, to use PDO::ATTR_TIMEOUT .

answered Jun 9, 2012 at 5:21

Ismael Vacco

1,0301 gold badge10 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lonnie Best · Accepted Answer · 2012-06-09 07:21:07Z

For others that may come across this thread, I ultimately wrote this function to detect if the table is locked, and only do the SQL updates if it is NOT locked:

function isLocked($databaseName,$tableName)
    {
        $sql = "show open tables from `".$databaseName."` where `table`=`".tableName."` and In_use > 0;";
        $result = @mysql_query($sql);
        if (mysql_num_rows($result) != 0 )
        {
            return true;
        }
        else
        {
            return false;
        }
    }

(note: This function assumes the database connection is already established)

This works, because I've modified the code so that the sql updates will ultimately get done the next time the page is loaded without a lock in place.

Lejo · Accepted Answer · 2012-06-09 05:21:39Z

1

i think the only way to do that, is split you script in pieces and using ajax calls, you can send the big update and continue with the rest of the script that doesn't depend with that process, if all script depend of the result of the update, the only way is make a nice loading ...

answered Jun 9, 2012 at 5:21

Lejo

1,0219 silver badges12 bronze badges

1 Comment

Lonnie Best Over a year ago

That's a good idea, but I need these sql updates to happen, even if the user doesn't have javascript enabled.

Collectives™ on Stack Overflow

PHP: Skip MySql Query if it takes longer than a second

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related