MySQL 全域性鎖、表級鎖、行級鎖，你搞清楚了嗎？

大家好，我是小林。

最近重新補充了《MySQL 有哪些鎖》文章內容：

增加記錄鎖、間隙鎖、net-key 鎖
增加插入意向鎖
增加自增鎖為 innodb_autoinc_lock_mode = 2 模式時，為什麼主從環境會有不安全問題的說明

所以，現在內容還是比較全面的，基本把 MySQL 用到的鎖都說了一遍，大家可以在複習複習。

這次，來說說 MySQL 的鎖，主要是 Q&A 的形式，看起來會比較輕鬆。

不多 BB 了，發車！

在 MySQL 裡，根據加鎖的範圍，可以分為全域性鎖、表級鎖和行鎖三類。

全域性鎖

全域性鎖是怎麼用的？

要使用全域性鎖，則要執行這條命：

flush tables with read lock

執行後，整個資料庫就處於唯讀狀態了，這時其他執行緒執行以下操作，都會被阻塞：

對資料的增刪改操作，比如 insert、delete、update等語句；
對錶結構的更改操作，比如 alter table、drop table 等語句。

如果要釋放全域性鎖，則要執行這條命令：

unlock tables

當然，當對談斷開了，全域性鎖會被自動釋放。

全域性鎖應用場景是什麼？

全域性鎖主要應用於做全庫邏輯備份，這樣在備份資料庫期間，不會因為資料或表結構的更新，而出現備份檔案的資料與預期的不一樣。

舉個例子大家就知道了。

在全庫邏輯備份期間，假設不加全域性鎖的場景，看看會出現什麼意外的情況。

如果在全庫邏輯備份期間，有使用者購買了一件商品，一般購買商品的業務邏輯是會涉及到多張資料庫表的更新，比如在使用者表更新該使用者的餘額，然後在商品表更新被購買的商品的庫存。

那麼，有可能出現這樣的順序：

先備份了使用者表的資料；
然後有使用者發起了購買商品的操作；
接著再備份商品表的資料。

也就是在備份使用者表和商品表之間，有使用者購買了商品。

這種情況下，備份的結果是使用者表中該使用者的餘額並沒有扣除，反而商品表中該商品的庫存被減少了，如果後面用這個備份檔案恢復資料庫資料的話，使用者錢沒少，而庫存少了，等於使用者白嫖了一件商品。

所以，在全庫邏輯備份期間，加上全域性鎖，就不會出現上面這種情況了。

加全域性鎖又會帶來什麼缺點呢？

加上全域性鎖，意味著整個資料庫都是唯讀狀態。

那麼如果資料庫裡有很多資料，備份就會花費很多的時間，關鍵是備份期間，業務只能讀資料，而不能更新資料，這樣會造成業務停滯。

既然備份資料庫資料的時候，使用全域性鎖會影響業務，那有什麼其他方式可以避免？

有的，如果資料庫的引擎支援的事務支援可重複讀的隔離級別，那麼在備份資料庫之前先開啟事務，會先建立 Read View，然後整個事務執行期間都在用這個 Read View，而且由於 MVCC 的支援，備份期間業務依然可以對資料進行更新操作。

因為在可重複讀的隔離級別下，即使其他事務更新了表的資料，也不會影響備份資料庫時的 Read View，這就是事務四大特性中的隔離性，這樣備份期間備份的資料一直是在開啟事務時的資料。

備份資料庫的工具是 mysqldump，在使用 mysqldump 時加上 –single-transaction 引數的時候，就會在備份資料庫之前先開啟事務。這種方法只適用於支援「可重複讀隔離級別的事務」的儲存引擎。

InnoDB 儲存引擎預設的事務隔離級別正是可重複讀，因此可以採用這種方式來備份資料庫。

但是，對於 MyISAM 這種不支援事務的引擎，在備份資料庫時就要使用全域性鎖的方法。

表級鎖

MySQL 表級鎖有哪些？具體怎麼用的。

