Mysql 的後設資料鎖

什麼是後設資料鎖？

英文名叫Metadata Lock，縮寫為MDL，顧名思義，它是針對後設資料的一種鎖，鎖的是後設資料。

那什麼是後設資料？

一張表有100條記錄，這裏的記錄我們可以稱之為表資料，一張表的名字叫t1，有c1、c2兩個欄位，c1的型別是int，c2的型別是varchar，這裏的表名、欄位名、欄位型別叫做表的後設資料，當我們修改某行記錄時，叫做修改表資料，當我們修改表名或欄位型別時，叫做修改表的後設資料。

什麼情況要鎖後設資料呢？

很簡單，用到後設資料的時候就要鎖，那什麼時候用到後設資料呢？注意，表名也是一個表的後設資料，因此，只要是針對表的操作，都需要用到對應表的後設資料，都需要給該表加後設資料鎖。

啊？任何對錶的操作都要加後設資料鎖嗎？就連最普通的select查詢也要嗎？

要，或者說MySQL就是這麼做的，不過，大家還記得大明湖畔的讀寫鎖嗎，或者說InnoDB中的共享鎖和排他鎖。

後設資料鎖也分為共享鎖和排他鎖，額外說一句，後設資料鎖是屬於MySQL級別的，而行鎖、間隙鎖這些是屬於InnoDB級別的，因此後設資料鎖的級別更高，一條SQL執行時，都會先在MySQL層加後設資料鎖，加成功了之後才加InnoDB中的鎖。

既然是後設資料鎖，讀後設資料是最基本的需求，在這個基礎上，再來根據其他需求劃分不同的型別。

如果只需要讀後設資料，不需要修改後設資料，不需要讀表資料，不需要修改表資料，那就加MDL_SHARED型別的後設資料鎖，比如CREATE TABLE t2 LIKE t1語句就屬於這種情況，只需要讀取t1表的後設資料。

如果需要讀後設資料，不需要修改後設資料，但需要讀表資料，不需要修改表資料，那就加MDL_SHARED_READ型別的鎖，比如大家關心的普通select查詢就屬於這種情況，既不需要修改後設資料，也不需要修改表資料，只需要讀資料。

如果需要讀後設資料，不需要修改後設資料，但需要讀表資料，並需要修改表資料，那就加MDL_SHARED_WRITE型別的鎖，比如大家熟悉的insert、update、delete就屬於這種情況，不需要修改後設資料，但需要修改表資料。

如果既需要修改後設資料，又需要修改表資料，那就加MDL_EXCLUSIVE型別的鎖，比如某些執行DDL語句就屬於這種情況，比如加欄位、修改欄位型別，這些操作不僅需要修改後設資料，還可能需要修改表資料，比如給表新增欄位時，如果指定了欄位預設值，那麼就需要修改所有記錄，大家可以瞭解一下資料行格式，從而可以瞭解到更多DDL時需要修改記錄的情況（關注我，我後面會分析，公眾號：IT周瑜）。

因此，大多數執行SQL的時候，都需要加後設資料鎖，只不過可能型別不一樣而已。

那一個連線（注意，我用的是連線，而不是事務，因為事務是InnoDB中的概念，後設資料鎖是屬於MySQL層的概念，當然，爲了方便理解，可以把連線和事務劃上等號），那一個連線給某張表加了某種後設資料鎖，其他連線還能操作這張表嗎？

這就得看各個連線分別加的什麼型別的後設資料鎖了，實際上，不管是MySQL中的後設資料鎖，還是InnoDB中的行鎖、意向鎖都有一個概念，叫做鎖相容。（行鎖和意向鎖，我已經研究清楚了，關注我，我後面也會分析，公眾號：IT周瑜）

什麼是鎖相容？

如果連線1加了A鎖，連線2現在想要加B鎖，如果A鎖和B鎖是相容的，那麼連線2就能成功加到B鎖，如果是不相容的，那麼連線2就需要等待連線1釋放A鎖。

