如何理解mysql Meta Lock 機制

如何理解mysql Meta Lock 機制，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

  原因   

  最近在上線的 過程中出現主從數據不一致的現象 。發現問題是 slave 進行ddl 操作時候等待   Waiting for table metadata lock

 發現查詢用戶有幾個進程在連接上，字節把這些線程kill掉。slave 基本上瞬間執行完同步

因為是停應用的上線操作對對等待時間較長。但是對業務未有有影響。

   發現時間，未啟動應用。也造成從庫的雪崩問題。

為了解決該問題。并且在未來對線上停機時間要求比較嚴格。停機時間較短 or 記性讀寫分離 or 進行ddl時候 進行下列分析

為什么要有MDL 鎖

在MySQL5.1及之前的版本中，如果有未提交的事務trx，當執行DROP/RENAME/ALTER TABLE RENAME操作時，不會被其他事務阻塞住。這會導致如下問題(MySQL bug#989)

master：

未提交的事務，但SQL已經完成(binlog也準備好了)，表schema發生更改，在commit的時候不會被察覺到。

slave：

在binlog里是以事務提交順序記錄的，DDL隱式提交，因此在備庫先執行DDL，后執行事務trx，由于trx作用的表已經發生了改變，因此trx會執行失敗。

在DDL時的主庫DML壓力越大，這個問題觸發的可能性就越高

一個簡單的例子：

session1，set autocommit=0，對表b執行一條DML

root@xxx 11:48:28>set autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

root@xxx 11:48:35>insert into b values (NULL,4);

Query OK, 1 row affected (0.00 sec)

session2，執行rename table a to tmp_b

root@xxx 11:48:23>rename table b to tmp_b;

Query OK, 0 rows affected (0.01 sec)

session1：commit;

root@xxx 11:49:00>show binlog events;

+——————+—–+—-———+———–+——–—–+—————————————+

| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |

+——————+—–+—-———+———–+——–—–+—————————————+

| mysql-bin.000001 |   4 | Format_desc |        12 |         106 | Server ver: 5.1.48-log, Binlog ver: 4 |

| mysql-bin.000001 | 106 | Query       |        12 |         191 | use `xxx`; rename table b to tmp_b    |

| mysql-bin.000001 | 191 | Query       |        12 |         258 | BEGIN                                 |

| mysql-bin.000001 | 258 | Table_map   |        12 |         298 | table_id: 195 (xxx.b)                 |

| mysql-bin.000001 | 298 | Write_rows  |        12 |         336 | table_id: 195 flags: STMT_END_F       |

| mysql-bin.000001 | 336 | Xid         |        12 |         363 | COMMIT /* xid=737 */                  |

+——————+—–+—-———+———–+——–—–+—————————————+

顯然當這樣的Binlog同步到備庫的話，必然會導致復制中斷。

在5.1里可以通過如下步驟繞過bug：

>set autocommit = 0;

>lock tables t1 write;

> drop table t1 / alter table t1 rename to t2

rename table t1 to t2這樣的DDL不適用于上述方法。

在5.5引入了MDL（meta data lock）鎖來解決在這個問題，至于5.1，官方已經明確回復不會FIX，太傷感了。。。

MDL 的類型
  metadata lock也是一種鎖。每個metadata lock都會定義鎖住的對象，鎖的持有時間和鎖的類型。
2.1 按照對象/范圍劃分

MDL按鎖住的對象來分類，可以分為global,commit,schema, table, function,procedure,trigger,event,這些對象發生鎖等待時，我們在show processlist可以分別看到如下等待信息。
 

1. Waiting for global read lock 
   

2. Waiting for commit lock
   

3. Waiting for schema metadata lock
   

4. Waiting for table metadata lock
   

5. Waiting for stored function metadata lock
   

6. Waiting for stored procedure metadata lock
   

7. Waiting for trigger metadata lock
   

8. Waiting for event metadata lock

2.2 按照鎖的持有時間

2.3 按照操作的對象

關于global對象
   主要作用是防止DDL和寫操作的過程中，執行set golbal_read_only =on或flush tables with read lock;
 關于commit對象鎖
  主要作用是執行flush tables with read lock后，防止已經開始在執行的寫事務提交。
  insert/update/delete在提交時都會上（COMMIT，MDL_EXPLICIT，MDL_INTENTION_EXCLUSIVE）鎖。

2.4 MDL 鎖的兼容性矩陣





三、幾種典型語句的加(釋放)鎖流程
1.select語句操作MDL鎖流程
   1)Opening tables階段，加共享鎖
     a)   加MDL_INTENTION_EXCLUSIVE鎖
     b)   加MDL_SHARED_READ鎖
   2)事務提交階段，釋放MDL鎖
     a)   釋放MDL_INTENTION_EXCLUSIVE鎖
     b)   釋放MDL_SHARED_READ鎖
