詳談innodb的鎖(record,gap,Next-Key lock)

Record lock單條索引記錄上加鎖，record lock鎖住的永遠(yuǎn)是索引，而非記錄本身，即使該表上沒有任何索引，那么innodb會(huì)在后臺(tái)創(chuàng)建一個(gè)隱藏的聚集主鍵索引，那么鎖住的就是這個(gè)隱藏的聚集主鍵索引。所以說當(dāng)一條sql沒有走任何索引時(shí)，那么將會(huì)在每一條聚集索引后面加X鎖，這個(gè)類似于表鎖，但原理上和表鎖應(yīng)該是完全不同的。

Gap lock在索引記錄之間的間隙中加鎖，或者是在某一條索引記錄之前或者之后加鎖，并不包括該索引記錄本身。gap lock的機(jī)制主要是解決可重復(fù)讀模式下的幻讀問題，關(guān)于幻讀的演示和gap鎖如何解決了幻讀。關(guān)于這一塊，先給出幾個(gè)定義

快照讀：

簡單的select操作，沒有l(wèi)ock in share mode或for update，快照讀不會(huì)加任何的鎖，而且由于mysql的一致性非鎖定讀的機(jī)制存在，任何快照讀也不會(huì)被阻塞。但是如果事務(wù)的隔離級(jí)別是SERIALIZABLE的話，那么快照讀也會(huì)被加上共享的next-key鎖，本文不對(duì)SERIALIZABLE隔離級(jí)別做敘述。

當(dāng)前讀：

官方文檔的術(shù)語叫l(wèi)ocking read，也就是insert，update，delete,select..in share mode和select..for update,當(dāng)前讀會(huì)在所有掃描到的索引記錄上加鎖，不管它后面的where條件到底有沒有命中對(duì)應(yīng)的行記錄。當(dāng)前讀可能會(huì)引起死鎖。

意向鎖：

innodb的意向鎖主要用戶多粒度的鎖并存的情況。比如事務(wù)A要在一個(gè)表上加S鎖，如果表中的一行已被事務(wù)B加了X鎖，那么該鎖的申請(qǐng)也應(yīng)被阻塞。如果表中的數(shù)據(jù)很多，逐行檢查鎖標(biāo)志的開銷將很大，系統(tǒng)的性能將會(huì)受到影響。為了解決這個(gè)問題，可以在表級(jí)上引入新的鎖類型來表示其所屬行的加鎖情況，這就引出了“意向鎖”的概念。舉個(gè)例子，如果表中記錄1億，事務(wù)A把其中有幾條記錄上了行鎖了，這時(shí)事務(wù)B需要給這個(gè)表加表級(jí)鎖，如果沒有意向鎖的話，那就要去表中查找這一億條記錄是否上鎖了。如果存在意向鎖，那么假如事務(wù)Ａ在更新一條記錄之前，先加意向鎖，再加Ｘ鎖，事務(wù)B先檢查該表上是否存在意向鎖，存在的意向鎖是否與自己準(zhǔn)備加的鎖沖突，如果有沖突，則等待直到事務(wù)Ａ釋放，而無須逐條記錄去檢測。事務(wù)Ｂ更新表時(shí)，其實(shí)無須知道到底哪一行被鎖了，它只要知道反正有一行被鎖了就行了。
說白了意向鎖的主要作用是處理行鎖和表鎖之間的矛盾，能夠顯示“某個(gè)事務(wù)正在某一行上持有了鎖，或者準(zhǔn)備去持有鎖”

不可重復(fù)讀：

指的是在同一個(gè)事務(wù)中，連續(xù)幾次快照讀，讀取的記錄應(yīng)該是一樣的

不可重復(fù)讀的演示較為簡單，本文不做討論。

幻讀：

