眾所周知，Mysql的事務隔離級別分為4個，分別是READ-UNCOMMITED，READ-COMMITED，REPEATABLE-READ，SERIALIZABLE，在常規資料庫概論中，前三種事務隔離級別會帶來臟讀、不可重覆讀、幻讀的問題，對應關係如下： ||臟讀|不可重覆讀|幻讀 | | | | ...
眾所周知，Mysql的事務隔離級別分為4個，分別是READ-UNCOMMITED，READ-COMMITED，REPEATABLE-READ，SERIALIZABLE，在常規資料庫概論中，前三種事務隔離級別會帶來臟讀、不可重覆讀、幻讀的問題，對應關係如下：
| 臟讀 | 不可重覆讀 | 幻讀 | |
|---|---|---|---|
| READ-UNCOMMITED | √ | √ | √ |
| READ-COMMITED | × | √ | √ |
| REPEATABLE-READ | × | × | √ |
| SERIALIZABLE | × | × | × |
但是在Mysql中使用了Next-key Block解決了幻讀問題,下麵我們通過討論該問題來詳細討論Next-key Block，這裡考慮一個常見的幻讀情況，首先創建示例表：
create database test;
use test;
CREATE TABLE `t` (
`t1` int(11) NOT NULL,
`t2` int(11) DEFAULT NULL,
PRIMARY KEY (`t1`),
KEY `t2` (`t2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
將其中加入幾條示例數據：
insert into t values(1,0),(2,10),(3,20),(4,30),(5,40);
接下來考慮一個常見的幻讀情況，我們可以先將mysql的Next-key Block關閉，可以採用如下兩種方式對其進行關閉：
- 將事務隔離級別設置為READ-COMMITTED
- 將參數
innodb_locks_unsafe_for_binlog設置為1，註意這裡設置為1是關閉Next-key Block
由於innodb_locks_unsafe_for_binlog參數需要重啟伺服器才能進行配置，因此我們採用第一種方式，將session的事務隔離級別設置為READ-COMMITTED。下麵考察一般的幻讀情況，我們的實驗方式如下：
| 事務1 | 事務2 |
|---|---|
| begin; | |
| select * from t where t2=20;（查到一條記錄，（3，20）） | |
| begin; | |
| insert into t value(6,20); | |
| commit; | |
| select * from t where t2=20;（查到兩條記錄（3，20），（6，20）） | |
| commit; |
事務1實驗過程如下：
mysql> set session transaction isolation level read committed; # 設置當前session的事務隔離級別為READ-COMMITED
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit = 0; # 取消自動Commit
Query OK, 0 rows affected (0.00 sec)
mysql> begin; # 開始一個新事務
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where t2=20; # 首次查詢t2為20的數據，查詢點1
+----+------+
| t1 | t2 |
+----+------+
| 3 | 20 |
+----+------+
1 row in set (0.00 sec)
mysql> select * from t where t2=20; # 事務2未提交時查詢t2為20的數據，查詢點2
+----+------+
| t1 | t2 |
+----+------+
| 3 | 20 |
+----+------+
1 row in set (0.00 sec)
mysql> select * from t where t2=20; # 事務2提交後查詢t2為20的數據，查詢點3（出現幻讀）
+----+------+
| t1 | t2 |
+----+------+
| 3 | 20 |
| 6 | 20 |
+----+------+
2 rows in set (0.00 sec)
mysql> commit; # 提交事務1
Query OK, 0 rows affected (0.00 sec)
事務2執行過程如下：
mysql> set session transaction isolation level read committed; # 設置當前session的事務隔離級別為READ-COMMITED
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit = 0; # 取消自動Commit
Query OK, 0 rows affected (0.00 sec)
mysql> begin; # 開始一個事務
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t value(6,20); # 調用點1、調用點2之間進行插入新數據 這裡同時也是為了營造t2列的索引是非唯一索引的情況，否則會簡化為Record Lock，為下一步的討論做準備
Query OK, 1 row affected (0.00 sec)
mysql> commit; # 調用點2、調用點3之間進行提交
Query OK, 0 rows affected (0.00 sec)
可以看到，這種情況下幻讀正常發生。
接下來，考察使用Next-key Block防止出現幻讀的情況時，會發生的情況。這裡我們再次強調一下我對幻讀的理解，考慮當前有事務A、B，事務A中具有兩條一模一樣的查詢語句執行（例如上述例子的調用點1和3，註意，我們不考慮調用點2），在兩條查詢語句執行的中間，事務B提交了會影響到事務A兩條查詢語句結果的插入請求（事務2的插入語句），這時，事務A的查詢語句的執行結果會和第一條的查詢結果不同，就好似出現了幻覺。那麼接下來真正開始討論Next-key Block。
Next key Block
討論Next-key Block之前，我們需要對一些基本概念進行解釋，Mysql的鎖演算法有3種：
- 記錄鎖（Record Lock），該鎖鎖的是一條索引記錄（註意是索引記錄）
- 間隙鎖（GAP Lock），該鎖鎖的是一個範圍，但是該範圍是(X,Y)類型的，註意是兩邊都是開區間
- Next-key Block，有人將其稱之為後碼鎖，不過我還是感覺英文名更貼近其意思，他將記錄鎖和間隙鎖組合應用，幻讀就是通過它解決的。
介紹完基礎概念之後我們繼續開始探究，基本的查詢語句顯而易見有3種，大於、小於、等於、不等於，這裡我們主要討論這四種情況，接下來對其進行一一討論，不過首先要都把事務隔離級別設置為REPEATABLE-READ。
1. 大於的情況
考慮查詢語句更改為如下語句：
select * from t where t2>20 for update;
在這種情況下，我們猜想應該給大於20的t2列的索引全部加鎖，而對於插入的方面又可以分為3類：
-
插入b列小於20的數據
insert into t value(7,19);胡亂猜想也可以知道，這種情況並不會導致插入語句鎖住的情況，因為上述的鎖並沒有涉及到t2列為19的情況，事實證明也是如此。
這裡給出實驗結果
事務1 事務2 begin; select * from t where t2>20 for update;（查到兩條記錄，（4，30），（5，40）） begin; insert into t value(7,19); commit; select * from t where t2=20 for update;（查到兩條記錄，（4，30），（5，40）） commit; 為了下麵的實驗，我們將資料庫還原，即刪除t1=7的數據。
-
插入b列等於20的數據
insert into t value(7,20);首先，我們猜想，如此情況插入數據不會被事務1中的查詢語句鎖住，因為沒有涉及到會更改查詢結果的部分，接下來進行實驗；
事務1 事務2 begin; select * from t where t2>20;（查到兩條記錄，（4，30），（5，40）） begin; insert into t value(7,20); # 阻塞了 這時我們考慮是哪個鎖阻塞掉了該插入操作，查詢
information_schema.innodb_locks表。結果如下：lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data '1371:23:4:5' '1371' 'X,GAP' 'RECORD' ' test.t''t2' '23' '4' '5' '30, 4' '1370:23:4:5' '1370' 'X' 'RECORD' ' test.t''t2' '23' '4' '5' '30, 4' 其中第一行是事務2導致的，第二行是事務1導致的。可以看到事務1的查詢語句還對t2為30的索引列加了寫鎖。而事務2請求的也是t2為30的寫鎖，我明明插入的是20為什麼是請求t2為30的寫鎖呢？
根據我們的猜想，我們瞭解對於t2>20的索引列都被加上了鎖，那麼為什麼插入的是20，卻鎖的是30呢？考慮之前的數據，我們發現30是20後面的一個索引值。這裡我們先給標記起來（mark 1）。
這裡我們直接rollback就好了，還是恢複數據庫。
-
插入b列大於20的數據
insert into t value(7,20);該情況與第二種插入等於20的數據加鎖一致，此處不再贅述。
2.小於的情況
考慮查詢語句更改為如下語句：
select * from t where t2<20 for update;
-
插入b列大於20的數據
insert into t value(7,21);這種情況其實和1.1情況類似，我們猜想插入數據與查詢數據無關，必定不會鎖住，實際上也是這樣。
-
插入b列等於20的數據
insert into t value(7,20);這裡我們猜想，應該也和1.2情況類似，會直接鎖住，但是實際上你錯了，這裡直接插入成功了，查看實驗結果：
事務1 事務2 begin; select * from t where t2<20 for update; begin; insert into t value(7,20);# 註意沒有阻塞 commit; select * from t where t2<20 for update; commit; 這是為什麼呢？明明上一個加鎖了啊，為什麼這個沒有加鎖，直接就添加上了，我們考察上一個加的鎖是大於20的間隙鎖，我們插入20時，鎖住的是t2為30的索引，而30正是20的下一個索引，這是否意味著：
索引的下一個值其實是用來鎖住上一個值到下一個值的區間的。簡單來講就是t2=30這個索引的鎖會鎖住[20,30)這個範圍。這裡我們繼續考察，恢複數據庫。
-
插入b列小於20的數據
insert into t value(7,19);這種情況下執行結果與1.3的情況類似，插入操作也被阻塞了，這裡列出加鎖情況。
lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data '1373:23:4:4' '1373' 'X，GAP' 'RECORD' ' test.t''t2' '23' '4' '4' '20， 3' '1372:23:4:4' '1372' 'X' 'RECORD' ' test.t''t2' '23' '4' '4' '20， 3' 這裡剛剛符合我們說的
索引的下一個值其實是用來鎖住上一個值到下一個值的區間的。結論，這裡應該鎖住的就是[10,20)的區間，所以該區間內的插入都不會成功。那麼此時我如果把他變為插入(7,9)這條數據呢？我猜想會鎖住10,2吧，這裡試驗一下。lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data '1373:23:4:3' '1373' 'X，GAP' 'RECORD' ' test.t''t2' '23' '4' '4' '10， 2' '1372:23:4:3' '1372' 'X' 'RECORD' ' test.t''t2' '23' '4' '4' '10， 2' 事實證明這裡我蒙對了。
3.等於的情況
考慮查詢語句更改為如下語句：
select * from t where t2=20 for update;
-
插入小於20的數據
這裡需要考慮多種情況，例如插入(10,20)範圍內的數據和插入 (0,10)範圍的數據，即(查詢條件中出現的索引之前的一個索引,查詢條件中出現的索引)和(查詢條件中出現的索引之前的第二個索引,查詢條件中出現的第一個索引)。
其中第二種情況是與查詢條件中出現的索引相鄰的索引值，第二種情況代表與查詢條件中出現的索引不相鄰的索引值，這裡我們分別考察：
-
考慮第一種情況
這種情況下新插入的數據需要在[10,20)之間，這裡我們嘗試插入(7,19)、(8,10)兩條數據。
實驗結果均如下所示：
事務1 事務2 begin; select * from t where t2=20 for update; begin; 插入語句 # 阻塞 這裡我們猜想，是因為select語句鎖住了t2=20的索引，導致無法添加上述兩條記錄。
我們考察一下此時的事務加鎖情況：
lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data '4887:36:4:4' '4887' 'X，GAP' 'RECORD' ' test.t''t2' '36' '4' '4' '20， 3' '4886:36:4:4' '4886' 'X' 'RECORD' ' test.t''t2' '36' '4' '4' '20， 3' 其中4887是事務2，4886是事務1。可以看到這裡對索引t2=20的記錄加了X鎖，而插入語句請求的是X鎖和間隙鎖。
還原資料庫，繼續進行實驗。
-
考慮第二種情況
這種情況下我們考慮插入(0,10)範圍內的數據，這裡我們嘗試插入(8,9)這一條數據，成功插入了沒有被阻塞。
可以發現t2=10的索引並沒有被鎖住。
-
-
插入等於的數據
這裡必定是會被阻塞的，畢竟我們的查詢操作都給t2=20加入了寫鎖，關鍵是到底是如何加鎖的。
現在進行試驗考察實驗過程中的加鎖信息：
lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data '4887:36:4:5' '4887' 'X，GAP' 'RECORD' ' test.t''t2' '36' '4' '5' '30， 4' '4886:36:4:5' '4886' 'X，GAP' 'RECORD' ' test.t''t2' '36' '4' '5' '30， 4' 註意這裡鎖住的索引並不是我們想的t2=20，而是t2=30的索引。而且這裡有個細節，3.1中的事務1中的select語句給t2=20加的鎖僅僅是一個X鎖，而這裡給t2=30不僅僅加了寫鎖，而且加了間隙鎖。
-
插入大於20的數據
這裡同樣要考慮兩種情況，第一種是插入(20,30)範圍內的數據，第二種是插入(30,40)範圍內的數據。
-
第一種情況
這裡我們選擇插入(8,21)，(9,30)兩條數據，發現在插入第一條數據時進行了阻塞，插入第二條時沒有阻塞。查看插入第一條數據時的加鎖信息：
lock_id lock_trx_id lock_mode lock_type lock_table lock_index lock_space lock_page lock_rec lock_data '4887:36:4:5' '4887' 'X，GAP' 'RECORD' ' test.t''t2' '36' '4' '5' '30， 4' '4886:36:4:5' '4886' 'X，GAP' 'RECORD' ' test.t''t2' '36' '4' '5' '30， 4' 可以看到此處事務1的select語句加的是X鎖、間隙鎖。事務2的insert語句加的也是X鎖、間隙鎖。
-
第二種情況
這裡我們選擇插入(10,31)數據，可以發現是正常插入，這裡證明沒有對t2=40加鎖。
-
小結
上面我們僅僅討論了大於、小於、等於的查詢情況下進行了一系列實驗，現在我們對上述實驗結果進行總結。
可以看到在進行類似於>A的查詢同時，另一條事務插入<A的數據都不會加鎖，但是插入>=A的數據時都會加鎖，而且加鎖類型也相同。
在進行>A的討論中，事務1在進行select查詢時，鎖住了(A,+無窮)中的所有的索引，註意 這裡鎖住的是索引，即記錄鎖，不是間隙鎖。結合上面討論的例子，也就是進行>20的討論時對30,40，無窮大進行了加鎖，由於使用的是select ... for update因此加的是X鎖，當進行插入數據的時候，例如插入t2=20的數據時，查找下一個索引即t2=30的索引，發現其被鎖住了，因此無法插入。插入>20的數據時同理。
在進行<A的查詢同時，另一條數據插入<A的數據會加鎖，但是在插入>=A的數據時都不會加鎖。
在進行<A的討論中，事務1在進行select查詢時，鎖住了[最小的索引，A)範圍中的所有索引，等價於(-無窮,A)範圍內的所有索引，註意這裡也是記錄鎖，對於試驗中我們的<20的條件，鎖住的是0,10兩個索引，具體可以在<20的試驗中進行插入(8,-1)，可以發現鎖住的是0,1。正因如此，在我們插入(7,20)、(7,21)時可以正常插入，因為t2=30沒有被鎖住，而插入(7,19)時被阻塞了，因為t2=20被鎖住了，所以無法正常插入。
最後在進行=A的查詢同時，另一條數據插入<(A前一個索引)和>=(A下一個索引)的數據時能正常插入，但是在插入該範圍以內的數據時都會被阻塞。
在進行=A的討論中，事務1在進行select查詢時，對A索引加鎖，同時給(A,A下一個索引)這部分加了一個間隙鎖。對A加鎖是select語句顯示要求的，而間隙鎖是因為無法讓你插入=A的數據，但是不能對A的下一個索引加鎖，因為=(A的下一個索引)的數據應該正常插入。結合上述的討論，也就是在=20的討論中，select語句給20，(20,30)加了鎖，t2=20的索引導致無法插入[10,20)的數據，而(20,30)間隙鎖導致了無法插入(20,30)範圍內的數據。這樣也就能解釋為何明明查詢條件是等於，卻要鎖住一個範圍了。
對於之前總結的索引的下一個值其實是用來鎖住上一個值到下一個值的區間的。也因此是錯誤的，應該是存在列t，是非唯一輔助索引，其有索引值A，我們將A的下一個索引值命名為B，如果在t列的B索引值上存在記錄鎖，或者(A,B)區間存在間隙鎖，那麼將無法插入[A,B)區間內的數據。例如，存在列t，A為20，B為30，那麼如果有t上有(20,30)間隙鎖或者t上有30的記錄鎖，無法插入t屬於[20,30)的數據。
驗證
這裡我們使用尚未討論的不等於查詢進行驗證。查詢sql如下：
select * from t where t2 != 20 for update;
這裡會對t2列不為20的所有索引加鎖即（-無窮，20），（20，+無窮）區間內所有的索引均加鎖。註意這裡加鎖加的也是記錄鎖。考慮討論=A的情況，間隙鎖的作用在此處只是禁止=A的數據插入罷了，這裡其實並無這種情況，因此，這裡使用的是記錄鎖。
這裡我們插入>20、<20、=20的數據發現其加鎖狀態有兩種情況，插入負無窮到最大索引範圍內的數據，即（-無窮，40）,加鎖情況類似於下表：
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
|---|---|---|---|---|---|---|---|---|---|
| '4897:36:4:1' | '4897' | 'X，GAP' | 'RECORD' | 'test.t' |
't2' | '36' | '4' | '1' | '20， 3' |
| '4896:36:4:1' | '4896' | 'X' | 'RECORD' | 'test.t' |
't2' | '36' | '4' | '1' | '20， 3' |
大於最大索引的數據，加鎖情況則會改變：
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
|---|---|---|---|---|---|---|---|---|---|
| '4897:36:4:1' | '4897' | 'X' | 'RECORD' | 'test.t' |
't2' | '36' | '4' | '1' | 'supremum pseudo-record' |
| '4896:36:4:1' | '4896' | 'X' | 'RECORD' | 'test.t' |
't2' | '36' | '4' | '1' | 'supremum pseudo-record' |
至於為何會這樣，就不得而知了，不過Next-key block的基本情況已經得到了論證。
總結
Next-key block的名字給人以太多誤解，讓人總以為是加鎖只會在(X,Y]範圍內加鎖，但是實際上其實是使用Next-key進行判斷是否應該鎖住。