2. DML語句操作MDL鎖流程
  1)Opening tables階段，加共享鎖
     a)   加MDL_INTENTION_EXCLUSIVE鎖
     b)   加MDL_SHARED_WRITE鎖
  2)事務提交階段，釋放MDL鎖
     a)   釋放MDL_INTENTION_EXCLUSIVE鎖
     b)   釋放MDL_SHARED_WRITE鎖
3. alter操作MDL鎖流程
  1)Opening tables階段，加共享鎖
     a)   加MDL_INTENTION_EXCLUSIVE鎖
     b)   加MDL_SHARED_UPGRADABLE鎖，升級到MDL_SHARED_NO_WRITE鎖
  2)操作數據，copy data，流程如下：
     a)   創建臨時表tmp，重定義tmp為修改后的表結構
     b)   從原表讀取數據插入到tmp表
  3)將MDL_SHARED_NO_WRITE讀鎖升級到MDL_EXCLUSIVE鎖
     a)   刪除原表，將tmp重命名為原表名
  4)事務提交階段，釋放MDL鎖
     a)   釋放MDL_INTENTION_EXCLUSIVE鎖
     b)   釋放MDL_EXCLUSIVE鎖

四、典型問題分析
通常情況下我們關注MDL鎖，大部分情況都是線上DB出現異常了。那么出現異常后，我們如何去判斷是MDL鎖導致的呢。監視MDL鎖主要有兩種方法，一種是通過show  processlist命令，判斷是否有事務處于“Waiting for table metadata lock”狀態，另外就是通過mysql的profile，分析特定語句在每個階段的耗時時間。
拋出幾個問題：
select 與alter是否會相互阻塞
dml與alter是否會相互阻塞
select與DML是否會相互阻塞
結合第三節幾種語句的上鎖流程，我們很容易得到這三個問題的答案。語句會在阻塞在具體某個環節，可以通過profile來驗證我們的答案是否正確。
第一個問題，當執行select語句時，只要select語句在獲取MDL_SHARED_READ鎖之前，alter沒有執行到rename階段，那么select獲取MDL_SHARED_READ鎖成功，后續有alter執行到rename階段，請求MDL_EXCLUSIVE鎖時，就會被阻塞。rename階段會持有MDL_EXCLUSIVE鎖，但由于這個過程時間非常短(大頭都在copy數據階段)，并且是alter的最后一個階段，所以基本感覺不到alter會阻塞select語句。由于MDL鎖在事務提交后才釋放，若線上存在大查詢，或者存在未提交的事務，則會出現ddl卡住的現象。這里要注意的是，ddl卡住后，若再有select查詢或DML進來，都會被堵住，就會出現threadrunning飆高的情況。
第二個問題，alter在opening階段會將鎖升級到MDL_SHARED_NO_WRITE，rename階段再將升級為MDL_EXCLUSIVE，由于MDL_SHARED_NO_WRITE與MDL_SHARED_WRITE互斥，所以先執行alter或先執行DML語句，都會導致語句阻塞在opening tables階段。

第三個問題，顯然，由于MDL_SHARED_WRITE與MDL_SHARED_READ兼容，所以它們不會因為MDL而導致等待的情況。

關于5.7 對MDL 鎖的改進

在MySQL 5.7里對MDL子系統做了更為徹底的優化。主要從以下幾點出發：

