On Mar 6, 2012, at 5:27 PM, Dragos CHIRIAC wrote:
> I have some issues with MySQL 5.5.20 ().
> I know "If the server acquires metadata locks for a statement that is syntactically
> valid but fails during execution, it does not release the locks early". But this is not my
> problem. My problem is that I have long time (2-10 minutes) of metadata lock on certain
> tables that do not seem to have any active (insert/update) transaction or DDL statemets on
> them (at least not in the innodb engine or processlist).
> This only happens on heavy load.
> First of all I would like to know how do I find the transactions that create the
> metadata lock.
> They do not show up in the "show engine innodb status\G".
> The :
> select * from performance_schema.rwlock_instances where write_locked_by_thread_id is
> not null or read_locked_by_count is not null;
> select * from performance_schema.mutex_instances where locked_by_thread_id is not
> Are (almost) always empty even with 500 queries waiting for the metadata lock.
> (I guess this an expected behaviour, as the metalocks are not actually part of the
> innodb engine).
> Also "A metadata lock on a table prevents changes to the table's structure." But I
> have hundreds of :
> 61034 | xxx | 192.168.1.240:55481 | xxx | Query | 832 | Waiting for table metadata
> lock | SELECT DISTINCT `e`.`attribute_set_id` FROM `catalog_product_entity` AS `e`
> INNER JOIN `catalog_pro | 0 | 0 | 1 |
> | 61035 | xxx | 192.168.1.240:55483 | xxx | Query | 832 | Waiting for table
> metadata lock | SELECT DISTINCT `e`.`attribute_set_id` FROM `catalog_product_entity` AS
> INNER JOIN `catalog_pro | 0 | 0 | 1 |
> piling up during the infamous metalock.
> So basically the medatada lock prevents me from "SELECT-ing". Is this the intendend
> behaviour ? (should this be a full/exclusive lock, or a read-lock ?)
> I attach the output of show processlist and innodb status. I am puzzled, as I have
> many SELECTs/tables that are "Waiting for table metadata lock" that should not have any
> kind of metadata lock.
> Maybe you can shed some light into this issue, and sorry for the size of the file
> attached , but as I said , it only happens under load. Maybe show me WHAT statements
> create the metadata lock ... so I can avoid locking ... at least the reporting (SELECT)
> Seems related to :
> Except that this basically freezes my database for minutes for most of the usual and
> basic operations.
> The my.cnf is my-huge.cnf, innodb enabled, and innodb_buffer_pool_size = 7G out of 12
> (if it helps).
> [not sure if rellevant]
> Some say that this kind of bahaviour can be caused also by metadata corruption
> (broken tables, that still work or smth like that). There is anedoctal evidence that
> dropping/reimporting the database, or running certain (3rd party) repair tools fix this
> kind of behaviour . If that is the case ... how do I find and "repair" the said tables
> ... ( probably this will be the next "dead chicken" that i'll wave, as i'm out of ideas
> ) .
> PS: (As you might have guessed, the app that uses the DB is a magento installation)
What is happening to you is fully explained in the chapter 7.10.4 in our 5.5 manual as
found on dev.mysql.com. You have some non-autocommit transaction that has done TRUNCATE or
some PARTITION function and has not committed, but is simply sleeping. It prohibits other
transactions from performing ANY statement on that table.
MySQL does not provide any means of getting a list of statements already executed within
some non-autocommit transaction, but this is one of the items on a TODO list for some
Also, this public list, bug@stripped, is not in the usage any more, so I would
suggest that you discuss your problems on our public forum at :
I am sure that you will find that forum a suitable place for any discussions on the topic
of our beloved product.
Sinisa Milivojevic <sinisa@stripped>
Larnaca, Republic of Cyprus
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
/_/ /_/\_, /___/\___\_\___/