『MySQL』搞懂 InnoDB 鎖機制 以及 高併發下如何解決超賣問題

MySQL知識梳理圖，一圖看完整篇文章:

MySQL系列文章：

「MySQL」高效能索引優化策略

「MySQL」揭開索引神祕面紗

「MySQL」 MySQL執行流程

1. 鎖知識

1.1 為什麼會有鎖的機制

• 最大程度的利用資料庫的併發訪問；
• 確保每個使用者能以一致的方式讀取和修改資料。

1.2 lock 與 latch

• latch 一般叫做閂鎖，輕量級。 在InnoDB儲存引擎中，latch分為 mutex （互斥鎖）和 rwlock（讀寫鎖），目的是用來保證併發執行緒操作臨界資源的正確性，並且通常也沒有死鎖檢測機制。很少用到。
• lock 是本文的主角，它的物件是事務，用來鎖定資料庫中的物件，如表、頁、行。且lock的物件需要再事務commit 或者 rollback 後進行釋放。有死鎖檢測機制。

1.3 鎖的型別

1.3.1 行鎖 和 表鎖

• 定義

  • 行鎖，顧名思義就是鎖表中對應的行，只限制當前行的讀寫。
  • 表鎖，鎖整張表，限制的是整張表的資料讀寫。

• 對比

  • 行鎖，計算機資源開銷大，加鎖校慢，同時會出現死鎖，但鎖定粒度小，鎖衝突的概率最低，併發度最高，效能高。
  • 表鎖，計算機資源開銷小，對比行鎖，加鎖快，也不會出現死鎖，但鎖定粒度大，鎖衝突的概率最高，併發度最低，效能低。

• 限制條件

  • 行鎖的實現，SQL語句必須使用索引。如果沒有使用索引，則變成了表鎖。

行鎖和表鎖，在不同引擎還有所區別，MyISAM只有表鎖，沒有行鎖，不支援事務。 InnoDB 有行鎖和表鎖，支援事務。

1.3.2 共享鎖(S Lock) 和 排他鎖(X Lock)

InnoDB 儲存引擎實現了兩種標準的行鎖，就是共享鎖，也稱叫S鎖，允許事務讀一行資料。排他鎖，也稱叫X鎖，允許事務刪除或更新一行資料。

• 特性

  • 共享鎖和共享鎖之間是相容的，但跟排他鎖不相容。這是什麼意思了，假設A事務對某行r資料加了共享鎖，那A是可以讀取和修改r的內容。其他事務B是可以讀取r的內容，獲取行r的共享鎖，但不能進行修改，也就是不能獲取行r的排他鎖。需要等待事務A釋放行r上的共享鎖。
  • 排他鎖與排他鎖以及共享鎖均不相容。假設A事務對行r加了排他鎖，A是可以讀取和修改行r的內容。但是其餘事務B不能對行r進行修改，即不能獲取排他鎖，也不能對行r加共享鎖讀取。

• 加鎖方式

  • select語句 在查詢語句中，可以通過在SQL語句中主動加鎖。

    • 共享鎖:
      select * from table where 索引限制 lock in share mode
      記住，行鎖查詢是需要具備索引條件。比如執行: select * from user where id=1 lock in share mode. 其中 id 是主鍵。

    • 排他鎖: select * from table where 索引限制 for update 比如執行： select name from user where id=1 for update.其中 id 是主鍵

  • insert or update or delete 語句。 InnoDB中對修改資料相關類SQL中，會自動給涉及到的資料加上排他鎖。

• 如何釋放鎖

  • 非事務中，語句執行完畢，立即釋放鎖
  • 行鎖在事務中，只有等當前事務進行了commit or rollback操作才能釋放鎖。

• 檢視當前鎖的狀態 可以通過SQL語句 ： show engine innodb status\G; 檢視。

1.4 一致性非鎖定讀 VS 一致性鎖定讀

1.4.1 一致性非鎖定讀

一致性的非鎖定讀是指InnoDB儲存引擎通過行多版本控制的方式來讀取當前執行時間資料庫中行的資料。 如果讀取的行的時候有正在執行的 Delete 或者 Update 操作，這時讀取操作不會等待行上鎖的釋放，而是InnoDB引擎會去讀取行的一個快照資料。

圖片來自於《MySQL技術內幕第2版》

可以得知一致性非鎖定讀機制大大提升了資料庫的併發性，這也是InnoDB預設的讀取方式，即讀取不會佔用和等待表上的鎖。但不同事務隔離級別下，讀取的方式不同，對快照的定義也不同，一個行記錄可能有多個快照資料，一般稱這種技術為行多版本技術，由此帶來的併發控制，稱之為多版本併發控制（MVCC）