那以上幾種型別的後設資料鎖相容性是怎樣的呢？

| | MDL_SHARED | MDL_SHARED_READ | MDL_SHARED_WRITE | MDL_EXCLUSIVE | | --- | --- | --- | --- | --- | | MDL_SHARED | + | + | + | - | | MDL_SHARED_READ | + | + | + | - | | MDL_SHARED_WRITE | + | + | + | - | | MDL_EXCLUSIVE | - | - | - | - |

+號表示相容，-號表示不相容

如何理解呢？

假如一個連線給t表加了MDL_SHARED鎖，另一個連線也想加MDL_SHARED鎖，當然沒問題了，大家都只是想讀後設資料鎖而已，並不衝突。

假如一個連線給t表加了MDL_SHARED鎖，但是另一個連線想加MDL_EXCLUSIVE鎖，那肯定不行了，我現在要讀後設資料，而你卻想要修改後設資料，會影響到我，會衝突，所以另一個連線只能等待，同時也表示MDL_SHARED鎖和MDL_EXCLUSIVE鎖是不相容的，實際上MDL_EXCLUSIVE鎖和誰都不相容，因為它想修改後設資料。

那如何理解MDL_SHARED和MDL_SHARED_READ、MDL_SHARED_WRITE都相容呢，實際上MDL_SHARED_READ和MDL_SHARED_WRITE相當於是MDL_SHARED的子類，是MySQL做的更進一步區分，select讀操作加的是MDL_SHARED_READ鎖，update等寫操作加的是MDL_SHARED_WRITE鎖，但它們都只是需要讀取後設資料，不需要修改後設資料，因此也是相容的，也就是說當一個連線在執行select，另一個連線在執行update，還來一個連線也執行select，至少在後設資料鎖層面是不衝突的，是可以放行的，是可以加後設資料鎖成功的，至於在InnoDB層面會不會衝突，那就不是後設資料鎖該管的了，後設資料鎖只管後設資料，不管表資料。

那MDL_SHARED_READ鎖和MDL_SHARED_WRITE鎖沒啥用唄？ 也不是，至少我們可以透過檢視後設資料鎖的情況，知道當前各個連線分別加了什麼型別的後設資料鎖，並且知道鎖是不是阻塞了，從而知道鎖衝突的情況，根據鎖型別知道是什麼樣的SQL阻塞了。

如何檢視後設資料鎖的阻塞情況？

後設資料鎖可以檢視？可以，我給你看看。

首先執行：

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

然後開啟事務一，並執行簡單的查詢，但是事務先不提交：

begin;
select * from user_info;

然後開啟事務二，並執行一個DDL語句：

begin;
alter table user_info drop column phone;

你會發現，DDL語句執行時會轉圈圈，也就是會阻塞住。

然後執行以下SQL檢視後設資料鎖的情況：

SELECT * FROM performance_schema.metadata_locks;

結果為：

先看OWNER_THREAD_ID欄位，該欄位表示持有後設資料鎖的執行緒，發現只有兩個執行緒61和67。

再看OBJECT_NAME欄位，該欄位表示後設資料鎖鎖的物件，比如61和67兩個執行緒都鎖了user_info這張表。

再看LOCK_TYPE欄位，該欄位表示後設資料鎖的型別，仔細看看就能發現：

1. 61執行緒針對user_info表加了SHARED_READ型別的鎖

2. 67執行緒針對user_info表加了EXCLUSIVE和SHARED_UPGRADABLE型別的鎖（SHARED_UPGRADABLE不用管）

再看LOCK_STATUS欄位，該欄位表示鎖當前的狀態，仔細看看就能發現：

1. 61執行緒，狀態是GRANTED，表示成功加到了SHARED_READ型別的後設資料鎖

