Bug #12153 LOCK TABLES with mysql_query() works wrong
Submitted: 25 Jul 2005 14:10 Modified: 2 Aug 2005 15:34
Reporter: [ name withheld ] Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.12,4.0.13,4.0.14 OS:Linux (Linux Fedora Core 4)
Assigned to: CPU Architecture:Any

[25 Jul 2005 14:10] [ name withheld ]
When I'am locking MySQL ISAM table from PHP env, all other threads still
can use this table for READ/WRITE actions. But I didn't get from
mysql_error() any message, and even cannot access other tables, untill
they not locked too. So it's seems like PHP really thinks that MySQL
locked. From MySQL console I can lock tables succesfuly, and after this lock even PHP cannot access this table.

How to repeat:

mysql_query("LOCK TABLES `test_table_1` WRITE");
mysql_query("INSERT INTO `test_table_1` (`a`) VALUES (1)"); // returns
mysql_query("INSERT INTO `test_table_2` (`a`) VALUES (1)"); // Table was
not locked with LOCK TABLES 
mysql_query("UNLOCK TABLES");

Suggested fix:
with this code I want to show you that all works fine, from side of
thread what lock MySQL table. But really nothing is locked, during this
100 seconds I can edit/read test_table_1, without any problems from
other threads. If I will even remove UNLOCK TABLES command and will use
presistent connection, I will not hang up system. Because nothing is

I tried it on PHP v 4.4.0/5.0.3/snapshots, CLI mode/Apache 2.0.54
compiled as worker/prefork, mysql v 4.1.12,13,14. And everywhere the
same picture.  I tried to compile PHP in different ways, from
--disable-all + some only important keys, to full configs. Also tried to
use budle MySQL libraries and what install on system. But all the same.

Hope on your fast bug_fix.
[31 Jul 2005 19:50] Jorge del Conde
I was able to reproduce this w/4.0.25:

jorge-jorge/mysql_code> mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.25

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database test_db;
Query OK, 1 row affected (0.02 sec)

mysql> use test_db;
Database changed
mysql> create table test_table_1 (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table test_table_2 (a int); 
Query OK, 0 rows affected (0.00 sec)

mysql> exit
jorge-jorge/mysql_code> ./main
Connected with the Server: 4.0.25
3-ERROR: Table 'test_table_2' was not locked with LOCK TABLES

-- main.c:
int main()
  MYSQL_RES     * res ;
  int i;
  int NUM = 1000000;
  char qry[100];
   if ((MySQL = mysql_init((MYSQL*) 0)) && 
       mysql_real_connect( MySQL,"localhost","root", NULL, "test_db", 3306, NULL,0))
      printf("Connected with the Server: %s\n", mysql_get_server_info(MySQL));
    printf("Failed to connect with the server\n");
    mysql_close( MySQL );

 if (mysql_query(MySQL, "LOCK TABLES `test_table_1` WRITE") != 0)
   printf ("1-ERROR: %s\n", mysql_error(MySQL));

 if (mysql_query(MySQL, "INSERT INTO `test_table_1` (`a`) VALUES (1)") != 0)
   printf ("2-ERROR: %s\n", mysql_error(MySQL));

 if (mysql_query(MySQL, "INSERT INTO `test_table_2` (`a`) VALUES (1)") != 0)
   printf ("3-ERROR: %s\n", mysql_error(MySQL));
 if (mysql_query(MySQL, "UNLOCK TABLES") != 0)
   printf ("4-ERROR: %s\n", mysql_error(MySQL));

  mysql_close( MySQL );
  return 0;
[1 Aug 2005 14:00] [ name withheld ]
Yes, you can reproduce it. And it must work so, you cannot use tables without lock, during lock session. It's rule of MySQL locking. But my problem, that in real time from PHP env., during this LOCK I can access this tables, from another threads for READ/WRITE actions. So I cannot LOCK tables. It works ALL like it must, but during lock i can access locked table from other threads.
[2 Aug 2005 10:41] Andrey Hristov
Tried with PHP 5.1.0-b3 but because this relies on the mysql extension the result should not be different with 5.0.x . MySQL 5.0.11-beta. Changed the table to have datetime field and put now(). one can see that after lock table xxx write the second script waits and even when trying to do a SELECT from mysql cmdline client - the lock is respected.
[2 Aug 2005 10:51] Andrey Hristov
Can't reproduce also with MySQL 4.1.12