编程知识 cdmana.com

In depth analysis of InnoDB implicit lock and visibility judgment from a case

author : Eight monsters ( Gao Peng ) Experts in the database of science and technology of China

One 、 Ask questions

I have a problem recently , Get the stack as follows (5.6.25):



When this problem occurs, there is only one read-write transaction , That's the business . I'm interested in the red part here , But not everything here is relevant to this question , It mainly focuses on visibility judgment and implicit lock determination , It's my thinking process . But yes. Innodb Limited cognitive level , If there is any misleading, please understand . Source code version used 5.7.29.

Two 、read view sketch

About read view There's a lot of commentary , I'd like to make a brief record of my study here . Consistent reads (consistent read), Depending on the level of isolation , Will be established at different times read view, as follows :

  • RR The first of the business select Set up when the command is initiated read view, Until the transaction commit is released

  • RC Every business select It's going to be built on its own read view

With read view The visibility of each row of data can be judged , Here is read view The key attributes in

  • m_up_limit_id: If it works trx id Less than m_up_limit_id It's invisible .

  • m_low_limit_id: If it works trx id It's bigger than m_low_limit_id Then visible .

  • m_ids: It's for record creation read view Read and write all the time vector Array , Used for the treatment of m_up_limit_id and m_low_limit_id Between trx It needs to be judged according to it , Is it active or not .

  • m_low_limit_no It is used to create records read view The smallest moment trx no, It is mainly used for purge Thread judgment cleanup undo Use .

How to get the value can be found in the appendix , For the judgment of visibility, we can refer to the following function :

/** Check whether the changes by id are visible.
 @param[in] id transaction id to check against the view
 @param[in] name table name
 @return whether the view sees the modifications of id. */
 bool changes_visible(
  trx_id_t  id,
  const table_name_t& name) const
  MY_ATTRIBUTE((warn_unused_result))
 {
  ut_ad(id > 0);
  if (id < m_up_limit_id || id == m_creator_trx_id) { // Less than   so 
   return(true);
  }
  check_trx_id_sanity(id, name);
  if (id >= m_low_limit_id) { // Greater than invisible 
   return(false);
  } else if (m_ids.empty()) { // If between  active  It's empty   Then visible  
   return(true);
  }
  const ids_t::value_type* p = m_ids.data();
  return(!std::binary_search(p, p + m_ids.size(), id)); // Otherwise, it's more like this trx id  Whether it's in this , If you can't see , On the contrary, we can see 
 }

3、 ... and 、 Some questions about visibility judgment

1、 There are a lot of deleted lines , And has submitted , But not by purge Thread cleanup

This situation is due to a large number of deleted lines ( perhaps update) And has submitted , But because of the long time select Sentences lead to read view The state of the record is also old , Therefore, according to m_low_limit_no The judgment of the purge Threads can't clean up some old ones undo Of , So this leads to a problem , If these del flag The records of will exist in the list of logical records , So the others select When you scan, you'll see next offset Scan to , But judging the conditions based on visibility del flag The record of trx id Less than Ben select Of the statement read view Of m_up_limit_id, So it's visible debug as follows :

387             return(view->changes_visible(trx_id, index->table->name));
(gdb) p view->changes_visible(trx_id, index->table->name)
$14 = true

But because it's already marked as del flag So we will do the skip processing as follows :

