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

最近有網(wǎng)友遇到了在RR隔離級(jí)別下insert A select B where B.COL=** 由于select表也就是B表引發(fā)的死鎖的問(wèn)題。分析死鎖日志后，筆者進(jìn)行模擬重現(xiàn)了這位網(wǎng)友遇到了2種場(chǎng)景并且在本文中進(jìn)行詳細(xì)的描述。

  • 本文使用版本percona 5.7.14修改版,能夠打印出事務(wù)所有的行鎖信息結(jié)構(gòu)鏈(不包含隱含鎖)
  • 本文中的測(cè)試是在RR隔離級(jí)別下完成的，RC不存在這樣的問(wèn)題
  • 筆者對(duì)源碼的理解有限，如有錯(cuò)誤請(qǐng)指正
  • 本文使用了自制工具innblock和bcview,前者用于掃描塊結(jié)構(gòu)后者用于更加方便的查看二進(jìn)制文件信息獲取地址
  1. innblock http://pan.baidu.com/s/1qYnyVWo
  2. bcview http://pan.baidu.com/s/1num76RJ

感謝葉金榮老師對(duì)本文的審核，筆者也曾是一名知數(shù)堂的學(xué)生

一、基本概念

在開始正文之前我打算介紹一下一些基本概念，特別是鎖模型和兼容矩陣會(huì)對(duì)本文的閱讀有相當(dā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 */

插入意向鎖，如果插入的記錄在某個(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è)偽記錄。頁(yè)中所有的行未刪除(或刪除未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)，頁(yè)的空閑空間掛載在page free鏈表中(頭插法)可以重用，但是重用此heap no不變，如果一直是insert 則heap no 不斷增加，并不是按照ROWID(主鍵)排序的邏輯鏈表順序，而是物理填充順序。

5、n bits

和這個(gè)page相關(guān)的鎖位圖的大小，每一行記錄都有1 bit的位圖信息與其對(duì)應(yīng)，用來(lái)表示是否加鎖，并且始終預(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 */

一般來(lái)說(shuō)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
  1. 如果B.COL有二級(jí)(唯一),并且執(zhí)行計(jì)劃使用到了(非using index)
  • B表二級(jí)索引對(duì)選中記錄加上LOCK_S|LOCK_REC_NOT_GAP
  • B表PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP
  1. 如果B.COL沒(méi)有二級(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)

使用語(yǔ)句：

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í)行語(yǔ)句：

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)

使用語(yǔ)句：

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í)行語(yǔ)句:

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沒(méi)有二級(jí)索引

使用語(yǔ)句：

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í)行語(yǔ)句:

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é)過(guò)出現(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)死鎖的概率無(wú)疑更加高，我通過(guò)測(cè)試模擬出這種情況，嚴(yán)格意義上是相同的語(yǔ)句在高并發(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 ;

語(yǔ)句都是一樣的:

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ā)下相同的語(yǔ)句卻表現(xiàn)出不同的死鎖情況，現(xiàn)在進(jìn)行分析:

情況1:
  • TX1:執(zhí)行update將表b主鍵id=2999的記錄加上LOCK_X
  • TX2:執(zhí)行insert...select語(yǔ)句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語(yǔ)句b表上的記錄(996,997,998,999,2995,2996,2997,2998,2999)會(huì)申請(qǐng)加上LOCK_S，因?yàn)樯湘i是有一個(gè)逐步加鎖的過(guò)程,假設(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語(yǔ)句是一條語(yǔ)句很難人為控制，也就是很讓他在特定條件下停止。但是為了能夠模擬出這種情況筆者對(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:
TX1 TX2
begin; -
update b set name2='test' where id=2999;對(duì)id:2999加LOCK_X鎖 -
- insert into a select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999);對(duì)id:996,997,998,999,2995,2996,2997,2998加LOCK_S鎖，但是對(duì)id:2999加LOCK_S鎖時(shí)發(fā)現(xiàn)已經(jīng)加LOCK_X鎖，需等待
update b set name2='test' where id=999;對(duì)id:999加LOCK_X鎖，但是發(fā)現(xiàn)已經(jīng)加LOCK_S鎖，需等待，觸發(fā)死鎖檢測(cè) -
TX1觸發(fā)死鎖，TX1在權(quán)重判定下回滾 -

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

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:

如上我們擬定在語(yǔ)句

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í)行順序如下:

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

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

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)

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

七、總結(jié)

分析死鎖一般要從死鎖日志中獲取如下信息

  • 1、加鎖發(fā)生在主鍵還是輔助索引
  • 2、加鎖的模式是什么
  • 3、是單行還是多行加鎖
  • 4、觸發(fā)死鎖事務(wù)最后的語(yǔ)句
  • 5、死鎖信息中事務(wù)順序是怎么樣的

在重現(xiàn)的時(shí)候，必須要做到和線上死鎖信息完全匹配那么這個(gè)死鎖場(chǎng)景才叫測(cè)試成功了，從這個(gè)例子我們就發(fā)現(xiàn)，同樣的語(yǔ)句產(chǎn)生的死鎖信息卻不一樣，我們當(dāng)然就要按照不通的場(chǎng)景去考慮，本文中的情況2比較復(fù)雜一般只是在高并發(fā)先出現(xiàn)，測(cè)試也相對(duì)麻煩，本文通過(guò)修改源碼的方式進(jìn)行測(cè)試的，否則很難重現(xiàn)。找到原因后就需要采取必要的措施，比如本文中的例子需要考慮：

  • 對(duì)insert...select中select表的修改是否及時(shí)提交。
  • insert...select是否可以用其他方式代替。這種語(yǔ)句在自增鎖上也存在一定風(fēng)險(xiǎn)。
  • 是否考慮使用RC隔離級(jí)別，在RC隔離級(jí)別下不存在對(duì)select表記錄加鎖的情況。

強(qiáng)調(diào)一點(diǎn)對(duì)于出現(xiàn)LOCK_S這樣的鎖最好深入分析，因?yàn)檫@種鎖并不多見。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容