Innodb中RR隔離級(jí)別下insert...select 對(duì)select表加鎖模型和死鎖案列分析

這篇文章主要介紹“Innodb中RR隔離級(jí)別下insert...select 對(duì)select表加鎖模型和死鎖案列分析”，在日常操作中，相信很多人在Innodb中RR隔離級(jí)別下insert...select 對(duì)select表加鎖模型和死鎖案列分析問題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”Innodb中RR隔離級(jí)別下insert...select 對(duì)select表加鎖模型和死鎖案列分析”的疑惑有所幫助！接下來，請(qǐng)跟著小編一起來學(xué)習(xí)吧！

一、基本概念

1、 innodb lock模型

• [LOCK_ORDINARY[next_key_lock]:]
  源碼定義:

#define LOCK_ORDINARY   0   /*!< this flag denotes an ordinary
next-key lock in contrast to LOCK_GAP
or LOCK_REC_NOT_GAP */

默認(rèn)是LOCK_ORDINARY即普通的next_key_lock,鎖住行及以前的間隙。

• [LOCK_GAP:]
  源碼定義:

#define LOCK_GAP    512 /*!< when this bit is set, it means that the
lock holds only on the gap before the record;
for instance, an x-lock on the gap does not
give permission to modify the record on which
the bit is set; locks of this type are created
when records are removed from the index chain

間隙鎖，鎖住行以前的間隙，不鎖住本行。

• [LOCK_REC_NOT_GAP:]
  源碼定義:

#define LOCK_REC_NOT_GAP 1024   /*!< this bit means that the lock is only on
the index record and does NOT block inserts
to the gap before the index record; this is
used in the case when we retrieve a record
with a unique key, and is also used in
locking plain SELECTs (not part of UPDATE
or DELETE) when the user has set the READ
COMMITTED isolation level */

行鎖，鎖住行而不鎖住任何間隙。

• [LOCK_INSERT_INTENTION:]
  源碼定義：

#define LOCK_INSERT_INTENTION 2048 /*!< this bit is set when we place a waiting
gap type record lock request in order to let
an insert of an index record to wait until
there are no conflicting locks by other
transactions on the gap; note that this flag
remains set when the waiting lock is granted,
or if the lock is inherited record */

插入意向鎖，如果插入的記錄在某個(gè)已經(jīng)鎖定的間隙內(nèi)為這個(gè)鎖。

2、 innodb lock兼容矩陣

/* LOCK COMPATIBILITY MATRIX
*    IS IX S  X  AI
* IS +  +  +  -  +
* IX +  +  -  -  +
* S  +  -  +  -  -
* X  -  -  -  -  -
* AI +  +  -  -  -

3、infimum和supremum

一個(gè)page中包含這兩個(gè)偽記錄。頁中所有的行未刪除(或刪除未purge)的行邏輯上都連接到這兩個(gè)虛列之間，表現(xiàn)為一個(gè)邏輯鏈表數(shù)據(jù)結(jié)構(gòu)，其中supremum偽記錄的鎖始終為next_key_lock。

4、heap no

heap no存儲(chǔ)在fixed_extrasize 中。heap no 為物理存儲(chǔ)填充的序號(hào)，頁的空閑空間掛載在page free鏈表中(頭插法)可以重用，但是重用此heap no不變，如果一直是insert 則heap no 不斷增加，并不是按照ROWID(主鍵)排序的邏輯鏈表順序，而是物理填充順序。

5、n bits

和這個(gè)page相關(guān)的鎖位圖的大小，每一行記錄都有1 bit的位圖信息與其對(duì)應(yīng)，用來表示是否加鎖，并且始終預(yù)留64bit。例如我的表有9條數(shù)據(jù)，同時(shí)包含infimum和supremum虛擬記錄即 64+9+2 bits，即75bits但是必須被8整除向上取整為一個(gè)字節(jié)，結(jié)果也就是就是80 bits。注意不管是否加鎖每行都會(huì)對(duì)應(yīng)一bit的位圖。

6、lock struct

這是LOCK的內(nèi)存結(jié)構(gòu)體源碼中用lock_t表示其可以包含

lock_table_t    tab_lock;/*!< table lock */
lock_rec_t  rec_lock;/*!< record lock */

