MySQL 全域性鎖和表鎖

邢闖洋發表於2021-05-26
MySql

根據加鎖的範圍，MySQL 裡面的鎖大致可以分成全域性鎖，表級鎖，行鎖。 
行鎖已經在前面幾篇文章說過

1. 全域性鎖

全域性鎖就是對整個資料庫例項加鎖。MySQL 提供了一個加全域性讀鎖的方法，命令是Flush tables with read lock (FTWRL)。 

當你需要讓整個庫處於只讀狀態的時候，可以使用這個命令，之後其他執行緒的以下語句會被阻塞：資料更新語句（資料的增刪改）、資料定義語句（包括建表、修改表結構等）和更新類事務的提交語句。

1.1 全域性鎖使用場景

全域性鎖的典型使用場景是，做全庫邏輯備份（mysqldump）。重新做主從時候 
也就是把整庫每個表都 select 出來存成文字。 
以前有一種做法，是通過 FTWRL 確保不會有其他執行緒對資料庫做更新，然後對整個庫做備份。注意，在備份過程中整個庫完全處於只讀狀態。

資料庫只讀狀態的危險性：

  • 如果你在主庫上備份，那麼在備份期間都不能執行更新，業務基本上就能停止。
  • 如果你在從庫上備份，那麼備份期間從庫不能執行主庫同步過來的binlog，會導致主從延遲。

注：上面邏輯備份，是不加--single-transaction引數

看來加全域性鎖不太好。但是細想一下，備份為什麼要加鎖呢？來看一下不加鎖會有什麼問題？

1.2 不加鎖產生的問題

比如手機卡，購買套餐資訊

這裡分為兩張表 u_acount (用於餘額表)，u_pricing (資費套餐表)
步驟:
1 . u_account 表中資料 使用者A 餘額：300
    u_pricing 表中資料 使用者A 套餐：空

2. 發起備份，備份過程中先備份u_account表，備份完了這個表，這個時候u_account 使用者餘額是300 
3. 這個時候套使用者購買了一個資費套餐100，餐購買完成，寫入到u_print套餐表購買成功，備份期間的資料。
4. 備份完成

可以看到備份的結果是，u_account 表中的資料沒有變， u_pricing 表中的資料 已近購買了資費套餐100.

哪這時候用這個備份檔案來恢復資料的話，使用者A 賺了100 ，使用者是不是很舒服啊。但是你的想想公司利益啊。　　

也就是說，不加鎖的話，備份系統備份的得到的庫不是一個邏輯時間點，這個資料是邏輯不一致的。

1.3 為什麼需要全域性讀鎖(FTWRL)

可能有的人在疑惑，官方自帶的邏輯備份工具是 mysqldump。當 mysqldump 使用引數--single-transaction的時候，導資料之前就會啟動一個事務，來確保拿到一致性快照檢視。而由於 MVCC 的支援，這個過程中資料是可以正常更新的。

為什麼還需要 FTWRL 呢？ 
一致性讀是好，但前提是引擎要支援這個隔離級別。比如，對於 MyISAM 這種不支援事務的引擎，如果備份過程中有更新，總是隻能取到最新的資料，那麼就破壞了備份的一致性。這時，我們就需要使用FTWRL 命令了。

所以，single-transaction 方法只適用於所有的表使用事務引擎的庫。如果有的表使用了不支援事務的引擎，那麼備份就只能通過 FTWRL 方法。這往往是 DBA 要求業務開發人員使用 InnoDB 替代 MyISAM 的原因之一。

1.4 全域性鎖兩種方法

一. FLUSH TABLES WRITE READ LOCK 
二. set global readonly=true

既然要全庫只讀，為什麼不使用 set global readonly=true 的方式呢？確實 readonly 方式也可以讓全庫進入只讀狀態，但我還是會建議你用 FTWRL 方式，主要有幾個原因： 

