Bug #80823 sys should have a mdl session oriented view

Description:
MDL instrumentation is not enabled by default, but when enabled there are not any helpful SyS views.  What might be helpful is a session-like view that summarizes either:

a) All locks (as shown below), or;
b) All exclusive locks and pending locks (less verbose)

This requires the following configuration:
performance-schema-instrument='wait/lock/metadata/sql/%=ON'

How to repeat:
mysql> SELECT ps.*,  lock_summary.lock_summary  FROM sys.processlist ps  INNER JOIN (SELECT owner_thread_id,  GROUP_CONCAT(   DISTINCT CONCAT(mdl.LOCK_STATUS, ' ', mdl.lock_type, ' on ', IF(mdl.object_type='USER LEVEL LOCK', CONCAT(mdl.object_name, ' (user lock)'), CONCAT(mdl.OBJECT_SCHEMA, '.', mdl.OBJECT_NAME)))   ORDER BY mdl.object_type ASC, mdl.LOCK_STATUS ASC, mdl.lock_type ASC   SEPARATOR '\n'  ) as lock_summary FROM performance_schema.metadata_locks mdl GROUP BY owner_thread_id) lock_summary ON (ps.thd_id=lock_summary.owner_thread_id)\G
*************************** 1. row ***************************
                thd_id: 10841
               conn_id: 10816
                  user: root@localhost
                    db: sys
               command: Query
                 state: Sending data
                  time: 0
     current_statement: SELECT ps.*,  lock_summary.loc ... =lock_summary.owner_thread_id)
     statement_latency: 2.20 ms
              progress: NULL
          lock_latency: 1.16 ms
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 6
       tmp_disk_tables: 2
             full_scan: YES
        last_statement: NULL
last_statement_latency: NULL
        current_memory: 2.94 MiB
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: 1.08 ms
             trx_state: ACTIVE
        trx_autocommit: YES
                   pid: 3585
          program_name: mysql
          lock_summary: GRANTED SHARED on sys.sys_get_config
GRANTED SHARED on sys.format_bytes
GRANTED SHARED on sys.format_time
GRANTED SHARED on sys.format_statement
GRANTED SHARED_READ on sys.sys_config
GRANTED SHARED_READ on performance_schema.metadata_locks
GRANTED SHARED_READ on performance_schema.session_connect_attrs
GRANTED SHARED_READ on performance_schema.memory_summary_by_thread_by_event_name
GRANTED SHARED_READ on sys.x$memory_by_thread_by_current_bytes
GRANTED SHARED_READ on performance_schema.events_transactions_current
GRANTED SHARED_READ on performance_schema.events_statements_current
GRANTED SHARED_READ on performance_schema.events_stages_current
GRANTED SHARED_READ on performance_schema.events_waits_current
GRANTED SHARED_READ on performance_schema.threads
GRANTED SHARED_READ on sys.processlist
1 row in set (0.06 sec)

Suggested fix:
CREATE VIEW session_metadata_locks as SELECT ps.*,  lock_summary.lock_summary  FROM sys.processlist ps  INNER JOIN (SELECT owner_thread_id,  GROUP_CONCAT(   DISTINCT CONCAT(mdl.LOCK_STATUS, ' ', mdl.lock_type, ' on ', IF(mdl.object_type='USER LEVEL LOCK', CONCAT(mdl.object_name, ' (user lock)'), CONCAT(mdl.OBJECT_SCHEMA, '.', mdl.OBJECT_NAME)))   ORDER BY mdl.object_type ASC, mdl.LOCK_STATUS ASC, mdl.lock_type ASC   SEPARATOR '\n'  ) as lock_summary FROM performance_schema.metadata_locks mdl GROUP BY owner_thread_id) lock_summary ON (ps.thd_id=lock_summary.owner_thread_id);

Hello Morgan,

Thank you for the feature request!

Thanks,
Umesh

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0

This one works well on 5.7 too, would be great to add it there as well.

This is a MySQL 8.0 version which is a little easier to read:

WITH lock_summary AS (
 SELECT
  owner_thread_id,
  GROUP_CONCAT(
   DISTINCT
   CONCAT(
    LOCK_STATUS, ' ',
    lock_type, ' on ',
    IF(object_type='USER LEVEL LOCK', CONCAT(object_name, ' (user lock)'), CONCAT(OBJECT_SCHEMA, '.', OBJECT_NAME))
   )
   ORDER BY object_type ASC, LOCK_STATUS ASC, lock_type ASC
   SEPARATOR '\n' 
  ) AS lock_summary
 FROM performance_schema.metadata_locks
 GROUP BY owner_thread_id
)
SELECT
 ps.*,
 lock_summary.lock_summary
FROM sys.processlist ps
INNER JOIN lock_summary ON ps.thd_id=lock_summary.owner_thread_id;