MySQL :: 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'

1	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 * from performance_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: USER LEVEL LOCK
        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 rows in set (0.00 sec)

mysql> select * from performance_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: USER LEVEL LOCK

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 rows in set (0.00 sec)

Using SYS

As I have previously demonstrated, SYS is the DBA’s companion to performance_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> 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: 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: select get_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: GRANTED SHARED_READ on wordpress_tockerca.wp_posts
GRANTED EXCLUSIVE on abcd (user lock)
*************************** 2. row ***************************
                thd_id: 1128
               conn_id: 1104
                  user: root@localhost
                    db: NULL
               command: Query
                 state: Sending data
                  time: 0
     current_statement: SELECT ps.*,  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: GRANTED SHARED on sys.format_statement
GRANTED SHARED on sys.format_time
GRANTED SHARED on sys.format_bytes
GRANTED SHARED on sys.sys_get_config
GRANTED SHARED_READ on sys.processlist
GRANTED SHARED_READ on performance_schema.threads
GRANTED SHARED_READ on performance_schema.events_waits_current
GRANTED SHARED_READ on performance_schema.events_stages_current
GRANTED SHARED_READ on performance_schema.events_statements_current
GRANTED SHARED_READ on performance_schema.events_transactions_current
GRANTED SHARED_READ on sys.x$memory_by_thread_by_current_bytes
GRANTED SHARED_READ on performance_schema.session_connect_attrs
GRANTED SHARED_READ on performance_schema.metadata_locks
GRANTED SHARED_READ on sys.sys_config
GRANTED SHARED_READ on performance_schema.memory_summary_by_thread_by_event_name
2 rows in set (0.09 sec)

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: 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: select get_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: GRANTED SHARED_READ on wordpress_tockerca.wp_posts

GRANTED EXCLUSIVE on abcd (user lock)

*************************** 2. row ***************************

thd_id: 1128

conn_id: 1104

user: root@localhost

db: NULL

command: Query

state: Sending data

time: 0

current_statement: SELECT ps.*, 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: GRANTED SHARED on sys.format_statement

GRANTED SHARED on sys.format_time

GRANTED SHARED on sys.format_bytes

GRANTED SHARED on sys.sys_get_config

GRANTED SHARED_READ on sys.processlist

GRANTED SHARED_READ on performance_schema.threads

GRANTED SHARED_READ on performance_schema.events_waits_current

GRANTED SHARED_READ on performance_schema.events_stages_current

GRANTED SHARED_READ on performance_schema.events_statements_current

GRANTED SHARED_READ on performance_schema.events_transactions_current

GRANTED SHARED_READ on sys.x$memory_by_thread_by_current_bytes

GRANTED SHARED_READ on performance_schema.session_connect_attrs

GRANTED SHARED_READ on performance_schema.metadata_locks

GRANTED SHARED_READ on sys.sys_config

GRANTED SHARED_READ on performance_schema.memory_summary_by_thread_by_event_name

2 rows in set (0.09 sec)

On my testing system I have saved this query as a view called sys.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 🙂