MySQL Bugs: #71365: "Waiting for table level lock" thread state is not explained properly

Bug #71365	"Waiting for table level lock" thread state is not explained properly
Submitted:	13 Jan 2014 16:24	Modified:	1 Mar 2016 17:46
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5, 5.6	OS:	Any
Assigned to:	Paul Dubois	CPU Architecture:	Any
Tags:	missing manual, thread state
Triage:	Needs Triage: D4 (Minor)

Description:
Manual page on general thread states (http://dev.mysql.com/doc/refman/5.5/en/general-thread-states.html) mentions "Waiting for table level lock" only in the following contexts:

"Table lock

The next thread state after System lock. The thread has acquired an external lock and is going to request an internal table lock.

This state was replaced in MySQL 5.5.6 with Waiting for table level lock."

"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 (added in MySQL 5.5.8)

Waiting for global metadata lock (replaced by Waiting for global read lock in MySQL 5.5.8)

Waiting for global read lock (added in MySQL 5.5.8)

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 (added in MySQL 5.5.8)"

I have the following questions about this status as a result:

1. Does the above mean that this state can happen only with external locks enabled?

2. Does this kind of waiting happens only for MyISAM table?

3. Why thread may have to wait for this lock? What may block it from getting it immediately? When the lock of this kind is released? Where one can get more information about the lock wait and what exact session blocks? And so on...

Manual page for MySQL 5.6 (http://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html) is even less helpful.

How to repeat:
Try to find out in the manual what can be the reason for the thread to stay in "Waiting for table level lock" state for a long time.

Suggested fix:
Explain this (and other "Waiting on ... lock") thread states in details, with proper examples or URLs to other pages explaining them. Make sure all the questions mentioned in description above are explicitly answered.

Thank you for the bug report.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

re: "1. Does the above mean that this state can happen only with external locks enabled?"

No, since a system lock was obtained, and that may have waited for either internal or external lock. I will modify "Table lock" description accordingly.

re: "2. Does this kind of waiting happens only for MyISAM table?"

In the state description, no storage engine is mentioned or implied, so, no. Also, for some of the metadata lock states, no table (and thus no storage engine) is involved.

re: "3. Why thread may have to wait for this lock? What may block it from getting it immediately? When the lock of this kind is released? Where one can get more information about the lock wait and what exact session blocks? And so on..."

I'll mention that you can use the Performance Schema lock tables to see what's blocking what.
(http://dev.mysql.com/doc/refman/5.7/en/performance-schema-lock-tables.html)
Also, http://dev.mysql.com/doc/refman/5.7/en/internal-locking.html provides information about related
status variables. I'll cross reference that as well.