row_search_mvcc:
 if (rec_get_deleted_flag(rec, comp)) {
  /* The record is delete-marked: we can skip it */
       ...
       goto next_rec;

That is, in fact, for a long time read view Of “ Protect ” Next , our undo Can't clean up , also del flag It can't be cleaned up. It's still there block In the logical list of , When scanning, it will actually scan , It's just skip processing . So there will be the following phenomenon

T1 T2 T3
select sleep(1000) from test( Simulate long queries )


begin;delete from test10;commit;


select * from test10;( It's still a long time )

That's why , Although there is no data , But queries are still slow .

2、 Delete a lot of , Has not been submitted

that select The scan will be based on next offset Scan to , But because of read view Judging the data trx id be located m_up_limit_id and m_low_limit_id Between , It depends on whether the transaction is active (read view Of m_ids, Obviously, it's active here ) adopt undo Build a front impression , The following judgment :

lock_clust_rec_cons_read_sees
 trx_id_t trx_id = row_get_rec_trx_id(rec, index, offsets);
 return(view->changes_visible(trx_id, index->table->name));
3、using index Maybe it's back to the table

We know that if the implementation plan uses using index Then the primary key data will not be retrieved from the table , Use the entire secondary index . But there's a special case here , Here we describe .

For secondary indexes , because row The record does not contain trx id and undo ptr Two pseudo columns , Then the visibility judgment and the previous impression construction need to return to the table to obtain the primary key record , Of course, the visibility judgment can be based on the secondary index page Of max trx id Is less than read view Of m_up_limit_id To do the first rough filtering , It's a lot of low visibility , If this comparison passes , Then the accurate judgment of the remainder still needs to go back to the table and compare through the primary key , as follows :

  • For secondary index back to table operation , The precise visibility judgment is placed behind the table lock_clust_rec_cons_read_sees On the function , On the return table of secondary index , Refer to appendix .

  • For non return table access (using index), After passing the rough judgment (lock_sec_rec_cons_read_sees), If you need accurate visibility judgment , So it's back to the table , The reason is explained earlier (row The record does not contain trx id and undo ptr), Refer to appendix .

For this problem, we can simply do the following test , Of course, you need a break :

 The test table is as follows :
mysql> show create table testimp4 \G
*************************** 1. row ***************************
       Table: testimp4
Create Table: CREATE TABLE `testimp4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `d` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`b`),
  KEY `d` (`d`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from testimp4;
+------+------+------+------------------------------------+
| id   | a    | b    | d                                  |
+------+------+------+------------------------------------+
|    5 |    5 |  300 | NULL                               |
|    6 | 7000 | 7700 | 1124                               |
|   11 | 7000 | 7700 | 1124                               |
|   12 | 7000 | 7700 | 1124                               |
|   13 | 2900 | 1800 | NULL                               |
|   14 | 2900 | 1800 | NULL                               |
| 1000 |   88 | 1499 | NULL                               |
| 4000 | 6000 | 5904 | iiiafsafasfihhhccccchhhigggofgo111 |
| 4001 | 7000 | 7700 | 1124454555                         |
| 9999 | 9999 | 9999 | a                                  |
+------+------+------+------------------------------------+
10 rows in set (0.00 sec)

For the execution of the following statements :


mysql> desc select b from testimp4  where b=300;
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | testimp4 | NULL       | ref  | b             | b    | 5       | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Let's make the following statement :

T1 T2
begin;delete from testimp4 where id=5;( No submission )

select b from testimp4 where b=300;( Here is the table to return to )

It's obvious here T2(5 ,5 ,300 ,NULL ) This record has been T1 Deleted , But didn't submit ,T2 First judge the secondary index b The data in the last line is page Its max trx id Is it smaller than Ben select Of the statement read view Of m_up_limit_id, Obviously it doesn't work , because T1 Still active , Then it enters the back table judgment process . The stack is as follows :

#0  lock_clust_rec_cons_read_sees (rec=0x7fff060980a8 "\200", index=0x7ffec0499330, offsets=0x7fffe8399a70, view=0x33b1368)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/lock/lock0lock.cc:369
#1  0x0000000001afbca4 in Row_sel_get_clust_rec_for_mysql::operator() (this=0x7fffe839a2d0, prebuilt=0x7ffec80c97a0, sec_index=0x7ffec049a2c0, rec=0x7fff060a008c "\200", 
    thr=0x7ffec80c9f88, out_rec=0x7fffe839a310, offsets=0x7fffe839a2e8, offset_heap=0x7fffe839a2f0, vrow=0x0, mtr=0x7fffe8399d90)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0sel.cc:3763
#2  0x0000000001b00a94 in row_search_mvcc (buf=0x7ffec80c8a00 <incomplete sequence \375>, mode=PAGE_CUR_GE, prebuilt=0x7ffec80c97a0, match_mode=1, direction=0)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0sel.cc:6051
4、 About page Of max trx id

We mentioned the secondary index many times above page Of max trx id, This max trx id The actual is PAGE_MAX_TRX_ID, It is located in page Of offset 56 After 8 Bytes , In fact, this value only exists in the secondary index , The primary key does not have this value , We can see the following :

#define PAGE_MAX_TRX_ID  18 /* highest id of a trx which may have modified
    a record on the page; trx_id_t; defined only
    in secondary indexes and in the insert buffer
    tree */

[root@mgr2 test]# ./bcview testimp2.ibd 16 56 8
******************************************************************
This Tool Is Uesed For Find The Data In Binary format(Hexadecimal)
Usage:./bcview file blocksize offset cnt-bytes!                   
fileIs Your File Will To Find Data!                             
blocksizeIs N kb Block.Eg: 8 Is 8 Kb Blocksize(Oracle)!         
                         Eg: 16 Is 16 Kb Blocksize(Innodb)!       
offset:Is Every Block Offset Your Want Start!                                     
cnt-bytes:Is After Offset,How Bytes Your Want Gets!                               
Edtor QQ:22389860!                                                
Used gcc version 4.1.2 20080704 (Red Hat 4.1.2-46)                
******************************************************************
----Current file size is :0.125000 Mb
----Current use set blockszie is 16 Kb
----Current file name is testimp2.ibd
current block:00000000--Offset:00056--cnt bytes:08--data is:0021000000060000
current block:00000001--Offset:00056--cnt bytes:08--data is:0000000000000000
current block:00000002--Offset:00056--cnt bytes:08--data is:0001000000000000
current block:00000003--Offset:00056--cnt bytes:08--data is:0000000000000000( The primary key does not have this value )
current block:00000004--Offset:00056--cnt bytes:08--data is:0000000000016903( Secondary indexes )
current block:00000005--Offset:00056--cnt bytes:08--data is:0000000000016924( Secondary indexes )

This value is updated after each row update , If it is greater than, modify , If it is smaller, it will remain the same . function page_update_max_trx_id There are the following fragments in

if (page_get_max_trx_id(buf_block_get_frame(block)) < trx_id) { // Whether this business is trx id Greater than page Of max trx id
  page_set_max_trx_id(block, page_zip, trx_id, mtr);
 }

Four 、 About the locking phase

What we usually need to lock is DML Statement and select for update Such a statement , Here, locking is divided into two stages: data search and data modification .

  • about select for update:

Primary keys access data : Access the primary key to determine whether there is an implicit lock , Then add the display lock . Secondary index access data ( When you need to go back to the table ): Access the secondary index to determine whether there is an implicit lock , Then add the display lock , Next, the primary key of the table is used to determine whether there is an implicit lock , Then add the display lock .

  • about update/delete:

Primary key access to modify data : Whether there is an implicit primary key search stage , Then add the display lock . The data modification phase involves other secondary indexes , Then maintain the corresponding secondary index and add implicit lock .

Secondary index access to modify data : In the data search phase, the secondary index determines whether there is an implicit lock ( You may need to go back to the table to judge ), Secondary index with display lock , In the data modification stage, the primary key data is modified back to the table, and the display lock is added , Then maintain each secondary index ( The secondary index or primary key involved in modifying the field contains all secondary indexes ) Add an implicit lock .

  • about insert If there's no blockage ( Insert the impression lock and gap lock jam ), So it's always an implicit lock .

Notice here that we see implicit locks , Implicit locks do not occupy row The structure of the body , So in show engine innodb status It's invisible , Unless there are other transaction displays, convert it to display lock . Let's do a few examples as follows (REPEATABLE READ Isolation level ):

 Table structure and data 
mysql> show create table testimp4 \G
*************************** 1. row ***************************
       Table: testimp4
Create Table: CREATE TABLE `testimp4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `d` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`b`),
  KEY `d` (`d`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select *from testimp4;
+------+------+------+------------------------------------+
| id   | a    | b    | d                                  |
+------+------+------+------------------------------------+
|    5 |    5 |  300 | NULL                               |
|    6 | 7000 | 7700 | 1124                               |
|   11 | 7000 | 7700 | 1124                               |
|   12 | 7000 | 7700 | 1124                               |
|   13 | 2900 | 1800 | NULL                               |
|   14 | 2900 | 1800 | NULL                               |
| 1000 |   88 | 1499 | NULL                               |
| 4000 | 6000 | 5904 | iiiafsafasfihhhccccchhhigggofgo111 |
| 4001 | 7000 | 7700 | 1124454555                         |
| 9999 | 9999 | 9999 | a                                  |
+------+------+------+------------------------------------+
10 rows in set (0.00 sec)

4.1 insert data
begin;insert into testimp4 values(10000,10000,10000,'gp');( No submission )

TIME S1 S2 S3 S4
T1 begin;insert into testimp4 values(10000,10000,10000,'gp');( No submission )


T2
select * from testimp4 where id=10000 for update

T3

select * from testimp4 where b=10000 for update
T4


select * from testimp4 where d='a' for update

# T1 moment S1 The lock state :
---TRANSACTION 94487, ACTIVE 5 seclock struct(s), heap size 11600 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736query id 482 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`testimp4` trx id 94487 lock mode IX

# T2 moment S1 The lock state :
---TRANSACTION 94487, ACTIVE 271 sec
2 lock struct(s), heap size 11601 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736query id 484 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`testimp4` trx id 94487 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
Record lockheap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80002710; asc   ' ;;
 1: len 6; hex 000000017117; asc     q ;;
 2: len 7; hex d0000002c40110; asc        ;;
 3: len 4; hex 80002710; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 2; hex 6770; asc gp;;

# T3 moment S1 The lock state :
---TRANSACTION 94487, ACTIVE 337 seclock struct(s), heap size 11602 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736query id 521 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`testimp4` trx id 94487 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
Record lockheap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80002710; asc   ' ;;
 1: len 6; hex 000000017117; asc     q ;;
 2: len 7; hex d0000002c40110; asc        ;;
 3: len 4; hex 80002710; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 2; hex 6770; asc gp;;

RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
Record lockheap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80002710; asc   ' ;;
 1: len 4; hex 80002710; asc   ' ;;

# T4 moment S1 The lock state :
---TRANSACTION 94487, ACTIVE 408 seclock struct(s), heap size 11603 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736query id 559 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`testimp4` trx id 94487 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
Record lockheap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80002710; asc   ' ;;
 1: len 6; hex 000000017117; asc     q ;;
 2: len 7; hex d0000002c40110; asc        ;;
 3: len 4; hex 80002710; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 2; hex 6770; asc gp;;

RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
Record lockheap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80002710; asc   ' ;;
 1: len 4; hex 80002710; asc   ' ;;

RECORD LOCKS space id 501 page no 5 n bits 80 index d of table `test`.`testimp4` trx id 94487 lock_mode X locks rec but not gap
Record lockheap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 2; hex 6770; asc gp;;
 1: len 4; hex 80002710; asc   ' ;;

In fact, we see here insert After the statement, the primary key and each index are locked implicitly, but you can't see , Through others S2,S3,S4 We gradually convert these implicit locks into display locks .

4.2 delete Statement to delete data through the primary key

TIME S1 S2 S3
T1 begin;delete from testimp4 where id=9999;( No submission )

T2
select * from testimp4 where b=9999 for update
T3

select * from testimp4 where d='a' for update;

# T1 moment S1 The lock state :
---TRANSACTION 94493, ACTIVE 3 seclock struct(s), heap size 11601 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736query id 567 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94493 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lockheap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 00000001711d; asc     q ;;
 2: len 7; hex 550000003b071b; asc U   ;  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 8000270f; asc   ' ;;
 5: len 1; hex 61; asc a;;

# T2 moment S1 The lock state :
---TRANSACTION 94493, ACTIVE 112 seclock struct(s), heap size 11603 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736query id 567 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94493 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lockheap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 00000001711d; asc     q ;;
 2: len 7; hex 550000003b071b; asc U   ;  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 8000270f; asc   ' ;;
 5: len 1; hex 61; asc a;;

RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lockheap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 4; hex 8000270f; asc   ' ;;

# T3 moment S1 The lock state :
---TRANSACTION 94493, ACTIVE 133 seclock struct(s), heap size 11603 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736query id 567 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94493 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lockheap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 00000001711d; asc     q ;;
 2: len 7; hex 550000003b071b; asc U   ;  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 8000270f; asc   ' ;;
 5: len 1; hex 61; asc a;;

RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lockheap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 4; hex 8000270f; asc   ' ;;

RECORD LOCKS space id 501 page no 5 n bits 80 index d of table `test`.`testimp4` trx id 94493 lock_mode X locks rec but not gap
Record lockheap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 1; hex 61; asc a;;
 1: len 4; hex 8000270f; asc   ' ;;

In fact, we see here delete After the statement , The primary key has a display lock , This is because the data search phase needs to add a display lock , However, the secondary indexes are implicitly locked due to maintenance , We go through S2,S3 Convert it to show locks .

4.3 delete Statement to delete data through a secondary index

TIME S1 S2
T1 begin;delete from testimp4 where b=9999;( No submission )
T2
select * from testimp4 where d='a' for update

#T1 moment S1 The lock state :
---TRANSACTION 94501, ACTIVE 109 seclock struct(s), heap size 11603 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736query id 576 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94501 lock mode IX
RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94501 lock_mode X
Record lockheap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lockheap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000270f; asc   ' ;;
1: len 4; hex 8000270f; asc   ' ;;

RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94501 lock_mode X locks rec but not gap
Record lockheap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
0: len 4; hex 8000270f; asc   ' ;;
1: len 6; hex 000000017125; asc     q%;;
2: len 7; hex 5a0000002518ea; asc Z   %  ;;
3: len 4; hex 8000270f; asc   ' ;;
4: len 4; hex 8000270f; asc   ' ;;
5: len 1; hex 61; asc a;;

# T2 moment S1 The lock state :
---TRANSACTION 94501, ACTIVE 119 seclock struct(s), heap size 11604 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736query id 576 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94501 lock mode IX
RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94501 lock_mode X
Record lockheap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lockheap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000270f; asc   ' ;;
1: len 4; hex 8000270f; asc   ' ;;

RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94501 lock_mode X locks rec but not gap
Record lockheap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
0: len 4; hex 8000270f; asc   ' ;;
1: len 6; hex 000000017125; asc     q%;;
2: len 7; hex 5a0000002518ea; asc Z   %  ;;
3: len 4; hex 8000270f; asc   ' ;;
4: len 4; hex 8000270f; asc   ' ;;
5: len 1; hex 61; asc a;;

RECORD LOCKS space id 501 page no 5 n bits 80 index d of table `test`.`testimp4` trx id 94501 lock_mode X locks rec but not gap
Record lockheap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 1; hex 61; asc a;;
1: len 4; hex 8000270f; asc   ' ;;

In fact, we see here delete After the statement , The display secondary index is locked , Then the primary key is locked for display , This is because in the data search phase, the secondary index is searched first, and then the primary key of the table is returned , But for secondary indexes d In other words, the implicit lock is added due to maintenance , We go through S2 Convert it to show locks .

4.4 update Statement to modify data through the primary key

Pay special attention here , The update of secondary index is usually deleted and inserted , So here is 2 All rows have implicit locks

TIME S1 S2 S3
T1 begin;update testimp4 set b=10000 where id=9999;( No submission )

T2
select * from testimp4 where b=9999 for update
T3

select * from testimp4 where b=10000 for update

# T1 moment S1 The lock state 
---TRANSACTION 94553, ACTIVE 7 sec
2 lock struct(s), heap size 1160, 1 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 730 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94553 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 000000017159; asc     qY;;
 2: len 7; hex 770000002a187f; asc w   *  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 1; hex 61; asc a;;

# T2 moment S1 The lock state 
---TRANSACTION 94553, ACTIVE 62 sec
3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 730 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94553 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 000000017159; asc     qY;;
 2: len 7; hex 770000002a187f; asc w   *  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 1; hex 61; asc a;;

RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 4; hex 8000270f; asc   ' ;;

# T3 moment S1 The lock state 
---TRANSACTION 94553, ACTIVE 128 sec
3 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 140737089492736, query id 730 localhost root
TABLE LOCK table `test`.`testimp4` trx id 94553 lock mode IX
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 000000017159; asc     qY;;
 2: len 7; hex 770000002a187f; asc w   *  ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 1; hex 61; asc a;;

RECORD LOCKS space id 501 page no 4 n bits 80 index b of table `test`.`testimp4` trx id 94553 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80002710; asc   ' ;;
 1: len 4; hex 8000270f; asc   ' ;;
Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 4; hex 8000270f; asc   ' ;;

Here, because of the secondary index to the table b It has been modified through the primary key , So the secondary index contains 2 Data , One is marked with del flag, The other is inserted as follows :

(11) normal record offset:266 heapno:12 n_owned 0,delflag:Y minflag:0 rectype:0
(12) normal record offset:126 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
(13) SUPREMUM record offset:112 heapno:1 n_owned 8,delflag:N minflag:0 rectype:3

So both lines are implicitly locked , This is due to secondary index maintenance , It is worth noting that the secondary index d Can't lock implicitly , because update The modification of the statement does not involve d Column index , So it won't maintain . If you inquire d The values in the column (for update), Will get d The lock on the column succeeded , And then it's blocked in the primary key id Up and down :

---TRANSACTION 94565, ACTIVE 4 sec starting index read
mysql tables in use 1locked 1
LOCK WAIT 3 lock struct(s), heap size 11602 row lock(s)
MySQL thread id 16, OS thread handle 140737086228224query id 748 localhost root Sending data
select * from testimp4 where d='a' for update
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94565 lock_mode X locks rec but not gap waiting
Record lockheap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 000000017161; asc     qa;;
 2: len 7; hex 7c0000002d25eb; asc |   -% ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 1; hex 61; asc a;;

------------------
TABLE LOCK table `test`.`testimp4` trx id 94565 lock mode IX
RECORD LOCKS space id 501 page no 5 n bits 80 index d of table `test`.`testimp4` trx id 94565 lock_mode X
Record lockheap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 61; asc a;;
 1: len 4; hex 8000270f; asc   ' ;;

RECORD LOCKS space id 501 page no 3 n bits 80 index PRIMARY of table `test`.`testimp4` trx id 94565 lock_mode X locks rec but not gap waiting
Record lockheap no 12 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8000270f; asc   ' ;;
 1: len 6; hex 000000017161; asc     qa;;
 2: len 7; hex 7c0000002d25eb; asc |   -% ;;
 3: len 4; hex 8000270f; asc   ' ;;
 4: len 4; hex 80002710; asc   ' ;;
 5: len 1; hex 61; asc a;;

There are a lot of things not to be listed one by one ,Innodb Row lock has always been a headache .

5、 ... and 、 On the judgment of lock

5.1 lock_sec_rec_read_check_and_lock function

It is mainly used to add display lock at the stage of searching data segment of secondary index ,, about update/delete for , The first is to find the data that needs to be modified , Before locking, it is necessary to determine whether there is an implicit lock in this record , Because the secondary index row data does not contain trx id, So first use page Of max trx id Compare with the currently active minimum read / write transaction , If it is greater than or equal to, there may be a display lock , Then we need to go back to the table and make fine judgment through the primary key . The refined back table determines whether there is an implicit lock in the row , Then the price is higher , So this requires a judgment process as follows

lock_sec_rec_read_check_and_lock:
                                
 if ((page_get_max_trx_id(block->frame) >= trx_rw_min_trx_id()
      || recv_recovery_is_on())
     && !page_rec_is_supremum(rec)) {

  lock_rec_convert_impl_to_expl(block, rec, index, offsets);// If you meet the above conditions, you can transfer in  lock_rec_convert_impl_to_expl
 } 

Call in as follows :

  ->lock_rec_convert_impl_to_expl
    ->lock_sec_rec_some_has_impl
      ->row_vers_impl_x_locked   Here we will return the clustered index back to the table , It is also located and returned through the secondary index btr_cur_search_to_nth_level
        ->row_vers_impl_x_locked_low  In the end  row_vers_impl_x_locked_low Function to make the core judgment  

The stack is as follows :

#0  row_vers_impl_x_locked_low (clust_rec=0x7fff39a21226 "\200", clust_index=0x7ffeb5092680, rec=0x7fff39a2ac30 "\200", index=0x7ffeb5093610, offsets=0x7fffe8461730, mtr=0x7fffe8460e90)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0vers.cc:101
#1  0x0000000001b2c84e in row_vers_impl_x_locked (rec=0x7fff39a2ac30 "\200", index=0x7ffeb5093610, offsets=0x7fffe8461730)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0vers.cc:390
#2  0x00000000019e8448 in lock_sec_rec_some_has_impl (rec=0x7fff39a2ac30 "\200", index=0x7ffeb5093610, offsets=0x7fffe8461730)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/lock/lock0lock.cc:1276
#3  0x00000000019f339a in lock_rec_convert_impl_to_expl (block=0x7fff38d94ca0, rec=0x7fff39a2ac30 "\200", index=0x7ffeb5093610, offsets=0x7fffe8461730)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/lock/lock0lock.cc:6124
#4  0x00000000019f3dd2 in lock_sec_rec_read_check_and_lock (flags=0, block=0x7fff38d94ca0, rec=0x7fff39a2ac30 "\200", index=0x7ffeb5093610, offsets=0x7fffe8461730, mode=LOCK_X, 
    gap_mode=1024, thr=0x7ffeb4c89358) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/lock/lock0lock.cc:6357
#5  0x0000000001af7271 in sel_set_rec_lock (pcur=0x7ffeb4c887d8, rec=0x7fff39a2ac30 "\200", index=0x7ffeb5093610, offsets=0x7fffe8461730, mode=3, type=1024, thr=0x7ffeb4c89358, 
    mtr=0x7fffe8461a50) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0sel.cc:1278
#6  0x0000000001b00049 in row_search_mvcc (buf=0x7ffeb4977070 "\370\211\037", mode=PAGE_CUR_GE, prebuilt=0x7ffeb4c885b0, match_mode=1, direction=1)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0sel.cc:5710

But here's the thing ,max trx id It will only be updated on the secondary index , And every time you update a line, you'll update , Then a problem is that if records on the same secondary index are deleted several times in succession **(delete from testimp4 where b=7700;), Except for the first time row_vers_impl_x_locked_low This function , Because this is to query, lock, modify, and modify a row ( Every line will be modified page Of max trx id) Of . however update It's different ,update If you modify the value of this secondary index, you will generally enter ( Such as :update testimp4 set b=1500 where b=1800;)Searching rows for update state **, First create a temporary file to store the line records that need to be changed , Then make batch changes to enter updating state , Then there will be no such problem , Because this is a judgment in the data search phase , Not the data modification phase . And such as **( Such as :update testimp2 set c='a' where b=1800) Such statements don't trigger either , This is because b The row records of the index have not changed , So it won't change b Indexes page Of max trx id. therefore update It's a good way to avoid this problem and not enter the function frequently row_vers_impl_x_locked_low** To judge , however delete But not .

About row_vers_impl_x_locked_low Function to determine whether there is an implicit lock in the secondary index , It's quite complicated and can be divided into many situations , No more description . So the problem we saw at the beginning , This process has entered row_vers_impl_x_locked_low function , So we can judge this delete More than one line may have been updated ( But judging from the number of lines of code, this is not the case ), Or it is possible that this statement transaction has modified other statements of the record , Fine judgment is needed .

5.2 lock_sec_rec_modify_check_and_lock

It is mainly used to add implicit lock in data modification stage , Secondary index due to row data modification (update Modify the primary key that contains segment value or tail of this secondary index word ) And passive maintenance locks . Notice if it is select for update where The condition is that the primary key will not determine whether the secondary index contains an implied lock , If there is a conflict, it will block the primary key .

5.3 lock_clust_rec_read_check_and_lock

Add display lock in data search phase , It is mainly used to add display lock to primary key lookup data or back table primary key after secondary index access , Before locking, it is necessary to determine whether there is an implicit lock . Because the primary key row contains trx id Pseudo column , So you can simply use the line trx id Whether or not the transaction of is still active , The cost of this process is very small , So there's always this process of locking every row , It's called every time lock_rec_convert_impl_to_expl Function to judge , as follows :

lock_clust_rec_read_check_and_lock
 ->lock_rec_convert_impl_to_expl
   ->lock_clust_rec_some_has_impl ( The primary key judgment is very simple )

The stack is as follows :

#0  lock_clust_rec_some_has_impl (rec=0x7fff05ad40db "\200", index=0x7ffe8802ce70, offsets=0x7fffe8461660)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/include/lock0priv.ic:69
#1  0x00000000019f3333 in lock_rec_convert_impl_to_expl (block=0x7fff050a0950, rec=0x7fff05ad40db "\200", index=0x7ffe8802ce70, offsets=0x7fffe8461660)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/lock/lock0lock.cc:6118
#2  0x00000000019f418d in lock_clust_rec_read_check_and_lock (flags=0, block=0x7fff050a0950, rec=0x7fff05ad40db "\200", index=0x7ffe8802ce70, offsets=0x7fffe8461660, mode=LOCK_X, 
    gap_mode=1024, thr=0x7ffeb49903c8) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/lock/lock0lock.cc:6430
