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

成都創(chuàng)新互聯(lián)公司是一家專注于成都網(wǎng)站制作、成都網(wǎng)站建設(shè)與策劃設(shè)計(jì),固始網(wǎng)站建設(shè)哪家好?成都創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)十多年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:固始等地區(qū)。固始做網(wǎng)站價(jià)格咨詢:13518219792

想必玩過mysql的人對(duì)Waiting for table metadata lock肯定不會(huì)陌生，一般都是進(jìn)行alter操作時(shí)被堵住了，導(dǎo)致了我們?cè)趕how processlist 時(shí)，看到線程的狀態(tài)是在等metadata lock。本文會(huì)對(duì)MySQL表結(jié)構(gòu)變更的Metadata Lock進(jìn)行詳細(xì)的介紹。

在線上進(jìn)行DDL操作時(shí)，相對(duì)于其可能帶來的系統(tǒng)負(fù)載，其實(shí)，我們最擔(dān)心的還是MDL其可能導(dǎo)致的阻塞問題。

一旦DDL操作因獲取不到MDL被阻塞，后續(xù)其它針對(duì)該表的其它操作都會(huì)被阻塞。典型如下，如阻塞稍久的話，我們會(huì)看到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)

如果發(fā)生在線上，無疑會(huì)影響到業(yè)務(wù)。所以，一般建議將DDL操作放到業(yè)務(wù)低峰期做，其實(shí)有兩方面的考慮，1. 避免對(duì)系統(tǒng)負(fù)載產(chǎn)生較大影響。2. 減少DDL被阻塞的概率。 

MDL引入的背景

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

RR事務(wù)隔離級(jí)別下不可重復(fù)讀的問題

如下所示，演示環(huán)境，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隔離級(jí)別，但在開啟事務(wù)的情況下，第二次查詢卻沒有結(jié)果。

主從復(fù)制問題

包括主從數(shù)據(jù)不一致，主從復(fù)制中斷等。

如下面的主從數(shù)據(jù)不一致。

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)

再來看看從庫的結(jié)果

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

看看binlog的內(nèi)容，可以看到，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/*!*/;

如果會(huì)話2執(zhí)行的是drop table操作，還會(huì)導(dǎo)致主從中斷。

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

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出現(xiàn)的初衷就是為了保護(hù)一個(gè)處于事務(wù)中的表的結(jié)構(gòu)不被修改。

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

3. MDL是事務(wù)級(jí)別的，只有在事務(wù)結(jié)束后才會(huì)釋放。在此之前，其實(shí)也有類似的保護(hù)機(jī)制，只不過是語句級(jí)別的。

需要注意的是，MDL不僅僅適用于表，同樣也適用于其它對(duì)象，如下表所示，其中，"等待狀態(tài)"對(duì)應(yīng)的是"show processlist"中的State。

 

為了提高數(shù)據(jù)庫的并發(fā)度，MDL被細(xì)分為了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操作。其它類型的對(duì)應(yīng)操作可參考源碼sql/mdl.h。

對(duì)于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是獨(dú)占鎖，在其持有期間是不允許其它類型的MDL被授予，自然也包括SELECT和DML操作。

這也就是為什么DDL操作被阻塞時(shí)，后續(xù)其它操作也會(huì)被阻塞。

關(guān)于MDL的補(bǔ)充

1. MDL的最大等待時(shí)間由lock_wait_timeout參數(shù)決定，其默認(rèn)值為31536000（365天）。在使用工具進(jìn)行DDL操作時(shí)，這個(gè)值就不太合理。事實(shí)上，pt-online-schema-change和gh-ost對(duì)其就進(jìn)行了相應(yīng)的調(diào)整，其中，前者60s，后者3s。

2. 如果一個(gè)SQL語法上有效，但執(zhí)行時(shí)報(bào)錯(cuò)，如，列名不存在，其同樣會(huì)獲取MDL鎖，直到事務(wù)結(jié)束才釋放。

上述內(nèi)容就是MySQL表結(jié)構(gòu)怎樣變更Metadata Lock，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。

文章題目：MySQL表結(jié)構(gòu)怎樣變更MetadataLock
本文來源：http://www.rwnh.cn/article24/gjhhje.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站導(dǎo)航、外貿(mào)建站、網(wǎng)站營銷、靜態(tài)網(wǎng)站、品牌網(wǎng)站建設(shè)、電子商務(wù)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)