切換語言為:簡體
Mysql 的後設資料鎖

Mysql 的後設資料鎖

  • 爱糖宝
  • 2024-07-26
  • 2081
  • 0
  • 0

什麼是後設資料鎖?

英文名叫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;

結果為: Mysql 的後設資料鎖

先看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演算法執行過程中會出現如下情況:

  Mysql 的後設資料鎖 

其中兩個奇怪名字的檔案就是臨時表相關的檔案,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來實現了,比如執行過程中會出現如下情況: 

Mysql 的後設資料鎖 也會生成臨時檔案,只不過是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表: Mysql 的後設資料鎖 然後用連線1開啟事務並執行查詢:

begin; 
select * from user_info;

注意,是能查出結果的!是能查出結果的!是能查出結果的! Mysql 的後設資料鎖

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

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

因為連線1的事務在執行select的時候,給user_info加了一把SHARED_READ的後設資料鎖,但是事務一直沒有提交,SHARED_READ後設資料鎖一直沒有釋放,而連線2的DDL操作在資料複製完了之後需要修改後設資料,因此需要給user_info加EXCLUSIVE型別的後設資料鎖,這個時候就阻塞了,從而導致DDL操作一直沒有完成,從而導致連線1的select的查詢結果一直沒有發生變化,此時我們也可以看一下檔案的情況: Mysql 的後設資料鎖 發現臨時檔案確實已經建立,並且資料也確實已經複製完成了,注意臨時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型別的鎖,按上面同樣的步驟,也會出現同樣的後設資料鎖情況: Mysql 的後設資料鎖

而檔案的情況也類似: 

Mysql 的後設資料鎖 也是資料複製完了,但臨時檔案一直在,就是因為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;

Mysql 的後設資料鎖 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(*)都不能執行,大家可以再試試別的)。

0則評論

您的電子郵件等資訊不會被公開,以下所有項目均必填

OK! You can skip this field.