第一，盡管對MDL HASH進行了分區，但由于是以表名+庫名的方式作為key值進行分區，如果查詢或者DML都集中在同一張表上，就會hash到相同的分區，引起明顯的MDL HASH上的鎖競爭

針對這一點，引入了LOCK-FREE的HASH來存儲MDL_lock，LF_HASH無鎖算法基于論文"Split-Ordered Lists: Lock-Free Extensible Hash Tables"，實現還比較復雜。 注：實際上LF_HASH很早就被應用于Performance Schema，算是比較成熟的代碼模塊。

由于引入了LF_HASH，MDL HASH分區特性自然直接被廢除了 。

對應WL#7305， PATCH(Rev:7249)

第二，從廣泛使用的實際場景來看，DML/SELECT相比DDL等高級別MDL鎖類型，是更為普遍的，因此可以針對性的降低DML和SELECT操作的MDL開銷。

為了實現對DML/SELECT的快速加鎖，使用了類似LOCK-WORD的加鎖方式，稱之為FAST-PATH，如果FAST-PATH加鎖失敗，則走SLOW-PATH來進行加鎖。

每個MDL鎖對象（MDL_lock）都維持了一個long long類型的狀態值來標示當前的加鎖狀態，變量名為MDL_lock::m_fast_path_state 舉個簡單的例子：（初始在sbtest1表上對應MDL_lock::m_fast_path_state值為0）

Session 1: BEGIN;

Session 1: SELECT * FROM sbtest1 WHERE id =1; //m_fast_path_state = 1048576, MDL ticket 不加MDL_lock::m_granted隊列

Session 2: BEGIN;

Session 2: SELECT * FROM sbtest1 WHERE id =2; //m_fast_path_state=1048576+1048576=2097152，同上，走FAST PATH

Session 3: ALTER TABLE sbtest1 ENGINE = INNODB; //DDL請求加的MDL_SHARED_UPGRADABLE類型鎖被視為unobtrusive lock，可以認為這個是比上述SQL的MDL鎖級別更高的鎖，并且不相容，因此被強制走slow path。而slow path是需要加MDL_lock::m_rwlock的寫鎖。m_fast_path_state = m_fast_path_state | MDL_lock::HAS_SLOW_PATH | MDL_lock::HAS_OBTRUSIVE

注:DDL還會獲得庫級別的意向排他MDL鎖或者表級別的共享可升級鎖，但為了表述方便，這里直接忽略了，只考慮涉及的同一個MDL_lock鎖對象。

Session 4: SELECT * FROM sbtest1 WHERE id =3; // 檢查m_fast_path_state &HAS_OBTRUSIVE，如果DDL還沒跑完，就會走slow path。

從上面的描述可以看出，MDL子系統顯式的對鎖類型進行了區分（OBTRUSIVE or UNOBTRUSIVE），存儲在數組矩陣m_unobtrusive_lock_increment。 因此對于相容類型的MDL鎖類型，例如DML/SELECT，加鎖操作幾乎沒有任何讀寫鎖或MUTEX開銷。

對應WL#7304, WL#7306 ， PATCH（Rev:7067,Rev:7129）(Rev:7586)

第三，由于引入了MDL鎖，實際上早期版本用于控制Server和引擎層表級并發的THR_LOCK 對于Innodb而言已經有些冗余了，因此Innodb表完全可以忽略這部分的開銷。

不過在已有的邏輯中，Innodb依然依賴THR_LOCK來實現LOCK TABLE tbname READ，因此增加了新的MDL鎖類型來代替這種實現。

實際上代碼的大部分修改都是為了處理新的MDL類型，Innodb的改動只有幾行代碼。

對應WL#6671，PATCH(Rev:8232)

第四，Server層的用戶鎖（通過GET_LOCK函數獲取）使用MDL來重新實現。

用戶可以通過GET_LOCK()來同時獲取多個用戶鎖，同時由于使用MDL來實現，可以借助MDL子系統實現死鎖的檢測。

注意由于該變化，導致用戶鎖的命名必須小于64字節，這是受MDL子系統的限制導致。

對應WL#1159, PATCH(Rev:8356)

關于如何理解mysql Meta Lock 機制問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。