RR模式下NEXT-KEY LOCK範圍到底有多大

我們知道MYSQL NEXT-KEY LOCK是用來防止幻讀，在RR模式下就有了用武之地
實際就是當前行鎖+前後的一個區間，但是這個區間到底有多大？
是簡單的一個輔助索引列上的閉區間嗎？
測試全部是在RR模式下RC模式不存在

建立測試表：
CREATE TABLE `test` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


插入幾行資料


mysql> insert into test values(10,2);
Query OK, 1 row affected (0.01 sec)


mysql> insert into test values(15,2);
Query OK, 1 row affected (0.02 sec)


mysql> insert into test values(20,4);
Query OK, 1 row affected (0.01 sec)


mysql> insert into test values(25,6);
Query OK, 1 row affected (0.02 sec)


mysql> insert into test values(99,8);
Query OK, 1 row affected (0.00 sec)


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


mysql> select * from test;
+----+------+
| a  | b    |
+----+------+
| 10 |    2 |
| 15 |    2 |
| 20 |    4 |
| 25 |    6 |
| 99 |    8 |
+----+------+
5 rows in set (0.00 sec)


會話A：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test where b=4 for update;
+----+------+
| a  | b    |
+----+------+
| 20 |    4 |
+----+------+
1 row in set (0.00 sec)

會話B：
mysql> select * from test where b=2 for update;
+----+------+
| a  | b    |
+----+------+
| 10 |    2 |
| 15 |    2 |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from test where b=6 for update;
+----+------+
| a  | b    |
+----+------+
| 25 |    6 |
+----+------+
1 row in set (0.00 sec)


都沒有問題，那顯然這些列都沒加X鎖,那是不是可以簡單的理解鎖定是
一個2-6的區間不包含2和6呢？
看下面的語句：
mysql> insert into test values(16,2);
^CCtrl-C -- sending "KILL QUERY 3" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into test values(16,6);
^CCtrl-C -- sending "KILL QUERY 3" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
均鎖定了

但是
mysql> insert into test values(14,2);
Query OK, 1 row affected (0.21 sec)
mysql> insert into test values(26,6);
Query OK, 1 row affected (0.02 sec)
是可以執行的。

這也證明了我們剛才的結論是不正確的，我們分析一下

| 15 |    2 |
| 20 |    4 |
| 25 |    6 |
這是原始的記錄我們對4進行了for update，為了更小的縮小範圍
實際上INNODB把鎖的方位定義到了
b列2 a列(15到正無窮) b列4 全部 b列6 a列(負無窮到25) 
之間全部的範圍，這看起來好像不是一個連續的區間，但是如果理解B+樹索引
同時INNODB在處理相同的值的時候按照主鍵升序進行排列就出現了一個連續的
區間，我們來畫一下，假設葉子節點如下排列，

實際上這樣我們就能看出這樣一個範圍，如果我們插入的是

 values(16,2)顯然在這個範圍內它應該插入在2 15 和4 20 之間，所以鎖定

 values(16,6)顯然也在範圍，他應該插入到4 20和6 25 之間，所以鎖定

 values(14,2)顯然不在這個範圍，他應該在2 10 和 2 15之間插入，所以OK

 values(26,6)在6 25和8 99 之間當然也可以。

如果要插入(3,3)顯然不行，因為首先是按照key排序的他肯定在這個範圍內。

最後我們得出我們的結論：

b列2 a列(15到正無窮)

b列4 全部

b列6 a列(負無窮到25)

這樣一個範圍的插入全部不允許，當然2 15  6 25本身不包含因為可以for update.

其實這樣做也是為了最小化鎖定範圍提高併發，所以輔助索引上的gap lock不僅取決

於輔助索引列還取決於主鍵列的值，但是要注意這個鎖是在輔助索引上的，而不是
主鍵上。
還有一點需要提醒：
如果鎖定是邊界記錄如上圖的
b=2 for update
和
b=8 for update
那麼鎖定的範圍將變大
b=2 for update鎖定的是 b列負無窮 到 b列4 a列(負無窮到20) 
如圖：
這裡將虛擬行infimum寫出來代表負無窮

b=8 for update鎖定的是b列 6 a列(25 到正無窮) 到 b列 正無窮
如圖：
這裡將supremum虛擬行列出來代表正無窮


實際就是看圖就理解了

最後就是需要驗證：

驗證從2個方面

1、對輔助索引的頁中連結串列進行分析,如果在輔助索引頁內的連結串列按照首先是KEY排序然後KEY相同的按照PRIMARY KEY排序那麼基本就驗證了我們的說法
   這個隨後可以補上

2、原始碼檢視，原始碼過於龐大就是B+樹索引資料結構的建立，查詢，插入，刪除都非常難看懂，如果要到我們需要的證據非常困難，以後盡力。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2126305/，如需轉載，請註明出處，否則將追究法律責任。