MySQL Bugs: #69711: Explain what operations lead to specific "Waiting for... lock" states

Bug #69711	Explain what operations lead to specific "Waiting for... lock" states
Submitted:	10 Jul 2013 14:58	Modified:	10 Jul 2013 18:07
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	>= MySQL 5.5	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
MySQL 5.5 and beyond replaced the old "Locked" process list status with a more detailed list of "Waiting for ... lock" states. These states are listed in the manual but it is not really clear which action(s) may lead to which lock state being shown.

This is especially confusing when it comes to "Waiting for table level lock" vs. "Waiting for table metadata lock":

* LOCK TABLES ... WRITE waiting on any other implicit or explicit lock -> "table metadata lock"

* anything else waiting on an explicit LOCK TABLES ... READ or an implicit MyISAM read or write lock -> "table level lock"

* anything waiting for an active LOCK TABLES ... WRITE -> "waiting on metadata lock"

I now understand that LOCK ... WRITE wants a metadata write lock in addition to a table data write lock as it not only allows for exclusive DML but also for exclusive DDL on a table, but at first sight it was quite confusing why lots of queries in SHOW PROCESSLIST were in "Waiting for table metadata lock" without any active DDL operation to be seen

How to repeat:
This is the only information i can find in the manual, no details in there:

http://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html

Waiting for lock_type lock

The server is waiting to acquire a lock, where lock_type indicates the type of lock:

    Waiting for event metadata lock

    Waiting for global read lock

    Waiting for schema metadata lock

    Waiting for stored function metadata lock

    Waiting for stored procedure metadata lock

    Waiting for table level lock

    Waiting for table metadata lock

    Waiting for trigger metadata lock 

Suggested fix:
Don't just list the possible lock states, add a bit of explanation what may cause them, e.g. which statement or statement combination may cause which kind of lock state

> I now understand that LOCK ... WRITE wants a metadata write lock

It actually grabs a shared metadata lock, table metadata can still be accessed. Statements that intend to read/write table data will be blocked.

I checked and, definitely, the manual has only titles for those "Waiting on ..... lock" statuses, so I find Hartmut's request for better documentation justifed.

I'll take this one.