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

Original: the full text brings a lot of self-awareness and understanding, may be wrong, because the level is limited, but on behalf of my efforts to analyze.

I. asking questions

The question was raised by Master Jiang and the questions are as follows:

Table:

Mysql > show create table c\ G

* * 1. Row *

Table: c

Create Table: CREATE TABLE `c` (

`a`int (11) NOT NULL AUTO_INCREMENT

`b` int (11) DEFAULT NULL

PRIMARY KEY (`a`)

UNIQUE KEY `b` (`b`)

) ENGINE=InnoDB

1 row in set (0.01 sec)

Open two sessions and execute continuously

Replace into c values (NULL,1)

It triggers a deadlock. Ask the cause of the deadlock trigger.

The environment I use:

MYSQL 5.7.14 debug version, isolation level RR, auto-commit, it is obvious that the record that can be select out of the c table here is always 1

It's just that column a continues to grow, but there is more than one item of actual storage space here, because from heap no's point of view, in the secondary index, heap no has already arrived.

7, that is, there are at least 7 (7-1) records, except that the other records are marked as del and placed in the page free_list by the purge thread.

II. Preparatory work and usage

1. Slightly modified the print part of the source code about the lock. We know that the lock memory structure lock is displayed under everything.

The struct will be connected into a linked list, and the memory lock struct will be printed as long as the memory is printed in order.

All information about this thing shows all the information about the lock and the locking order is as follows:

Click (here) to collapse or open

-TRANSACTION 184771, ACTIVE 45 sec

4 lock struct (s), heap size 1160, 3 row lock (s)

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

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

TABLE LOCK table `test`.`c4` trx id 184771 lock mode IX

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

RECORD LOCKS space id 413 page no 4 n bits 72 index id2 of table `test`.`c4` trx id 184771 lock_mode X (LOCK_X)

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

0: len 4; hex 80000014; asc

1: len 4; hex 80000014; asc

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

RECORD LOCKS space id 413 page no 3 n bits 72 index PRIMARY of table `test`.`c4` trx id 184771 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP)

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

0: len 4; hex 80000014; asc

1: len 6; hex 00000002d1bd; asc

2: len 7; hex a600000e230110; asc #

3: len 4; hex 80000014; asc

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

RECORD LOCKS space id 413 page no 4 n bits 72 index id2 of table `test`.`c4` trx id 184771 lock_mode X (LOCK_X) locks gap before rec (LOCK_GAP)

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

0: len 4; hex 8000001e; asc

1: len 4; hex 8000001e; asc

The normal version is only

Click (here) to collapse or open

-TRANSACTION 184771, ACTIVE 45 sec

4 lock struct (s), heap size 1160, 3 row lock (s)

MySQL thread id 2, OS thread handle 140737154311936, query id 642 localhost root cleaning up are all added by me, in fact, the modification is very simple, innodb actually wrote it but did not open it, I opened it with a serial number to indicate the order.

Above is a select * from c where id2= 20 for update; b listed as a secondary index of all 4 lock struct (s), you can see that with this information analysis

It's not that hard.

Here's a little analysis.

The structure of the table is:

Mysql > show create table c4

+- -- +

| | Table | Create Table |

+- -- +