指的是在一個(gè)事務(wù)A中執(zhí)行了一個(gè)當(dāng)前讀操作，而另外一個(gè)事務(wù)B在事務(wù)A的影響區(qū)間內(nèi)insert了一條記錄，這時(shí)事務(wù)A再執(zhí)行一個(gè)當(dāng)前讀操作時(shí)，出現(xiàn)了幻行。這和不可重復(fù)讀的主要區(qū)別就在與事務(wù)A中一個(gè)是快照讀，一個(gè)當(dāng)前讀；并且事務(wù)B中一個(gè)是任何的dml操作，一個(gè)只是insert。比如在A中select * from test where id10 lock in share mode結(jié)果集為（1,2,3），這時(shí)在B中對(duì)test表插入了一條記錄4，這時(shí)在A中重新查詢結(jié)果集就是(1,2,3,4），和事務(wù)A在第一次查詢出來的結(jié)果集不一致，這里的4就是幻行。

演示條件：由于可重讀的隔離級(jí)別下，默認(rèn)采用Next-Key Locks，就是Record lock和gap lock的結(jié)合，即除了鎖住記錄本身，還要再鎖住索引之間的間隙，所以這個(gè)gap lock機(jī)制默認(rèn)打開，并不會(huì)產(chǎn)生幻行，那么我們要演示幻行的話，要么將隔離級(jí)別改為read-commited，要么在REPEATABLE-READ模式下禁用掉gap lock，這里我們采用的是第二種方式。

幻讀的演示在演示之前又引入了innodb_locks_unsafe_for_binlog參數(shù)，該參數(shù)可以禁用gap lock。

innodb_locks_unsafe_for_binlog：靜態(tài)參數(shù)，默認(rèn)為0，表示啟動(dòng)gap lock，如果設(shè)置為1，表示禁用gap lock，這時(shí)mysql就只有record lock了，不過值得注意的是，即使了設(shè)置了1，關(guān)于外鍵和唯一鍵重復(fù)檢查方面用到的gap lock依舊有效。這時(shí)可以簡單地理解成事務(wù)的隔離級(jí)別退化成可重復(fù)讀，然后兩者應(yīng)該還是有所區(qū)別的。建議是不要隨便設(shè)置，我們這里設(shè)置只是做個(gè)簡單的幻讀演示，mysql后續(xù)的版本可能都會(huì)廢棄掉這個(gè)參數(shù)了。

session 1 先將myid>95的記錄加一個(gè)當(dāng)前讀

mysql> show create table test_gap_lock\G
*************************** 1. row ***************************
Table: test_gap_lock
Create Table: CREATE TABLE `test_gap_lock` (
`id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`myid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_name` (`name`),
KEY `idex_myid` (`myid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> begin;
mysql> select * from test_gap_lock where myid>95 for update;
+----+------------+------+
| id | name | myid |
+----+------------+------+
| 1 | jiang | 99 |
| 2 | hubingmei | 99 |
| 5 | hubingmei4 | 100 |
+----+------------+------+
3 rows in set (0.00 sec)

session 2 這時(shí)session 2插入myid=98的記錄成功了。

insert into test_gap_lock values(6,'jiang2',98);

Query OK, 1 row affected (0.00 sec)

session 1 這時(shí)session 1再次查看時(shí)發(fā)現(xiàn)記錄myid=98的記錄已經(jīng)存在了，這條記錄就是幻行。

mysql> select * from test_gap_lock where myid>95 for update;
+----+------------+------+
| id | name | myid |
+----+------------+------+
| 1 | jiang | 99 |
| 2 | hubingmei | 99 |
| 5 | hubingmei4 | 100 |
| 6 | jiang2 | 98 |
+----+------------+------+
4 rows in set (0.00 sec)

gap lock機(jī)制解決幻讀問題演示條件：我們?cè)侔裪nnodb_locks_unsafe_for_binlog值改回默認(rèn)值0，并且tx_isolation為

REPEATABLE-READ，演示時(shí)務(wù)必explain下，確保該sql走了非唯一索引idx_myid(因?yàn)槿绻麥y試數(shù)據(jù)較少的話，可能優(yōu)化器直接走全表掃描，那就導(dǎo)致鎖住所有記錄，無法模擬出gap鎖)

演示范例 1（非唯一索引+范圍當(dāng)前讀）mysql> show create table test_gap_lock\G

