Shulou(Shulou.com)05/31 Report--

This article mainly introduces "insert...select locking model and deadlock case column analysis for select table under RR isolation level in Innodb". In daily operation, it is believed that many people have doubts about select table locking model and deadlock case column analysis under RR isolation level in Innodb. The editor consulted all kinds of data and sorted out simple and useful operation methods. I hope it will be helpful for you to answer the doubt of "insert...select on select table locking model and deadlock case column analysis under RR isolation level in Innodb"! Next, please follow the editor to study!

I. basic concepts

1. Innodb lock model

[LOCK_ ORDINARY[next _ key_lock]:]

Source code definition:

# define LOCK_ORDINARY 0 / *!

< this flag denotes an ordinarynext-key lock in contrast to LOCK_GAPor LOCK_REC_NOT_GAP */ 默认是LOCK_ORDINARY即普通的next_key_lock,锁住行及以前的间隙。 [LOCK_GAP:] 源码定义: #define LOCK_GAP 512 /*!< when this bit is set, it means that thelock holds only on the gap before the record;for instance, an x-lock on the gap does notgive permission to modify the record on whichthe bit is set; locks of this type are createdwhen 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 onthe index record and does NOT block insertsto the gap before the index record; this isused in the case when we retrieve a recordwith a unique key, and is also used inlocking plain SELECTs (not part of UPDATEor DELETE) when the user has set the READCOMMITTED isolation level */ 行锁，锁住行而不锁住任何间隙。 [LOCK_INSERT_INTENTION:] 源码定义： #define LOCK_INSERT_INTENTION 2048 /*!< this bit is set when we place a waitinggap type record lock request in order to letan insert of an index record to wait untilthere are no conflicting locks by othertransactions on the gap; note that this flagremains set when the waiting lock is granted,or if the lock is inherited record */ 插入意向锁，如果插入的记录在某个已经锁定的间隙内为这个锁。 2、 innodb lock兼容矩阵 /* LOCK COMPATIBILITY MATRIX* IS IX S X AI* 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_t tab_lock;/*!< table lock */lock_rec_t rec_lock;/*!< record lock */ 一般来说innodb上锁都会对表级加上IX,这占用一个结构体。然后分别对二级索引和主键进行加锁，每一个BLOCK会占用这样一个结构体。 7、row lock 这个信息描述了当前事务加锁的行数,他是所有lock struct结构体中排除table lock以外所有加锁记录的总和，并且包含了infimum和supremum伪列。 8、逐步加锁 如果细心的朋友应该会发现在show engine 中事务信息中的row lock在对大量行进行加锁的时候会不断的增加，因为加行锁最终会调用lock_rec_lock逐行加锁，这也会增加了大数据量加锁的触发死锁的可能性。 二、Innodb层对insert...select 中select表的加锁模式 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] 三、Innodb层对insert...select中select表的加锁测试 下面我们分别对其进行测试和打印输出： 1. 如果B.COL有二级(唯一),并且执行计划使用到了(非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'); 查看执行计划： 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 | 1 | SIMPLE | T1 | NULL | ref | N1 | 23 | const | 3 | NULL | +-+ -+

Execute the statement:

Begin;insert into T2 select * from T1 force index (N1) where N1 gao2'

Observation results:

The secondary index adds LOCK_S to the record | LOCK_ Ordinal [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_ heap no PHYSICAL RECORD: n_fields 2; compact format; info bits 00: 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 00: 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 00: len 4; hex 67616f32; asc gao2;;1: len 4; hex 80000006; asc

PRIMARY plus 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 00: len 4; hex 80000004; asc; 1: len 6; hex 0000006f20; asc o; 2: len 7; hex bc000001300134; asc 04; 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 00: len 4; hex 80000005; asc; 1: len 6; hex 0000006f20; asc obot; 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 00: len 4; hex 80000006; asc ; 1: len 6; hex 000000006f20; asc o;; 2: len 7; hex bc00000130014c; asc 0 L politics 3: len 4; hex 67616f32; asc gao2;;4: len 3; hex 67616f; asc gao

Add LOCK_S to the next record in the secondary index | 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 00: len 4; hex 67616f33; asc gao3;;1: len 4; hex 80000007; asc

As shown in the picture, the red parts are records that need to be locked.

1.png

two。 If the B.COL has a second tier (unique) and the execution plan is used (non-using index)

Use the statement:

Drop table T1 drop table T2 politics 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), (2), (2), (3), (3), (3), (4), (4), (5), (6), (6), (7), (7), (8), (8)