#3  0x0000000001af7193 in sel_set_rec_lock (pcur=0x7ffeb498fe38, rec=0x7fff05ad40db "\200", index=0x7ffe8802ce70, offsets=0x7fffe8461660, mode=3, type=1024, thr=0x7ffeb49903c8, 
    mtr=0x7fffe8461980) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0sel.cc:1263
#4  0x0000000001b00049 in row_search_mvcc (buf=0x7ffeb498f380 "\371\005", mode=PAGE_CUR_GE, prebuilt=0x7ffeb498fc10, match_mode=1, direction=0)
    at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/row/row0sel.cc:5710
5.4 lock_clust_rec_modify_check_and_lock

Implicit lock is added in the modification phase of primary key data , At present, it is found that in direct update Primary key value or delete During operation , However, in this case, the primary key has already added a display lock in the data query phase .

6、 ... and 、update Not exactly equal to delete&&insert

The direct distinction is as follows :

  • Primary key update , Interface row_upd_clust_step

row_upd_changes_ord_field_binary  Determine whether the value of the clustered index has been updated 
 If it's updated 
  -> row_upd_clust_rec_by_insert  Delete and insert the primary key ( Set up del flag)
 If there is no update 
  ->row_upd_clust_rec
    ->btr_cur_optimistic_update  Just think about optimism update
      ->row_upd_changes_field_size_or_external  Determine whether the new record exceeds the existing size of the line 
       If not 
        ->btr_cur_update_in_place  Update in place  
       If it is 
        ->page_cur_delete_rec  You need to delete the primary key ( Actually delete non settings del falg)
        ->btr_cur_insert_if_possible  Insert 
  • Secondary index update , Interface row_upd_sec_step Always insert for deletion ( Set up del flag)