一是，在有些系統中，readonly 的值會被用來做其他邏輯，比如用來判斷一個庫是主庫還是備庫。因此，修改 global 變數的方式影響面更大，我不建議你使用。 

二是，在異常處理機制上有差異。如果執行FTWRL 命令之後由於客戶端發生異常斷開，那麼 MySQL 會自動釋放這個全域性鎖，整個庫回到可以正常更新的狀態。而將整個庫設定為 readonly 之後，如果客戶端發生異常，則資料庫就會一直保持 readonly 狀態，這樣會導致整個庫長時間處於不可寫狀態，風險較高。

三是，readonly 對super使用者許可權無效

注 ：業務的更新不只是增刪改資料（DML)，還有可能是加欄位等修改表結構的操作（DDL）。不論是哪種方法，一個庫被全域性鎖上以後，你要對裡面任何一個表做加欄位操作，都是會被鎖住的。

即使沒有被全域性鎖住，加欄位也不是就能一帆風順的，還有表級鎖了

2. 表級鎖

MySQL 裡面表級別的鎖有兩種：一種是表鎖，一種是後設資料鎖（meta data lock，MDL)。

2.1 表鎖

lock tables 表名 read; #該表可以讀，不能ddl 和 dml 中增刪改，只能讀取表資料 
lock tables 表名 read; # 既不能讀，也不能寫 

 

 

表鎖的語法是 lock tables … read/write。與 FTWRL 類似，可以用 unlock tables 主動釋放鎖，也可以在客戶端斷開的時候自動釋放。需要注意，lock tables 語法除了會限制別的執行緒的讀寫外，也限定了本執行緒接下來的操作物件。

舉個例子, 如果在某個執行緒 A 中執行 lock tables t1 read, t2 write; 這個語句，則其他執行緒寫 t1、讀寫 t2 的語句都會被阻塞。同時，執行緒 A 在執行 unlock tables 之前，也只能執行讀 t1、讀寫 t2 的操作。連寫 t1 都不允許，自然也不能訪問其他表。

在還沒有出現更細粒度的鎖的時候，表鎖是最常用的處理併發的方式。而對於 InnoDB 這種支援行鎖的引擎，一般不使用 lock tables 命令來控制併發，畢竟鎖住整個表的影響面還是太大

 

2.2 MDL 鎖

另一類表級的鎖是 MDL（metadata lock)。MDL 不需要顯式使用，在訪問一個表的時候會被自動加上。MDL 的作用是，保證讀寫的正確性。你可以想象一下，如果一個查詢正在遍歷一個表中的資料，而執行期間另一個執行緒對這個表結構做變更，刪了一列，那麼查詢執行緒拿到的結果跟表結構對不上，肯定是不行的。

 因此，在 MySQL 5.5 版本中引入了 MDL，當對一個表做增刪改查操作的時候，加 MDL讀鎖；當要對錶做結構變更操作的時候，加 MDL 寫鎖

  • 讀鎖之間不互斥，因此你可以有多個執行緒同時對一張表增刪改查。

  • 讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性。因此，如果有兩個執行緒要同時給一個表加欄位，其中一個要等另一個執行完才能開始執行。

雖然 MDL 鎖是系統預設會加的，但卻是你不能忽略的一個機制。 
比如下面這個例子，我經常看到有人掉到這個坑裡：給一個小表加個欄位，導致整個庫掛了。 
肯定知道，給一個表加欄位，或者修改欄位，或者加索引，需要掃描全表的資料。在對大表操作的時候，你肯定會特別小心，以免對線上服務造成影響。而實際上，即使是小表，操作不慎也會出問題。我們來看一下下面的操作序列，假設表 t 是一個小表。

 

注: 表t 是 innodb 表，mysql版本是5.7.24 自動提交開啟
1. sessionA:
begin;
select * from t limit 1;

2. sessionB:
select * from t limit 1;

3. sessionC:
alter table t add f int;
#會mdl鎖住

4. sessionD:
select * from t limit 1;

　　

show full processlist 檢視mdl 鎖詳情

