MySQL InnoDB Locking – Why Can’t Find Locked Table?

innodblockingMySQL

I locked 3 innodb tables with write lock 

lock tables hive.NEXT_COMPACTION_QUEUE_ID write;
lock tables hive.NEXT_LOCK_ID write;
lock tables hive.NEXT_TXN_ID write;

I find out which table is locked through SQL

SHOW OPEN TABLES WHERE `Database` LIKE 'hive' AND In_use > 0;

but only one table (last table I locked) ,why ？

+--------------------+----------------------------------------------+--------+-------------+
| Database           | Table                                        | In_use | Name_locked |
+--------------------+----------------------------------------------+--------+-------------+
| hive               | NEXT_TXN_ID                                  |      1 |           0 |

but when I want to show the first table which I thought isn't locked,it locks!why? is show open tables not correct?

 show create table hive.NEXT_COMPACTION_QUEUE_ID;
ERROR 1100 (HY000): Table 'NEXT_COMPACTION_QUEUE_ID' was not locked with LOCK TABLES

Best Answer

Your queries are equivalent to the following:

UNLOCK TABLES;
lock tables hive.NEXT_COMPACTION_QUEUE_ID write;
UNLOCK TABLES;
lock tables hive.NEXT_LOCK_ID write;
UNLOCK TABLES;
lock tables hive.NEXT_TXN_ID write;

LOCK TABLES implicitly releases any table locks held by the current session before acquiring new locks.

— http://dev.mysql.com/doc/refman/5.7/en/lock-tables.html

The correct way to lock multiple tables is in a single statement:

lock tables hive.NEXT_COMPACTION_QUEUE_ID write,
            hive.NEXT_LOCK_ID write,
            hive.NEXT_TXN_ID write;
Related Question