MySQL 裡面表級別的鎖有這幾種：

表鎖；
後設資料鎖（MDL）;
意向鎖；
AUTO-INC 鎖；

表鎖

先來說說表鎖。

如果我們想對學生表（t_student）加表鎖，可以使用下面的命令：

//表級別的共用鎖，也就是讀鎖；
lock tables t_student read;

//表級別的獨佔鎖，也就是寫鎖；
lock tables t_stuent write;

需要注意的是，表鎖除了會限制別的執行緒的讀寫外，也會限制本執行緒接下來的讀寫操作。

也就是說如果本執行緒對學生表加了「共用表鎖」，那麼本執行緒接下來如果要對學生表執行寫操作的語句，是會被阻塞的，當然其他執行緒對學生表進行寫操作時也會被阻塞，直到鎖被釋放。

要釋放表鎖，可以使用下面這條命令，會釋放當前對談的所有表鎖：

unlock tables

另外，當對談退出後，也會釋放所有表鎖。

不過儘量避免在使用 InnoDB 引擎的表使用表鎖，因為表鎖的顆粒度太大，會影響並行效能，InnoDB 牛逼的地方在於實現了顆粒度更細的行級鎖。

後設資料鎖

再來說說後設資料鎖（MDL）。

我們不需要顯示的使用 MDL，因為當我們對資料庫表進行操作時，會自動給這個表加上 MDL：

對一張表進行 CRUD 操作時，加的是 MDL 讀鎖；
對一張表做結構變更操作的時候，加的是 MDL 寫鎖；

MDL 是為了保證當用戶對錶執行 CRUD 操作時，防止其他執行緒對這個表結構做了變更。

當有執行緒在執行 select 語句（加 MDL 讀鎖）的期間，如果有其他執行緒要更改該表的結構（申請 MDL 寫鎖），那麼將會被阻塞，直到執行完 select 語句（釋放 MDL 讀鎖）。

反之，當有執行緒對錶結構進行變更（加 MDL 寫鎖）的期間，如果有其他執行緒執行了 CRUD 操作（申請 MDL 讀鎖），那麼就會被阻塞，直到表結構變更完成（釋放 MDL 寫鎖）。

MDL 不需要顯示呼叫，那它是在什麼時候釋放的?

MDL 是在事務提交後才會釋放，這意味著事務執行期間，MDL 是一直持有的。

那如果資料庫有一個長事務（所謂的長事務，就是開啟了事務，但是一直還沒提交），那在對錶結構做變更操作的時候，可能會發生意想不到的事情，比如下面這個順序的場景：

首先，執行緒 A 先啟用了事務（但是一直不提交），然後執行一條 select 語句，此時就先對該表加上 MDL 讀鎖；
然後，執行緒 B 也執行了同樣的 select 語句，此時並不會阻塞，因為「讀讀」並不衝突；
接著，執行緒 C 修改了表欄位，此時由於執行緒 A 的事務並沒有提交，也就是 MDL 讀鎖還在佔用著，這時執行緒 C 就無法申請到 MDL 寫鎖，就會被阻塞，

那麼線上程 C 阻塞後，後續有對該表的 select 語句，就都會被阻塞，如果此時有大量該表的 select 語句的請求到來，就會有大量的執行緒被阻塞住，這時資料庫的執行緒很快就會爆滿了。

為什麼執行緒 C 因為申請不到 MDL 寫鎖，而導致後續的申請讀鎖的查詢操作也會被阻塞？

這是因為申請 MDL 鎖的操作會形成一個佇列，佇列中寫鎖獲取優先順序高於讀鎖，一旦出現 MDL 寫鎖等待，會阻塞後續該表的所有 CRUD 操作。

所以為了能安全的對錶結構進行變更，在對錶結構變更前，先要看看資料庫中的長事務，是否有事務已經對錶加上了 MDL 讀鎖，如果可以考慮 kill 掉這個長事務，然後再做表結構的變更。

意向鎖

接著，說說意向鎖。

