Hi,
I'm trying to understand LOCKing mechanism, but it is not enough described in the
manual, so I have to test it myself.
To see what happens when I use tables I haven't locked, I created a PHP script like
this:
$sql = "USE fakturace"; if(!$res =
mysql_query($sql,$spoj)){ decho(' A '.$sError = sql_error(mysql_error(),$sql)); }
$sql = "LOCK TABLES $gt_firmy WRITE"; if(!$res = mysql_query($sql,$spoj)){ decho('
A '.$sError = sql_error(mysql_error(),$sql)); }
$sql = "SELECT * FROM $gt_firmy AS f"; if(!$res = mysql_query($sql,$spoj)){
decho(' B '.$sError = sql_error(mysql_error(),$sql)); }
$sql = "INSERT INTO test SET id = NULL"; if(!$res = mysql_query($sql,$spoj)){
decho(' C '.$sError = sql_error(mysql_error(),$sql)); }
$sql = 'UNLOCK TABLES'; if(!$res = mysql_query($sql,$spoj)){
decho(' Z '.$sError = sql_error(mysql_error(),$sql)); }
The result is expected:
B MySQL error: Table 'f' was not locked with LOCK TABLES
SQL: SELECT * FROM firmy AS f
C MySQL error: Table 'test' was not locked with LOCK TABLES
SQL: INSERT INTO test SET id = NULL
The output doesn't change, until PhpMyAdmin sends
SHOW TABLE STATUS FROM fakturace
This query hangs on the server and no other query to database fakturace can be done.
Is that ok?
That's not all. When I did SHOW PROCESSLIST, I got this:
+----+-----------+-----------+-----------+---------+------+--------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+----+-----------+-----------+-----------+---------+------+--------+------------------------------------+
| 1 | fakturace | localhost | fakturace | Sleep | 716 | | NULL
|
| 2 | fakturace | localhost | fakturace | Sleep | 606 | | NULL
|
| 5 | fakturace | localhost | fakturace | Sleep | 549 | | NULL
|
| 7 | fakturace | localhost | fakturace | Sleep | 472 | | NULL
|
| 9 | root | localhost | zona3d | Query | 410 | Locked | SHOW TABLE STATUS
FROM `fakturace` |
| 10 | root | localhost | NULL | Query | 47 | Locked | SHOW TABLE STATUS
FROM fakturace |
| 11 | fakturace | localhost | fakturace | Query | 30 | Locked | SELECT * FROM
firmy AS f |
| 12 | root | localhost | NULL | Query | 0 | NULL | show processlist
|
+----+-----------+-----------+-----------+---------+------+--------+------------------------------------+
8 rows in set (0.00 sec)
So I killed that threads what locked the table: kill 1; kill 2; kill 5; kill 7;
+----+-----------+-----------+-----------+---------+------+--------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+----+-----------+-----------+-----------+---------+------+--------+------------------------------------+
| 1 | fakturace | localhost | fakturace | Killed | 1036 | | NULL
|
| 2 | fakturace | localhost | fakturace | Killed | 926 | | NULL
|
| 5 | fakturace | localhost | fakturace | Killed | 869 | | NULL
|
| 7 | fakturace | localhost | fakturace | Killed | 792 | | NULL
|
| 9 | root | localhost | zona3d | Query | 730 | Locked | SHOW TABLE STATUS
FROM `fakturace` |
| 10 | root | localhost | NULL | Query | 367 | Locked | SHOW TABLE STATUS
FROM fakturace |
| 11 | fakturace | localhost | fakturace | Query | 350 | Locked | SELECT * FROM
firmy AS f |
| 12 | root | localhost | NULL | Query | 0 | NULL | show processlist
|
+----+-----------+-----------+-----------+---------+------+--------+------------------------------------+
But the queries SHOW TABLE STATUS are still hanging and after a while whole computers
gets stunned, nothingchanges in processlist and the processor runs at 100% (P4 1.5
GHz). While writing this mail, thread id # 11 changed to Reopen tables, then Waiting
for tables, then NULL.
I don't understand it at alll as I am not familiar fwith locking, but i think this
behavior is bad. Should I report this as a bug? I am using 3.23.58 and I DON'T want to
install newer, because 3.x is running on servers I use for hosting.
Thanks, Ondra
