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

2024-07-24 13:12:51

字體：大中小

來源：轉載

供稿：網友

Record lock單條索引記錄上加鎖，record lock鎖住的永遠是索引，而非記錄本身，即使該表上沒有任何索引，那么innodb會在后臺創建一個隱藏的聚集主鍵索引，那么鎖住的就是這個隱藏的聚集主鍵索引。所以說當一條sql沒有走任何索引時，那么將會在每一條聚集索引后面加X鎖，這個類似于表鎖，但原理上和表鎖應該是完全不同的。

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

快照讀：

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

當前讀：

官方文檔的術語叫locking read，也就是insert，update，delete,select..in share mode和select..for update,當前讀會在所有掃描到的索引記錄上加鎖，不管它后面的where條件到底有沒有命中對應的行記錄。當前讀可能會引起死鎖。

意向鎖：

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

不可重復讀：

指的是在同一個事務中，連續幾次快照讀，讀取的記錄應該是一樣的

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

幻讀：

指的是在一個事務A中執行了一個當前讀操作，而另外一個事務B在事務A的影響區間內insert了一條記錄，這時事務A再執行一個當前讀操作時，出現了幻行。這和不可重復讀的主要區別就在與事務A中一個是快照讀，一個當前讀；并且事務B中一個是任何的dml操作，一個只是insert。比如在A中select * from test where id<10 lock in share mode結果集為（1,2,3），這時在B中對test表插入了一條記錄4，這時在A中重新查詢結果集就是(1,2,3,4），和事務A在第一次查詢出來的結果集不一致，這里的4就是幻行。

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

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

innodb_locks_unsafe_for_binlog：靜態參數，默認為0，表示啟動gap lock，如果設置為1，表示禁用gap lock，這時mysql就只有record lock了，不過值得注意的是，即使了設置了1，關于外鍵和唯一鍵重復檢查方面用到的gap lock依舊有效。這時可以簡單地理解成事務的隔離級別退化成可重復讀，然后兩者應該還是有所區別的。建議是不要隨便設置，我們這里設置只是做個簡單的幻讀演示，mysql后續的版本可能都會廢棄掉這個參數了。

session 1 先將myid>95的記錄加一個當前讀

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 這時session 2插入myid=98的記錄成功了。

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

Query OK, 1 row affected (0.00 sec)

session 1 這時session 1再次查看時發現記錄myid=98的記錄已經存在了，這條記錄就是幻行。

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機制解決幻讀問題演示條件：我們再把innodb_locks_unsafe_for_binlog值改回默認值0，并且tx_isolation為

REPEATABLE-READ，演示時務必explain下，確保該sql走了非唯一索引idx_myid(因為如果測試數據較少的話，可能優化器直接走全表掃描，那就導致鎖住所有記錄，無法模擬出gap鎖)

演示范例 1（非唯一索引+范圍當前讀）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的當前讀sql執行走了索引idx_myid

session 2 先插入myid=56的成功，因為鎖住的間隙是myid>100,56不在該范圍內；再插入myid=109時，會一直卡住直到session 1commit，rollback或者直接鎖等待超時，在鎖等待超時前在session 1中執行同樣的sql，得到的結果依舊只有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（非唯一索引+等值當前讀）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（主鍵索引+范圍當前讀）

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,因為該記錄上存在record lock；id=125可以被select..in share mode和update,這點比較奇怪，應該這也算是當前讀，不過后來查看官方文檔得知，gap鎖只會阻塞insert操作，因為gap間隙中是不存在任何記錄的，除了insert操作，其他的操作結果應該都等價于空操作，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的內部加鎖原理gap lock的前置條件：1 事務隔離級別為REPEATABLE-READ，innodb_locks_unsafe_for_binlog參數為0，且sql走的索引為非唯一索引

2 事務隔離級別為REPEATABLE-READ，innodb_locks_unsafe_for_binlog參數為0，且sql是一個范圍的當前讀操作，這時即使不是非唯一索引也會加gap lock

gap lock的加鎖步驟

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

innodb,gap,lock

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

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

Next-Key Locks

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

下面我們針對大部分的SQL類型分析是如何加鎖的，假設事務隔離級別為可重復讀。

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會在insert的行對應的索引記錄上加一個排它鎖，這是一個record lock，并沒有gap，所以并不會阻塞其他session在gap間隙里插入記錄。不過在insert操作之前，還會加一種鎖，官方文檔稱它為insertion intention gap lock，也就是意向的gap鎖。這個意向gap鎖的作用就是預示著當多事務并發插入相同的gap空隙時，只要插入的記錄不是gap間隙中的相同位置，則無需等待其他session就可完成，這樣就使得insert操作無須加真正的gap lock。想象一下，如果一個表有一個索引idx_test，表中有記錄1和8，那么每個事務都可以在2和7之間插入任何記錄，只會對當前插入的記錄加record lock，并不會阻塞其他session插入與自己不同的記錄，因為他們并沒有任何沖突。

假設發生了一個唯一鍵沖突錯誤，那么將會在重復的索引記錄上加讀鎖。當有多個session同時插入相同的行記錄時，如果另外一個session已經獲得改行的排它鎖，那么將會導致死鎖。

insert導致的死鎖現象演示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 這時session2一直被卡住

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

session 3 這時session3也一直被卡住

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(1);

session 1 這時我們回滾session1

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

發現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插入一條記錄，獲得該記錄的排它鎖，這時session2和session3都檢測到了主鍵沖突錯誤，但是由于session1并沒有提交，所以session1并不算插入成功，于是它并不能直接報錯吧，于是session2和session3都申請了該記錄的共享鎖，這時還沒獲取到共享鎖，處于等待隊列中。這時session1 rollback了，也就釋放了該行記錄的排它鎖，那么session2和session3都獲取了該行上的共享鎖。而session2和session3想要插入記錄，必須獲取排它鎖，但由于他們自己都擁有了共享鎖，于是永遠無法獲取到排它鎖，于是死鎖就發生了。如果這時session1是commit而不是rollback的話，那么session2和session3都直接報錯主鍵沖突錯誤。查看死鎖日志也是一目了然

innodb,gap,lock

insert導致的死鎖現象2

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

INSERT ... ON DUPLICATE KEY UPDATE

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

replace

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

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

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

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

自增id的加鎖策略

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

外鍵檢測的加鎖策略

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

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