MySQL表結構怎樣變更Metadata Lock

145次閱讀

共計 6534 個字符，預計需要花費 17 分鐘才能閱讀完成。

本篇文章為大家展示了 MySQL 表結構怎樣變更 Metadata Lock，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

想必玩過 mysql 的人對 Waiting for table metadata lock 肯定不會陌生，一般都是進行 alter 操作時被堵住了，導致了我們在 show processlist 時，看到線程的狀態是在等 metadata lock。本文會對 MySQL 表結構變更的 Metadata Lock 進行詳細的介紹。

在線上進行 DDL 操作時，相對于其可能帶來的系統負載，其實，我們最擔心的還是 MDL 其可能導致的阻塞問題。

一旦 DDL 操作因獲取不到 MDL 被阻塞，后續其它針對該表的其它操作都會被阻塞。典型如下，如阻塞稍久的話，我們會看到 Threads_running 飆升，CPU 告警。

mysql  show processlist;
+----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 122 | Waiting on empty queue | NULL |
| 9 | root | localhost | NULL | Sleep | 57 | | NULL |
| 12 | root | localhost | employees | Query | 40 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
| 13 | root | localhost | employees | Query | 35 | Waiting for table metadata lock | select * from slowtech.t1 |
| 14 | root | localhost | employees | Query | 30 | Waiting for table metadata lock | select * from slowtech.t1 |
| 15 | root | localhost | employees | Query | 19 | Waiting for table metadata lock | select * from slowtech.t1 |
| 16 | root | localhost | employees | Query | 10 | Waiting for table metadata lock | select * from slowtech.t1 |
| 17 | root | localhost | employees | Query | 0 | starting | show processlist |
+----+-----------------+-----------+-----------+---------+------+---------------------------------+------------------------------------+
rows in set (0.00 sec)

如果發生在線上，無疑會影響到業務。所以，一般建議將 DDL 操作放到業務低峰期做，其實有兩方面的考慮，1. 避免對系統負載產生較大影響。2. 減少 DDL 被阻塞的概率。

MDL 引入的背景

MDL 是 MySQL 5.5.3 引入的，主要用于解決兩個問題，

RR 事務隔離級別下不可重復讀的問題

如下所示，演示環境，MySQL 5.5.0。

session1  begin;
Query OK, 0 rows affected (0.00 sec)
session1  select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
rows in set (0.00 sec)
session2  alter table t1 add c1 int;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
session1  select * from t1;
Empty set (0.00 sec)
session1  commit;
Query OK, 0 rows affected (0.00 sec)
session1  select * from t1;
+------+------+------+
| id | name | c1 |
+------+------+------+
| 1 | a | NULL |
| 2 | b | NULL |
+------+------+------+
rows in set (0.00 sec)

可以看到，雖然是 RR 隔離級別，但在開啟事務的情況下，第二次查詢卻沒有結果。

主從復制問題

包括主從數據不一致，主從復制中斷等。

如下面的主從數據不一致。

