【MySQL】MySQL中的鎖 - tw511教學網

全域性鎖

全域性鎖是對整個資料庫範例加鎖，整個庫處於唯讀狀態。

 flush tables with read lock

適用場景

全域性鎖適用於做全庫邏輯備份，但是整個庫處於唯讀狀態，在備份期間，所有的更新操作、DDL將會被阻塞，會對業務產生影響。

single-transaction

mysqldump備份時可以使用–single-transaction引數，在備份資料之前啟動一個事務，藉助於MVCC獲取到一致性檢視，保證在備份的過程中，還支援資料的更新操作。

但是single-transaction只能用於支援事務的引擎，比如MyISAM不支援事務，所以使用MyISAM引擎的時候，是無法使用single-transaction的。

表級鎖

表級鎖分為表鎖和後設資料鎖。

表鎖

表鎖從名字上就可以看出鎖的是資料庫表（Table），語法為：

# 鎖住某張表
lock tables 表名 read/write
# 釋放鎖
unlock tables 表名

因為表鎖的粒度太大，將整張表鎖住，所以一般不使用表鎖。

後設資料鎖MDL

後設資料鎖（meta data lock）不需要顯示的使用，存取表的時候會自動新增MDL鎖，新增MDL鎖的原因是防止表結構出現不一致，假設查詢資料的過程中，突然表結構被修改了，與最開始拿到的表結構不一致，在某些場景下可能會影響非常大。

MDL讀鎖

在對錶做增刪改查的時候，新增的是MDL讀鎖。

為什麼新增的是讀鎖？

因為讀鎖之間不互斥，可以保證多個執行緒同時對一張表進行增刪改查。

MDL寫鎖

在對錶結構做修改的時候，新增的是MDL寫鎖。

為什麼是寫鎖？

因為寫鎖與讀鎖之間相互互斥，當然寫鎖和寫鎖之間更是互斥的，既然要保證資料修改的安全性，那麼如果有讀操作在進行，是不能進行表結構變更操作的，反之亦是如此，如果正在修改表結構，也是不能進行讀操作的，必須要等待前一個操作完成才可以進行下一個操作。所以使用了寫鎖，通過互斥保證資料操作的安全性。

需要注意的是在事務中新增MDL鎖的時候，直到整個事務提交後才會釋放鎖，如果此時遇到長事務，就會一直佔用鎖。如果在這種情況下需要修改表結構，可以通過以下兩種方式：

通過innodb_trx查詢事務的trx_mysql_thread_id，將事務kill掉：

mysql> SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id,trx_autocommit_non_locking  FROM  information_schema.innodb_trx; 
+-----------------+-----------+---------------------+---------------------+----------------------------+
| trx_id          | trx_state | trx_started         | trx_mysql_thread_id | trx_autocommit_non_locking |
+-----------------+-----------+---------------------+---------------------+----------------------------+
| 422151119956664 | RUNNING   | 2021-07-02 23:27:06 |                   5 |                          0 |
+-----------------+-----------+---------------------+---------------------+----------------------------+
1 row in set (0.00 sec)

kill事務命令：kill 事務執行緒ID(trx_mysql_thread_id)

mysql> kill 5;
Query OK, 0 rows affected (0.00 sec)

如果請求很頻繁，可能剛kill掉就有新的事務到來，這個時候可以在修改表結構的時指定等待時間來獲取MDL鎖，如果在等待時間內都沒有拿到鎖，就先放棄，之後在合適的時間再修改表結構。

行鎖

行鎖鎖住的是資料庫表的行記錄，但不是所有的引擎都支援行鎖，比如MyISAM就不支援，所以對於MyISAM只能使用表鎖。

如果某個欄位存在索引，那麼以該欄位為查詢條件時新增的行鎖只需要鎖住滿足條件的資料行即可，如果不存在索引，MySQL需要全表掃描查詢資料，此時會鎖住所有的行，也就是退化為了表鎖。

兩階段鎖協定

