Shulou(Shulou.com)06/01 Report--

First, prepare data and questions

RR isolation level

CREATE TABLE `ty` (`id` int (11) NOT NULL AUTO_INCREMENT, `a` int (11) DEFAULT NULL, `b` int (11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idxa` (`a`) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4insert into ty (amemb) values (2), (5) 4), (6) 7)

Q:

Image.png

This situation will result in a deadlock, if you set the

Insert into ty (a ~ (b)) values (2 ~ (10))

Change to:

Insert into ty (a ~ (b)) values (5 ~ ~ 10)

There will be no deadlock. Why?

2. Initialization data drawing

The blockage of this deadlock is mainly concentrated in the secondary index. We roughly arrange the data of the secondary index KEY idxa (a) and the primary key according to the way stored by the Innodb engine, as shown in the figure:

Image.png

T2 step 1

Step T2 1:delete from ty where astat5

-TRX NO:334719 LOCK STRUCT (1) (Add by gaopeng) RECORD LOCKS space id 653 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 334719 lock_mode X (LOCK_X) locks gap and rec (LOCK_ PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 80000005; asc; 1: len 4; hex 80000009; asc -TRX NO:334719 LOCK STRUCT (1) (Add by gaopeng) RECORD LOCKS space id 653 page no 3 n bits 72 index PRIMARY of table `test`.`ty` trx id 334719 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP) Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 320: len 4; hex 80000009; asc; 1: len 6; hex 0000051b7f; asc; 2: len 7; hex 760000082b13fd; asc v +; 3: len 4 Hex 80000005; asc; 4: len 4; hex 80000004; asc;-TRX NO:334719 LOCK STRUCT (1) (Add by gaopeng) RECORD LOCKS space id 653 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 334719 lock_mode X (LOCK_X) locks gap before rec (LOCK_GAP) Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 80000006; asc; 1: len 4; hex 80000a; asc

According to this record, we can draw as follows. The red part is locked and the arrow is gap lock:

Image.png

4. T1 step 2

Step T2 1:delete from ty where ajar 5; blockage

-TRX NO:334724 LOCK STRUCT (1) (Add by gaopeng) RECORD LOCKS space id 653 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 334724 lock_mode X (LOCK_X) locks gap and rec (LOCK_ ORDINARY [next _ key_lock]) waiting (LOCK_WAIT) Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 80000005; asc; 1: len 4; hex 80000009; asc

According to this record, we can draw as follows: the yellow part prepares the locked but blocked part for transaction T1, and the record containing the yellow part and the red part shows that it is locked by T2 and T1 cannot get the lock of this record, it is actually a next key lock jam:

Image.png

Fifth, step T2 step 3 if it is:

Insert into ty (a ~ (b)) values (2 ~ (10))

Then a deadlock occurs. In fact, the value of this record recorded in the secondary index is (2Power11), and 11 is the value of the primary key, then the drawing is as follows:

Image.png

In this case, T2 is also blocked, because this area T1 is also blocked, deadlock occurs. The deadlock record is as follows:

* (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 653 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 334712 lock_mode X (LOCK_X) locks gap before rec (LOCK_GAP) insert intention (LOCK_INSERT_INTENTION) waiting (LOCK_WAIT) Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 320: len 4; hex 80000005; asc; 1: len 4; hex 80000009; asc

And insert impression lock jam

If this step is:

Insert into ty (a ~ (b)) values (5 ~ ~ 10)

No deadlock will occur. In fact, if the value of this record recorded in the secondary index is (5c11), and 11 is the value of the primary key, the drawing is as follows:

Image.png

If this is the case, there will be no deadlock, and we can see that there is no other blockage in this area for the secondary index, but T2 got it in the first place, and there will be no problem for this transaction to get again.

VI. Summary

The purpose of this case is to see in which area of the secondary index the inserted record that finally triggered the deadlock falls.

Author Wechat:

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.