PHP MySQLi timeout on locked tables?

Question

Hi guys

I have a weird problem with mysqli timeout options, here you go:

I am using mysqli_init() and real_connect() in order to set MYSQLI_OPT_CONNECT_TIMEOUT

$this->__mysqli = mysqli_init();
if(!$this->__mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT,1))
    throw new Exception('Timeout settings failed')

$this->__mysqli->real_connect(host,user,pass,db);
....

Then I am initiating query on locked table (LOCKE TABLE users WRITE) and its just hanging, ignoring all my settings even:

set_time_limit(1);
ini_set('max_execution_time',1);
ini_set('default_socket_timeout',1);
ini_set('mysql.connect_timeout',1);

I understand why set_time_limit(1) and max_execution_time is ignored but why other timeouts and especially MYSQLI_OPT_CONNECT_TIMEOUT are ignored and how to solve it.

I am using PHP 5.3.1 on Windows and Linux boxes, please help.

Answer 1

MYSQLI_OPT_CONNECT_TIMEOUT seems to configure the timeout at connect-time :

*connection timeou*t in seconds (supported on Windows with TCP/IP since PHP 5.3.1)

Here, you are trying to execute a query on a locked table... Which means you have a query that takes a lot of time (like forever) ; but you are already connected to the database.

So, what should be configured is not the connection timeout ; but some "query timeout".

Not sure how to set that "query timeout", though...

Maybe the MYSQLI_CLIENT_INTERACTIVE flag for mysql_real_connect could help, in a way or another ?

Answer 2

There's innodb_lock_wait_timeout. But as the name suggests it's only for InnoDB tables.

The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Answer 3

In addition to Pascal MARTIN's answer:

PHP is sleeping until the query completes - so anything you've configured for PHP is ignored. If the query ever does return, then PHP will wake up and continue processing - at which point it will realise it has run out execution time and end abruptly - will it release the locks it has acquired? Maybe.

One solution would be to implement your own locking schema, e.g.

$qry="UPDATE mydb.mylocks SET user='$pid' WHERE tablename='$table_to_lock' AND user IS NULL";
$basetime=time();
$nottimedout=5;
do {
   mysql_query($qry);
   $locked=mysql_affected_rows();
   if (!$locked && $nottimedout--) sleep(1);
} while (!$locked && $nottimedout);
if ($nottimedout) {
   // do stuff here
}
mysql_qry("UPDATE mydb.mylocks SET user=NULL WHERE tablename='$table_to_lock' AND user='$pid'";

I guess a neater solution would be to implement this as a trigger on the table - but my MySQL PL/SQL is a bit ropey.

C.