2. 67執行緒，雖然SHARED_UPGRADABLE型別的鎖加成功了，但是EXCLUSIVE型別的鎖是PENDING狀態，表示EXCLUSIVE型別的元數鎖沒有加成功，在等待，等待什麼呢？自然就是等待61執行緒釋放掉SHARED_READ型別的後設資料鎖，因為EXCLUSIVE和SHARED_READ是不相容的，所以67執行緒需要等待

那61執行緒什麼時候釋放SHARED_READ後設資料鎖呢？很簡單，當事務提交或回滾就會釋放了。

能否先總結一下後設資料鎖的作用？

可以，一般的select查詢和DML操作只需要讀取後設資料，而DDL操作則需要修改後設資料，所以後設資料鎖更多是用來保證DML和DDL、DDL和DDL之間的併發安全性，DML和DML之間的併發安全由InnoDB中的鎖來解決。

那傳說中的Online DDL是不是跟這個後設資料鎖有關係？

必須有關係，而且有很大的關係，要理解Online DDL就得理解DDL的底層執行過程，在MySQL5.7中有兩種DDL演算法，一種是COPY，一種是INPLACE，MySQL8.0中新增了一種INSTANT演算法（本文不介紹），在執行DDL語句時，MySQL會自動判斷應該用哪種演算法，當然你可以手動指定（後面會看到如何指定），但並不是你指定了INPLACE演算法，MySQL就一定會用，如果當前DDL操作不支援INPLACE演算法，MySQL會給出提示，讓你用COPY演算法。

COPY演算法DDL的底層執行流程

比如，給t1表新增一個欄位，如果選擇的是COPY演算法，那麼大概流程是這樣的：

1. 在磁碟生成一張臨時表，臨時表的表名是MySQL自動生成的，臨時表的欄位是t1表+新欄位

2. 然後將t1表的資料複製到臨時表中，我透過原始碼debug發現，這個過程是從透過呼叫InnoDB的查詢介面每次查詢一行，然後再呼叫InnoDB的插入數據接口，將資料插入到臨時表中，從而完成資料的複製，就是這麼一行一行進行復制的，所以效率很低

3. 資料複製完之後，刪除t1表，並將臨時表重新命名為t1

可以發現，COPY演算法的核心是第二步，最耗時的也是第二步，COPY演算法完全是屬於MySQL層的，它只是呼叫了InnoDB提供的查詢和插入資料的介面。

COPY演算法執行過程中會出現如下情況:

其中兩個奇怪名字的檔案就是臨時表相關的檔案，frm表示表定義檔案，ibd表示表資料檔案，奇怪的名字就是臨時表名。

INPLACE演算法DDL的底層執行流程

而INPLACE演算法，表示“就地”修改，“就地”的意思是直接在InnoDB內部進行修改，你可以認為，如果能採用INPLACE演算法進行DDL（注意，我說的是能，並不是所有的DDL都能採用INPLACE演算法），那麼MySQL層需要做的事情就比較少了，核心步驟都交給InnoDB了，而InnoDB中也分爲了三個步驟，原始碼函式分別為：

1. ha_innobase::prepare_inplace_alter_table()

2. ha_innobase::inplace_alter_table()

3. ha_innobase::commit_inplace_alter_table()

那採用INPLACE演算法，還要不要複製表的資料呢，仍然需要，只不過是InnoDB來實現了，比如執行過程中會出現如下情況： 

也會生成臨時檔案，只不過是InnoDB生成的，當然，frm檔案是MySQL層生成，而另外一個ibd檔案則是InnoDB生成的，我還發現，如果採用INPLACE演算法，是不會像COPY演算法那樣每次從原始表查一行資料，然後插一行資料到臨時表的，至於InnoDb是怎麼將原始表資料複製到臨時表的，肯定採用了效率更高的方式，只不過我還沒研究清楚，這裏就先不介紹了。

不過可以肯定的是，採用INPLACE演算法效率會更高，一方面由InnoDB自身複製資料肯定比COPY演算法那樣由MySQL層呼叫InnoDB查詢和插入資料的介面來複制資料效率要高，另外一方面就是後設資料鎖的使用了。

