MySQL Bugs: #69527: Please, expose information about MDL locks

Bug #69527	Please, expose information about MDL locks
Submitted:	20 Jun 2013 18:51	Modified:	3 Oct 2013 13:44
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S4 (Feature request)
Version:	5.6+	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	information_schema, lock, mdl

Description:
It seems there is no easy way now to find out easily what MDL locks are set in MySQL server and what sessions are waiting on what exact MDL lock.

Please, implement something to let DBAs get this information easily. It can be INFORMATION_SCHEMA tables (somewhat similar to INNODB_LOCKS and INNODB_LOCK_WAITS), some clearly documented PERFORMANCE_SCHEMA instruments or way of use, something like innodb_lock_monitor "fake" table to start regular debug outputs to the error log, or some plugin. Even extended mysqladmin debug output with list of MDL locks and lock requests per session/thread is better than nothing.

How to repeat:
Try to find out what exact session prevents getting metadata lock for another session, like in case when you see ALTER waiting on metadata lock and 100+ other sessions with active transactions not running any SQL statement at the moment, with most of them started before the blocked ALTER...

Related: BUG 13819913 - WHO PREVENTS METADATA LOCK ACQUISITION ?

I find tha this is a well justified feature request.

Yes, I agree, this is an excellent feature request!

I was about to open a similar request, but ran across this one.

My request was slightly more specific, but I feel this one would cover it, and more, so all the better.  However, please let me know if I should file a new feature request though.

At any rate, my specific request is to track the MDLs when MyISAM tables are involved.  Basically, it would be nice to know if a transaction is started at all, or at least if one is started *and* followed by a SELECT (or other command) on a MyISAM table (plus any InnoDB, of course, as is already done).  I say MyISAM though because if you start a transaction (START TRANSACTION, BEGIN, and SET AUTOCOMMIT=0) and follow it with a SELECT on any MyISAM table, after that, the table will be locked to other DDL statements until the transaction is committed (or ended).

Further, you will have no idea what transaction holds the lock, as nothing tracks it in this case - not SHOW FULL PROCESSLIST, mysqladmin debug output, INFORMATION_SCHEMA, SHOW ENGINE INNODB STATUS, InnoDB Lock Monitor, etc., not to mention one generally doesn't associate transactions with MyISAM tables anyway.

Implemented in 5.7.3, see the release notes:

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html