*************************** 1. row ***************************
Table: test_gap_lock
Create Table: CREATE TABLE `test_gap_lock` (
`id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`myid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_name` (`name`),
KEY `idex_myid` (`myid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

session 1 先explain確保session的當(dāng)前讀sql執(zhí)行走了索引idx_myid

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from test_gap_lock where myid>100 for update;
+----+-------------+---------------+-------+---------------+-----------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+-----------+---------+------+------+-----------------------+
| 1 | SIMPLE | test_gap_lock | range | idex_myid | idex_myid | 5 | NULL | 2 | Using index condition |
+----+-------------+---------------+-------+---------------+-----------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
mysql> select * from test_gap_lock where myid>100 for update;
+----+------------+------+
| id | name | myid |
+----+------------+------+
| 5 | hubingmei4 | 101 |
| 98 | test | 105 |
+----+------------+------+
2 rows in set (0.00 sec)

session 2 先插入myid=56的成功，因?yàn)殒i住的間隙是myid>100,56不在該范圍內(nèi)；再插入myid=109時(shí)，會(huì)一直卡住直到session 1commit，rollback或者直接鎖等待超時(shí)，在鎖等待超時(shí)前在session 1中執(zhí)行同樣的sql，得到的結(jié)果依舊只有id=5,98的記錄，這樣就避免了幻讀問題

mysql> insert into test_gap_lock values(999,'test2',56);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_gap_lock values(123,'test4',109);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

演示范例2（非唯一索引+等值當(dāng)前讀）mysql> select * from test_gap_lock;

+-----+------------+------+
| id | name | myid |
+-----+------------+------+
| 1 | jiang | 98 |
| 2 | hubingmei | 99 |
| 5 | hubingmei4 | 101 |
| 6 | jiang2 | 100 |
| 7 | jiang22 | 70 |
| 67 | jiang222 | 80 |
| 98 | test | 105 |
| 123 | test4 | 109 |
| 999 | test2 | 56 |
+-----+------------+------+
9 rows in set (0.00 sec)
session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> explain delete from test_gap_lock where myid=100;
+----+-------------+---------------+-------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+-----------+---------+-------+------+-------------+
| 1 | SIMPLE | test_gap_lock | range | idex_myid | idex_myid | 5 | const | 2 | Using where |
+----+-------------+---------------+-------+---------------+-----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> delete from test_gap_lock where myid=100;
Query OK, 2 rows affected (0.00 sec)

session 2 插入myid=99的記錄依舊阻塞，存在gap鎖；插入myid=97的記錄成功

mysql> insert into test_gap_lock values(676,'gap recored test',99);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test_gap_lock values(675,'gap recored test1',97);
Query OK, 1 row affected (0.00 sec)

范例3（主鍵索引+范圍當(dāng)前讀）

mysql> select * from test_gap_lock ;
+-----+------------+------+
| id | name | myid |
+-----+------------+------+
| 1 | jiang | 98 |
| 2 | hubingmei | 98 |
| 5 | hubingmei4 | 100 |
| 6 | jiang2 | 100 |
| 7 | jiang22 | 70 |
| 67 | jiang222 | 80 |
| 98 | test | 105 |
| 123 | test4 | 109 |
| 999 | test2 | 56 |
+-----+------------+------+
9 rows in set (0.00 sec)
session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from test_gap_lock where id > 100 for update;
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | test_gap_lock | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> select * from test_gap_lock where id > 100 for update;
+-----+-------+------+
| id | name | myid |
+-----+-------+------+
| 123 | test4 | 109 |
| 999 | test2 | 56 |
+-----+-------+------+
2 rows in set (0.00 sec)

session 2(id=3可插入；id=108無法插入，存在gap lock；id=123的記錄無法select..in share mode,因?yàn)樵撚涗浬洗嬖趓ecord lock；id=125可以被select..in share mode和update,這點(diǎn)比較奇怪，應(yīng)該這也算是當(dāng)前讀，不過后來查看官方文檔得知，gap鎖只會(huì)阻塞insert操作，因?yàn)間ap間隙中是不存在任何記錄的，除了insert操作，其他的操作結(jié)果應(yīng)該都等價(jià)于空操作，mysql就不去阻塞它了)

