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:
Category:MySQL Server: Locking Severity:S4 (Feature request)
Version:5.7.3 OS:Any (All)
Assigned to: CPU Architecture:Any
Tags: information_schema, lock

[7 Oct 2006 16:29] Mark Leith
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.
[17 Sep 2012 19:22] Sveta Smirnova
Maybe SHOW SERVER LOCKS to include all locks which server can hold: table, MDL, maybe some introduced in future.
[5 Mar 2014 14:03] Daniël van Eeden
It looks like this might be available in MySQL 5.7:
[5 Mar 2014 14:35] Marc ALFF
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
[5 Mar 2014 14:35] Ståle Deraas
Implemented in worklog mentioned above, and pushed to 5.7.3.