7、 ... and 、 About History list length The unit of

actually History list length To be one update undo log ( Not insert) The counter of , There is only one transaction undo log . The source is trx_sys->rseg_history_len, This value is updated when the transaction is committed , Regardless of transaction size . But because of a lot of internal affairs , This value will be much larger than the number of observable transactions . The stack is as follows :

#0  trx_purge_add_update_undo_to_history (trx=0x7fffeac7df50, undo_ptr=0x7fffeac7e370, undo_page=0x7fff2837c000 "\373\252\223T", update_rseg_history_len=true, n_added_logs=1, 
    mtr=0x7fffe8399830) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/trx/trx0purge.cc:354
#1  0x0000000001b9c064 in trx_undo_update_cleanup (trx=0x7fffeac7df50, undo_ptr=0x7fffeac7e370, undo_page=0x7fff2837c000 "\373\252\223T", update_rseg_history_len=true, n_added_logs=1, 
    mtr=0x7fffe8399830) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/trx/trx0undo.cc:1970
#2  0x0000000001b8b639 in trx_write_serialisation_history (trx=0x7fffeac7df50, mtr=0x7fffe8399830) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/trx/trx0trx.cc:1684
#3  0x0000000001b8c9b0 in trx_commit_low (trx=0x7fffeac7df50, mtr=0x7fffe8399830) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/trx/trx0trx.cc:2184
Come here , Miscellaneous records a lot of , Record here for future use .