事務隔離級別 READ-COMMITTED vs REPEATABLE-READ

REPEATABLE-READ 是InnoDB預設的事務隔離級別，REPEATABLE-READ 對於快照資料，非一致性讀總是讀取事務開始時的行資料版本。

READ-COMMITTED 事務隔離級別下，對於快照資料，非一致性讀總是讀取被鎖定行的最新一份快照資料.

我們來舉例看看，開啟2個終端，可以通過下面命令開始事務會話：

`start transaction;` or `begin;` or `set autocommit=0`
複製程式碼

通過 select @@tx_isolation\G; 可以檢視事務隔離級別，先來看看 REPEATABLE-READ 的情況， 在SessionA 和 SessionB中，總共執行了6步，先執行1和2，都能查到id=2的內容，然後再SessionA中執行update操作，將id=2改為3，如果不執行commit操作，無論是REPEATABLE-READ or READ-COMMITTED ，都是能查到id=2的內容，但如果commit之後，REPEATABLE-READ還是可以繼續檢視id=2的內容，演示資料如下 1-2-3-4-5-6 順序。

Session A:

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

mysql> select @@tx_isolation\G;
*************************** 1. row ***************************
@@tx_isolation: REPEATABLE-READ

mysql> select * from user where id=2;     # 1
+----+--------+--------+------------+-----+
| id | gender | name   | birthday   | age |
+----+--------+--------+------------+-----+
|  2 | boy    | xiao12 | 1995-08-03 |  20 |
+----+--------+--------+------------+-----+
1 row in set (0.01 sec)

mysql> update user set id=3 where id=2;   # 3
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;                          # 5
Query OK, 0 rows affected (0.00 sec)
複製程式碼

Session B:

mysql> select * from user where id=2;    # 2
+----+--------+--------+------------+-----+
| id | gender | name   | birthday   | age |
+----+--------+--------+------------+-----+
|  2 | boy    | xiao12 | 1995-08-03 |  20 |
+----+--------+--------+------------+-----+
1 row in set (0.00 sec)

mysql> select * from user where id=2;    # 4
+----+--------+--------+------------+-----+
| id | gender | name   | birthday   | age |
+----+--------+--------+------------+-----+
|  2 | boy    | xiao12 | 1995-08-03 |  20 |
+----+--------+--------+------------+-----+
1 row in set (0.00 sec)

mysql> select * from user where id=2;   # 6
+----+--------+--------+------------+-----+
| id | gender | name   | birthday   | age |
+----+--------+--------+------------+-----+
|  2 | boy    | xiao12 | 1995-08-03 |  20 |
+----+--------+--------+------------+-----+
1 row in set (0.00 sec)
複製程式碼

再來事務隔離級別為READ-COMMITTED的情況：

可以通過命令 set session transaction isolation level read committed; 修改會話級的事務隔離級別。

如下面順序 1-2-3-4-5-6，可以看出READ-COMMITTED下，SessionA commit之後，SessionB就更改了。

Session A

mysql> select @@tx_isolation\G;
*************************** 1. row ***************************
@@tx_isolation: READ-COMMITTED

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

mysql> select * from user where id=4;      # 1
+----+--------+--------+------------+-----+
| id | gender | name   | birthday   | age |
+----+--------+--------+------------+-----+
|  4 | boy    | xiao12 | 1995-08-03 |  20 |
+----+--------+--------+------------+-----+
1 row in set (0.01 sec)

mysql> update user set id=3 where id=4;   # 3
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;                          # 5
Query OK, 0 rows affected (0.00 sec)
複製程式碼

Session B

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

mysql> select @@tx_isolation\G;
*************************** 1. row ***************************
@@tx_isolation: READ-COMMITTED

mysql> select * from user where id=4;        # 2
+----+--------+--------+------------+-----+
| id | gender | name   | birthday   | age |
+----+--------+--------+------------+-----+
|  4 | boy    | xiao12 | 1995-08-03 |  20 |
+----+--------+--------+------------+-----+
1 row in set (0.00 sec)

mysql> select * from user where id=4;       # 4
+----+--------+--------+------------+-----+
| id | gender | name   | birthday   | age |
+----+--------+--------+------------+-----+
|  4 | boy    | xiao12 | 1995-08-03 |  20 |
+----+--------+--------+------------+-----+
1 row in set (0.00 sec)