在InnoDB事務中，行鎖在需要的時候才加上，比如開始執行一個UPDATE語句，但是並不是UPDATE語句結束之後鎖就釋放了，而是在事務結束之後才釋放，所以在實際開發中，可以將容易引起鎖衝突的操作儘量往後放，減少鎖的時間。

間隙鎖Gap Lock

MySQL預設的隔離級別為可重複讀，以下情況沒有特殊說明，預設都是在可重複讀隔離級別下。

當前讀和快照讀
在看間隙鎖之前先看下MySQL的當前讀和快照讀。

快照讀
MySQL的MVCC機制，在每個事務開啟時會為其生成一個一致性檢視，以此實現讀提交/可重複讀隔離級別，快照讀指的就是從這個生成的一致性檢視讀取資料。
關於MVCC機制可參考：【MySQL】MVVC機制

當前讀
當前讀指的是讀取undo log版本鏈中最新的記錄，也就是讀取最新的資料（已經提交的），如果是更新（update/insert/delete）操作都是當前讀，select在可重複讀的隔離級別下是快照讀，不過可以使用以下語句使其變成當前讀：

select xx from xx lock in share;
select xx from xx for update;

lock in share mode會加讀鎖，for update會加寫鎖，這兩種語句都會進行當前讀。

間隙鎖
行鎖是在資料表行記錄上新增的鎖，並不能鎖住間隙，如果有INSERT操作，一樣可以執行成功，此時就出現了幻讀問題，為了解決幻讀的問題，引入了間隙鎖Gap Lock。間隙鎖，就是在行與行之間的間隙處也增加了鎖，它鎖住的是一個範圍區間，範圍左右都是開區間。

來看一個例子，現有一張user表，分別有id（主鍵索引）、name、age三個欄位，有以下1條資料：

id	name	age
1	a	15

假設沒有間隙鎖，加鎖時只針對記錄加行鎖，來看一個例子：

事務A在T1時刻查詢age為15的資料，這裡使用for update表示當前讀，並且對這條記錄加鎖，此時可以查到一條記錄；
T2時刻，事務B又新增了一條age為15的資料，並進行了提交；
T3時刻事務A中再查詢時，使用了當前讀，會發現可以查到兩條記錄，多出了一條age為15（事務B提交的那條）的資料，與T1時刻的資料不一致，此時就產生了幻讀；

為什麼使用for update進行當前讀？

因為MySQL預設隔離級別是可重複讀，如果不使用for update進行當前讀，事務開啟時建立一致性檢視，使用的是快照讀，所以讀不到本事務開啟後其他事務所做的操作，不會出現幻讀。
而for update每次都要讀取最新的資料，所以會出現幻讀問題。

為什麼會出現幻讀？

for update已經加了寫鎖，按理說age為15的資料應該都會被鎖住才對，為什麼還可以新增一條age為15的資料？
因為行鎖只能鎖住某行資料，由於age欄位上沒有加索引，會鎖住所有行，但是並沒有鎖住行之間的間隙，此時新增的那條資料還不存在，可以利用間隙這個漏洞新增一條age為15的資料。

為了解決幻讀問題，引入了間隙鎖，假設當前表中有三條資料，age分別為15、20、25：

id	name	age
1	a	15
2	b	20
3	c	25

此時會存在四個間隙：
(-∞,15)、(15,20)、(20,25)、(25,+∞)

如果此時在age上執行查詢（for update當前讀，會加寫鎖）：

select * from test where age = 15 for update;

因為age列沒有新增索引，mysql會鎖住所有行以及行之間的間隙，同一個時刻另外一個事務再執行INSERT語句：

insert into user(id, name, age) values(2, b, 15);

由於所有的區間都加了鎖，此時會被阻塞，這樣就防止了幻讀。

需要注意間隙鎖在在可重複讀隔離級別下才會生效。

臨鍵鎖next-key lock

行數鎖住的是某行記錄，間隙鎖鎖的是行之間的間隙（左右都是開區間），將行數和間隙鎖結合起來就是臨鍵鎖next-key lock，每個next-key lock都是左開右閉區間，以上面為例，next-key lock的所有區間為：

(-∞，15]、(15，20]、(20，25]、(25，+supremum]