appendix 1 The function interface

1、read view
  • MVCC::view_open: establish read view

  • ReadView::prepare: Get ready read view The value in

  • ReadView::complete: write in read view The value in

  • MVCC::view_close: Release read view

2、 Visibility judgment
  • lock_clust_rec_cons_read_sees: Primary key visibility judgment

  • lock_sec_rec_cons_read_sees: Second level index visibility judgment

appendix 2 Specific functions

1、read view
 /** The read should not see any transaction with trx id >= this
 value. In other words, this is the "high water mark". */
 trx_id_t m_low_limit_id; 

 /** The read should see all trx ids which are strictly
 smaller (<) than this value.  In other words, this is the
 low water mark". */
 trx_id_t m_up_limit_id;

 /** trx id of creating transaction, set to TRX_ID_MAX for free
 views. */
 trx_id_t m_creator_trx_id;

 /** Set of RW transactions that was active when this snapshot
 was taken */
 ids_t  m_ids;

 /** The view does not need to see the undo logs for transactions
 whose transaction number is strictly smaller (<) than this value:
 they can be removed in purge if not needed by other views */
 trx_id_t m_low_limit_no;

void
ReadView::prepare(trx_id_t id)
{
 ut_ad(!m_cloned);
 ut_ad(mutex_own(&trx_sys->mutex));

 m_creator_trx_id = id;

 m_low_limit_no = m_low_limit_id = trx_sys->max_trx_id;

 if (!trx_sys->rw_trx_ids.empty()) {
  copy_trx_ids(trx_sys->rw_trx_ids);
 } else {
  m_ids.clear();
 }

 if (UT_LIST_GET_LEN(trx_sys->serialisation_list) > 0) {
  const trx_t* trx;

  trx = UT_LIST_GET_FIRST(trx_sys->serialisation_list);

  if (trx->no < m_low_limit_no) {
   m_low_limit_no = trx->no;
  }
 }
}

