ADADADADAD

Innodb中RR隔离级别下insert...select 对select表加锁模型和死锁案列分析[ mysql数据库 ]

mysql数据库时间：2024-11-26 22:11:57

作者：文/会员上传

如何提高mysql查询效率

简介：

一、基本概念1、 innodb lock模型[LOCK_ORDINARY[next_key_lock]:]源码定义:#defineLOCK_ORDINARY0/*!<thisflagdenotesanordinarynext-keylockincontrasttoLOCK_GAPorLOCK_

以下为本文的正文内容，内容仅供参考！本站为公益性网站，复制本文以及下载DOC文档全部免费。

一、基本概念

1、 innodb lock模型

[LOCK_ORDINARY[next_key_lock]:]
源码定义:

#defineLOCK_ORDINARY0/*!<thisflagdenotesanordinarynext-keylockincontrasttoLOCK_GAPorLOCK_REC_NOT_GAP*/

默认是LOCK_ORDINARY即普通的next_key_lock,锁住行及以前的间隙。

[LOCK_GAP:]
源码定义:

#defineLOCK_GAP512/*!<whenthisbitisset,itmeansthatthelockholdsonlyonthegapbeforetherecord;forinstance,anx-lockonthegapdoesnotgivepermissiontomodifytherecordonwhichthebitisset;locksofthistypearecreatedwhenrecordsareremovedfromtheindexchain

间隙锁，锁住行以前的间隙，不锁住本行。

[LOCK_REC_NOT_GAP:]
源码定义:

#defineLOCK_REC_NOT_GAP1024/*!<thisbitmeansthatthelockisonlyontheindexrecordanddoesNOTblockinsertstothegapbeforetheindexrecord;thisisusedinthecasewhenweretrievearecordwithauniquekey,andisalsousedinlockingplainSELECTs(notpartofUPDATEorDELETE)whentheuserhassettheREADCOMMITTEDisolationlevel*/

行锁，锁住行而不锁住任何间隙。

[LOCK_INSERT_INTENTION:]
源码定义：

#defineLOCK_INSERT_INTENTION2048/*!<thisbitissetwhenweplaceawaitinggaptyperecordlockrequestinordertoletaninsertofanindexrecordtowaituntiltherearenoconflictinglocksbyothertransactionsonthegap;notethatthisflagremainssetwhenthewaitinglockisgranted,orifthelockisinheritedrecord*/

插入意向锁，如果插入的记录在某个已经锁定的间隙内为这个锁。

2、 innodb lock兼容矩阵

/*LOCKCOMPATIBILITYMATRIX*ISIXSXAI*IS+++-+*IX++--+*S+-+--*X-----*AI++---

3、infimum和supremum

一个page中包含这两个伪记录。页中所有的行未删除(或删除未purge)的行逻辑上都连接到这两个虚列之间，表现为一个逻辑链表数据结构，其中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相关的锁位图的大小，每一行记录都有1 bit的位图信息与其对应，用来表示是否加锁，并且始终预留64bit。例如我的表有9条数据，同时包含infimum和supremum虚拟记录即 64+9+2 bits，即75bits但是必须被8整除向上取整为一个字节，结果也就是就是80 bits。注意不管是否加锁每行都会对应一bit的位图。

6、lock struct

这是LOCK的内存结构体源码中用lock_t表示其可以包含

lock_table_ttab_lock;/*!<tablelock*/lock_rec_trec_lock;/*!<recordlock*/

一般来说innodb上锁都会对表级加上IX,这占用一个结构体。然后分别对二级索引和主键进行加锁，每一个BLOCK会占用这样一个结构体。

7、row lock

这个信息描述了当前事务加锁的行数,他是所有lock struct结构体中排除table lock以外所有加锁记录的总和，并且包含了infimum和supremum伪列。

8、逐步加锁

如果细心的朋友应该会发现在show engine 中事务信息中的row lock在对大量行进行加锁的时候会不断的增加，因为加行锁最终会调用lock_rec_lock逐行加锁，这也会增加了大数据量加锁的触发死锁的可能性。

RR隔离级别下insert A select B where B.COL=**,innodb层会对B表满足条件的数据进行加锁，但是RC模式下B表记录不会加任何innodb层的锁，表现如下：

如果B.COL有二级(非唯一),并且执行计划使用到了(非using index)

