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

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

編輯:MySQL綜合教程

詳談innodb的鎖(record,gap,Next-Key lock)。本站提示廣大學習愛好者:(詳談innodb的鎖(record,gap,Next-Key lock))文章只能為提供參考,不一定能成為您想要的結果。以下是詳談innodb的鎖(record,gap,Next-Key lock)正文


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需要給這個表加表級鎖,如果沒有意向鎖的話,那就要去表中查找這一億條記錄是否上鎖了。如果存在意向鎖,那麼假如事務A在更新一條記錄之前,先加意向鎖,再加X鎖,事務B先檢查該表上是否存在意向鎖,存在的意向鎖是否與自己准備加的鎖沖突,如果有沖突,則等待直到事務A釋放,而無須逐條記錄去檢測。事務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

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的成功,因為鎖住的間隙是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的加鎖過程分析舉例,手寫加鎖過程如下圖


從圖中的數據組織順序可以看出,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都直接報錯主鍵沖突錯誤。查看死鎖日志也是一目了然



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) 就是小編分享給大家的全部內容了,希望能給大家一個參考,也希望大家多多支持。

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