The effect of MySQL query timeout on php execution

Recently encountered a problem, that is, under high concurrency, MySQL performance bottlenecks, because PHP is a weak type of language, no type one said. Therefore, when MySQL returns is not the expected result, it causes subsequent logic errors.

1) Thread blocking test

When SQL statement execution is too slow, the number of connections to MySQL is exhausted and new requests cannot be processed.

Test method

Executes the SET global Max_connections=1 statement and opens a long connection in another program to occupy the connection, at which point the MySQL service is no longer available.

The PHP code is as follows:

<?php$con=mysql_connect (' 127.0.0.1 ', ' root ', '); Var_dump ($con); sleep () mysql_select_db (' Test ', $con); $ Cursor=mysql_query ("Select * from ' timeout_test ' where ' ID ' =2 for Update"); Var_dump ($cursor); Var_dump (Mysql_fetch_ Assoc ($cursor)); echo "done!";

Returns the result, both Mysql_connect and mysql_query return false errors

2) Lock blocking test

Restore the normal number of links, execute set autocomit=0; I am using the InnoDB engine, the MyISAM engine has no transaction concept, the value of Autocomit is always 1 under MyISAM.

In the MySQL link, execute the statement:

Mysql> select * from ' timeout_test ' where ' id ' =2 for update;+----+------+| ID | Name |+----+------+|  2 | KK   |+----+------+1 row in Set (0.00 sec)

Next, execute the above PHP and return the results as follows:

Resource (5) of type (MySQL link) bool (false) WARNING:MYSQL_FETCH_ASSOC () expects parameter 1 to be resource, boolean given In D:\test\mysql-index\timeout.php on line 7Call Stack:    0.0002     234472   1. {main} () d:\test\mysql-index\timeout.php:0   51.1252     242496   2. MYSQL_FETCH_ASSOC () D:\test\mysql-index\ timeout.php:7nulldone!  PHP Warning:  mysql_fetch_assoc () expects parameter 1 to BES resource, boolean given in D:\test\mysql-index\timeout.php On line 7PHP Stack trace:php   1. {main} () d:\test\mysql-index\timeout.php:0php   2. MYSQL_FETCH_ASSOC () D:\test\mysql-index\timeout.php:7

Mysql_query returns FALSE, which will return NULL if you force a function to get the result using MYSQL_FETCH_ASSOC, etc.

3) Summary

Function name	Thread blocking	Lock blocking
mysql_connect	The function value returns false and throws a warning level error and does not terminate the program.	return resource (3, mysql link), normal
Mysql_query	The function value returns false and throws a warning level error and does not terminate the program. If Mysql_connect returns false, Mysql_query will not reconnect to MySQL even if the connection is released	Returns false, but does not throw an exception. For normal mysql_query, execution failure returns false. This error should be judged in the procedure.

The effect of MySQL query timeout on php execution