Help Center/ Relational Database Service/ Huawei Cloud MySQL Kernel/ MDL Views
Updated on 2023-08-21 GMT+08:00
View PDF

MDL Views

Introduction

MySQL Community Edition cannot obtain table MDLs when performance_schema was disabled. If Waiting for metadata lock is displayed, blocking DML or DDL, you may need to reboot DB instances because the association among sessions cannot be identified. This has an impact on service running.

In complex service scenarios, such problems will frequently occur if exclusive operations like DDL and LOCK Table are performed on database metadata, bringing troubles to you.

To resolve the problems, RDS for MySQL introduces the MDL view, enabling you to view MDLs that each session is holding and waiting for. You can effectively diagnose the system and identify the problematic sessions, minimizing the impact on services.

Description

The MDL view is displayed as a system table. The table is named metadata_lock_info and contained in the information_schema database. The table structure is as follows.

desc information_schema.metadata_lock_info;
+---------------+-----------------------+---------+------+----------+--------|
| Field         | Type                  | Null    |Key   |Default   |  Extra |
+---------------+-----------------------+---------+------+----------+--------|
| THREAD_ID     | bigint(20) unsigned   | NO      |      |   0      |        |
| LOCK_STATUS   | varchar(24)           | NO      |      |          |        |
| LOCK_MODE     | varchar(24)           | YES     |      |  NULL    |        |
| LOCK_TYPE     | varchar(30)           | YES     |      |  NULL    |        |
| LOCK_DURATION | varchar(30)           | YES     |      |  NULL    |        |
| TABLE_SCHEMA  | varchar(64)           | YES     |      |  NULL    |        |
| TABLE_NAME    | varchar(64)           | YES     |      |  NULL    |        |
+---------------+-----------------------+---------+------+----------+--------|
Table 1 metadata_lock_info fields

No.

Field Name

Type

Description

0

THREAD_ID

bigint(20) unsigned

Session ID.

1

LOCK_STATUS

varchar(24)

Two statuses of MDL:

  • PENDING: The session is waiting for the MDL.
  • GRANTED: The session has obtained the MDL.

2

LOCK_MODE

varchar(24)

MDL mode, such as MDL_SHARED, MDL_EXCLUSIVE, MDL_SHARED_READ, and MDL_SHARED_WRITE.

3

LOCK_TYPE

varchar(30)

MDL type, such as Table metadata lock, Schema metadata lock, Global read lock, and Tablespace lock.

4

LOCK_DURATION

varchar(30)

MDL range. The value options are as follows:

  • MDL_STATEMENT: statement-level MDLs
  • MDL_TRANSACTION: transaction-level MDLs
  • MDL_EXPLICIT: global-level MDLs

5

TABLE_SCHEMA

varchar(64)

Database name. For some global-level MDLs, this parameter is left empty.

6

TABLE_NAME

varchar(64)

Table name. For some global-level MDLs, this parameter is left empty.

Examples

Scenario: If no transaction is committed for a long time, DDL operations are blocked, and then all operations on the same table are blocked.

Table 2 MDL view example

Table Name

Session

Session 2

Session 3

Session 4

Session 5

t1

begin;

select * from t1;

-

-

-

t2

-

begin;

select * from t2;

-

-

t3

-

-

truncate table t2;

(blocked)

-

t4

-

-

-

begin;

select * from t2;

(blocked)

Case Analysis

Description

After TRUNCATE operations on table t2 are blocked, SELECT operations on table t2 are also blocked in the service process.

Problem Analysis

  • Without the MDL view

    If DDL operations are blocked, run the show processlist command. Information in the following figure is displayed.

    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        |
+------+--------+--------------+--------+-----------+----------+-----------------------------------|-------------------------|

    According to the preceding thread list:

    • When executing TRUNCATE, session 4 is blocked by the table metadata lock held by other sessions.
    • When executing SELECT, session 5 is also blocked by the table metadata lock held by other sessions.
    • You cannot determine which session blocks session 4 and session 5.

    In this case, killing other sessions randomly will cause great risks to online services. Therefore, you can only wait for other sessions to release the MDL.

  • With the MDL view

    Run the select * from information_schema.metadata_lock_info command to view the MDL information. The following information is displayed.

    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 command output shows information about threads and MDL views.

    • Session 4 is waiting for an MDL on table t2.
    • Session 3 holds a transaction-level MDL on table t2. If the transaction hold by session 3 is not committed, session 4 will be kept blocked.

    You only need to run the commit command on session 3 or kill session 3 to keep services running.