mysql> select * from user where id=4;     # 6
Empty set (0.00 sec)
複製程式碼

1.4.2 一致性鎖定讀

預設情況下，InnoDB是一致性非鎖定讀，如果有些業務場景需要顯式的對資料庫讀取操作進行加鎖以保證資料邏輯的一致性。這就需要進行加鎖了，加鎖方式上面描述共享鎖和排他鎖的時候已經提到過，這裡不再重複。

select ... for update 和 select ... lock in share mode

下面演示一下： 順序是 1-2-3-4，加鎖的前提是必須在一個事務中，所以開始一個事務，然後進行加共享鎖，如果未進行commit， SessionB執行update操作則會等待，等待的時候預設是50s，可以檢視相關mysql配置，如果再超時之前，SessionA執行了commit操作，則SessionB會馬上執行成功。

Session A:

ysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id=3 lock in share mode; # 1
+----+--------+--------+------------+-----+
| id | gender | name   | birthday   | age |
+----+--------+--------+------------+-----+
|  3 | boy    | xiao12 | 1995-08-03 |  20 |
+----+--------+--------+------------+-----+
1 row in set (0.00 sec)

mysql> commit;                          # 3
Query OK, 0 rows affected (0.00 sec)

複製程式碼

Session B:

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

mysql> update user set id=4 where id=3;   # 2 
# 等待

ysql> update user set id=4 where id=3;    # 4
Query OK, 1 row affected (18.18 sec)
Rows matched: 1  Changed: 1  Warnings: 0
複製程式碼

1.4 鎖的演算法

• RecordLock: 表示單個行記錄上的鎖，會去鎖定索引記錄，如果InnoDB儲存引擎表在建立的時候沒有設定任何一個索引，則InnoDB會去使用隱式的主鍵來鎖定。
• Gap Lock: 間隙鎖，鎖定一個範圍，但不包含記錄本身。
• Next-Key Lock: GapLock + RecordLock 的結合，鎖定一個範圍，並記錄範圍本身。

舉例，索引有10,11,13,20這四個值。

• InnoDB使用Record Lock將10，11，13，20四個索引鎖住，
• InnoDB使用Gap Lock將(-∞,10)，(10,11)，(11,13)，(13,20)，(20, +∞)五個範圍區間鎖住，
• InnoDB使用Next-Key Lock鎖住的區間有為(-∞,10]，(10,11]，(11,13]，(13,20]，(20, +∞)。

InnoDB預設 REPEATABLE-READ 事務隔離下，是使用的是Next-Key Lock演算法。但是如果出現查詢的列式唯一索引的情況下，會發生降級。比如： select * from user where id=3 則只會鎖定id=3這一行，即降級為Record Rock演算法。 如果是輔助索引，則情況會有所不同，舉例解釋一下，這裡有點繞。

CREATE TABLE z (a INT, b INT, PRIMARY KEY(a), KEY(b));
INSERT INTO z SELECT 1,1;
INSERT INTO z SELECT 3,1;
INSERT INTO z SELECT 5,3;
INSERT INTO z SELECT 7,6;
INSERT INTO z SELECT 10,8;
複製程式碼

執行上面語句，會建立一個z表，同時資料庫裡有如下資料

mysql> select * from z;
+----+------+
| a  | b    |
+----+------+
|  1 |    1 |
|  3 |    1 |
|  5 |    3 |
|  7 |    6 |
| 10 |    8 |
+----+------+
5 rows in set (0.00 sec)
複製程式碼

現在開啟一個會話A和B。

Session A:

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

mysql> select * from z where b=3 for update; # 1 給輔助索引b=3加上X鎖
+---+------+
| a | b    |
+---+------+
| 5 |    3 |
+---+------+
複製程式碼

給輔助索引b=3加上X鎖之後，由於使用的Next-Key Lock演算法，並且有涉及到a=5的主鍵索引，會首先對 a=5 進行Record Lock鎖定，然後對b=3進行Next-Key Lock鎖定，即鎖定(1, 3]。需要特別注意的是，InnoDB還會對輔助索引的下一個鍵（6）加上Gap Lock鎖，即鎖定(3, 6)。

所以如果再SessionB中執行下面語句會是等待嗎？

Session B:

select * from z where a=5 lock in share mode; #2

insert into z select 4, 2; #3

insert into z select 6, 5; #4

insert into z select 8, 6; #5
複製程式碼