B表二级索引对选中记录加上LOCK_S|LOCK_ORDINARY[next_key_lock],并且对下一条记录加上LOCK_S|LOCK_GAP

B表PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP

如果B.COL有二级(唯一),并且执行计划使用到了(非using index)

B表二级索引对选中记录加上LOCK_S|LOCK_REC_NOT_GAP

B表PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP

如果B.COL没有二级索引

对整个B表上的所有记录加上LOCK_S|LOCK_ORDINARY[next_key_lock]

下面我们分别对其进行测试和打印输出：

1. 如果B.COL有二级(唯一),并且执行计划使用到了(非using index)

使用语句：

droptablet1;droptablet2;createtablet1(idintprimarykey,n1varchar(20),n2varchar(20),key(n1));createtablet2liket1;insertintot1values(1,'gao1','gao'),(2,'gao1','gao'),(3,'gao1','gao'),(4,'gao2','gao'),(5,'gao2','gao'),(6,'gao2','gao'),(7,'gao3','gao'),(8,'gao4','gao');

查看执行计划：

mysql>descinsertintot2select*fromt1forceindex(n1)wheren1='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|+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

执行语句：

begin;insertintot2select*fromt1forceindex(n1)wheren1='gao2';

观察结果：

二级索引对记录加上LOCK_S|LOCK_ORDINARY[next_key_lock]

-----TRXNO:28470LOCKSTRUCT(1)(Addbygaopeng)RECORDLOCKSspaceid86pageno4nbits80indexn1oftable`test`.`t1`trxid28470lockmodeS(LOCK_S)locksgapandrec(LOCK_ORDINARY[next_key_lock])Recordlock,heapno5PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex67616f32;ascgao2;;1:len4;hex80000004;asc;;Recordlock,heapno6PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex67616f32;ascgao2;;1:len4;hex80000005;asc;;Recordlock,heapno7PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex67616f32;ascgao2;;1:len4;hex80000006;asc;;

PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP

-----TRXNO:28470LOCKSTRUCT(1)(Addbygaopeng)RECORDLOCKSspaceid86pageno3nbits80indexPRIMARYoftable`test`.`t1`trxid28470lockmodeS(LOCK_S)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno5PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000004;asc;;1:len6;hex000000006f20;asco;;2:len7;hexbc000001300134;asc04;;3:len4;hex67616f32;ascgao2;;4:len3;hex67616f;ascgao;;Recordlock,heapno6PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000005;asc;;1:len6;hex000000006f20;asco;;2:len7;hexbc000001300140;asc0@;;3:len4;hex67616f32;ascgao2;;4:len3;hex67616f;ascgao;;Recordlock,heapno7PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000006;asc;;1:len6;hex000000006f20;asco;;2:len7;hexbc00000130014c;asc0L;;3:len4;hex67616f32;ascgao2;;4:len3;hex67616f;ascgao;;

对二级索引下一条记录加上LOCK_S|LOCK_GAP

-----TRXNO:28470LOCKSTRUCT(1)(Addbygaopeng)RECORDLOCKSspaceid86pageno4nbits80indexn1oftable`test`.`t1`trxid28470lockmodeS(LOCK_S)locksgapbeforerec(LOCK_GAP)Recordlock,heapno8PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex67616f33;ascgao3;;1:len4;hex80000007;asc;;

如图红色部分都是需要锁定的记录

1.png

2. 如果B.COL有二级(唯一),并且执行计划使用到了(非using index)

使用语句：

droptablet1;droptablet2;createtablet1(idintprimarykey,n1varchar(20),n2varchar(20),uniquekey(n1));createtablet2liket1;insertintot1values(1,'gao1','gao'),(2,'gao2','gao'),(3,'gao3','gao'),(4,'gao4','gao'),(5,'gao5','gao'),(6,'gao6','gao'),(7,'gao7','gao'),(8,'gao8','gao');

查看执行计划：

