Extending the SYS schema to show metadata locks

http://mysqlserverteam.com/extending-the-sys-schema-to-show-metadata-locks/

Starting with MySQL 5.7, performance_schema has been enhanced to instrument metadata locks , and can be turned on by adding the following line to your my.cnf file:

performance-schema-instrument='wait/lock/metadata/sql/%=ON'

(At runtime, it can also be enabled by modifying the setup_instruments table in performance_schema.)

From here, you can now query performance_schema.metadata_locks to reveal all currently open metadata locks on your server:

mysql> select * fromperformance_schema.metadata_locks\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: wordpress_tockerca
          OBJECT_NAME: wp_posts
OBJECT_INSTANCE_BEGIN: 139671129331184
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
              SOURCE: sql_parse.cc:5920
      OWNER_THREAD_ID: 1817
      OWNER_EVENT_ID: 25
*************************** 2. row ***************************
          OBJECT_TYPE: USERLEVELLOCK
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: abcd
OBJECT_INSTANCE_BEGIN: 139671129776448
            LOCK_TYPE: EXCLUSIVE
        LOCK_DURATION: EXPLICIT
          LOCK_STATUS: GRANTED
              SOURCE: item_func.cc:5636
      OWNER_THREAD_ID: 1817
      OWNER_EVENT_ID: 26
*************************** 3. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
OBJECT_INSTANCE_BEGIN: 139672612788784
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
              SOURCE: sql_parse.cc:5920
      OWNER_THREAD_ID: 1128
      OWNER_EVENT_ID: 22
3 rowsin set (0.00 sec)

Using SYS

As I have previously demonstrated , SYS is the DBA’s companion toperformance_schema and contains a number of views that are more task-oriented . Seeing the raw locks is not always immediately useful, what is preferable is a view that shows this next to user sessions.

SYS has a view called session , but in this example I am going to join metadata locks to sys.processlist instead. Processlist is a superset of session, that also includes background threads.

mysql> SELECTps.*,  lock_summary.lock_summary  FROMsys.processlistps  INNERJOIN (SELECTowner_thread_id,  GROUP_CONCAT(  DISTINCTCONCAT(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)))  ORDERBYmdl.object_typeASC, mdl.LOCK_STATUSASC, mdl.lock_typeASC  SEPARATOR '\n'  ) as lock_summaryFROMperformance_schema.metadata_locksmdlGROUPBYowner_thread_id) lock_summaryON (ps.thd_id=lock_summary.owner_thread_id)\G
*************************** 1. row ***************************
                thd_id: 1817
              conn_id: 1793
                  user: root@localhost
                    db: wordpress_tockerca
              command: Sleep
                state: NULL
                  time: 317
    current_statement: NULL
    statement_latency: NULL
              progress: NULL
          lock_latency: 0 ps
        rows_examined: 0
            rows_sent: 1
        rows_affected: 0
            tmp_tables: 0
      tmp_disk_tables: 0
            full_scan: NO
        last_statement: selectget_lock('abcd', 10)
last_statement_latency: 315.92 us
        current_memory: 259.14 KiB
            last_wait: NULL
    last_wait_latency: NULL
                source: NULL
          trx_latency: 6.00 m
            trx_state: ACTIVE
        trx_autocommit: NO
                  pid: 21518
          program_name: mysql
          lock_summary: GRANTEDSHARED_READonwordpress_tockerca.wp_posts
GRANTEDEXCLUSIVEonabcd (userlock)
*************************** 2. row ***************************
                thd_id: 1128
              conn_id: 1104
                  user: root@localhost
                    db: NULL
              command: Query
                state: Sendingdata
                  time: 0
    current_statement: SELECTps.*,  lock_summary.loc ... =lock_summary.owner_thread_id)
    statement_latency: 11.04 ms
              progress: NULL
          lock_latency: 4.08 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.97 MiB
            last_wait: NULL
    last_wait_latency: NULL
                source: NULL
          trx_latency: 70.00 ms
            trx_state: ACTIVE
        trx_autocommit: YES
                  pid: 21498
          program_name: mysql
          lock_summary: GRANTEDSHAREDonsys.format_statement
GRANTEDSHAREDonsys.format_time
GRANTEDSHAREDonsys.format_bytes
GRANTEDSHAREDonsys.sys_get_config
GRANTEDSHARED_READonsys.processlist
GRANTEDSHARED_READonperformance_schema.threads
GRANTEDSHARED_READonperformance_schema.events_waits_current
GRANTEDSHARED_READonperformance_schema.events_stages_current
GRANTEDSHARED_READonperformance_schema.events_statements_current
GRANTEDSHARED_READonperformance_schema.events_transactions_current
GRANTEDSHARED_READonsys.x$memory_by_thread_by_current_bytes
GRANTEDSHARED_READonperformance_schema.session_connect_attrs
GRANTEDSHARED_READonperformance_schema.metadata_locks
GRANTEDSHARED_READonsys.sys_config
GRANTEDSHARED_READonperformance_schema.memory_summary_by_thread_by_event_name
2 rowsin set (0.09 sec)

On my testing system I have saved this query as a view calledsys.session_metadata_locks . I have also opened BUG #80823 so that the sys developers can evaluate if this should be included as part of a future release.

It is always interesting to hear from DBAs on what information they find useful to be included in views. Please try it out, and let me know your feedback