session1  create table t1(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
session1  begin;
Query OK, 0 rows affected (0.00 sec)
session1  insert into t1 values(1, a 
Query OK, 1 row affected (0.00 sec)
session2  truncate table t1;
Query OK, 0 rows affected (0.46 sec)
session1  commit;
Query OK, 0 rows affected (0.35 sec)
session1  select * from t1;
Empty set (0.00 sec)

再來看看從庫的結果

session1  select * from slowtech.t1;
+------+------+------+
| id | name | c1 |
+------+------+------+
| 1 | a | NULL |
+------+------+------+
row in set (0.00 sec)

看看 binlog 的內容，可以看到，truncate 操作記錄在前，insert 操作記錄在后。

# at 7140
#180714 19:32:14 server id 1 end_log_pos 7261 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567934/*!*/;
create table t1(id int,name varchar(10)) engine=innodb
/*!*/;
# at 7261
#180714 19:32:30 server id 1 end_log_pos 7333 Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1531567950/*!*/;
BEGIN
/*!*/;
# at 7333
#180714 19:32:30 server id 1 end_log_pos 7417 Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1531567950/*!*/;
truncate table t1
/*!*/;
# at 7417
#180714 19:32:30 server id 1 end_log_pos 7444 Xid = 422
COMMIT/*!*/;
# at 7444
#180714 19:32:34 server id 1 end_log_pos 7516 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567954/*!*/;
BEGIN
/*!*/;
# at 7516
#180714 19:32:24 server id 1 end_log_pos 7611 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567944/*!*/;
insert into t1 values(1, a)
/*!*/;
# at 7611
#180714 19:32:34 server id 1 end_log_pos 7638 Xid = 421
COMMIT/*!*/;

如果會話 2 執行的是 drop table 操作，還會導致主從中斷。

有意思的是，如果會話 2 執行的是 alter table 操作，其依舊會被阻塞，阻塞時間受 innodb_lock_wait_timeout 參數限制。

mysql  show processlist;
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
| 54 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 58 | root | localhost | slowtech | Sleep | 1062 | | NULL |
| 60 | root | localhost | slowtech | Query | 11 | copy to tmp table | alter table t1 add c1 int |
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
rows in set (0.00 sec)

MDL 的基本概念

首先，看看官方的說法，

To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session.

The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends.

A metadata lock on a table prevents changes to the table s structure.

This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

從上面的描述可以看到，

1. MDL 出現的初衷就是為了保護一個處于事務中的表的結構不被修改。

2. 這里提到的事務包括兩類，顯式事務和 AC-NL-RO（auto-commit non-locking read-only）事務。顯式事務包括兩類：1. 關閉 AutoCommit 下的操作，2. 以 begin 或 start transaction 開始的操作。AC-NL-RO 可理解為 AutoCommit 開啟下的 select 操作。

3. MDL 是事務級別的，只有在事務結束后才會釋放。在此之前，其實也有類似的保護機制，只不過是語句級別的。

需要注意的是，MDL 不僅僅適用于表，同樣也適用于其它對象，如下表所示，其中，等待狀態對應的是 show processlist 中的 State。

為了提高數據庫的并發度，MDL 被細分為了 11 種類型。

MDL_INTENTION_EXCLUSIVE

MDL_SHARED

MDL_SHARED_HIGH_PRIO

MDL_SHARED_READ

MDL_SHARED_WRITE

MDL_SHARED_WRITE_LOW_PRIO

MDL_SHARED_UPGRADABLE

MDL_SHARED_READ_ONLY

MDL_SHARED_NO_WRITE

MDL_SHARED_NO_READ_WRITE

MDL_EXCLUSIVE

常用的有 MDL_SHARED_READ,MDL_SHARE D_WRITE 及 MDL_EXCLUSIVE，其分別用于 SELECT 操作，DML 操作及 DDL 操作。其它類型的對應操作可參考源碼 sql/mdl.h。

對于 MDL_EXCLUSIVE，官方的解釋是，

/*
An exclusive metadata lock.
A connection holding this lock can modify both table s metadata and data.
No other type of metadata lock can be granted while this lock is held.
To be used for CREATE/DROP/RENAME TABLE statements and for execution of
certain phases of other DDL statements.
*/

簡而言之，MDL_EXCLUSIVE 是獨占鎖，在其持有期間是不允許其它類型的 MDL 被授予，自然也包括 SELECT 和 DML 操作。

這也就是為什么 DDL 操作被阻塞時，后續其它操作也會被阻塞。

關于 MDL 的補充

1. MDL 的最大等待時間由 lock_wait_timeout 參數決定，其默認值為 31536000（365 天）。在使用工具進行 DDL 操作時，這個值就不太合理。事實上，pt-online-schema-change 和 gh-ost 對其就進行了相應的調整，其中，前者 60s，后者 3s。

2. 如果一個 SQL 語法上有效，但執行時報錯，如，列名不存在，其同樣會獲取 MDL 鎖，直到事務結束才釋放。

上述內容就是 MySQL 表結構怎樣變更 Metadata Lock，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注丸趣 TV 行業資訊頻道。

正文完