View the execution plan:

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 | | 1 | SIMPLE | T1 | NULL | range | | N1 | N1 | 23 | NULL | 3 | 100.00 | Using index condition | +-- +-| -+-+

Execute the statement:

Begin;insert into T2 select * from T1 force index (N1) where N1 in ('gao2','gao3','gao4')

Observe the output:

The secondary index of Table B adds LOCK_S to the selected record | 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 00: 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 00: 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 00: len 4; hex 67616f34; asc gao4;;1: len 4; hex 80000004; asc

Table B PRIMARY plus 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 00: len 4; hex 80000002; asc;; 1: len 6; hex 0000007728; 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 00: len 4; hex 80000003; asc; 1: len 6; hex 0000007728; 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 00: len 4; hex 80000004 Asc;; 1: len 6; hex 0000007728; asc w (; 2: len 7; hex a2000001150134; asc 4 len 4; hex 67616f34; asc gao4;;4: len 3; hex 67616f; asc gao

As shown in the picture, the red parts are records that need to be locked.

2.png

3. If B.COL does not have a secondary index

Use the statement:

Drop table T1 id int primary key,n1 varchar drop table T2 politics create table T1 (id int primary key,n1 varchar (20), N2 varchar (20)); create table T2 like T1 Insert into T1 values (1), (2), (2), (3), (3), (3), (4), (4), (5), (6), (6), (7), (7), (8), (8)

View the execution plan:

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 | 1 | SIMPLE | T1 | NULL | ALL | NULL | 8 | 37.50 | Using where | +- -+

Execute the statement:

Begin;insert into T2 select * from T1 where N1 in ('gao2','gao3','gao4')

Observe the output:

-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 00: len 8; hex 73757072656d756d; asc supremum;;Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 80000001; asc; 1: len 6 Hex 0000007728; 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 00: len 4; hex 80000002; asc; 1: len 6; hex 0000007728; 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 00: len 4; hex 80000003; asc; 1: len 6; hex 0000007728; 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 00: len 4; hex 80000004; asc; 1: len 6; hex 000000007728 Asc w (;; 2: len 7; hex a2000001150134; asc 4 X 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 00: len 4; hex 80000005; asc;; 1: len 6; hex 0000007728; 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 00: len 4; hex 80000006; asc; 1: len 6; hex 0000007728; asc w (;; 2: len 7; hex a200000115014c; asc Lten 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 00: len 4; hex 80000007; asc; 1: len 6; hex 000000007728; asc w ( ; 2: len 7; hex a2000001150158; asc X politics 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 00: len 4; hex 80000008; asc;; 1: len 6; hex 0000007728; asc w (;; 2: len 7; hex a2000001150164; asc d politics 3: len 4; hex 67616f38; asc gao8;;4: len 3; hex 67616f; asc gao

As shown in the picture, the red parts are records that need to be locked.

3.png

4. Deadlock caused by select query table in insert...select

With the above theory, we know that under the RR isolation level, insert...select will add LOCK_S locks to select eligible data, and I have summarized the conditions for deadlocks:

At least 2 separate threads (sessions)

The unit operation contains several relatively independent locking steps with a certain time difference.

Locking objects between multiple threads (sessions) must wait for each other and wait for a ring to occur.

Due to the existence of adding LOCK_ S locks to select qualified data, the probability of deadlocks in insert...select in RR mode is undoubtedly higher. I simulated this situation through tests. Strictly speaking, the same statement behaves as two kinds of deadlocks in high concurrency cases.

Test script:

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

The default is 0, which means it is not enabled. What they mean is as follows:

Innodb_gaopeng_sl_heap_no: the heap no where the record is located

Innodb_gaopeng_sl_ind_id: the index_id where the record is located

Innodb_gaopeng_sl_page_no: the page_no where the record is located

Innodb_gaopeng_sl_time: how many seconds do you sleep?

With index_id, page_no, and heap no, you can only limit one piece of data, and sleep time can also be artificially specified.

And add the following code at the beginning of the source code 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

In this way, once the record is determined to meet the criteria, the locked money recorded in this section will sleep for a specified second. If we plan to sleep for 30 seconds before LOCK_S:id=2997, then case 2 will surely happen as shown below:

Situation 3.jpg

VI. Practical testing

Case 1:

TX1TX2begin;-update b set name2='test' where id=2999; adds a LOCK_X lock to id:2999-insert into a select * from b where id in (996997pje 9995jn995j2996jng2997pm2997pm2998pl 299999); add LOCK_S lock to id:996,997,998,999,2995,2996,2997,2998, but find that LOCK_X lock has been added when adding lock _ S lock to id: 2999, need to wait for update b set name2='test' where id=999; lock to id:999 plus LOCK_X lock, but find that LOCK_S lock has been added, you need to wait, trigger deadlock detection-TX1 triggers deadlock, TX1 rollback under weight determination.

Deadlock error statement:

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

Deadlock log:

* * (1) TRANSACTION:TRANSACTION 48423, ACTIVE 7 sec starting index readmysql tables in use 2, locked 2LOCK WAIT 5 lock struct (s), heap size 1160, 9 row lock (s), undo log entries 8MySQL thread id 4, OS thread handle 140737223177984 Query id 9110 localhost root Sending datainsert into a select * from b where id in (996 page no 997 locks rec but not gap (LOCK_WAIT) waiting (LOCK_WAIT) Record lock, heap no 86 PHYSICAL RECORD: n_fields 5) * (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) waiting (LOCK_WAIT) Compact format; info bits 00: len 4; hex 80000bb7; asc; 1: len 6; hex 000000bd26; 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 readmysql tables in use 1, locked 13 lock struct (s), heap size 1160, 2 row lock (s), undo log entries 1MySQL thread id 3, OS thread handle 140737223444224, query id 9111 localhost root updatingupdate b set name2='test' where id=999*** (2) HOLDS THE LOCK (S): RECORD LOCKS space id 119page 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 00: len 4; hex 80000bb7; asc; 1: len 6; hex 000000bd26; 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 119n 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 00: len 4; hex 800003e7; asc; 1: len 6 Hex 000000b534; asc 4 politics 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)

The information is extracted as follows:

TRX1:48423 LOCK HOLD: deadlock information is not provided LOCK WAIT: table: B index: `PRIMARY` lock mode: LOCK_S | LOCK_REC_NOT_GAP | LOCK_WAIT record: primary key is 0Xbb7 (2999) additional information: space id page no 18 heap no 86 CURRENT SQL: insert into a select * from b where id in TRX2:48422 (trigger deadlock, Weight rollback) LOCK HOLD: table: B index: `PRIMARY` lock mode: LOCK_X | LOCK_REC_NOT_GAP record: primary key is 0Xbb7 (2999) additional information: pace id 119page no 18 heap no 86 LOCK WAIT: table: B index: `PRIMARY` Lock mode: LOCK_X | LOCK_REC_NOT_GAP | LOCK_WAIT record: primary key is 0X3e7 additional information: space id 119page no 10 heap no 11 CURRENT SQL: update b set name2='test' where id=999

Case 2:

As mentioned above, we draw up in the sentence

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

When locking a b-table record, pause for 30 seconds before 2997 is locked, then I need to find the index_id, page_no and heap_no information of the b-table primary key 2997. Here I use my innblock tool

Level: 0 | * | block_no: 15 department level: 0 | * | block_no: 17 department level: 0 | * | block_no: 18 department level: 0 | * |

Because for sequential insertion, then 2997 must be in page 18 and then as follows:

. / innblock b.ibd 18 16 minflag:0 rectype:0 = Block base info = block_no:18 space_id:121 index_id:121.... (84) normal record offset:3287 heapno:83 n_owned 0 Delphi minflag:0 rectype:0 N minflag:0 rectype:0 (85) normal record offset:3326 heapno:84 n_owned 0 Delphi N minflag:0 rectype:0 (86) normal record offset:3365 heapno:85 n_owned 0 delta 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 delaying minflag:0 rectype:0 N minflag:0 rectype:0

Because inserting heap_no 84 sequentially is a record with an id of 2997. We use another tool, bcview, for verification.

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

Of course, 0Xbb5 is 2997.

Therefore, the setting parameters are:

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

Then the execution order of case 2 is as follows:

TX1TX2begin;-update b set name2='test' where id=2999; to id: 2999 plus lock _ X lock-- insert into a select * from b where id in (996, 9997, 9998, 2996, 2999, 2999, 2999, 2999, 2999, 2999, 2999, LOCK_S, LOCK_S, sleep 30 seconds before locking id:2997, make time for the following update statement) update b set name2='test' where id=999 Add lock _ X lock to id: 999 but find that LOCK_S lock has been added. Wait-after waking up, continue to add LOCK_S lock to 2997, 2998, 2999, but find that id:2999 has added LOCK_X lock, need to wait, trigger deadlock to detect TX1 weight rollback-

Deadlock error statement:

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

Deadlock log:

* * (1) TRANSACTION:TRANSACTION 51545, ACTIVE 41 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct (s), heap size 1160, 2 row lock (s), undo log entries 1MySQL thread id 9, OS thread handle 140737223444224 Query id 18310 localhost root updatingupdate b set name2='test' where id=999*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 121page no 10n 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 00: len 4; hex 800003e7; asc; 1: len 6; hex 000000c167; asc g Tabit 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 readmysql tables in use 2, locked 25 lock struct (s), heap size 1160, 9 row lock (s), undo log entries 8MySQL thread id 8, OS thread handle 140737223177984 Query id 18309 localhost root Sending datainsert into a select * from b where id in (996 page no 997 locks rec but not gap) 2995 locks rec but not gap 2999) * * (2) HOLDS THE LOCK (S): RECORD LOCKS space id 121LOCK_REC_NOT_GAP 10n 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 00: len 4; hex 800003e4; asc; 1: len 6; hex 000000c164; asc dscape 2: len 7; hex b90000012d0110; asc -; 3: len 6; hex 67616f3936; asc gao996;;4: len 7; hex 67616f70656e67; asc gaopeng;;Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 00: len 4; hex 800003e5; asc; 1: len 6; hex 000000c165; asc econtrol 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 00: len 4; hex 800003e6; asc;; 1: len 6; hex 000000c166; asc f Tans2: len 7; hex bb0000012f0110; asc /; 3: len 6; hex 67616f393938; asc gao998;;4: len 7; hex 67616f70656e67e; asc gaopeng;;Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format Info bits 00: len 4; hex 800003e7; asc; 1: len 6; hex 000000c167; asc g Tabit 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 121page no 18 n bits 160index 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 00: len 4; hex 80000bb7; asc; 1: len 6; hex 000000c959; asc Ywitters2: len 7; hex 000002240110; asc $; 3: len 7 Hex 67616f32393939; asc gao2999;;4: len 4; hex 74657374; asc test;;*** WE ROLL BACK TRANSACTION (1)

The information is extracted as follows:

TRX1:51545 LOCK HOLD: deadlock information is not provided LOCK WAIT: table: B index: `PRIMARY` lock mode: LOCK_MODE:LOCK_X | LOCK_REC_NOT_GAP | LOCK_WAIT record: primary key is 0X3e7 additional information: space id 121page no 10 heap no 11 CURRENT SQL: update b set name2='test' where id=999 TRX2:51546 LOCK HOLD: table: B index: `PRIMARY` lock mode: LOCK_MODE:LOCK_S | LOCK_REC_NOT_GAP record: primary key is multiple row locks from 0X3e4 to 0X3e7 additional information: space id 121index 10 LOCK WAIT: table: B Index: `PRIMARY` lock mode: LOCK_MODE:LOCK_S | LOCK_REC_NOT_GAP | LOCK_WAIT record: primary key is 0Xbb7 additional information: space id 121 page no 10 heap no 86 CURRENT SQL: insert into a select * from b where id in

Through the deadlock log, we can clearly see that the deadlock information reported by the same statement is not the same. Under the high concurrency of the same statement, both deadlock scenarios are possible.

At this point, the study of "insert...select locking model and deadlock case column analysis of select table under RR isolation level in Innodb" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.