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, Huawei Cloud 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 | | +---------------+-----------------------+---------+------+----------+--------|
No. |
Field Name |
Type |
Description |
|---|---|---|---|
0 |
THREAD_ID |
bigint(20) unsigned |
Session ID. |
1 |
LOCK_STATUS |
varchar(24) |
Two statuses of 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:
|
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 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.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
