innodb next-key lock解析

參考http://blog.csdn.net/zbszhangbosen/article/details/7434637#reply

這裡補充一些：

（1）InnoDB預設加鎖方式是next-key locking

(2)在聚集索引中，假設主鍵有唯一性限制（unique，auto increment），next-key locking 會自己主動降級為record locking。

（3）因為事務的隔離性和一緻性要求，會對全部掃描到的record加鎖。

比方：update ... where/delete .. where/select ...from...lock in share mode/ select .. from .. for update這都是next-key lock。

（4）注意優化器的選擇。

包含聚集索引和輔助索引。有時會用全表掃描替代索引掃描。這時整張表（聚集索引表）都會被加鎖。

record lock：記錄鎖，也就是隻鎖着單獨的一行

gap lock：區間鎖。隻鎖住一個區間（注意這裡的區間都是開區間。也就是不包含邊界值，至于為什麼這麼定義？innodb官方定義的）

next-key lock：record lock+gap lock，是以next-key lock也就半開半閉區間，且是下界開，上界閉。（為什麼這麼定義？innodb官方定義的）

以下來舉個手冊上的樣例看什麼是next-key lock。

假如一個索引的行有10,11,13,20

那麼可能的next-key lock的包含：

(無窮小, 10]

(10,11]

(11,13]

(13,20]

(20, 無窮大) (這裡無窮大為什麼不是閉合？你數學不到家~~)

好了如今通過舉樣例說明：

表test

mysql> show create table test;

+-------+--------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                           |

| test  | CREATE TABLE `test` (

  `a` int(11) NOT NULL,

  PRIMARY KEY (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

mysql> select * from test;

+----+

| a  |

| 11 |

| 12 |

| 13 |

| 14 |

4 rows in set (0.00 sec)

開始實驗：

（一）

session 1：

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> delete from test where a=11;

Query OK, 1 row affected (0.00 sec)

session 2:

mysql> insert into test values(10);

mysql> insert into test values(15);

mysql> insert into test values(9);

mysql> insert into test values(16);

Query OK, 1 row affected (0.01 sec)

mysql> rollback;

ok，上面的情況是預期的。由于a上有索引。那麼當然就僅僅要鎖定一行，是以其它行的插入不會被堵塞。

那麼接下來的情況就有意思了

（二）

session 1(跟上一個session 1同樣)：

delete from test where a=22;

Query OK, 0 rows affected (0.01 sec)

session 2：

mysql> insert into test values (201);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into test values (20);

mysql> insert into test values (19);

mysql> insert into test values (18);

mysql> insert into test values (16);

mysql> insert into test values (9);

從上面的結果來看，在a=11後面全部的行，也就是區間(11,無窮大)都被鎖定了。先不解釋原因，再來看一種情況：

（三）

|  7 |

|  9 |

| 10 |

| 15 |

| 22 |

| 23 |

| 24 |

| 25 |

11 rows in set (0.00 sec)

mysql> delete from test where a=21;

mysql> insert into test values (26);

mysql> insert into test values (21);

mysql> insert into test values (6);

從這裡能夠看出。如今被鎖住的區間就僅僅有[16,21)了。

有了前面對三種類型的加鎖解釋，如今能夠來解釋為什麼會這樣了，在innodb表中 delete from where ..針對掃描到的索引記錄加next-key鎖(詳細的什麼語句加什麼鎖能夠檢視手冊，另外須要說明一下。行鎖加鎖對象永遠是索引記錄，由于innodb中表即索引) 。

在（一）中。實際上加的next-key lock就是(11,11] 是以也僅僅有a=11這一條記錄被鎖住。其它全部插入都沒有關系。

在（二）中，由于a=22這條記錄不存在，并且22比表裡全部的記錄值都大，是以在innodb看來鎖住的區間就是(14, 無窮大)。

是以在插入14以後的值都提示被鎖住，而14之前的則能夠。