【MySQL】MySQL中的鎖

全局鎖

全局鎖是對整個資料庫實例加鎖，整個庫處於只讀狀態。

 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鎖的時候，直到整個事務提交後才會釋放鎖，如果此時遇到長事務，就會一直占用鎖。如果在這種情況下需要修改表結構，可以通過以下兩種方式：

1. 通過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)
   

2. 如果請求很頻繁，可能剛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條數據：

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

1. 事務A在T1時刻查詢age為15的數據，這裡使用for update表示當前讀，並且對這條記錄加鎖，此時可以查到一條記錄；

2. T2時刻，事務B又新增了一條age為15的數據，併進行了提交；

3. 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：

此時會存在四個間隙：
(-∞,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的加鎖基本單位是臨鍵鎖，不過有兩個優化：

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

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

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

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

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

2. T2時刻，向user表插入age為17的數據，由於對(0，15]和(15，20)這兩個區間加鎖，所以session B會進行阻塞；

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

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

1. 事務A在T1時刻查詢age為15的數據，註意這裡是普通的select語句是快照讀，此時可以查到一條記錄；

2. T2時刻，事務B又新增了一條age為15的數據，併進行了提交；

3. T3時刻事務A中再查詢時，同樣使用快照讀，由於是從一致性視圖中讀取，並不會讀到事務B提交的數據；

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

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

1. 事務A在T1時刻查詢age為15的數據，註意這裡是普通的select語句是快照讀，此時可以查到一條記錄；

2. T2時刻，事務B又新增了一條age為15的數據，併進行了提交；

3. T3時刻事務A中再查詢時，使用for update進行當前讀，此時依舊可以查詢到事務B提交的數據，所以就出現了幻讀；

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

死鎖

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

同樣以上面的user表為例：

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

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

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