MDL Views

Introduction

Metadata locks (MDLs) are used to manage concurrent access to database objects. When a transaction holds an MDL for an extended period, other sessions may be blocked from acquiring the same lock. In complex workloads, users need the ability to quickly locate and diagnose such issues.

In MySQL Community Edition, detailed MDL information is available only when Performance Schema is enabled. However, because enabling Performance Schema may affect performance, it is often disabled by default. As a result, users cannot determine MDL dependencies between sessions and may resort to killing large numbers of suspicious sessions or even restarting instances to restore workloads. This increases troubleshooting costs and significantly affects workloads.

To address these challenges, TaurusDB provides the MDL view feature. It allows you to inspect MDLs held or waited by each session, simplifying system diagnostics and minimizing impact on workloads.

Technical Details

The MDL view is exposed as a system catalog under the information_schema database. The catalog name is metadata_lock_info. The table structure is as follows:

desc information_schema.metadata_lock_info;
+---------------+-----------------+------+-----+---------+-------+
| Field         | Type            | Null | Key | Default | Extra |
+---------------+-----------------+------+-----+---------+-------+
| THREAD_ID     | bigint unsigned | NO   |     |         |       |
| LOCK_STATUS   | varchar(32)     | NO   |     |         |       |
| LOCK_MODE     | varchar(32)     | YES  |     |         |       |
| LOCK_TYPE     | varchar(32)     | YES  |     |         |       |
| LOCK_DURATION | varchar(32)     | YES  |     |         |       |
| TABLE_SCHEMA  | varchar(64)     | NO   |     |         |       |
| TABLE_NAME    | varchar(64)     | NO   |     |         |       |
+---------------+-----------------+------+-----+---------+-------+

Case Analysis

Scenario: A long-running, uncommitted transaction blocks a DDL statement. As a result, all other operations on the same table are blocked as well.

Problem description: As illustrated in the following table, session 3 holds an MDL_SHARED_READ (SR) lock on table t2 because its transaction has not been committed. When session 4 attempts to run TRUNCATE TABLE on table t2, it needs an MDL_EXCLUSIVE (X) lock. Because the X lock is incompatible with the SR lock, session 4 is blocked. Later, session 5 attempts a DML operation, which requires an SR lock. However, it detects that an X lock request with higher priority is already waiting in the MDL queue. Therefore, session 5's SR lock request also enters the waiting state. In this case, you must identify the session that is causing the MDL blocking.

Troubleshooting

• Without Using the MDL View

  If the DDL statement is blocked, run show processlist to view thread information:

  show processlist;
  +------+--------+--------------+--------+-----------+----------+-----------------------------------|-------------------------|
  | Id   | User   |  Host        |  db    |  Command  |   Time   |   State                           |Info                     |
  +---------------+-----------------------+-----------+----------+-----------------------------------+-------------------------|
  | 2    | root   |  localhost   |  test  |  Sleep    |   73     |                                   | Null                    |
  | 3    | root   |  localhost   |  test  |  Sleep    |   63     |                                   | Null                    |
  | 4    | root   |  localhost   |  Null  |  Query    |   35     | Waiting for table metadata lock   | truncate table test.t2  |
  | 5    | root   |  localhost   |  test  |  Query    |   17     | Waiting for table metadata lock   | select * from test.t2   |
  | 6    | root   |  localhost   |  test  |  Query    |    0     | starting                          | show processlist        |
  +------+--------+--------------+--------+-----------+----------+-----------------------------------|-------------------------|

  The thread list shows that:

  • Session 4 (Id = 4) is blocked by the table metadata lock held by another session when executing TRUNCATE.
  • Session 5 (Id = 5) is also blocked when running a query.
  • It fails to display which session is actually holding the lock that blocks sessions 4 and 5.

  Randomly killing sessions introduces severe risks. Without the MDL view, you can only wait for the blocking session to release the lock.

• Using the MDL view

  Run select * from information_schema.metadata_lock_info to view the MDL information. The result is as follows:

  select * from information_schema.metadata_lock_info;
  +-------------+-------------+--------------------------+----------------------+-------------------+----------------+----------------+
  | THREAD_ID   | LOCK_STATUS |  LOCK_MODE               | LOCK_TYPE            |  LOCK_DURATION    |  TABLE_SCHEMA  |   TABLE_NAME   |
  +-------------+-------------+--------------------------+----------------------+-------------------+----------------+----------------+
  | 2           | GRANTED     |  MDL_SHARED_READ         | Table metadata lock  |  MDL_TRANSACTION  |  test          |   t1           |
  | 3           | GRANTED     |  MDL_SHARED_READ         | Table metadata lock  |  MDL_TRANSACTION  |  test          |   t2           |
  | 4           | GRANTED     |  MDL_INTENTION_EXCLUSIVE | Global read lock     |  MDL_STATEMENT    |                |                |
  | 4           | GRANTED     |  MDL_INTENTION_EXCLUSIVE | Schema metadata lock |  MDL_TRANSACTION  |  test          |                |
  | 4           | PENDING     |  MDL_EXCLUSIVE           | Table metadata lock  |                   |  test          |   t2           |
  | 5           | PENDING     |  MDL_SHARED_READ         | Table metadata lock  |                   |  test          |   t2           |
  +-------------+-------------+--------------------------+----------------------+-------------------+----------------+----------------+

  The show processlist output and the MDL view show that:

  • The session with THREAD_ID set to 4 is waiting for an MDL on table t2.
  • The session with THREAD_ID set to 3 holds a transaction-level MDL on table t2. Consequently, the session with THREAD_ID set to 4 is always blocked as long as the transaction in the session (THREAD_ID=3) is not committed.

  Therefore, you only need to run commit for the session with THREAD_ID set to 3 or terminate this session.

FAQ

Q: How do I quickly find the session that is causing MDL blocking?

A: Although the MDL view helps identify the root cause of large numbers of MDL waits, scanning the entire view can be time-consuming when many sessions are active. In such cases, a large amount of unrelated MDL information may slow down troubleshooting. To address this, TaurusDB provides a quick diagnostic SQL query that directly locates the blocking session. When an MDL blocking issue occurs, simply execute this SQL statement to immediately identify the session ID that must be terminated.

SELECT f.processlist_id, p.Info AS sql_info
FROM (
     SELECT DISTINCT c.blocking_processlist_id AS processlist_id
     FROM (
         SELECT DISTINCT b.THREAD_ID AS blocking_processlist_id
         FROM information_schema.metadata_lock_info a
             JOIN information_schema.metadata_lock_info b
             ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
                 AND a.TABLE_NAME = b.TABLE_NAME
                 AND a.lock_status = 'PENDING'
                 AND b.lock_status = 'GRANTED'
                 AND a.THREAD_ID <> b.THREAD_ID
     ) c
     WHERE c.blocking_processlist_id NOT IN (
         SELECT DISTINCT d.THREAD_ID AS blocked_processlist_id
         FROM information_schema.metadata_lock_info d
             JOIN information_schema.metadata_lock_info e
             ON d.TABLE_SCHEMA = e.TABLE_SCHEMA
                 AND d.TABLE_NAME = e.TABLE_NAME
                 AND d.lock_status = 'PENDING'
                 AND e.lock_status = 'GRANTED'
                 AND d.THREAD_ID <> e.THREAD_ID
     )
 ) f
     JOIN information_schema.processlist p ON processlist_id = p.Id;