我們可以看到 session A 先啟動，這時候會對錶 t 加一個 MDL 讀鎖。由於 session B 需要的也是 MDL 讀鎖，因此可以正常執行。

之後 session C 會被 blocked，是因為 session A 的 MDL 讀鎖還沒有釋放，而 sessionC 需要MDL 寫鎖，因此只能被阻塞。

如果只有 session C 自己被阻塞還沒什麼關係，但是之後所有要在表 t 上新申請 MDL 讀鎖的請求也會被 session C 阻塞。前面說了，所有對錶的增刪改查操作都需要先申請MDL 讀鎖，就都被鎖住，等於這個表現在完全不可讀寫了。

 如果某個表上的查詢語句頻繁，而且客戶端有重試機制，也就是說超時後會再起一個新session 再請求的話，這個庫的執行緒很快就會爆滿。

事務中的 MDL 鎖，在語句執行開始時申請，但是語句結束後並不會馬上釋放，而會等到整個事務提交後再釋放。 
注 ： 一般行鎖都有鎖超時時間。但是MDL鎖沒有超時時間的限制，只要事務沒有提交就會一直鎖注。

2.2.1 怎麼解決了這個MDL鎖

上面不是說了嗎， 提交或者回滾這個事務。所以要找到這個事務 

怎麼找到這個事務， 通過information_schema.innodb_trx 檢視事務的執行時間

 

# 檢視事務超過60s的事務
mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\G;
trx_started 表示什麼時候執行的這個事務

#檢視系統當前時間
mysql> select now();

事務開始時間和系統現在時間，一看事務執行了這麼久。

檢視這個執行緒id

怎麼處理了這個長事務的執行緒id了 

首先看show full processlist; 中host 哪個欄位 ，到底是誰連線了資料庫。例：我上面是localhost環境，進去commit或者/rollback ,哪如果不是localhost 環境了，是程式連線了這時候就要kill掉了

2.2.2 我身上發生的趣事

上次有個DBA 問我， 造成很大主從延遲，說要怎麼解決。 
我說你怎麼解決的延遲， 知道主從延遲造成的具體的原因嗎？ 
ta給我說，開啟了多執行緒， 但是延遲還是很大，基本沒怎麼用到多執行緒。 
我說你怎麼知道主從延遲，需要開啟多執行緒複製來解決， ta給我說，網上別人blog這莫說的，我一口老血吐出來。

後來又問ta, 主從延遲正常情況下，做了什麼操作， ta給我說修改了alter 表結構。 
然後讓ta 看是不是mdl 鎖造成的，讓ta show full processlist，一看果然是mdl 鎖原因。 
然後跟ta 說 找長事務， 找到以後跟開發商議，這個長事務在做什麼操作可不可以kill掉。

注：這是我真實遇到，別人問我這樣問題。首先你要知道造成這個結果，你事先做了什麼操作， 然後解決這個問題，最根本你還是要知道原因，然後下次避免。 
還有網上的環境，系統版本，應用版本，遇到問題的情況，跟你是不是一樣，有時候不要盲目相信。

2.3 如何安全地給小表加欄位？

首先我們要解決長事務，事務不提交，就會一直佔著 MDL 鎖。在 MySQL 的information_schema 庫的 innodb_trx 表中，你可以查到當前執行中的事務。如果你要做 DDL 變更的表剛好有長事務在執行，要考慮先暫停 DDL，或者 kill 掉這個長事務。這也是為什麼需要在低峰期做ddl 變更，當然也要考慮具體做什麼ddl，參考官方的online ddl。

2.4 online ddl 過程

  1. 拿MDL寫鎖

  2. 降級成MDL讀鎖

  3. 真正做DDL

  4. 升級成MDL寫鎖

  5. 釋放MDL鎖

1、2、4、5如果沒有鎖衝突，執行時間非常短。第3步佔用了DDL絕大部分時間，這期間這個個表可以正常讀寫資料，是因此稱為”online”

相關文章