一般來說innodb上鎖都會(huì)對(duì)表級(jí)加上IX,這占用一個(gè)結(jié)構(gòu)體。然后分別對(duì)二級(jí)索引和主鍵進(jìn)行加鎖，每一個(gè)BLOCK會(huì)占用這樣一個(gè)結(jié)構(gòu)體。

7、row lock

這個(gè)信息描述了當(dāng)前事務(wù)加鎖的行數(shù),他是所有l(wèi)ock struct結(jié)構(gòu)體中排除table lock以外所有加鎖記錄的總和，并且包含了infimum和supremum偽列。

8、逐步加鎖

如果細(xì)心的朋友應(yīng)該會(huì)發(fā)現(xiàn)在show engine 中事務(wù)信息中的row lock在對(duì)大量行進(jìn)行加鎖的時(shí)候會(huì)不斷的增加，因?yàn)榧有墟i最終會(huì)調(diào)用lock_rec_lock逐行加鎖，這也會(huì)增加了大數(shù)據(jù)量加鎖的觸發(fā)死鎖的可能性。

二、Innodb層對(duì)insert...select 中select表的加鎖模式

RR隔離級(jí)別下insert A select B where B.COL=**,innodb層會(huì)對(duì)B表滿足條件的數(shù)據(jù)進(jìn)行加鎖，但是RC模式下B表記錄不會(huì)加任何innodb層的鎖，表現(xiàn)如下：

1. 如果B.COL有二級(jí)(非唯一),并且執(zhí)行計(jì)劃使用到了(非using index)

• B表二級(jí)索引對(duì)選中記錄加上LOCK_S|LOCK_ORDINARY[next_key_lock],并且對(duì)下一條記錄加上LOCK_S|LOCK_GAP

• B表PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP

2. 如果B.COL有二級(jí)(唯一),并且執(zhí)行計(jì)劃使用到了(非using index)

• B表二級(jí)索引對(duì)選中記錄加上LOCK_S|LOCK_REC_NOT_GAP

• B表PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP

3. 如果B.COL沒有二級(jí)索引

• 對(duì)整個(gè)B表上的所有記錄加上LOCK_S|LOCK_ORDINARY[next_key_lock]

三、Innodb層對(duì)insert...select中select表的加鎖測(cè)試

下面我們分別對(duì)其進(jìn)行測(cè)試和打印輸出：

1. 如果B.COL有二級(jí)(唯一),并且執(zhí)行計(jì)劃使用到了(非using index)

使用語句：

drop table  t1;
drop table  t2;
create table t1(id int primary key,n1 varchar(20),n2 varchar(20),key(n1));
create table t2 like t1;
insert into t1 values(1,'gao1','gao'),(2,'gao1','gao'),(3,'gao1','gao'),(4,'gao2','gao'),(5,'gao2','gao'),(6,'gao2','gao'),(7,'gao3','gao'),(8,'gao4','gao');

查看執(zhí)行計(jì)劃：

mysql> desc insert into t2 select * from t1 force index(n1) where n1='gao2';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | INSERT      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL  | NULL |     NULL | NULL  |
|  1 | SIMPLE      | t1    | NULL       | ref  | n1            | n1   | 23      | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

執(zhí)行語句：

begin;insert into t2 select * from t1 force index(n1) where n1='gao2';

觀察結(jié)果：

• 二級(jí)索引對(duì)記錄加上LOCK_S|LOCK_ORDINARY[next_key_lock]

-----TRX NO:28470 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 86 page no 4 n bits 80 index n1 of table `test`.`t1` trx id 28470 lock mode S(LOCK_S) locks gap and rec(LOCK_ORDINARY[next_key_lock])
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 67616f32; asc gao2;;
1: len 4; hex 80000004; asc     ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 67616f32; asc gao2;;
1: len 4; hex 80000005; asc     ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 67616f32; asc gao2;;
1: len 4; hex 80000006; asc     ;;

• PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP

