Bug #32422 deadlocks with 'closing tables' and 'Opening tables' impossible to debug
Submitted: 15 Nov 2007 21:10 Modified: 4 Dec 2013 8:32
Reporter: Mark Callaghan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S4 (Feature request)
Version:5.0.37 OS:Any
Assigned to: Marc ALFF CPU Architecture:Any
Tags: debug, dictionary, lock, table

[15 Nov 2007 21:10] Mark Callaghan 
Description:
A server locked up for ~15 minutes with all new queries in state 'Opening tables' or 'closing tables'. The problem went away after 15 minutes. Given the way MySQL gets locks, this is impossible to debug as you cannot list lock holders and lock waiters as you can for InnoDB.

I would like:
* commands to list lock holders and lock waiters
* a background thread that dumps lock holder and waiter info into the error log when there a long wait for a lock.

How to repeat:
Run many servers for a long time.

Suggested fix:
Add features similar to what InnoDB provides.
[15 Nov 2007 21:19] Mark Callaghan 
The first session to get stuck was in state 'closing tables'.

It was probably stuck somewhere in close_thread_stables() and stayed in that state for ~15 minutes.
[16 Nov 2007 5:12] Valeriy Kravchuk 
Thank you for a feature request. Have you tried to use

mysqladmin debug

command? It writes some information about table-level locks to the error log.
[16 Nov 2007 8:05] Mark Callaghan 
I always forget about that. However, I don't catch these errors in real time. I usually catch them a day after they happen. I then debug from archives of SHOW PROCESSLIST samples and the contents of the database error log. This is why I want dumping of lock state to be done when a session waits too long. Also, given that much of the locking in the MySQL layer is done directly on pthread_mutex, rather than using the mutex to protect the read/write of a 'locked' flag, many of the lock have/wait relationships cannot be displayed.
[16 Nov 2007 8:14] Valeriy Kravchuk 
Thank you for a reasonable feature request :)
[4 Dec 2013 8:32] Marc ALFF 
Resolved in MySQL 5.7.3, with the performance schema instrumentation

http://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html
http://dev.mysql.com/doc/refman/5.7/en/table-handles-table.html