0

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
  • Yikes! LOCK TABLES on InnoDB tables? Why? – Rick James Oct 9 '16 at 5:02
  • See if SHOW ENGINE INNODB STATUS; answers your question. – Rick James Oct 9 '16 at 5:02
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.