中文字幕日韩精品一区二区免费_精品一区二区三区国产精品无卡在_国精品无码专区一区二区三区_国产αv三级中文在线

Innodb中RR隔離級別下insert...select對select表加鎖模型和死鎖案列分析

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

成都創(chuàng)新互聯(lián)公司是一家集網(wǎng)站建設(shè),德州企業(yè)網(wǎng)站建設(shè),德州品牌網(wǎng)站建設(shè),網(wǎng)站定制,德州網(wǎng)站建設(shè)報價,網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,德州網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競爭力。可充分滿足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時我們時刻保持專業(yè)、時尚、前沿，時刻以成就客戶成長自我，堅持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實用型網(wǎng)站。

一、基本概念

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 */

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

2、 innodb lock兼容矩陣

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

3、infimum和supremum

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

4、heap no

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

5、n bits

和這個page相關(guān)的鎖位圖的大小，每一行記錄都有1 bit的位圖信息與其對應(yīng)，用來表示是否加鎖，并且始終預(yù)留64bit。例如我的表有9條數(shù)據(jù)，同時包含infimum和supremum虛擬記錄即 64+9+2 bits，即75bits但是必須被8整除向上取整為一個字節(jié)，結(jié)果也就是就是80 bits。注意不管是否加鎖每行都會對應(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上鎖都會對表級加上IX,這占用一個結(jié)構(gòu)體。然后分別對二級索引和主鍵進(jìn)行加鎖，每一個BLOCK會占用這樣一個結(jié)構(gòu)體。

7、row lock

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

8、逐步加鎖

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

二、Innodb層對insert...select 中select表的加鎖模式

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

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

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

  • B表PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP

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

  • B表二級索引對選中記錄加上LOCK_S|LOCK_REC_NOT_GAP

  • B表PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP

  1. 如果B.COL沒有二級索引

  • 對整個B表上的所有記錄加上LOCK_S|LOCK_ORDINARY[next_key_lock]

三、Innodb層對insert...select中select表的加鎖測試

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

1. 如果B.COL有二級(唯一),并且執(zhí)行計劃使用到了(非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í)行計劃：

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é)果：

  • 二級索引對記錄加上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;;

  • 對二級索引下一條記錄加上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     ;;

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

Innodb中RR隔離級別下insert...select 對select表加鎖模型和死鎖案列分析

1.png

2. 如果B.COL有二級(唯一),并且執(zhí)行計劃使用到了(非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í)行計劃：

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表二級索引對選中記錄加上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;;

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

Innodb中RR隔離級別下insert...select 對select表加鎖模型和死鎖案列分析

2.png

3.如果B.COL沒有二級索引

使用語句：

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í)行計劃：

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;;

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

Innodb中RR隔離級別下insert...select 對select表加鎖模型和死鎖案列分析

3.png

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

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

  1. 至少2個獨立的線程(會話)

  2. 單位操作中包含多個相對獨立的加鎖步驟，有一定的時間差

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

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

測試腳本:

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 ;

語句都是一樣的:

TX1 TX2
begin; -
update b set name2='test' where id=2999; -
- insert into a select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999);
update b set name2='test' where id=999; -

但是在高并發(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)會申請加上LOCK_S,
    但是id=2999已經(jīng)加上LOCK_X,顯然不能獲得只能等待.

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

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

Innodb中RR隔離級別下insert...select 對select表加鎖模型和死鎖案列分析

情況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)會申請加上LOCK_S，因為上鎖是有一個逐步加鎖的過程,假設(shè)此時加鎖到2997前那么TX2并不會等待

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

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

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

Innodb中RR隔離級別下insert...select 對select表加鎖模型和死鎖案列分析

情況2.jpg

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

情況2的測試需要在高并發(fā)下才會出現(xiàn)，因為insert...select語句是一條語句很難人為控制，也就是很讓他在特定條件下停止。但是為了能夠模擬出這種情況筆者對innodb增加了4個參數(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ù)了，并且睡眠時間也是可以人為指定的。

并且在源碼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

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

Innodb中RR隔離級別下insert...select 對select表加鎖模型和死鎖案列分析

情況3.jpg

六、實際測試

情況1:

TX1 TX2
begin; -
update b set name2='test' where id=2999;對id:2999加LOCK_X鎖 -
- insert into a select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999);對id:996,997,998,999,2995,2996,2997,2998加LOCK_S鎖，但是對id:2999加LOCK_S鎖時發(fā)現(xiàn)已經(jīng)加LOCK_X鎖，需等待
update b set name2='test' where id=999;對id:999加LOCK_X鎖，但是發(fā)現(xiàn)已經(jīng)加LOCK_S鎖，需等待，觸發(fā)死鎖檢測 -
TX1觸發(fā)死鎖，TX1在權(quán)重判定下回滾 -

死鎖報錯語句:

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)

對b表記錄加鎖時在2997加鎖前停頓30秒，那么我就需要找到b表主鍵2997的index_id、page_no、heap_no三個信息，這里使用到我的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|*|

因為為順序插入那么2997必定到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

因為為順序插入heap_no 84就是id為2997的記錄。我們使用另外一個工具bcview進(jì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í)行順序如下:

TX1 TX2
begin; -
update b set name2='test' where id=2999; 對id:2999加LOCK_X鎖 -
- insert into a select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999);對id:在加鎖到996,997,998,999,2995,2996加LOCK_S鎖，在對id:2997加鎖前睡眠30秒，為下面的update語句騰出時間)
update b set name2='test' where id=999;對id:999加LOCK_X鎖等待但發(fā)現(xiàn)已經(jīng)加LOCK_S鎖，需等待 -
- 醒來后繼續(xù)對2997、2998、2999加LOCK_S鎖，但是發(fā)現(xiàn)id:2999已經(jīng)加LOCK_X鎖,需等待，觸發(fā)死鎖檢測
TX1權(quán)重回滾 -

死鎖報錯語句:

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的多個行鎖
             附加信息: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)

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

到此，關(guān)于“Innodb中RR隔離級別下insert...select 對select表加鎖模型和死鎖案列分析”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>

新聞標(biāo)題：Innodb中RR隔離級別下insert...select對select表加鎖模型和死鎖案列分析
URL網(wǎng)址：http://www.rwnh.cn/article6/jeppig.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、商城網(wǎng)站、手機(jī)網(wǎng)站建設(shè)、網(wǎng)站制作、ChatGPT、品牌網(wǎng)站設(shè)計

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

網(wǎng)站建設(shè)網(wǎng)站維護(hù)公司
汉沽区| 洪洞县| 铅山县| 萨迦县| 通渭县| 汉川市| 安徽省| 育儿| 洪江市| 镇宁| 颍上县| 沾化县| 天门市| 新龙县| 平舆县| 碌曲县| 巴中市| 泸溪县| 九江县| 射洪县| 钦州市| 晋城| 镇巴县| 海南省| 阳江市| 濉溪县| 霍城县| 望城县| 石门县| 台南县| 古交市| 瑞昌市| 连山| 扎兰屯市| 孟州市| 石屏县| 盐城市| 沙湾县| 梁河县| 疏附县| 兴山县|