-----TRX NO:28470 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 86 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 28470 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000004; asc     ;;
1: len 6; hex 000000006f20; asc     o ;;
2: len 7; hex bc000001300134; asc     0 4;;
3: len 4; hex 67616f32; asc gao2;;
4: len 3; hex 67616f; asc gao;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000005; asc     ;;
1: len 6; hex 000000006f20; asc     o ;;
2: len 7; hex bc000001300140; asc     0 @;;
3: len 4; hex 67616f32; asc gao2;;
4: len 3; hex 67616f; asc gao;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000006; asc     ;;
1: len 6; hex 000000006f20; asc     o ;;
2: len 7; hex bc00000130014c; asc     0 L;;
3: len 4; hex 67616f32; asc gao2;;
4: len 3; hex 67616f; asc gao;;

• 對(duì)二級(jí)索引下一條記錄加上LOCK_S|LOCK_GAP

-----TRX NO:28470 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 86 page no 4 n bits 80 index n1 of table `test`.`t1` trx id 28470 lock mode S(LOCK_S) locks gap before rec(LOCK_GAP)
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 67616f33; asc gao3;;
1: len 4; hex 80000007; asc     ;;

• 如圖紅色部分都是需要鎖定的記錄

1.png

2. 如果B.COL有二級(jí)(唯一),并且執(zhí)行計(jì)劃使用到了(非using index)

使用語句：

drop table  t1;
drop table  t2;
create table t1(id int primary key,n1 varchar(20),n2 varchar(20),unique key(n1));
create table t2 like t1;
insert into t1 values(1,'gao1','gao'),(2,'gao2','gao'),(3,'gao3','gao'),(4,'gao4','gao'),(5,'gao5','gao'),(6,'gao6','gao'),(7,'gao7','gao'),(8,'gao8','gao');

查看執(zhí)行計(jì)劃：

mysql> desc insert into t2 select * from t1 force index(n1) where n1 in ('gao2','gao3','gao4');
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | INSERT      | t2    | NULL       | ALL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL                  |
|  1 | SIMPLE      | t1    | NULL       | range | n1            | n1   | 23      | NULL |    3 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

執(zhí)行語句:

begin;insert into t2 select * from t1 force index(n1) where n1 in ('gao2','gao3','gao4');

觀察輸出：

• B表二級(jí)索引對(duì)選中記錄加上LOCK_S|LOCK_REC_NOT_GAP

-----TRX NO:30514 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 94 page no 4 n bits 80 index n1 of table `test`.`t1` trx id 30514 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 67616f32; asc gao2;;
1: len 4; hex 80000002; asc     ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 67616f33; asc gao3;;
1: len 4; hex 80000003; asc     ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 67616f34; asc gao4;;
1: len 4; hex 80000004; asc     ;;

• B表PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP

