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

This article mainly introduces the MySQL InnoDB transaction lock reading lock information status example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

⒈ table structure and data

The ⑴ locktest2 table is as follows: id is the primary key and an is the unique index

CREATE TABLE `locktest2` (

`id`int (11) NOT NULL

`a`int (11) NOT NULL

`b` varchar (30) NOT NULL DEFAULT 'xddd'

PRIMARY KEY (`id`)

UNIQUE KEY `a` (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

The following data exists in the ⑵ table:

Mysql > select * from locktest2

+-+

| | id | a | b | |

+-+

| | 3 | 2 | xddd |

| | 9 | 20 | ddd |

| | 12 | 13 | ddd |

| | 19 | 7 | cccc |

| | 20 | 5 | abcd |

| | 21 | 4 | fff |

+-+

What ⒉ constructs are waiting for

Session1 is as follows:

The session2 is as follows (session2 is waiting):

⒊ outputs transaction lock status information and field comments

TABLE LOCK table `test1`.`locktest2` trx id 4955 lock mode IX

TABLE LOCK: is a table lock

Table `test1`.`locktest2`: the table locked is the locktest2 table of the test1 library

Trx id 4955: the transaction id of this transaction (the DB_TRX_ID field above the row updated by this transaction is 4599, and this 4599 is mainly used by MVCC for visibility judgment)

Lock mode IX:IX lock mode, which is mainly aimed at multi-granularity locks. When there is a table lock, conflicts are detected in advance.

RECORD LOCKS space id 40 page no 4 n bits 80 index an of table `test1`.`locktest2` trx id 4955 lock_mode X locks gap before rec insert intention waiting

RECORD LOCKS: record lock

Space id 40 page no 4 n bits 80: the tablespace number is 40, the tablespace page number is 4, and 80 bits are used to indicate locking (bitmap, assuming that the record of heap no=2 is locked, the first element in bitmap is set to 1, and the rest is 0)

Index an of table `test1`.`locktest2`: locked on index an of the locktest2 table in the test1 library

Lock_mode X locks gap before rec insert intention waiting: lock mode is LOCK_X, lock type is LOCK_GAP | LOCK_INSERT_INTENTION, this lock is waiting; combine this lock, it is LOCK_X | LOCK_GAP | LOCK_INTENTION | LOCK_WAITING (please refer to my previous article on lock and lock mode)

Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

Record lock: record lock

Heap no 7: this record is numbered 7 in the middle of the page

PHYSICAL RECORD: n_fields 2; compact format; info bits 0: there are two fields in the physical record and the row format is compact; record information that is not marked for deletion

0: len 4; hex 80000014; asc

The first field: length 4 bytes, field value represented in hexadecimal, 14 or 20 in decimal

1: len 4; hex 80000009; asc

The second field: 4 bytes in length, the field value represented in hexadecimal is 9, that is, 9 in decimal.

Note: the above two fields make up the record of the secondary index, that is, (20Power9), with a value of 20 for the index field and 9 for the primary key field

RECORD LOCKS space id 40 page no 3 n bits 80 index PRIMARY of table `test1`.`locktest2` trx id 4954 lock_mode X locks rec but not gap

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

0: len 4; hex 80000009; asc; / / the value of the primary key column is 9

1: len 6; hex 000000000f6f; asc ostentatium; database TRXID, that is, 4954

2: len 7; hex d200000167011e; asc g;; / / rollback pointer rollback ptr

3: len 4; hex 80000014; asc; / / the value of the column

4: len 3; hex 646464; value of asc ddd;;//b column

⒋ lock waiting for simple analysis (here we mainly look at the lock status information, so this lock waits for simple analysis)

⑴ session 1 looks for records through index a (where a between 13 and 20), so LOCK_X | LOCK_ORDINARY | LOCK_REC is added to secondary index 13 to indicate that secondary index is locked (7jue 13]; to secondary index 20, LOCK_X | LOCK_ORDINARY | LOCK_REC is added to secondary index 20 to indicate that secondary index is locked. Add LOCK_X | LOCK_ORDINARY | LOCK_REC to the supremun record of the second-level index page, indicating that the interval is locked (2015 + infinity) (as for why the supermum record is locked, because this is the first record that does not meet the condition). Note that the specific primary key record found (actually the table data row) will also be locked, but it will not affect the lock waiting for analysis here.

The unique key an of ⑵ session insert has a value of 16, which is inserted in the range (13 LOCK_ORDINARY 20), and this range is added with a LOCK_X | LOCK_ORDINARY | LOCK_REC lock, which conflicts with insert's LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION, so the insert statement is set to wait state, that is, LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION/LOCK_WAITTING (see my previous article on lock type and lock mode conflict matrix).

Thank you for reading this article carefully. I hope the article "sample Analysis of the status of InnoDB transaction Lock Reading Lock in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.