程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> innodb next-key lock解析

innodb next-key lock解析

編輯:MySQL綜合教程

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> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(15);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(9);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(16);
Query OK, 1 row affected (0.01 sec)


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


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);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values (19);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values (18);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values (16);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values (9);
Query OK, 1 row affected (0.00 sec)
從上面的結果來看,在a=11後面所有的行,也就是區間(11,無窮大)都被鎖定了。先不解釋原因,再來看一種情況:
(三)
session 1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+----+
| a  |
+----+
|  7 |
|  9 |
| 10 |
| 12 |
| 13 |
| 14 |
| 15 |
| 22 |
| 23 |
| 24 |
| 25 |
+----+
11 rows in set (0.00 sec)


mysql> delete from test where a=21;
Query OK, 0 rows affected (0.00 sec)
session 2:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values (20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values (26);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (21);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values (16);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values (6);
Query OK, 1 row affected (0.01 sec)
從這裡可以看出,現在被鎖住的區間就只有[16,21)了。
有了前面對三種類型的加鎖解釋,現在可以來解釋為什麼會這樣了,在innodb表中 delete from where ..針對掃描到的索引記錄加next-key鎖(具體的什麼語句加什麼鎖可以查看手冊,另外需要說明一下,行鎖加鎖對象永遠是索引記錄,因為innodb中表即索引) 。
在(一)中,實際上加的next-key lock就是(11,11] 因此也只有a=11這一條記錄被鎖住,其他所有插入都沒有關系。
在(二)中,因為a=22這條記錄不存在,而且22比表裡所有的記錄值都大,所以在innodb看來鎖住的區間就是(14, 無窮大)。所以在插入14以後的值都提示被鎖住,而14之前的則可以。
在(三)種,a=21也是不存在,但是在表裡面21前後都有記錄,因此這裡next-key lock的區間也就是(15,21],因此不在這個區間內的都可以插入。
那麼為什麼next-key lock都是下界開區間,上界閉區間呢?這個倒不重要,管它呢,但是有一點我個人卻覺得比較怪,比如說
delete test where a > 11           #------- 1
它的next-key lock是(11, 無窮大) 
delete test where a < 11           #------- 2
它的next-key lock是(無窮小, 10]
這樣給人的感覺就很怪,因為在手冊上對next-key lock的定義:
Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gapbefore the index record.
而在1那種情況下,如果按照手冊上的解釋,記錄鎖和它之前的gap那麼就會有些牽強。[今天再次看了一遍官方手冊,是之前自己的理解不到位,這個before是對的,因為innodb在加鎖時是所有掃描過程中遇到的記錄都會被加鎖,那麼對於1那種情況,實際上是從12開始掃描,但是因為要保證a>11的都被delete掉,因此得一直掃描下去那自然最大值就是無窮大,因為這個next-key lock就是無窮大這條記錄(這是假設的一條記錄,表示一個邊界)加上它之前的gap lock (11, 無窮大),所以在任何時候next-lock都是record lock加上這個record之前的一個gap lock]
但是只要我們自己能理解就行了:記錄鎖---鎖單條記錄;區間鎖---鎖一個開區間;next-key 鎖---前面兩者的結合,而不要管什麼before。
另外next-key lock雖然在很多時候是鎖一個區間,但要明白一個區間也可能只有一個元素,因此在稱delete from tb where key=x 這種情況下加next-key鎖也是完全正確的。
另外還提兩點:
1.如果我們的SQL語句裡面沒有利用到索引,那麼加鎖對象將是所有行(但不是加表鎖),所以建索引是很重要的
2.next-key lock是為防止幻讀的發生,而只有repeatable-read以及以上隔離級別才能防止幻讀,所以在read-committed隔離級別下面沒有next-key lock這一說法。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved