From: Sinisa Milivojevic
Date: March 6 2012 5:03pm
Subject: Re: Mysql Metadata Locking bug ?
List-Archive: http://lists.mysql.com/bugs/15797
Message-Id: <237980D5-1639-449B-9585-C601EA8DDC6F@cytanet.com.cy>
MIME-Version: 1.0 (Apple Message framework v1084)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable


On Mar 6, 2012, at 5:27 PM, Dragos CHIRIAC wrote:

> I have some issues with MySQL 5.5.20 ().
>=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).
>=20
> This only happens on heavy load.
>=20
> 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".
>=20
> 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 null;
> Are (almost) always empty even with 500 queries waiting for the =
metadata lock.
>=20
> (I guess this an expected behaviour, as the metalocks are not actually =
part of the innodb engine).
>=20
> Also "A metadata lock on a table prevents changes to the table's =
structure." But I have hundreds of :
>=20
> 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 `e`
> INNER JOIN `catalog_pro |         0 |             0 |         1 |
>=20
> piling up during the infamous metalock.
>=20
> 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 ?)
>=20
> 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.
>=20
> 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) part.
>=20
> Seems related to :
> http://bugs.mysql.com/bug.php?id=3D60563
> http://bugs.mysql.com/bug.php?id=3D61935
> Except that this basically freezes my database for minutes for most of =
the usual and basic operations.
>=20
> The my.cnf is my-huge.cnf, innodb enabled, and innodb_buffer_pool_size =
=3D 7G out of 12 (if it helps).
>=20
> [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 [1]. If that =
is the case ... how do I find and "repair" the said tables ... ( =
probably this will be the next "dead chicken[2]" that i'll wave, as i'm =
out of ideas ) .
>=20
> D.
>=20
> PS: (As you might have guessed, the app that uses the DB is a magento =
installation)

Hi!

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.=20

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 future release.

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 :

http://forums.mysql.com/index.php=20

I am sure that you will find that forum a suitable place for any =
discussions on the topic of our beloved product.

--

Pozdrav,

--=20
Sinisa Milivojevic <sinisa@stripped>
MySQL Developer
Larnaca, Republic of Cyprus
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /
 / /|_/ / // /\ \/ /_/ / /__
/_/  /_/\_, /___/\___\_\___/
       <___/   www.mysql.com