Mysql – Why can’t I get a lock on an InnoDB table

innodblockingMySQL

I have problem with lock table command: LOCK TABLE v3_cam_date WRITE;
the command always running with state: "Waiting for table metadata lock".

Try to find out which thread was hold the lock on the table v3_cam_date by using command:

SHOW OPEN TABLES FROM reportingdb 
 LIKE '%v3_cam_date';

Database    Table         In_use    Name_locked

reportingdb v3_cam_date 0   0

(In_use=0 => have no any lock on the table).
And I can't get write lock only on the table v3_cam_date while can get write lock on other tables.

Try to SHOW FULL PROCESSLIST;
return about 30 thread with idle state.

Thanks,

Best Answer

According to the MySQL Documentation on MetaData Locking

If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

Somone submitted this state as a bug, but the bug report claims it is not a bug.

Both of these links describe DDL (Data Definition Language) against a table as a contributing factor. What is interesting to keep in mind is that doing something like TRUNCATE TABLE is, in reality, DDL. We have to aware of any SQL we run. We may think it is SQL that is part of a transaction when, in fact, it is DDL. TRUNCATE TABLE is an example of DDL we think is DML. This can cause current transactions to commit and may have unpredictable results when it comes to writing binary log events as the first link states:

Before MySQL 5.5.3, when a transaction acquired a metadata lock for a table used within a statement, it released the lock at the end of the statement. This approach had the disadvantage that if a DDL statement occurred for a table that was being used by another session in an active transaction, statements could be written to the binary log in the wrong order.

Related Question