mysql> insert into test_gap_lock values(108,'gap lock test3',123);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test_gap_lock values(3,'gap lock test3',123);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_gap_lock where id=125 lock in share mode;
Empty set (0.00 sec)
mysql> explain select * from test_gap_lock where id=125 lock in share mode;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> update test_gap_lock set myid=12345 where id=125;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

gap lock的內(nèi)部加鎖原理gap lock的前置條件：1 事務(wù)隔離級(jí)別為REPEATABLE-READ，innodb_locks_unsafe_for_binlog參數(shù)為0，且sql走的索引為非唯一索引

2 事務(wù)隔離級(jí)別為REPEATABLE-READ，innodb_locks_unsafe_for_binlog參數(shù)為0，且sql是一個(gè)范圍的當(dāng)前讀操作，這時(shí)即使不是非唯一索引也會(huì)加gap lock

gap lock的加鎖步驟

針對(duì)上面的范例1（非唯一索引+范圍當(dāng)前讀）和范例3（主鍵索引+范圍當(dāng)前讀）比較好理解，那為什么范例2（非主鍵索引+等值當(dāng)前讀）為什么也會(huì)產(chǎn)生gap lock，這要從btree 索引的原理講起，我們都知道，btree索引是按照順序排列的，并且innodb存在主鍵聚集索引，本人繪圖能力有限，已范例2的加鎖過程分析舉例，手寫加鎖過程如下圖

從圖中的數(shù)據(jù)組織順序可以看出，myid=100的記錄有兩條，如果加gap鎖就會(huì)產(chǎn)生三個(gè)間隙，分別是gap1（98,100），gap2（100,100），gap3（100,105），在這三個(gè)開區(qū)間（如果我高中數(shù)學(xué)沒記錯(cuò)的話）內(nèi)的myid數(shù)值無法插入，顯然gap1還有(myid=99，id=3)(myid

=99,id=4)等記錄，gap2無實(shí)際的間隙，gap3還有（myid=101，id=7）等記錄。并且，在myid=100的兩條記錄上加了record lock，也就是這兩條數(shù)據(jù)業(yè)務(wù)無法被其他session進(jìn)行當(dāng)前讀操作（范例三可以看出）

Next-Key Locks

在默認(rèn)情況下，mysql的事務(wù)隔離級(jí)別是可重復(fù)讀，并且innodb_locks_unsafe_for_binlog參數(shù)為0，這時(shí)默認(rèn)采用next-key locks。所謂Next-Key Locks，就是Record lock和gap lock的結(jié)合，即除了鎖住記錄本身，還要再鎖住索引之間的間隙。

下面我們針對(duì)大部分的SQL類型分析是如何加鎖的，假設(shè)事務(wù)隔離級(jí)別為可重復(fù)讀。

select .. from

不加任何類型的鎖

select...from lock in share mode

在掃描到的任何索引記錄上加共享的（shared）next-key lock，還有主鍵聚集索引加排它鎖

select..from for update

在掃描到的任何索引記錄上加排它的next-key lock，還有主鍵聚集索引加排它鎖

update..where delete from..where

在掃描到的任何索引記錄上加next-key lock，還有主鍵聚集索引加排它鎖

insert into..

簡單的insert會(huì)在insert的行對(duì)應(yīng)的索引記錄上加一個(gè)排它鎖，這是一個(gè)record lock，并沒有g(shù)ap，所以并不會(huì)阻塞其他session在gap間隙里插入記錄。不過在insert操作之前，還會(huì)加一種鎖，官方文檔稱它為insertion intention gap lock，也就是意向的gap鎖。這個(gè)意向gap鎖的作用就是預(yù)示著當(dāng)多事務(wù)并發(fā)插入相同的gap空隙時(shí)，只要插入的記錄不是gap間隙中的相同位置，則無需等待其他session就可完成，這樣就使得insert操作無須加真正的gap lock。想象一下，如果一個(gè)表有一個(gè)索引idx_test，表中有記錄1和8，那么每個(gè)事務(wù)都可以在2和7之間插入任何記錄，只會(huì)對(duì)當(dāng)前插入的記錄加record lock，并不會(huì)阻塞其他session插入與自己不同的記錄，因?yàn)樗麄儾]有任何沖突。