void
ReadView::complete()
{
 ut_ad(!m_cloned);
 /* The first active transaction has the smallest id. */
 m_up_limit_id = !m_ids.empty() ? m_ids.front() : m_low_limit_id;

 ut_ad(m_up_limit_id <= m_low_limit_id);

 m_closed = false;
}

2、 Visibility judgment

 The secondary index returns to the table to determine the visibility 

Row_sel_get_clust_rec_for_mysql::operator()
 ->lock_clust_rec_cons_read_sees ( After returning to the table, judge its visibility according to the primary key )
 ->row_sel_build_prev_vers_for_mysql( Pre build version )
  ->row_vers_build_for_consistent_read
     This function loop builds , Until the conditions are met , Or the previous version was NULL
        if (view->changes_visible(trx_id, index->table->name)) {

            /* The view already sees this version: we can copy
           it to in_heap and return */
            
           buf = static_cast<byte*>(
            mem_heap_alloc(
             in_heap, rec_offs_size(*offsets)));
            
           *old_vers = rec_copy(buf, prev_version, *offsets);
           rec_offs_make_valid(*old_vers, index, *offsets);
            
           if (vrow && *vrow) {
            *vrow = dtuple_copy(*vrow, in_heap);
            dtuple_dup_v_fld(*vrow, in_heap);
           }
           break;

Finally, the primary key value of the previous version will be returned to MySQL layer

About using index Also need to return to the table process

row_search_mvcc:
if (!srv_read_only_mode
       && !lock_sec_rec_cons_read_sees( //  If the secondary index record is judged to be invisible 
     rec, index, trx->read_view)) {
    /* We should look at the clustered index.
    However, as this is a non-locking read,
    we can skip the clustered index lookup if
    the condition does not match the secondary
    index entry. */
    switch (row_search_idx_cond_check(
      buf, prebuilt, rec, offsets)) {
    case ICP_NO_MATCH:
     goto next_rec;
    case ICP_OUT_OF_RANGE:
     err = DB_RECORD_NOT_FOUND;
     goto idx_cond_failed;
    case ICP_MATCH:
     goto requires_clust_rec; // Go here to enter the back table judgment process 
    }


lock_sec_rec_cons_read_sees:
trx_id_t max_trx_id = page_get_max_trx_id(page_align(rec));// Get the page's max trx id
 ut_ad(max_trx_id > 0);
return(view->sees(max_trx_id));

The full text after .

Enjoy MySQL :)

Mr. Ye's 「MySQL Core optimization 」 The big class has been upgraded to MySQL 8.0, Scan the code to open MySQL 8.0 A journey of practice

版权声明
本文为[I don't know.]所创,转载请带上原文链接,感谢

Scroll to Top