How to interpret the contents of the lock part of show engine innodb status

This article is about how to explain the lock part of show engine innodb status. Xiaobian thinks it is quite practical, so share it with you. I hope you can gain something after reading this article. Let's not say much. Let's take a look at it together with Xiaobian.

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 4, OS thread handle 140665176164096, query id 575 localhost root update

insert into testpri values(13,10)

------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 723 page no 3 n bits 80 index PRIMARY of table `test`.` testpri` trx id 6953526 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 8000000f; asc ;;

1: len 6; hex 0000006a1a29; asc j );;

2: len 7; hex ba000040370110; asc @7 ;;

3: len 4; hex 80000014; asc ;;

As above:

lock struct memory structureAn object can contain multiple lock structuresincluding table locks and row lock structurescorresponding to lock_rect and lock_table_t structuresLOCK WAIT is a state of the lock

To do this, I modified the source code for lock printing as follows to see the class content of each lock structure:

---TRANSACTION 173210, ACTIVE 8 sec

2 lock struct(s), heap size 1160, 1 row lock(s)

MySQL thread id 2, OS thread handle 140737154311936, query id 174 localhost root cleaning up

---lock strcut(1):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK chain! for this Trx

TABLE LOCK table `test`.` testmmm` trx id 173210 lock mode IX

---lock strcut(2):(Add by gaopeng) In modify Version I force check all REC_LOCK/TAB_LOCK chain! for this Trx

RECORD LOCKS space id 253 page no 3 n bits 80 index PRIMARY of table `test`.` testmmm` trx id 173210 lock_mode X locks rec but not gap

Record lock, heap no 9 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 4; hex 80000023; asc #;;

1: len 6; hex 0000000298ee; asc ;;

2: len 7; hex d90000023d0110; asc = ;;

3: len 7; hex 67616f70656e67; asc gaopeng;;

heap size The heap size occupied by these memory structures

1 row lock(s) row lock structure locks a row of data, a lock_rec_t contains page no and contains a bitmap with rows +64 bits, each bit corresponds to

The position of heap no in the page.

RECORD LOCKS space id 723 page no 3: that is, the tablespace id and page number

n bits 80: The size of the lock bitmap associated with this page My table has 9 entries and contains 2 start and end virtual columns and 64+11 bits, and 75bits, but must be divisible by 8 into one byte, which is 80 bits.

heap no 5: this line in the page heap no heap no stored in fixed_extrasize, heap no is the number of physical storage fill, the free space of the page mounted in the page free list (header insertion method) can be reused, but reuse this heap no unchanged,

If it is always insert, then heap no increases continuously, not in logical list order sorted by KEY size, but in physical fill order.

0: len 4; hex 800000 f; asc ;; aggregate index actual KEY 0XF 15 8 most significant bits sign bit 1 positive

1: len 6; hex 000006 a1a29; asc j );;transaction id Virtual line

2: len 7; hex ba000040370110; asc@7;;roll pointer Virtual line

3: len 4; hex 80000014; asc ;; Other data in row There is only one 0x14 20

The above is how to explain the contents of the lock part in show engine innodb status. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.