mysql>descinsertintot2select*fromt1forceindex(n1)wheren1in('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|Usingindexcondition|+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

执行语句:

begin;insertintot2select*fromt1forceindex(n1)wheren1in('gao2','gao3','gao4');

观察输出：

B表二级索引对选中记录加上LOCK_S|LOCK_REC_NOT_GAP

-----TRXNO:30514LOCKSTRUCT(1)(Addbygaopeng)RECORDLOCKSspaceid94pageno4nbits80indexn1oftable`test`.`t1`trxid30514lockmodeS(LOCK_S)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno3PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex67616f32;ascgao2;;1:len4;hex80000002;asc;;Recordlock,heapno4PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex67616f33;ascgao3;;1:len4;hex80000003;asc;;Recordlock,heapno5PHYSICALRECORD:n_fields2;compactformat;infobits00:len4;hex67616f34;ascgao4;;1:len4;hex80000004;asc;;

B表PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP

-----TRXNO:30514LOCKSTRUCT(1)(Addbygaopeng)RECORDLOCKSspaceid94pageno3nbits80indexPRIMARYoftable`test`.`t1`trxid30514lockmodeS(LOCK_S)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno3PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000002;asc;;1:len6;hex000000007728;ascw(;;2:len7;hexa200000115011c;asc;;3:len4;hex67616f32;ascgao2;;4:len3;hex67616f;ascgao;;Recordlock,heapno4PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000003;asc;;1:len6;hex000000007728;ascw(;;2:len7;hexa2000001150128;asc(;;3:len4;hex67616f33;ascgao3;;4:len3;hex67616f;ascgao;;Recordlock,heapno5PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000004;asc;;1:len6;hex000000007728;ascw(;;2:len7;hexa2000001150134;asc4;;3:len4;hex67616f34;ascgao4;;4:len3;hex67616f;ascgao;;

如图红色部分都是需要锁定的记录

2.png

3.如果B.COL没有二级索引

使用语句：

droptablet1;droptablet2;createtablet1(idintprimarykey,n1varchar(20),n2varchar(20));createtablet2liket1;insertintot1values(1,'gao1','gao'),(2,'gao2','gao'),(3,'gao3','gao'),(4,'gao4','gao'),(5,'gao5','gao'),(6,'gao6','gao'),(7,'gao7','gao'),(8,'gao8','gao');

查看执行计划：

mysql>descinsertintot2select*fromt1wheren1in('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|Usingwhere|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

执行语句:

begin;insertintot2select*fromt1wheren1in('gao2','gao3','gao4');

观察输出:

-----TRXNO:30535LOCKSTRUCT(1)(Addbygaopeng)RECORDLOCKSspaceid94pageno3nbits80indexPRIMARYoftable`test`.`t1`trxid30535lockmodeS(LOCK_S)locksgapandrec(LOCK_ORDINARY[next_key_lock])Recordlock,heapno1PHYSICALRECORD:n_fields1;compactformat;infobits00:len8;hex73757072656d756d;ascsupremum;;Recordlock,heapno2PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000001;asc;;1:len6;hex000000007728;ascw(;;2:len7;hexa2000001150110;asc;;3:len4;hex67616f31;ascgao1;;4:len3;hex67616f;ascgao;;Recordlock,heapno3PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000002;asc;;1:len6;hex000000007728;ascw(;;2:len7;hexa200000115011c;asc;;3:len4;hex67616f32;ascgao2;;4:len3;hex67616f;ascgao;;Recordlock,heapno4PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000003;asc;;1:len6;hex000000007728;ascw(;;2:len7;hexa2000001150128;asc(;;3:len4;hex67616f33;ascgao3;;4:len3;hex67616f;ascgao;;Recordlock,heapno5PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000004;asc;;1:len6;hex000000007728;ascw(;;2:len7;hexa2000001150134;asc4;;3:len4;hex67616f34;ascgao4;;4:len3;hex67616f;ascgao;;Recordlock,heapno6PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000005;asc;;1:len6;hex000000007728;ascw(;;2:len7;hexa2000001150140;asc@;;3:len4;hex67616f35;ascgao5;;4:len3;hex67616f;ascgao;;Recordlock,heapno7PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000006;asc;;1:len6;hex000000007728;ascw(;;2:len7;hexa200000115014c;ascL;;3:len4;hex67616f36;ascgao6;;4:len3;hex67616f;ascgao;;Recordlock,heapno8PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000007;asc;;1:len6;hex000000007728;ascw(;;2:len7;hexa2000001150158;ascX;;3:len4;hex67616f37;ascgao7;;4:len3;hex67616f;ascgao;;Recordlock,heapno9PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000008;asc;;1:len6;hex000000007728;ascw(;;2:len7;hexa2000001150164;ascd;;3:len4;hex67616f38;ascgao8;;4:len3;hex67616f;ascgao;;

如图红色部分都是需要锁定的记录

3.png

有了上面的理论，我们知道在RR隔离级别下insert...select会对select符合条件的数据加上LOCK_S锁，我曾经总结过出现死锁的条件:

至少2个独立的线程(会话)

单位操作中包含多个相对独立的加锁步骤，有一定的时间差

多个线程(会话)之间加锁对象必须有相互等待的情况发生，并且等待出现环状。

由于存在对select符合条件的数据加上LOCK_S锁的情况，RR模式下insert...select出现死锁的概率无疑更加高，我通过测试模拟出这种情况，严格意义上是相同的语句在高并发情况下表现为两种死锁情况。

测试脚本:

createtableb(idintprimarykey,name1varchar(20),name2varchar(20));altertablebaddkey(name1);DELIMITER//CREATEPROCEDUREtest_i()begindeclarenumint;setnum=1;whilenum<=3000doinsertintobvalues(num,concat('gao',num),'gaopeng');setnum=num+1;endwhile;end//calltest_i()//createtablealikeb//DELIMITER;

语句都是一样的:

但是在高并发下相同的语句却表现出不同的死锁情况，现在进行分析:

情况1:

TX1:执行update将表b主键id=2999的记录加上LOCK_X

TX2:执行insert...select语句b表上的记录(996,997,998,999,2995,2996,2997,2998,2999)会申请加上LOCK_S,
但是id=2999已经加上LOCK_X,显然不能获得只能等待.

TX1:执行update需要获得表b主键id=999的LOCK_X显然这个记录已经被TX2加锁LOCK_S，只能等待，触发死锁检测

如下图红色记录为不能获得锁的记录:

情况1.jpg

情况2:

这种情况比较极端只能在高并发上出现

TX1:执行update将表b主键id=2999的记录加上LOCK_X

TX2:执行insert...select语句b表上的记录(996,997,998,999,2995,2996,2997,2998,2999)会申请加上LOCK_S，因为上锁是有一个逐步加锁的过程,假设此时加锁到2997前那么TX2并不会等待

TX1:执行update需要获得表b主键id=999的LOCK_X显然这个记录已经被TX2加锁LOCK_S，只能等待

TX2:继续加锁LOCK_S 2997、2998、2999 发现2999已经被TX1加锁LOCK_X，只能等待，触发死锁检测

如下图红色记录为不能获得锁的记录:

情况2.jpg

情况2的测试需要在高并发下才会出现，因为insert...select语句是一条语句很难人为控制，也就是很让他在特定条件下停止。但是为了能够模拟出这种情况笔者对innodb增加了4个参数如下，为了方便识别我都加上了自己的名字的拼音：

mysql>showvariableslike'%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|+---------------------------+-------+

默认情况都是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就能唯一限定一条数据了，并且睡眠时间也是可以人为指定的。

并且在源码lock_rec_lock 开头增加如下代码:

//addbygaopeng/*iffindindex_idheapnopagenotosleepsrv_gaopeng_sl_timesecs*/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();}}//addend

这样一旦判定为符合条件的记录，本条记录的加锁钱便会休眠指定的秒如果我们拟定在LOCK_S:id=2997之前睡眠30秒，那么情况2能够必定发生如下图:

情况3.jpg

情况1:

死锁报错语句:

mysql>updatebsetname2='test'whereid=999;ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction

死锁日志:

***(1)TRANSACTION:TRANSACTION48423,ACTIVE7secstartingindexreadmysqltablesinuse2,locked2LOCKWAIT5lockstruct(s),heapsize1160,9rowlock(s),undologentries8MySQLthreadid4,OSthreadhandle140737223177984,queryid9110localhostrootSendingdatainsertintoaselect*frombwhereidin(996,997,998,999,2995,2996,2997,2998,2999)***(1)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid119pageno18nbits160indexPRIMARYoftable`test`.`b`trxid48423lockmodeS(LOCK_S)locksrecbutnotgap(LOCK_REC_NOT_GAP)waiting(LOCK_WAIT)Recordlock,heapno86PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000bb7;asc;;1:len6;hex00000000bd26;asc&;;2:len7;hex21000001511e7d;asc!Q};;3:len7;hex67616f32393939;ascgao2999;;4:len4;hex74657374;asctest;;***(2)TRANSACTION:TRANSACTION48422,ACTIVE24secstartingindexreadmysqltablesinuse1,locked13lockstruct(s),heapsize1160,2rowlock(s),undologentries1MySQLthreadid3,OSthreadhandle140737223444224,queryid9111localhostrootupdatingupdatebsetname2='test'whereid=999***(2)HOLDSTHELOCK(S):RECORDLOCKSspaceid119pageno18nbits160indexPRIMARYoftable`test`.`b`trxid48422lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno86PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000bb7;asc;;1:len6;hex00000000bd26;asc&;;2:len7;hex21000001511e7d;asc!Q};;3:len7;hex67616f32393939;ascgao2999;;4:len4;hex74657374;asctest;;***(2)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid119pageno10nbits456indexPRIMARYoftable`test`.`b`trxid48422lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)waiting(LOCK_WAIT)Recordlock,heapno11PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex800003e7;asc;;1:len6;hex00000000b534;asc4;;2:len7;hexbd000001310110;asc1;;3:len6;hex67616f393939;ascgao999;;4:len7;hex67616f70656e67;ascgaopeng;;***WEROLLBACKTRANSACTION(2)

信息提取如下:

TRX1:48423LOCKHOLD:死锁信息不提供LOCKWAIT:表:b索引:`PRIMARY`锁模式:LOCK_S|LOCK_REC_NOT_GAP|LOCK_WAIT记录:主键为0Xbb7(2999)附加信息:spaceid119pageno18heapno86CURRENTSQL:insertintoaselect*frombwhereidin(996,997,998,999,2995,2996,2997,2998,2999)TRX2:48422(触发死锁、权重回滚)LOCKHOLD:表:b索引:`PRIMARY`锁模式:LOCK_X|LOCK_REC_NOT_GAP记录:主键为0Xbb7(2999)附加信息:paceid119pageno18heapno86LOCKWAIT:表:b索引:`PRIMARY`锁模式:LOCK_X|LOCK_REC_NOT_GAP|LOCK_WAIT记录:主键为0X3e7(999)附加信息:spaceid119pageno10heapno11CURRENTSQL:updatebsetname2='test'whereid=999

情况2:

如上我们拟定在语句

insertintoaselect*frombwhereidin(996,997,998,999,2995,2996,2997,2998,2999)

对b表记录加锁时在2997加锁前停顿30秒，那么我就需要找到b表主键2997的index_id、page_no、heap_no三个信息，这里使用到我的innblock工具

./innblockb.ibdscan16===INDEX_ID:121level1totalblockis(1)block_no:3,level:1|*|level0totalblockis(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中然后如下:

./innblockb.ibd1816====Blockbaseinfo====block_no:18space_id:121index_id:121....(84)normalrecordoffset:3287heapno:83n_owned0,delflag:Nminflag:0rectype:0(85)normalrecordoffset:3326heapno:84n_owned0,delflag:Nminflag:0rectype:0(86)normalrecordoffset:3365heapno:85n_owned0,delflag:Nminflag:0rectype:0(87)normalrecordoffset:3404heapno:86n_owned0,delflag:Nminflag:0rectype:0(88)normalrecordoffset:3443heapno:87n_owned0,delflag:Nminflag:0rectype:0

因为为顺序插入heap_no 84就是id为2997的记录。我们使用另外一个工具bcview进行验证

./bcviewb.ibd1633264currentblock:00000018--Offset:03326--cntbytes:04--datais:80000bb5

当然0Xbb5就是2997
因此设置参数为：

setglobalinnodb_gaopeng_sl_heap_no=84;setglobalinnodb_gaopeng_sl_ind_id=121;setglobalinnodb_gaopeng_sl_page_no=18;setglobalinnodb_gaopeng_sl_time=30;mysql>showvariableslike'%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执行顺序如下:

死锁报错语句:

mysql>updatebsetname2='test'whereid=999;ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction

死锁日志:

***(1)TRANSACTION:TRANSACTION51545,ACTIVE41secstartingindexreadmysqltablesinuse1,locked1LOCKWAIT3lockstruct(s),heapsize1160,2rowlock(s),undologentries1MySQLthreadid9,OSthreadhandle140737223444224,queryid18310localhostrootupdatingupdatebsetname2='test'whereid=999***(1)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid121pageno10nbits456indexPRIMARYoftable`test`.`b`trxid51545lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)waiting(LOCK_WAIT)Recordlock,heapno11PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex800003e7;asc;;1:len6;hex00000000c167;ascg;;2:len7;hexbc000001300110;asc0;;3:len6;hex67616f393939;ascgao999;;4:len7;hex67616f70656e67;ascgaopeng;;***(2)TRANSACTION:TRANSACTION51546,ACTIVE30secstartingindexreadmysqltablesinuse2,locked25lockstruct(s),heapsize1160,9rowlock(s),undologentries8MySQLthreadid8,OSthreadhandle140737223177984,queryid18309localhostrootSendingdatainsertintoaselect*frombwhereidin(996,997,998,999,2995,2996,2997,2998,2999)***(2)HOLDSTHELOCK(S):RECORDLOCKSspaceid121pageno10nbits456indexPRIMARYoftable`test`.`b`trxid51546lockmodeS(LOCK_S)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno8PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex800003e4;asc;;1:len6;hex00000000c164;ascd;;2:len7;hexb90000012d0110;asc-;;3:len6;hex67616f393936;ascgao996;;4:len7;hex67616f70656e67;ascgaopeng;;Recordlock,heapno9PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex800003e5;asc;;1:len6;hex00000000c165;asce;;2:len7;hexba0000014f0110;ascO;;3:len6;hex67616f393937;ascgao997;;4:len7;hex67616f70656e67;ascgaopeng;;Recordlock,heapno10PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex800003e6;asc;;1:len6;hex00000000c166;ascf;;2:len7;hexbb0000012f0110;asc/;;3:len6;hex67616f393938;ascgao998;;4:len7;hex67616f70656e67;ascgaopeng;;Recordlock,heapno11PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex800003e7;asc;;1:len6;hex00000000c167;ascg;;2:len7;hexbc000001300110;asc0;;3:len6;hex67616f393939;ascgao999;;4:len7;hex67616f70656e67;ascgaopeng;;***(2)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid121pageno18nbits160indexPRIMARYoftable`test`.`b`trxid51546lockmodeS(LOCK_S)locksrecbutnotgap(LOCK_REC_NOT_GAP)waiting(LOCK_WAIT)Recordlock,heapno86PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000bb7;asc;;1:len6;hex00000000c959;ascY;;2:len7;hex00000002240110;asc$;;3:len7;hex67616f32393939;ascgao2999;;4:len4;hex74657374;asctest;;***WEROLLBACKTRANSACTION(1)

信息提取如下:

TRX1:51545LOCKHOLD:死锁信息不提供LOCKWAIT:表:b索引:`PRIMARY`锁模式:LOCK_MODE:LOCK_X|LOCK_REC_NOT_GAP|LOCK_WAIT记录:主键为0X3e7附加信息:spaceid121pageno10heapno11CURRENTSQL:updatebsetname2='test'whereid=999TRX2:51546LOCKHOLD:表:b索引:`PRIMARY`锁模式:LOCK_MODE:LOCK_S|LOCK_REC_NOT_GAP记录:主键为0X3e4到0X3e7的多个行锁附加信息:spaceid121pageno10LOCKWAIT:表:b索引:`PRIMARY`锁模式:LOCK_MODE:LOCK_S|LOCK_REC_NOT_GAP|LOCK_WAIT记录:主键为0Xbb7附加信息:spaceid121pageno10heapno86CURRENTSQL:insertintoaselect*frombwhereidin(996,997,998,999,2995,2996,2997,2998,2999)

我们通过死锁日志明显的看出同样的语句报出来的死锁信息却不一样，在高并发下相同语句，两种死锁场景都是可能发生的。

Innodb中RR隔离级别下insert...select 对select表加锁模型和死锁案列分析.docx

将本文的Word文档下载到电脑

下载

热门标签: innodbrr

精品

申请https需要什么条件

Innodb中RR隔离级别下insert...select 对select表加锁模型和死锁案列分析[ mysql数据库 ]

Innodb中RR隔离级别下insert...select 对select表加锁模型和死锁案列分析.docx

精品

热门推荐

大家都在看