-----TRX NO:30514 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 30514 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000002; asc     ;;
1: len 6; hex 000000007728; asc     w(;;
2: len 7; hex a200000115011c; asc        ;;
3: len 4; hex 67616f32; asc gao2;;
4: len 3; hex 67616f; asc gao;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000003; asc     ;;
1: len 6; hex 000000007728; asc     w(;;
2: len 7; hex a2000001150128; asc       (;;
3: len 4; hex 67616f33; asc gao3;;
4: len 3; hex 67616f; asc gao;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000004; asc     ;;
1: len 6; hex 000000007728; asc     w(;;
2: len 7; hex a2000001150134; asc       4;;
3: len 4; hex 67616f34; asc gao4;;
4: len 3; hex 67616f; asc gao;;

• 如圖紅色部分都是需要鎖定的記錄

2.png

3.如果B.COL沒有二級(jí)索引

使用語句：

drop table  t1;
drop table  t2;
create table t1(id int primary key,n1 varchar(20),n2 varchar(20));
create table t2 like t1;
insert into t1 values(1,'gao1','gao'),(2,'gao2','gao'),(3,'gao3','gao'),(4,'gao4','gao'),(5,'gao5','gao'),(6,'gao6','gao'),(7,'gao7','gao'),(8,'gao8','gao');

查看執(zhí)行計(jì)劃：

mysql> desc insert into t2 select * from t1  where n1 in ('gao2','gao3','gao4');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | INSERT      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL        |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |    37.50 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

執(zhí)行語句:

begin;insert into t2 select * from t1  where n1 in ('gao2','gao3','gao4');

觀察輸出:

-----TRX NO:30535 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 30535 lock mode S(LOCK_S) locks gap and rec(LOCK_ORDINARY[next_key_lock])
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc     ;;
1: len 6; hex 000000007728; asc     w(;;
2: len 7; hex a2000001150110; asc        ;;
3: len 4; hex 67616f31; asc gao1;;
4: len 3; hex 67616f; asc gao;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000002; asc     ;;
1: len 6; hex 000000007728; asc     w(;;
2: len 7; hex a200000115011c; asc        ;;
3: len 4; hex 67616f32; asc gao2;;
4: len 3; hex 67616f; asc gao;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000003; asc     ;;
1: len 6; hex 000000007728; asc     w(;;
2: len 7; hex a2000001150128; asc       (;;
3: len 4; hex 67616f33; asc gao3;;
4: len 3; hex 67616f; asc gao;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000004; asc     ;;
1: len 6; hex 000000007728; asc     w(;;
2: len 7; hex a2000001150134; asc       4;;
3: len 4; hex 67616f34; asc gao4;;
4: len 3; hex 67616f; asc gao;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000005; asc     ;;
1: len 6; hex 000000007728; asc     w(;;
2: len 7; hex a2000001150140; asc       @;;
3: len 4; hex 67616f35; asc gao5;;
4: len 3; hex 67616f; asc gao;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000006; asc     ;;
1: len 6; hex 000000007728; asc     w(;;
2: len 7; hex a200000115014c; asc       L;;
3: len 4; hex 67616f36; asc gao6;;
4: len 3; hex 67616f; asc gao;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000007; asc     ;;
1: len 6; hex 000000007728; asc     w(;;
2: len 7; hex a2000001150158; asc       X;;
3: len 4; hex 67616f37; asc gao7;;
4: len 3; hex 67616f; asc gao;;
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000008; asc     ;;
1: len 6; hex 000000007728; asc     w(;;
2: len 7; hex a2000001150164; asc       d;;
3: len 4; hex 67616f38; asc gao8;;
4: len 3; hex 67616f; asc gao;;

• 如圖紅色部分都是需要鎖定的記錄

3.png

四、insert...select由于select查詢表引起的死鎖

有了上面的理論，我們知道在RR隔離級(jí)別下insert...select會(huì)對(duì)select符合條件的數(shù)據(jù)加上LOCK_S鎖，我曾經(jīng)總結(jié)過出現(xiàn)死鎖的條件:

1. 至少2個(gè)獨(dú)立的線程(會(huì)話)

2. 單位操作中包含多個(gè)相對(duì)獨(dú)立的加鎖步驟，有一定的時(shí)間差

3. 多個(gè)線程(會(huì)話)之間加鎖對(duì)象必須有相互等待的情況發(fā)生，并且等待出現(xiàn)環(huán)狀。

由于存在對(duì)select符合條件的數(shù)據(jù)加上LOCK_S鎖的情況，RR模式下insert...select出現(xiàn)死鎖的概率無疑更加高，我通過測(cè)試模擬出這種情況，嚴(yán)格意義上是相同的語句在高并發(fā)情況下表現(xiàn)為兩種死鎖情況。

測(cè)試腳本:

create table b(id int primary key,name1 varchar(20),name2 varchar(20));
alter table b add key(name1);
DELIMITER //  
CREATE PROCEDURE test_i()
begin 
 declare num int;
 set num = 1; 
while num <= 3000 do
 insert into b values(num,concat('gao',num),'gaopeng');
 set num=num+1;
end while;
end// 
call test_i()//
create table a like b//
DELIMITER ;

語句都是一樣的:

但是在高并發(fā)下相同的語句卻表現(xiàn)出不同的死鎖情況，現(xiàn)在進(jìn)行分析:

情況1:

• TX1:執(zhí)行update將表b主鍵id=2999的記錄加上LOCK_X

• TX2:執(zhí)行insert...select語句b表上的記錄(996,997,998,999,2995,2996,2997,2998,2999)會(huì)申請(qǐng)加上LOCK_S,
  但是id=2999已經(jīng)加上LOCK_X,顯然不能獲得只能等待.

• TX1:執(zhí)行update需要獲得表b主鍵id=999的LOCK_X顯然這個(gè)記錄已經(jīng)被TX2加鎖LOCK_S，只能等待，觸發(fā)死鎖檢測(cè)

如下圖紅色記錄為不能獲得鎖的記錄:

情況1.jpg

情況2:

這種情況比較極端只能在高并發(fā)上出現(xiàn)

• TX1:執(zhí)行update將表b主鍵id=2999的記錄加上LOCK_X

• TX2:執(zhí)行insert...select語句b表上的記錄(996,997,998,999,2995,2996,2997,2998,2999)會(huì)申請(qǐng)加上LOCK_S，因?yàn)樯湘i是有一個(gè)逐步加鎖的過程,假設(shè)此時(shí)加鎖到2997前那么TX2并不會(huì)等待

• TX1:執(zhí)行update需要獲得表b主鍵id=999的LOCK_X顯然這個(gè)記錄已經(jīng)被TX2加鎖LOCK_S，只能等待

• TX2:繼續(xù)加鎖LOCK_S 2997、2998、2999 發(fā)現(xiàn)2999已經(jīng)被TX1加鎖LOCK_X，只能等待，觸發(fā)死鎖檢測(cè)

如下圖紅色記錄為不能獲得鎖的記錄:

情況2.jpg

五、源碼修改和參數(shù)增加

情況2的測(cè)試需要在高并發(fā)下才會(huì)出現(xiàn)，因?yàn)閕nsert...select語句是一條語句很難人為控制，也就是很讓他在特定條件下停止。但是為了能夠模擬出這種情況筆者對(duì)innodb增加了4個(gè)參數(shù)如下，為了方便識(shí)別我都加上了自己的名字的拼音：

mysql> show variables like '%gaopeng%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_gaopeng_sl_heap_no | 0     |
| innodb_gaopeng_sl_ind_id  | 0     |
| innodb_gaopeng_sl_page_no | 0     |
| innodb_gaopeng_sl_time    | 0     |
+---------------------------+-------+

默認(rèn)情況都是0，即不啟用。他們的意思如下:

• innodb_gaopeng_sl_heap_no:記錄所在的heap no

• innodb_gaopeng_sl_ind_id:記錄所在的index_id

• innodb_gaopeng_sl_page_no:記錄所在的page_no

• innodb_gaopeng_sl_time:睡眠多少秒
  有了index_id、page_no、heap no就能唯一限定一條數(shù)據(jù)了，并且睡眠時(shí)間也是可以人為指定的。

并且在源碼lock_rec_lock 開頭增加如下代碼:

 //add by gaopeng 
   /*if find index_id heap no page no to sleep srv_gaopeng_sl_time secs*/
       if(srv_gaopeng_sl_ind_id && srv_gaopeng_sl_page_no && srv_gaopeng_sl_heap_no)
       {
           if(heap_no == (ulint)(srv_gaopeng_sl_heap_no) && (block->page.id).page_no() ==(ib_uint32_t)(srv_gaopeng_sl_page_no) 
               && index->id ==(index_id_t)(srv_gaopeng_sl_ind_id) )
               {
                   lock_mutex_exit();
                   sleep(srv_gaopeng_sl_time);
                   lock_mutex_enter();
               }
       }
   //add end

這樣一旦判定為符合條件的記錄，本條記錄的加鎖錢便會(huì)休眠指定的秒如果我們擬定在LOCK_S:id=2997之前睡眠30秒，那么情況2能夠必定發(fā)生如下圖:

情況3.jpg

六、實(shí)際測(cè)試

情況1:

死鎖報(bào)錯(cuò)語句:

mysql> update b set name2='test' where id=999;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

死鎖日志:

*** (1) TRANSACTION:
TRANSACTION 48423, ACTIVE 7 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 1160, 9 row lock(s), undo log entries 8
MySQL thread id 4, OS thread handle 140737223177984, query id 9110 localhost root Sending data
insert into a  select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999)

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 119 page no 18 n bits 160 index PRIMARY of table `test`.`b` trx id 48423 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
Record lock, heap no 86 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000bb7; asc     ;;
1: len 6; hex 00000000bd26; asc      &;;
2: len 7; hex 21000001511e7d; asc !   Q };;
3: len 7; hex 67616f32393939; asc gao2999;;
4: len 4; hex 74657374; asc test;;

*** (2) TRANSACTION:
TRANSACTION 48422, ACTIVE 24 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 140737223444224, query id 9111 localhost root updating
update b set name2='test' where id=999
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 119 page no 18 n bits 160 index PRIMARY of table `test`.`b` trx id 48422 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 86 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000bb7; asc     ;;
1: len 6; hex 00000000bd26; asc      &;;
2: len 7; hex 21000001511e7d; asc !   Q };;
3: len 7; hex 67616f32393939; asc gao2999;;
4: len 4; hex 74657374; asc test;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 119 page no 10 n bits 456 index PRIMARY of table `test`.`b` trx id 48422 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 800003e7; asc     ;;
1: len 6; hex 00000000b534; asc      4;;
2: len 7; hex bd000001310110; asc     1  ;;
3: len 6; hex 67616f393939; asc gao999;;
4: len 7; hex 67616f70656e67; asc gaopeng;;

*** WE ROLL BACK TRANSACTION (2)

信息提取如下:

TRX1:48423 
   LOCK HOLD:死鎖信息不提供
   LOCK WAIT: 
            表:b
            索引:`PRIMARY`  
            鎖模式:LOCK_S|LOCK_REC_NOT_GAP|LOCK_WAIT
            記錄:主鍵為0Xbb7(2999)
            附加信息:space id 119 page no 18 heap no 86
   CURRENT SQL: 
            insert into a  select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999)
           
TRX2:48422(觸發(fā)死鎖、權(quán)重回滾)
    LOCK HOLD:
             表:b
             索引:`PRIMARY`  
             鎖模式:LOCK_X|LOCK_REC_NOT_GAP
             記錄:主鍵為0Xbb7(2999)
             附加信息:pace id 119 page no 18 heap no 86          
    LOCK WAIT:
             表:b
             索引:`PRIMARY`  
             鎖模式:LOCK_X|LOCK_REC_NOT_GAP|LOCK_WAIT
             記錄:主鍵為0X3e7(999)
             附加信息:space id 119 page no 10 heap no 11
    CURRENT SQL:
             update b set name2='test' where id=999

情況2:

如上我們擬定在語句

insert into a  select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999)

對(duì)b表記錄加鎖時(shí)在2997加鎖前停頓30秒，那么我就需要找到b表主鍵2997的index_id、page_no、heap_no三個(gè)信息，這里使用到我的innblock工具

./innblock b.ibd scan 16
===INDEX_ID:121
level1 total block is (1)
block_no:         3,level:   1|*|
level0 total block is (9)
block_no:         5,level:   0|*|block_no:         6,level:   0|*|block_no:         7,level:   0|*|
block_no:        10,level:   0|*|block_no:        11,level:   0|*|block_no:        13,level:   0|*|
block_no:        15,level:   0|*|block_no:        17,level:   0|*|block_no:        18,level:   0|*|

因?yàn)闉轫樞虿迦肽敲?997必定到page 18中然后如下:

./innblock b.ibd 18 16
==== Block base info ====
block_no:18         space_id:121          index_id:121       
....
(84) normal record offset:3287 heapno:83 n_owned 0,delflag:N minflag:0 rectype:0
(85) normal record offset:3326 heapno:84 n_owned 0,delflag:N minflag:0 rectype:0
(86) normal record offset:3365 heapno:85 n_owned 0,delflag:N minflag:0 rectype:0
(87) normal record offset:3404 heapno:86 n_owned 0,delflag:N minflag:0 rectype:0
(88) normal record offset:3443 heapno:87 n_owned 0,delflag:N minflag:0 rectype:0

因?yàn)闉轫樞虿迦雋eap_no 84就是id為2997的記錄。我們使用另外一個(gè)工具bcview進(jìn)行驗(yàn)證

./bcview b.ibd 16 3326 4
current block:00000018--Offset:03326--cnt bytes:04--data is:80000bb5

當(dāng)然0Xbb5就是2997
因此設(shè)置參數(shù)為：

set global innodb_gaopeng_sl_heap_no=84;
set global innodb_gaopeng_sl_ind_id=121;
set global innodb_gaopeng_sl_page_no=18;
set global innodb_gaopeng_sl_time=30;

mysql> show variables like '%gaopeng%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_gaopeng_sl_heap_no | 84    |
| innodb_gaopeng_sl_ind_id  | 121   |
| innodb_gaopeng_sl_page_no | 18    |
| innodb_gaopeng_sl_time    | 30    |
+---------------------------+-------+

那么情況2執(zhí)行順序如下:

死鎖報(bào)錯(cuò)語句:

mysql> update b set name2='test' where id=999;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

死鎖日志:

*** (1) TRANSACTION:
TRANSACTION 51545, ACTIVE 41 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 140737223444224, query id 18310 localhost root updating
update b set name2='test' where id=999

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 121 page no 10 n bits 456 index PRIMARY of table `test`.`b` trx id 51545 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 800003e7; asc     ;;
1: len 6; hex 00000000c167; asc      g;;
2: len 7; hex bc000001300110; asc     0  ;;
3: len 6; hex 67616f393939; asc gao999;;
4: len 7; hex 67616f70656e67; asc gaopeng;;

*** (2) TRANSACTION:
TRANSACTION 51546, ACTIVE 30 sec starting index read
mysql tables in use 2, locked 2
5 lock struct(s), heap size 1160, 9 row lock(s), undo log entries 8
MySQL thread id 8, OS thread handle 140737223177984, query id 18309 localhost root Sending data
insert into a  select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 121 page no 10 n bits 456 index PRIMARY of table `test`.`b` trx id 51546 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 800003e4; asc     ;;
1: len 6; hex 00000000c164; asc      d;;
2: len 7; hex b90000012d0110; asc     -  ;;
3: len 6; hex 67616f393936; asc gao996;;
4: len 7; hex 67616f70656e67; asc gaopeng;;
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 800003e5; asc     ;;
1: len 6; hex 00000000c165; asc      e;;
2: len 7; hex ba0000014f0110; asc     O  ;;
3: len 6; hex 67616f393937; asc gao997;;
4: len 7; hex 67616f70656e67; asc gaopeng;;
Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 800003e6; asc     ;;
1: len 6; hex 00000000c166; asc      f;;
2: len 7; hex bb0000012f0110; asc     /  ;;
3: len 6; hex 67616f393938; asc gao998;;
4: len 7; hex 67616f70656e67; asc gaopeng;;
Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 800003e7; asc     ;;
1: len 6; hex 00000000c167; asc      g;;
2: len 7; hex bc000001300110; asc     0  ;;
3: len 6; hex 67616f393939; asc gao999;;
4: len 7; hex 67616f70656e67; asc gaopeng;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 121 page no 18 n bits 160 index PRIMARY of table `test`.`b` trx id 51546 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT)
Record lock, heap no 86 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000bb7; asc     ;;
1: len 6; hex 00000000c959; asc      Y;;
2: len 7; hex 00000002240110; asc     $  ;;
3: len 7; hex 67616f32393939; asc gao2999;;
4: len 4; hex 74657374; asc test;;

*** WE ROLL BACK TRANSACTION (1)

信息提取如下:

TRX1:51545 
   LOCK HOLD:死鎖信息不提供
   LOCK WAIT: 
            表:b
            索引:`PRIMARY`  
            鎖模式:LOCK_MODE:LOCK_X|LOCK_REC_NOT_GAP|LOCK_WAIT
            記錄:主鍵為0X3e7 
            附加信息: space id 121 page no 10 heap no 11
   CURRENT SQL: 
            update b set name2='test' where id=999
           
TRX2:51546
    LOCK HOLD:
             表:b
             索引:`PRIMARY`  
             鎖模式:LOCK_MODE:LOCK_S|LOCK_REC_NOT_GAP
             記錄:主鍵為0X3e4到0X3e7的多個(gè)行鎖
             附加信息:space id 121 page no 10
                          
    LOCK WAIT:
             表:b
             索引:`PRIMARY`  
             鎖模式:LOCK_MODE:LOCK_S|LOCK_REC_NOT_GAP|LOCK_WAIT
             記錄:主鍵為0Xbb7
             附加信息:space id 121 page no 10  heap no 86
    CURRENT SQL:
             insert into a  select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999)

我們通過死鎖日志明顯的看出同樣的語句報(bào)出來的死鎖信息卻不一樣，在高并發(fā)下相同語句，兩種死鎖場(chǎng)景都是可能發(fā)生的。

到此，關(guān)于“Innodb中RR隔離級(jí)別下insert...select 對(duì)select表加鎖模型和死鎖案列分析”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！