假設(shè)發(fā)生了一個(gè)唯一鍵沖突錯(cuò)誤，那么將會(huì)在重復(fù)的索引記錄上加讀鎖。當(dāng)有多個(gè)session同時(shí)插入相同的行記錄時(shí)，如果另外一個(gè)session已經(jīng)獲得改行的排它鎖，那么將會(huì)導(dǎo)致死鎖。

insert導(dǎo)致的死鎖現(xiàn)象演示1

mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`i` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

session 1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (0.00 sec)

session 2 這時(shí)session2一直被卡住

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES(1);

session 3 這時(shí)session3也一直被卡住

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(1);

session 1 這時(shí)我們回滾session1

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

發(fā)現(xiàn)session 2的insert成功，而session3檢測到死鎖回滾

session 2 Query OK, 1 row affected (28.87 sec)

session 3 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

死鎖原因分析：

首先session1插入一條記錄，獲得該記錄的排它鎖，這時(shí)session2和session3都檢測到了主鍵沖突錯(cuò)誤，但是由于session1并沒有提交，所以session1并不算插入成功，于是它并不能直接報(bào)錯(cuò)吧，于是session2和session3都申請(qǐng)了該記錄的共享鎖，這時(shí)還沒獲取到共享鎖，處于等待隊(duì)列中。這時(shí)session1 rollback了，也就釋放了該行記錄的排它鎖，那么session2和session3都獲取了該行上的共享鎖。而session2和session3想要插入記錄，必須獲取排它鎖，但由于他們自己都擁有了共享鎖，于是永遠(yuǎn)無法獲取到排它鎖，于是死鎖就發(fā)生了。如果這時(shí)session1是commit而不是rollback的話，那么session2和session3都直接報(bào)錯(cuò)主鍵沖突錯(cuò)誤。查看死鎖日志也是一目了然

insert導(dǎo)致的死鎖現(xiàn)象2

另外一個(gè)類似的死鎖是session1刪除了id=1的記錄并未提交，這時(shí)session2和session3插入id=1的記錄。這時(shí)session1 commit了，session2和session3需要insert的話，就需要獲取排它鎖，那么死鎖也就發(fā)生了；session1 rollback，則session2和session3報(bào)錯(cuò)主鍵沖突。這里不再做演示。

INSERT ... ON DUPLICATE KEY UPDATE

這種sql和insert加鎖的不同的是，如果檢測到鍵沖突，它直接申請(qǐng)加排它鎖，而不是共享鎖。

replace

replace操作如果沒有檢測到鍵沖突的話，那么它的加鎖策略和insert相似；如果檢測到鍵沖突，那么它也是直接再申請(qǐng)加排它鎖

INSERT INTO T SELECT ... FROM S WHERE ...

在T表上的加鎖策略和普通insert一致，另外還會(huì)在S表上的相關(guān)記錄上加共享的next-key lock。（如果是可重復(fù)讀模式，則不會(huì)加鎖）

CREATE TABLE ... SELECT ...在select的表上加共享的next-key lock

自增id的加鎖策略

當(dāng)一張表的某個(gè)字段是自增列時(shí)，innodb會(huì)在該索引的末位加一個(gè)排它鎖。為了訪問這個(gè)自增的數(shù)值，需要加一個(gè)表級(jí)鎖，不過這個(gè)表級(jí)鎖的持續(xù)時(shí)間只有當(dāng)前sql，而不是整個(gè)事務(wù)，即當(dāng)前sql執(zhí)行完，該表級(jí)鎖就釋放了。其他session無法在這個(gè)表級(jí)鎖持有時(shí)插入任何記錄。

外鍵檢測的加鎖策略

如果存在外鍵約束，任何的insert，update，delete將會(huì)檢測約束條件，將會(huì)在相應(yīng)的記錄上加共享的record lock，無論是否存在外鍵沖突。

以上這篇詳談innodb的鎖(record,gap,Next-Key lock) 就是小編分享給大家的全部內(nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。