Question: How to Retrieve Table Level or Global Read Lock Status from Running MySQL Server?
Prior to MySQL 5.7, it is not possible to figure out table level or global read locks held by each sessions. As of MySQL 5.7, a new performance schema table, metadata_locks is added. This performance schema can achieve the goal. Please look at the example below.
mysql> use performance_schema ... snip ... mysql> update performance_schema.setup_instruments set enabled='yes', timed='yes' where name = 'wait/lock/metadata/sql/mdl'; ... snip ... mysql> select * from performance_schema.metadata_locks; +-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+ | GLOBAL | NULL | NULL | 140149010871792 | SHARED | EXPLICIT | GRANTED | lock.cc:1108 | 28 | 305 | | COMMIT | NULL | NULL | 140149010788016 | SHARED | EXPLICIT | GRANTED | lock.cc:1192 | 28 | 305 | | TABLE | performance_schema | metadata_locks | 140149077965408 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:5927 | 29 | 186 | +-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+ 3 rows in set (0.00 sec)
As you see, the instrumentation for this performance schema table is not enabled by default. The instrumentation should be enabled beforehand.
The query result has three rows. The first row represents the Global Read Lock, which is acquired by ‘FLUSH TABLES WITH READ LOCK’ command. This is identified by the fact that OBJECT_TYPE is GLOBAL and LOCK_TYPE is SHARED. The second row is also acquired by ‘FLUSH TABLES WITH READ LOCK’ command, because committing to storage engines is not allowed when Global Read Lock is held. So, FLUSH TABLES WITH READ LOCK will actually acquire these two locks at the same time.
Please note that, thread identifier shown in OWNER_THREAD_ID does not represent Id field shown in SHOW PROCESSLIST command. You can find the thread ID in performance_schema.threads table. If you want to retrieve connection identifiers shown in SHOW PROCESSLIST command and lock status at once, please JOIN these tables.
The third row represents a required lock for the query “select * from metadata_lock” itself. You can see that the lock is a table level lock and the target table is performance_schema.metadata_lock. Next, the following example is a content of metadata_lock table when one table is waiting for a table level lock held by another session.
mysql> select * from performance_schema.metadata_locks; +-------------+--------------------+----------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+----------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+ | GLOBAL | NULL | NULL | 139997689989408 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5440 | 27 | 5370 | | SCHEMA | world | NULL | 139997689910048 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5425 | 27 | 5370 | | TABLE | world | City | 139997680320816 | SHARED_NO_READ_WRITE | TRANSACTION | GRANTED | sql_parse.cc:5937 | 27 | 5370 | | TABLE | world | City | 139997411860784 | SHARED_READ | TRANSACTION | PENDING | sql_parse.cc:5937 | 28 | 13 | | TABLE | performance_schema | metadata_locks | 139997278198112 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:5937 | 30 | 94 | +-------------+--------------------+----------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+ 5 rows in set (0.00 sec)
Before executing this query, two queries were issued. One is “LOCK TABLES City WRITE” from one session, and the other is “SELECT * FROM City” from another session. This results in the second session being blocked. The first and second session’s OWNER_THREAD_ID are 27 and 28 respectively.
The first session, “LOCK TABLES City WRITE”, acquires three locks. This includes the global level lock, but its lock type is INTENTION_EXCLUSIVE. This means that LOCK TABLES t WRITE and FLUSH TABLES WITH READ LOCK will conflict each other. The second row is a schema level lock, which will block ALTER DATABASE command until the table-level lock is released. The third row indicates the table lock.
The firth row represents a waiting table lock required by “SELECT * FROM City”. You can see that the lock has not been acquired because LOCK_STATUS is PENDING. You may think that the output of metadata_locks is not intuitive and handy to find out which session is blocked by which session. MySQL 5.7 solves this problem by newly introduced “sys” schema, which is a collection of useful views and other types of objects for server monitoring. The schema_table_lock_waits table figures out the same lock contention as above like below.
mysql> select * from sys.schema_table_lock_waits\G
*************************** 1. row ***************************
object_schema: world
object_name: City
waiting_thread_id: 28
waiting_pid: 3
waiting_account: msandbox@localhost
waiting_lock_type: SHARED_READ
waiting_lock_duration: TRANSACTION
waiting_query: select * from City
waiting_query_secs: 4
waiting_query_rows_affected: 0
waiting_query_rows_examined: 0
blocking_thread_id: 27
blocking_pid: 2
blocking_account: msandbox@localhost
blocking_lock_type: SHARED_NO_READ_WRITE
blocking_lock_duration: TRANSACTION
sql_kill_blocking_query: KILL QUERY 2
sql_kill_blocking_connection: KILL 2
1 row in set (0.01 sec)
This output perfectly depicts which session is blocking which session due to table-level locks. However, conflicts between the Global Read Lock and table-level locks cannot be inspected by this sys schema view, schema_table_lock_waits. The only viable solution is examining performance_schema.metadata_lock table.