Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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,

share|improve this question

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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