我相信大家都聽過這樣的結論：如果採用COPY演算法，DDL在複製資料的過程中，DML操作是會被阻塞的，而如果採用INPLACE演算法，DDL在複製資料的過程中，DML操作是不會阻塞的，這也是為什麼叫做Online DDL，表示DDL的過程中，可以線上同時執行DML操作。

真的是這樣嗎？我們來測測。

COPY演算法會阻塞select嗎？

我做了一個實驗，表結構是這樣的：

CREATE TABLE `user_info` (
  `id` int,
  `name` varchar(255),
  `phone` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

用儲存過程模擬插入資料：

CREATE DEFINER=`root`@`%` PROCEDURE `InsertUserInfo`(IN num_records INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE phone_prefix CHAR(3) DEFAULT '123';
    DECLARE phone_suffix CHAR(8) DEFAULT '';
    DECLARE name VARCHAR(255);

    WHILE i <= num_records DO
        SET phone_suffix = LPAD(i, 8, '0');
        SET name = CONCAT('User_', i);

        INSERT INTO user_info (id, name, phone)
        VALUES (i, name, CONCAT(phone_prefix, phone_suffix));

        SET i = i + 1;
    END WHILE;
END

呼叫儲存過程：

call my_db.InsertUserInfo(1000000);

開啟三個連線：

1. 連線1用來開啟事務執行select語句

2. 連線2用來進行DDL

3. 連線3用來檢視後設資料情況

先用連線2進行DDL操作，刪除表中的phone欄位，先測試COPY演算法：

alter table user_info drop column phone, ALGORITHM=COPY, LOCK=SHARED;

注意，使用COPY演算法時，LOCK只能是SHARED，不能是NONE，不然MySQL會提示你要用SHARED。

然後用連線3檢視後設資料鎖情況，沒有PENDDING，因為現在只有連線2，執行緒id為78，在使用user_info表： 然後用連線1開啟事務並執行查詢：

begin; 
select * from user_info;

注意，是能查出結果的！是能查出結果的！是能查出結果的！

但是！等了很久之後，它始終能查出phone欄位，難道DDL操作一直沒執行完（DDL操作是在刪除phone欄位）?

並不是，我們用連線3檢視後設資料鎖，發現78執行緒對user_info多加了一把後設資料鎖，而且是EXCLUSIVE型別的，而且是PENDDING的： 78執行緒就是DDL操作的執行緒，也就代表DDL執行緒被阻塞了，為什麼會被阻塞呢？

因為連線1的事務在執行select的時候，給user_info加了一把SHARED_READ的後設資料鎖，但是事務一直沒有提交，SHARED_READ後設資料鎖一直沒有釋放，而連線2的DDL操作在資料複製完了之後需要修改後設資料，因此需要給user_info加EXCLUSIVE型別的後設資料鎖，這個時候就阻塞了，從而導致DDL操作一直沒有完成，從而導致連線1的select的查詢結果一直沒有發生變化，此時我們也可以看一下檔案的情況： 發現臨時檔案確實已經建立，並且資料也確實已經複製完成了，注意臨時ibd檔案的大小。

因此，現在只要連線1提交事務，整個過程就不會阻塞了，連線1提交之後，連線2的DDL操作纔會真正結束，此後連線1再查詢資料時，纔不會查詢phone欄位。

因此，可以得出結論，COPY演算法的DDL在資料複製過程中，並不會阻塞查詢操作，但是由於最後階段會加EXCLUSIVE型別的後設資料鎖，從���導致DDL操作可能會阻塞（比如連線1的事務一直不提交），一直完成不了。

因此如果有長事務，儘管是查詢操作，也可能會導致DDL操作阻塞，所以要儘量避免長事務。

也就是說，select如果一直不提交，會阻塞DDL，反過來，DDL操作複製資料之後都會需要修改後設資料，需要加EXCLUSIVE型別的後設資料鎖，也可能會阻塞select操作，因此我們只能說，DDL操作的資料複製過程中不會阻塞select，不能說DDL整個操作不會阻塞select。

INPLACE演算法會阻塞select嗎？

再來看看INPLACE演算法，首先，INPLACE演算法的執行速度要快很多，在不產生鎖衝突的情況下，COPY演算法刪除欄位：

alter table user_info drop column phone, ALGORITHM=COPY, LOCK=SHARED;

需要執行17秒左右。

而INPLACE演算法：

alter table user_info drop column phone, ALGORITHM=INPLACE, LOCK=NONE;

只需要執行3秒左右。

這效率不是一般的高。

經過上面同樣的測試步驟，發現INPLACE演算法DDL在複製資料過程中，也不會阻塞select查詢，但是在資料複製完之後，也會加EXCLUSIVE型別的鎖，按上面同樣的步驟，也會出現同樣的後設資料鎖情況：

而檔案的情況也類似： 

也是資料複製完了，但臨時檔案一直在，就是因為DDL操作最後被阻塞了，和COPY演算法類似，只有連線1提交事務，整個DDL過程纔會結束。

從這個測試看，COPY演算法和INPLACE演算法，只有效率上的不同，在資料複製過程中都可以查詢資料，但是查詢事務如果一直不提交，則會阻塞DDL操作，因為DDL操作的最後需要修改後設資料，會給表加EXCLUSIVE型別的鎖，從而和查詢事務的SHARED_READ型別的鎖衝突，導致DDL操作阻塞。

注意，我說的是資料複製過程中不會阻塞select查詢，資料複製的前面步驟和後面步驟其實都會給表加EXCLUSIVE型別的鎖（時間比較短），從而可能導致短暫的阻塞select查詢

那假如是不是查詢語句呢，而是insert、update、delete這些DML語句呢？

COPY演算法會阻塞DML操作嗎？為什麼？

先測試COPY演算法的DDL操作，先利用連線2執行：

alter table user_info drop column phone, ALGORITHM=COPY, LOCK=SHARED;

這個語句，需要執行一段時間（表裏的資料增加到了200萬），在這個過程中，用連線1執行：

begin; 
insert into user_info values(3000001, 'zhangfei');

發現insert語句會直接阻塞。

連線3檢視後設資料鎖的情況：

SELECT * FROM performance_schema.metadata_locks;

79執行緒是DDL執行緒，81執行緒是insert語句的執行緒，加的是SHARED_WRITE型別的鎖，狀態是PENDING，因此insert語句阻塞了，因此看出，在使用COPY演算法執行DDL過程中，就算是資料複製過程中，其他連線都不能執行insert語句，同樣，我也測了update和delete，和insert語句一樣，都是加的SHARED_WRITE型別的鎖，所以都會阻塞，因此COPY演算法的DDL，不僅資料複製慢，而且過程中還導致其他事務不能執行DML操作，只能執行select。

INPLACE演算法會阻塞DML操作嗎？為什麼？

採用同樣的流程，來測試INPLACE演算法。

先用連線2執行INPLACE演算法的DDL操作：

alter table user_info drop column phone, ALGORITHM=INPLACE, LOCK=NONE;

然後用連線1執行insert、update、delete操作：

begin;
update user_info set name = '111' where id = 1;
insert into user_info values(3000011, 'zhangfei', '123');
delete from user_info where id = 1;

會發現和COPY演算法確實不一樣，此時連線1是能執行這些DML操作的，這就是Online的意思，如果使用INPLACE演算法進行DDL，那麼在資料複製過程中，其他連線可以同時進行DML操作。

但是和select查詢一樣，如果DML操作的事務一直不提交，那麼DDL操作也一直無法完成，因為DDL操作在資料複製完了之後，需要修改後設資料，從而會加EXCLUSIVE型別的後設資料鎖，而其他連線，不管是執行select讀操作，還是執行insert、update、delete這些寫操作，都會加SHARED_READ或SHARED_WRITE類的後設資料鎖，而事務一直不提交，那麼鎖就一直不釋放，從而導致DDL操作在執行完資料複製操作之後，EXCLUSIVE型別的鎖加不成功，導致DDL操作完成不了。

COPY演算法和INPLACE演算法有什麼相同點和不同點？

最後，再總結一遍：

1. 對於COPY演算法和INPLACE演算法的DDL操作，如果另外一個連線執行的是select查詢，那麼兩種演算法除開資料複製效率的差別之外，沒有其他差別，都能查到資料。

2. 對於COPY演算法的DDL操作，如果另外一個連線執行的是insert、update、delete等寫操作，則這些寫操作會阻塞，直到DDL操作完成

3. 對於INPLACE演算法的DDL操作，如果另外一個連線執行的insert、update、delete等寫操作，這些寫操作不會阻塞

長事務和Online DDL會產生什麼影響？

不管是COPY演算法還是INPLACE演算法，只要DDL操作複製資料結束之後，DDL操作都需要修改後設資料，從而都需要給後設資料加EXCLUSIVE型別的鎖，因此其他連線如果一直不提交事務，就會導致DDL操作一直獲取不到EXCLUSIVE型別後設資料鎖，也就會導致DDL操作一直完成不了，因此大家在進行DDL操作時，就算是用INPLACE演算法，最好也在業務低峰期，這樣DDL操作能儘快完成。

Online DDL什麼情況下會導致鎖表？

特別要注意，當DDL操作給表加了EXCLUSIVE型別的後設資料鎖之後，假如來了一個新連線4，它執行最簡單的select查詢都是執行不了的，因為它需要加SHARED_READ型別的後設資料鎖，SHARED_READ和EXCLUSIVE型別的後設資料鎖是衝突的。

也就是可能會出現以下“鎖表”的情況:

1. 連線2先執行DDL操作（不管是COPY還是INPLACE演算法）

2. 連線1在DDL操作過程中執行了select、insert、update、delete等操作，但一直不提交，一直佔用著SHARED_READ或SHARED_WRITE型別的鎖

3. 連線2的DDL運算元據複製執行完了，需要加EXCLUSIVE型別的鎖，但是隻能阻塞

4. 連線3來執行select、insert、update、delete等操作，需要加SHARED_READ或SHARED_WRITE型別的鎖，此時就會阻塞，導致SQL執行不了，轉圈圈

這種情況就比較嚴重了，因此一定要儘快讓連線1的事務提交，不然就會一直卡著，整個表都無法處理請求，看上去就是“鎖表”了。

總結

最後，再總結一下，凡事要用到表的時候，就會給表加後設資料鎖，只不過不同操作加的不同型別的後設資料鎖，不同型別的後設資料鎖之間可能相容，可能衝突。

比如select、insert、update、delete這些操作，只需要讀表的後設資料，因此會給表加SHARED型別的後設資料鎖，而很多DDL操作需要修改表的後設資料，比如新增欄位，因此需要加EXCLUSIVE型別的鎖，EXCLUSIVE和SHARED是不相容的，所以可能存在，DML和DML之間的後設資料鎖不會衝突，但是DML和DDL，DDL和DDL之間的後設資料鎖會產生衝突。

而DML和DDL之間，就是通常說的Online DDL，表示DDL的資料複製期間，其他事務可以正常執行DML，但是資料複製完了之後仍然會跟DML產生衝突。

因此，系統中一定儘量避免比較長事務，比如慢查詢，如果在DDL期間遇到了慢查詢，那麼DDL整個操作就得等待慢查詢執行結束纔會完成，而如果DDL操作一直不結束，就相當於一直給表加了EXCLUSIVE型別的鎖，導致“鎖表”，一旦“鎖表”，其他新事務針對這個表的任何SQL都無法執行了(我測了select * 和 select count(*)都不能執行，大家可以再試試別的)。