| | c4 | CREATE TABLE `c4` (

`id1` int (11) NOT NULL

`id2` int (11) DEFAULT NULL

PRIMARY KEY (`id1`)

KEY `id2` (`id2`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+- -- +

1 row in set (0.00 sec)

The data are:

Mysql > select * from c4

+-+ +

| | id1 | id2 |

+-+ +

| | 1 | 1 |

| | 10 | 10 |

| | 20 | 20 |

| | 30 | 30 |

+-+ +

4 rows in set (0.00 sec)

The statement is:

Select * from c where id2= 20 for update

RR mode

From the lock structure linked list, this statement is locked in the secondary index

Id2:20 id1:20 LOCK_X | LOCK_ORDINARY is NEXT KEY LOCK

Locked at the same time.

Id2:30 id1:30 LOCK_X | LOCK_GAP, that is, gap lock, does not contain this column

So drawing a picture is easy to understand that the yellow part is the locked part:

Is it clear at a glance? If it's rc, then only two yellow arrows are recorded.

It means that without gap, there will be no drawing.

2. Call this print function to print to the err log file before deadlock detection rollback, and the display memory lock struct for printing all the things is as follows, here

No, there will be an output of the deadlock structure triggered by replace

3. Use MYSQL TRACE SQL statement to get most of the function calls to analyze the process of replace

Modification of the problem: modify the source code to print out all lock struct online is obviously not available. Because when printed out, show engine innodb status will be very

Long, and even cause other problems, but the test is OK, then modified the print deadlock transaction chain list to the log, you can print every time you encounter deadlock information

To the log, but every time the MYSQLD will hang up, but does not affect the analysis.

III. Preparatory knowledge (self-understanding)

1 、

Precise modes:

# define LOCK_ORDINARY 0 / *!

< this flag denotes an ordinary next-key lock in contrast to LOCK_GAP or LOCK_REC_NOT_GAP */ 默认是LOCK_ORDINARY及普通的next_key_lock,锁住行及以前的间隙 #define LOCK_GAP 512 /*!< when this bit is set, it means that the lock holds only on the gap before the record; for instance, an x-lock on the gap does not give permission to modify the record on which the bit is set; locks of this type are created when records are removed from the index chain of records */ 间隙锁，锁住行以前的间隙，不锁住本行 #define LOCK_REC_NOT_GAP 1024 /*!< this bit means that the lock is only on the index record and does NOT block inserts to the gap before the index record; this is used in the case when we retrieve a record with a unique key, and is also used in locking plain SELECTs (not part of UPDATE or DELETE) when the user has set the READ COMMITTED isolation level */ 行锁，锁住行而不锁住任何间隙 #define LOCK_INSERT_INTENTION 2048 /*!< this bit is set when we place a waiting gap type record lock request in order to let an insert of an index record to wait until there are no conflicting locks by other transactions on the gap; note that this flag remains set when the waiting lock is granted, or if the lock is inherited record */ 插入意向锁，如果插入的记录在某个已经锁定的间隙内为这个锁 2、参数innodb_autoinc_lock_mode的值为1，也许不能保证replace into的顺序。 3、infimum和supremum 一个page中包含这两个伪列，页中所有的行未删除(删除未purge)的行都连接到这两个虚列之间，其中 supremum伪列的锁始终为next_key_lock。 4、heap no 此行在page中的heap no heap no存储在fixed_extrasize 中，heap no 为物理存储填充的序号，页的空闲空间挂载在page free链表中(头插法)可以重用， 但是重用此heap no不变，如果一直是insert 则heap no 不断增加，并非按照KEY大小排序的逻辑链表顺序，而是物理填充顺序 5、n bits 和这个page相关的锁位图的大小如果我的表有9条数据 还包含2个infimum和supremum虚拟列 及 64+11 bits，及75bits但是必须被8整除为一个字节就是 80 bits 6、隐含锁(Implicit lock)和显示锁(explict) 锁有隐含和显示之分。隐含锁通常发生在 insert 的时候对cluster index和second index 都加隐含锁，如果是UPDATE(DELETE)对cluster index加显示锁 辅助 索引加隐含锁。目的在于减少锁结构的内存开销，如果有事务需要和这个隐含锁而不兼容，这个事务需要帮助 insert或者update(delete)事物将隐含 锁变为显示锁，然后给自己加锁，通常insert主键检查会给自己加上S锁，REPLACE、delete、update通常会给自己加上X锁。 四、replace过程分析 通过replace的trace找到了这些步骤的大概调用： 首先我们假设 TRX1：replace 不提交 TRX2：replace 堵塞 TRX1：replace 提交 TRX2：replace 继续执行直到完成 这样做的目的在于通过trace找到TRX2在哪里等待，确实如我所愿我找到了。 1、检查是否冲突，插入主键 点击(此处)折叠或打开 569 T@4: | | | | | | | | >

Row_ins

570 Thum4: | row_ins: table: test/c

571 Troup4: | > row_ins_index_entry_step

572Thum4: | > row_ins_clust_index_entry

573Thum4: | > row_ins_clust_index_entry_low

574Thum4: | > btr_cur_search_to_nth_level

| 575 Toner 4: | btr_cur_search_to_nth_level |

586 Troup4: | btr_cur_search_to_nth_level

588 Troup4: | row_vers_impl_x_locked_low

590 Thum4: | info: Implicit lock is held by trx:183803

591 Troup4: | thd_report_row_lock_wait

| 593Thum4: | btr_cur_search_to_nth_level |

| 607 Toner 4: | innobase_trx_init |

644 Troup4: | row_ins_clust_index_entry

697 Troup4: | > row_ins_clust_index_entry_low

698 Troup4: | > btr_cur_search_to_nth_level

| 699 Toner 4: | btr_cur_search_to_nth_level |

711 Troup4: | btr_cur_search_to_nth_level

713 Troup4: | row_vers_impl_x_locked_low

715 Troup4: | btr_cur_search_to_nth_level

717 Troup4: | row_vers_impl_x_locked_low

719 Troup4: | | info: Implicit lock is held by trx:183808

720 Thum4: |

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.