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

作者：小林coding 2022-10-24 00:33:59

數據庫 MySQL

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

大家好，我是小林。

最近重新補充了《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，這就是事務四大特性中的隔離性，這樣備份期間備份的數據一直是在開啟事務時的數據。

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》?

責任編輯：武曉燕來源：小林coding

MySQL 全局鎖行級鎖