在使用 InnoDB 引擎的表裡對某些記錄加上「共用鎖」之前，需要先在表級別加上一個「意向共用鎖」；
在使用 InnoDB 引擎的表裡對某些紀錄加上「獨佔鎖」之前，需要先在表級別加上一個「意向獨佔鎖」；

也就是，當執行插入、更新、刪除操作，需要先對錶加上「意向獨佔鎖」，然後對該記錄加獨佔鎖。

而普通的 select 是不會加行級鎖的，普通的 select 語句是利用 MVCC 實現一致性讀，是無鎖的。

不過，select 也是可以對記錄加共用鎖和獨佔鎖的，具體方式如下：

//先在表上加上意向共用鎖，然後對讀取的記錄加共用鎖
select ... lock in share mode;

//先表上加上意向獨佔鎖，然後對讀取的記錄加獨佔鎖
select ... for update;

意向共用鎖和意向獨佔鎖是表級鎖，不會和行級的共用鎖和獨佔鎖發生衝突，而且意向鎖之間也不會發生衝突，只會和共用表鎖（lock tables ... read）和獨佔表鎖（lock tables ... write）發生衝突。

表鎖和行鎖是滿足讀讀共用、讀寫互斥、寫寫互斥的。

如果沒有「意向鎖」，那麼加「獨佔表鎖」時，就需要遍歷表裡所有記錄，檢視是否有記錄存在獨佔鎖，這樣效率會很慢。

那麼有了「意向鎖」，由於在對記錄加獨佔鎖前，先會加上表級別的意向獨佔鎖，那麼在加「獨佔表鎖」時，直接查該表是否有意向獨佔鎖，如果有就意味著表裡已經有記錄被加了獨佔鎖，這樣就不用去遍歷表裡的記錄。

所以，意向鎖的目的是為了快速判斷表裡是否有記錄被加鎖。

AUTO-INC 鎖

表裡的主鍵通常都會設定成自增的，這是通過對主鍵欄位宣告 AUTO_INCREMENT 屬性實現的。

之後可以在插入資料時，可以不指定主鍵的值，資料庫會自動給主鍵賦值遞增的值，這主要是通過 AUTO-INC 鎖實現的。

AUTO-INC 鎖是特殊的表鎖機制，鎖不是再一個事務提交後才釋放，而是再執行完插入語句後就會立即釋放。

在插入資料時，會加一個表級別的 AUTO-INC 鎖，然後為被 AUTO_INCREMENT 修飾的欄位賦值遞增的值，等插入語句執行完成後，才會把 AUTO-INC 鎖釋放掉。

那麼，一個事務在持有 AUTO-INC 鎖的過程中，其他事務的如果要向該表插入語句都會被阻塞，從而保證插入資料時，被 AUTO_INCREMENT 修飾的欄位的值是連續遞增的。

但是， AUTO-INC 鎖再對大量資料進行插入的時候，會影響插入效能，因為另一個事務中的插入會被阻塞。

因此，在 MySQL 5.1.22 版本開始，InnoDB 儲存引擎提供了一種輕量級的鎖來實現自增。

一樣也是在插入資料的時候，會為被 AUTO_INCREMENT 修飾的欄位加上輕量級鎖，然後給該欄位賦值一個自增的值，就把這個輕量級鎖釋放了，而不需要等待整個插入語句執行完後才釋放鎖。

InnoDB 儲存引擎提供了個 innodb_autoinc_lock_mode 的系統變數，是用來控制選擇用 AUTO-INC 鎖，還是輕量級的鎖。

當 innodb_autoinc_lock_mode = 0，就採用 AUTO-INC 鎖，語句執行結束後才釋放鎖；
當 innodb_autoinc_lock_mode = 2，就採用輕量級鎖，申請自增主鍵後就釋放鎖，並不需要等語句執行後才釋放。
當 innodb_autoinc_lock_mode = 1：
- 普通 insert 語句，自增鎖在申請之後就馬上釋放；
- 類似 insert … select 這樣的批次插入資料的語句，自增鎖還是要等語句結束後才被釋放；

