MySQL Bugs: #23076: No way of viewing locks from within the server

Bug #23076	No way of viewing locks from within the server
Submitted:	7 Oct 2006 16:29	Modified:	5 Mar 2014 14:35
Reporter:	Mark Leith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S4 (Feature request)
Version:	5.7.3	OS:	Any (All)
Assigned to:		CPU Architecture:	Any
Tags:	information_schema, lock

Description:
Currently there is no easy way to views locks that are held by sessions, or view the lock types that sessions hold. The only way to do this at the moment is with something like mysqladmin debug, which is difficult to interpret, and can not be invoked by general clients. 

This feature request is to provide a new INFORMATION_SCHEMA table - INFORMATION_SCHEMA.LOCKS, which shows all table locks held within the server, as well as the lock mode, and the session that is holding the locks, so that can easily be joined against INFORMATION_SCHEMA.PROCESSLIST.

To take it one step further, it would also be nice to list lock requests here as well (to easily diagnose "blocked" sessions). 

This would be analogous to Oracle's V$LOCK view. 

How to repeat:
No test case.

Suggested fix:
See above.

Maybe SHOW SERVER LOCKS to include all locks which server can hold: table, MDL, maybe some introduced in future.

It looks like this might be available in MySQL 5.7:
http://dev.mysql.com/worklog/task/?id=5879

Now implemented in 5.7, with the performance schema table metadata_locks.

See https://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html

Implemented in worklog mentioned above, and pushed to 5.7.3.