執行2，發現需要等待，原因是a=5索引已經被加上了X鎖。 執行3，主鍵寫入4沒有問題，但輔助索引2是在鎖定的範圍（1，3）中。 執行4，主鍵寫入6沒有問題，但輔助索引5是在鎖定的範圍（3，6）中。 執行5，主鍵8和輔助索引6均沒有問題，可以寫入。

說了這麼多，接下來說一下如何關閉Gap Lock。

• 將事務隔離級別改為READ-COMMITTED。
• 將引數innodb_locks_unsafe_for_binlog設定為1。

所以將隔離級別設定為READ-COMMITTED要謹慎。關閉GapLock之後，除了外來鍵約束和唯一性檢查還需要GapLoc，其餘情況僅使用RecordLock進行鎖定。這樣設定會破壞事務的隔離性。下面來說一下這個問題。

• Phantom Problem

先來認識一個名詞： Phantom Problem，幻像問題。Innodb儲存引擎採用Next-Key Lock演算法就是為了避免Phantom Problem。

Phantom Problem是指同一個事務下，連續執行兩次同樣的SQL語句可能導致不同的結果，第二次的SQL語句可能返回之前不存在的行。

舉例分別在SessionA 和 SessionB中按順序1-2-3-4執行。

Session A:

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

mysql> select @@tx_isolation\G;
*************************** 1. row ***************************
@@tx_isolation: READ-COMMITTED

mysql> select * from z where a>2 for update;  # 1
+----+------+
| a  | b    |
+----+------+
|  3 |    1 |
|  5 |    3 |
|  7 |    6 |
| 10 |    8 |
+----+------+
4 rows in set (0.00 sec)

mysql> select * from z where a>2 for update; # 4 
+----+------+
| a  | b    |
+----+------+
|  3 |    1 |
|  4 |    0 |
|  5 |    3 |
|  7 |    6 |
| 10 |    8 |
+----+------+
5 rows in set (0.00 sec)

複製程式碼

Session B:

mysql> insert into z select 4, 0;   # 2
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec) # 3
複製程式碼

結果發現SessionA中，事務還沒結束，執行1和4返回的結果不一樣，這樣就是違法了事務的隔離性。

如果使用事務隔離級別為： REPEATABLE-READ。會使用Next-Key Lock演算法，則上面執行1則會鎖定（2，+∞），從而第2步會等待，進而避免了Phantom Problem問題。

說了這麼多，總結幾點InnoDB預設下的幾種情況：

• 在沒有索引條件查詢時，InnoDB 會鎖定表中的所有記錄。
• 使用了主鍵索引，InnoDB會鎖住主鍵索引；使用輔助索引時，InnoDB會鎖住輔助索引，也會鎖定主鍵索引。且不僅會鎖住輔助索引值所在的範圍，還會將其下一個輔助索引加上Gap LOCK。
• 當查詢只使用唯一索引時， InnoDB儲存引擎會將Next-Key Lock降級為Record Lock，即只鎖住該行索引。
• InnoDB預設事務隔離級別是REPEATABLE-READ，只有在該隔離下使用Next-Key Lock演算法機制， 目的是避免Phantom Problem（幻像問題）。

1.5 鎖帶來的問題

鎖機制雖然可以實現事務的隔離性要求，使得事務可以併發的工作，不過也會帶來幾個潛在的問題。

1.5.1 髒讀

髒讀是指不同事務下， 當前事務可以讀到另外事務未提交的資料。這個一般生產環境很少遇到，且只會發生在事務隔離級別為READ-UNCOMMITTED的情況下，這種事務隔離設定很少見。具體演示，感興趣的可以試一下。

1.5.2 不可重複讀

不可重複讀是指一個事務內多次讀取同一資料集合，得到資料結果不一樣。與髒讀的區別是，髒讀讀取到未提交的資料，而不可重複讀讀取到了已經提交的資料，但是違反了資料庫事務一致性的要求，當前事務未結束，前後兩次相同查詢得到了不一樣的結果。

這種情況上面已經有演示過，當事務隔離級別是READ-COMMITTED,則會發生這種情況。

1.5.3 丟失更新

丟失更新就是一個事務的更新操作會被另外一個事務的更新操作所覆蓋，從而導致資料的不一致。 比如：

• 事務T1將行記錄r更新為v1，但是事務T1並未提交。
• 與此同時，事務T1將行記錄r更新為v2，事務T2未提交。
• 事務T1提交
• 事務T2提交

上面舉例理論上在MySQL的事務隔離級別，都不會發生丟失更新，因為對行進行更新操作，都會對行繼續加鎖，所以第2步並不會執行成功，而是會阻塞，等待事務T1提交。