當 innodb_autoinc_lock_mode = 2 是效能最高的方式，但是當搭配 binlog 的紀錄檔格式是 statement 一起使用的時候，在「主從複製的場景」中會發生資料不一致的問題。

舉個例子，考慮下面場景：

session A 往表 t 中插入了 4 行資料，然後建立了一個相同結構的表 t2，然後兩個 session 同時執行向表 t2 中插入資料。

如果 innodb_autoinc_lock_mode = 2，意味著「申請自增主鍵後就釋放鎖，不必等插入語句執行完」。那麼就可能出現這樣的情況：

session B 先插入了兩個記錄，(1,1,1)、(2,2,2)；
然後，session A 來申請自增 id 得到 id=3，插入了（3,5,5)；
之後，session B 繼續執行，插入兩條記錄 (4,3,3)、 (5,4,4)。

可以看到，session B 的 insert 語句，生成的 id 不連續。

當「主庫」發生了這種情況，binlog 面對 t2 表的更新只會記錄這兩個 session 的 insert 語句，如果 binlog_format=statement，記錄的語句就是原始語句。記錄的順序要麼先記 session A 的 insert 語句，要麼先記 session B 的 insert 語句。

但不論是哪一種，這個 binlog 拿去「從庫」執行，這時從庫是按「順序」執行語句的，只有當執行完一條 SQL 語句後，才會執行下一條 SQL。因此，在從庫上「不會」發生像主庫那樣兩個 session 「同時」執行向表 t2 中插入資料的場景。所以，在備庫上執行了 session B 的 insert 語句，生成的結果裡面，id 都是連續的。這時，主從庫就發生了資料不一致。

要解決這問題，binlog 紀錄檔格式要設定為 row，這樣在 binlog 裡面記錄的是主庫分配的自增值，到備庫執行的時候，主庫的自增值是什麼，從庫的自增值就是什麼。

所以，當 innodb_autoinc_lock_mode = 2 時，並且 binlog_format = row，既能提升並行性，又不會出現資料一致性問題。

行級鎖

InnoDB 引擎是支援行級鎖的，而 MyISAM 引擎並不支援行級鎖。

前面也提到，普通的 select 語句是不會對記錄加鎖的，因為它屬於快照讀。如果要在查詢時對記錄加行鎖，可以使用下面這兩個方式，這種查詢會加鎖的語句稱為鎖定讀。

//對讀取的記錄加共用鎖
select ... lock in share mode;

//對讀取的記錄加獨佔鎖
select ... for update;

上面這兩條語句必須在一個事務中，因為當事務提交了，鎖就會被釋放，所以在使用這兩條語句的時候，要加上 begin、start transaction 或者 set autocommit = 0。

共用鎖（S鎖）滿足讀讀共用，讀寫互斥。獨佔鎖（X鎖）滿足寫寫互斥、讀寫互斥。

行級鎖的型別主要有三類：

Record Lock，記錄鎖，也就是僅僅把一條記錄鎖上；
Gap Lock，間隙鎖，鎖定一個範圍，但是不包含記錄本身；
Next-Key Lock：Record Lock + Gap Lock 的組合，鎖定一個範圍，並且鎖定記錄本身。

Record Lock

Record Lock 稱為記錄鎖，鎖住的是一條記錄。而且記錄鎖是有 S 鎖和 X 鎖之分的：

當一個事務對一條記錄加了 S 型記錄鎖後，其他事務也可以繼續對該記錄加 S 型記錄鎖（S 型與 S 鎖相容），但是不可以對該記錄加 X 型記錄鎖（S 型與 X 鎖不相容）;
當一個事務對一條記錄加了 X 型記錄鎖後，其他事務既不可以對該記錄加 S 型記錄鎖（S 型與 X 鎖不相容），也不可以對該記錄加 X 型記錄鎖（X 型與 X 鎖不相容）。

舉個例子，當一個事務執行了下面這條語句：

