面試官：MySQL 有哪些鎖？？

小林coding發表於2021-08-16
面試MySql

大家好，我是小林。

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

不多 BB 了，發車！

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

全域性鎖

全域性鎖是怎麼用的？

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

flush tables with read lock

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

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

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

unlock tables

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

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

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

舉個例子大家就知道了。

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

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

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

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

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

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

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

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

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

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

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

有的，如果資料庫的引擎支援的事務支援可重複讀的隔離級別，那麼在備份資料庫之前先開啟事務，會先建立 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 wirte;

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

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

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

unlock tables

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

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

後設資料鎖

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

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

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

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

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

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

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

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

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

  1. 首先，執行緒 A 先啟用了事務（但是一直不提交），然後執行一條 select 語句，此時就先對該表加上 MDL 讀鎖；
  2. 然後，執行緒 B 也執行了同樣的 select 語句，此時並不會阻塞，因為「讀讀」並不衝突；
  3. 接著，執行緒 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-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，這個是預設值，兩種鎖混著用，如果能夠確定插入記錄的數量就採用輕量級鎖，不確定時就採用 AUTO-INC 鎖。

不過，當 innodb_autoinc_lock_mode = 2 是效能最高的方式，但是會帶來一定的問題。因為併發插入的存在，在每次插入時，自增長的值可能不是連續的，這在有主從複製的場景中是不安全的。

行級鎖有哪些？

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

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

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

前面也提到，普通的 select 語句是不會對記錄加鎖的，如果要在查詢時對記錄加行鎖，可以使用下面這兩個方式：

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

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

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

那具體跟在哪些紀錄上加鎖，就跟具體的 select 語句有關係了，比較複雜，這個留到下篇再講啦。

參考資料：《MySQL技術內幕：innodb》、《MySQL實戰45講》、《從根兒上理解MySQL》。

下次見啦～

相關文章