但丟失更新在生產環境是會發生的，出現在下面的情況：

1. 事務T1查詢到r行資料，放入本地內容，並顯示給使用者User1。
2. 事務T2也查詢到r行資料，並將取得的資料顯示給使用者User2。
3. User1修改這行記錄，更新資料庫提交。
4. User2修改這行記錄，更新資料庫提交。

導致這個問題，並不是因為資料庫本身的問題，而是在多使用者系統環境下，高併發讀取資訊都有可能會產生這個問題。比如容易發生在賬單金額方面的場景。 要避免此類丟失更新傳送，則需要事務在這種情況下的操作變成序列化，而不是並行操作。需要再1)中使用者讀取的記錄加上一個排他鎖（X鎖），這樣2）則讀的時候需要等待1）3）事務結束才可以讀到。從而避免了丟失更新的問題。

1.6 死鎖

接下來，來看看死鎖問題。 死鎖是指兩個或兩個以上的事務在執行過程中，因爭奪鎖資源而造成的一種互相等待的現象。

一般比較簡單的解決死鎖的問題是超時，當兩個事務互相等待時，當一個等待時間超過設定的閥值時，則該事務進行回滾，另一個等待的事務則繼續進行。可以通過innodb_lock_wait_timeout來設定超時的時間。

除了超時機制，資料庫普通採用等待圖(wait-for graph)的方式來進行死鎖檢測，Innodb採用的是這種方式來進行死鎖檢測。

wait-for graph需要2個資訊：

• 鎖的資訊連結串列
• 事務等待連結串列 通過上面連結串列可以構造一張圖，如果圖上存在迴路，則代表存在死鎖。

舉例：

圖中有t1,t2,t3,t4 4個事務，事務t1需要等待t2中row1的資源，則wait-for graph有節點t1指向t2。事務t2又需要等待t1,t4的資源，事務t3需要等待t1,t4,t2的資源，從而構成以下wait-for graph

可以看見t1和t2之間形成迴路，從而存在死鎖。

死鎖實際舉例:

A	B
select * from user where id=2 for update;	begin
	select * from user where id=8 for update;
select * from user where id=8 for update; (等待)
	select * from user where id=2 for update; \ ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

如上表，SessionA 先給id=2行加上X鎖，SessionB則給id=8加上X鎖，SessionA也準備想給id=8加上X鎖，從而處於等待中，需要等待SessionB是否id=8的鎖，SessionB在未是否id=8的鎖之前，又想給id=2加上X鎖，從而使SessionA和SessionB互相等待，出現死鎖。

2. 秒殺系統中資料庫層面如何防止超買超賣

上面講了那麼一大堆鎖相關的知識，接下來來看看了解這些知識有什麼用。 常見的一個場景，秒殺系統。雙11或者電商搶購的時候，經常是多使用者搶購一個商品，庫存肯定是很有限的，如何控制庫存不讓出現超買超賣，以防止造成不必要的損失。

仔細想想，其實跟上面鎖知識中描述的丟失更新類似，假設庫存只剩下一個，如果查詢的時候不加任何鎖，也不開啟事務。同時a、b、c三個使用者讀到了這一個庫存，然後程式也均通過了，a、b、c使用者付款後，依次更新資料庫的庫存，這時候發現庫存出現負值，造成商家的損失。

如何避免了？

如果顯式的給查詢的時候加上S鎖(共享鎖)，有用嗎？顯然根據上面的鎖知識得知，還是會出現，因為共享鎖跟共享鎖是相容了，可以都讀取，只是不能寫入。這樣a、b、c還是會都讀到最後一個庫存。

所以只能使用排他鎖了(X鎖)。

總結如下：

• 開始事務。
• 查詢庫存，並顯式的設定排他鎖，通過 SELECT * FROM table_name WHERE … FOR UPDATE。
• 生成訂單。
• 去庫存，會隱式的設定排他鎖，因為update操作，Innodb會預設設定。通過 UPDATE products SET count=count-1 WHERE id=1。
• commit，釋放鎖。

如果不開啟事務，讀取結束後就會是否鎖，所以一定要先開啟事務。

當然這樣加鎖，高併發的情況，實際生產環境不會這麼做，大量的資料庫讀寫對效能和DB都有很大的壓力。實際過程中，均會引入快取、佇列等來協助實現秒殺系統。這只是單純從資料庫層面進行分析。

這一篇文章就到這裡，下一篇繼續對MySQL事務繼續分析瞭解。

更多精彩文章，請關注公眾號『天澄技術雜談』