InnoDB會為每個索引增加一個不存在的最大值supremum。

在可重複讀隔離級別下，MySQL的加鎖基本單位是臨鍵鎖，不過有兩個優化：

索引上進行等值查詢，如果是唯一索引，臨鍵鎖將會退化為行鎖；
索引上進行等值查詢，向右遍歷時且最後一個值不滿足等值條件時，臨鍵鎖退化為間隙鎖；

第一個優化比較好理解，因為是唯一索引，為了提高效能，可以退化為行鎖，只需要對那條資料加鎖即可。

來看第二個優化，還是以上面的user表為例，有id（主鍵索引）、name（未新增索引）、age（新增了索引，注意與上面的例子區別，這裡加了索引）三個欄位，此時有以下資料：

id	name	age
0	aaa	0
1	a	15
2	b	20

此時next-key lock區間為：
(0，15]、(15，20]、(20，+supremum]

T1時刻使用lock in share mode從user表中查詢age是15的id；
由於預設加鎖單位是臨鍵鎖，此時會給(0，15]這個區間加臨鍵鎖，由於age列是普通索引，需要繼續向右遍歷區間，查到age為20停止，存取到的物件都要加鎖，
所以本應該對(15，20]這個區間也加鎖，但是根據優化2，這個區間的最後一個值20不滿足age=15這個等值條件，所以退化為間隙鎖(15，20)。
T2時刻，向user表插入age為17的資料，由於對(0，15]和(15，20)這兩個區間加鎖，所以session B會進行阻塞；

MySQL在可重複讀隔離級別下是否能解決幻讀問題？

答案是不能，MVCC機制可以實現讀提交/可重複讀兩個級別，在可重複讀隔離級別下，如果使用快照讀，確實可以避免出現幻讀的問題：

事務A在T1時刻查詢age為15的資料，注意這裡是普通的select語句是快照讀，此時可以查到一條記錄；
T2時刻，事務B又新增了一條age為15的資料，並進行了提交；
T3時刻事務A中再查詢時，同樣使用快照讀，由於是從一致性檢視中讀取，並不會讀到事務B提交的資料；

在可重複讀隔離級別下，如果使用當前讀，由於臨鍵鎖和間隙鎖的存在，也可以避免幻讀，上面的臨鍵鎖和間隙鎖的例子都可以說明。

不過這並不等價於在可重複讀隔離級別下就可以避免幻讀的問題，來看一個例子：

事務A在T1時刻查詢age為15的資料，注意這裡是普通的select語句是快照讀，此時可以查到一條記錄；
T2時刻，事務B又新增了一條age為15的資料，並進行了提交；
T3時刻事務A中再查詢時，使用for update進行當前讀，此時依舊可以查詢到事務B提交的資料，所以就出現了幻讀；

所以如果有快照讀又有當前讀的情況下，並不能解決幻讀問題。

死鎖

在使用鎖的過程中，如果不同執行緒之間出現迴圈依賴資源，都在互相等待對方釋放鎖，就有可能造成死鎖。

同樣以上面的user表為例：

T1時刻事務A更新id為1的資料，會對id為1的行加鎖；
T2時刻事務B更新id為2的資料，會對id為2的行加鎖；
T3時刻事務A同樣去更新id為2的資料，此時已被事務B加鎖，只能等待；
T4時刻事務B需要更新id為1的資料，而這行資料已經被事務A加鎖，事務B只能等待；
事務A和事務B互相等待對方佔有的鎖，形成迴圈，造成死鎖；

對於死鎖的解決方案有以下兩種：
（1）通過innodb_lock_wait_timeout指定超時時間，預設值是50s，如果在某個時間內還沒有獲取到鎖就超時放棄。
（2）將innodb_deadlock_detect設定為on開啟死鎖檢測，每個事務到來被鎖阻塞的時候，都會檢測是否有可能導致死鎖，當然開啟死鎖檢測是有效能消耗的，高並行情況下需要消耗大量的CPU資源。

參考
極客時間 --- 林曉斌（丁奇）：MySQL實戰