mysql > begin;
mysql > select * from t_test where id = 1 for update;

就是對 t_test 表中主鍵 id 為 1 的這條記錄加上 X 型的記錄鎖，這樣其他事務就無法對這條記錄進行修改了。

當事務執行 commit 後，事務過程中生成的鎖都會被釋放。

Gap Lock

Gap Lock 稱為間隙鎖，只存在於可重複讀隔離級別，目的是為了解決可重複讀隔離級別下幻讀的現象。

假設，表中有一個範圍 id 為（3，5）間隙鎖，那麼其他事務就無法插入 id = 4 這條記錄了，這樣就有效的防止幻讀現象的發生。

間隙鎖雖然存在 X 型間隙鎖和 S 型間隙鎖，但是並沒有什麼區別，間隙鎖之間是相容的，即兩個事務可以同時持有包含共同間隙範圍的間隙鎖，並不存在互斥關係，因為間隙鎖的目的是防止插入幻影記錄而提出的。

Next-Key Lock

Next-Key Lock 稱為臨鍵鎖，是 Record Lock + Gap Lock 的組合，鎖定一個範圍，並且鎖定記錄本身。

假設，表中有一個範圍 id 為（3，5] 的 next-key lock，那麼其他事務即不能插入 id = 4 記錄，也不能修改 id = 5 這條記錄。

所以，next-key lock 即能保護該記錄，又能阻止其他事務將新紀錄插入到被保護記錄前面的間隙中。

next-key lock 是包含間隙鎖+記錄鎖的，如果一個事務獲取了 X 型的 next-key lock，那麼另外一個事務在獲取相同範圍的 X 型的 next-key lock 時，是會被阻塞的。

比如，一個事務持有了範圍為 (1, 10] 的 X 型的 next-key lock，那麼另外一個事務在獲取相同範圍的 X 型的 next-key lock 時，就會被阻塞。

雖然相同範圍的間隙鎖是多個事務相互相容的，但對於記錄鎖，我們是要考慮 X 型與 S 型關係，X 型的記錄鎖與 X 型的記錄鎖是衝突的。

插入意向鎖

一個事務在插入一條記錄的時候，需要判斷插入位置是否已被其他事務加了間隙鎖（next-key lock 也包含間隙鎖）。

如果有的話，插入操作就會發生阻塞，直到擁有間隙鎖的那個事務提交為止（釋放間隙鎖的時刻），在此期間會生成一個插入意向鎖，表明有事務想在某個區間插入新記錄，但是現在處於等待狀態。

舉個例子，假設事務 A 已經對錶加了一個範圍 id 為（3，5）間隙鎖。

當事務 A 還沒提交的時候，事務 B 向該表插入一條 id = 4 的新記錄，這時會判斷到插入的位置已經被事務 A 加了間隙鎖，於是事物 B 會生成一個插入意向鎖，然後將鎖的狀態設定為等待狀態（PS：MySQL 加鎖時，是先生成鎖結構，然後設定鎖的狀態，如果鎖狀態是等待狀態，並不是意味著事務成功獲取到了鎖，只有當鎖狀態為正常狀態時，才代表事務成功獲取到了鎖），此時事務 B 就會發生阻塞，直到事務 A 提交了事務。

插入意向鎖名字雖然有意向鎖，但是它並不是意向鎖，它是一種特殊的間隙鎖，屬於行級別鎖。

如果說間隙鎖鎖住的是一個區間，那麼「插入意向鎖」鎖住的就是一個點。因而從這個角度來說，插入意向鎖確實是一種特殊的間隙鎖。

插入意向鎖與間隙鎖的另一個非常重要的差別是：儘管「插入意向鎖」也屬於間隙鎖，但兩個事務卻不能在同一時間內，一個擁有間隙鎖，另一個擁有該間隙區間內的插入意向鎖（當然，插入意向鎖如果不在間隙鎖區間內則是可以的）。

參考資料：

《MySQL技術內幕：innodb》
《MySQL實戰45講》
《從根兒上理解MySQL》