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

This article introduces the relevant knowledge of "expanding the scope of UPDATE locks in a RR mode in mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. Preface

Here we only study the locked mode, borrowing the tables and sentences of Mr. Ye.

Mysql > select * from T1 +-- + | C1 | c2 | c3 | c4 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 3 | 3 | 0 | 4 | 2 | 0 | 6 | 8 | 5 | 0 | 7 | 6 | 6 | 10 | 10 | 4 | 0 | + | -+ CREATE TABLE `t1` (`c1` int (10) unsigned NOT NULL DEFAULT' `c2` int (10) unsigned NOT NULL DEFAULT '0locks, `c3` int (10) unsigned NOT NULL DEFAULT' 0locks, `c4` int (10) unsigned NOT NULL DEFAULT '0locks, PRIMARY KEY (`c1`), KEY `c2` (`c2`) lock modes in ENGINE=InnoDB DEFAULT CHARSET=utf8 II and RR modes

Let's first take a look at the execution plan of the following two statements

Mysql > desc update T1 set c4x123 where c2 > = 8 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | UPDATE | T1 | NULL | range | c2 | c2 | 4 | const | 2 | 100.00 | Using where Using temporary | +-+- -+ mysql > desc update T1 set c4x123 where c2 > = 6 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | UPDATE | T1 | NULL | index | c2 | PRIMARY | 4 | NULL | 7 | 100.00 | Using where Using temporary | +-+- -- +

The execution plans of the following two statements are inconsistent, the main note

Type:index and range

Key:PRIMARY and c2

We need to know the difference between type:index and range first.

Here is an article I wrote before.

Http://blog.itpub.net/7728585/viewspace-2139010/

Type:index does not use the index B + tree structure, but only uses the index leaf node linked list structure to scan. We know that there is a two-way pointer between the leaf nodes in the index.

And the data of leaf nodes are sorted. Its approach is similar to that of ALL, and its access efficiency is not high. Its main application scenario is to avoid order by using using filesort.

That is, to avoid sorting. It is a way to access data, just like range, const, ref, eq_ref, and so on.

Type:range is obviously used for range queries such as > between. Its access method takes into account the B + tree structure of the index, and needs to be accessed sequentially through the root node-> branch node-- > leaf node.

In fact, const, ref, eq_ref and so on also need such a positioning process.

I'll probably draw a picture, just a diagram, but it's enough to explain what I mean.

1.jpg

Then we need to consider the structure of the following statements in RR mode:

Mysql > desc update T1 set c4x123 where c2 > = 6 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | UPDATE | T1 | NULL | index | c2 | PRIMARY | 4 | NULL | 7 | 100.00 | Using where Using temporary | +-+- -- + RECORD LOCKS space id 532 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 348084 lock_mode X (LOCK_X) Record lock Heap no 1 PHYSICAL RECORD: n_fields 1 Compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 00: len 4; hex 00000000; asc; 1: len 6; hex 0000054abd; asc J;; 2: len 7; hex ba00000e180110; asc; 3: len 4; hex 00000000; asc; 4: len 4; hex 00000000; asc; 5: len 4; hex 00000000; asc Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 00: len 4; hex 00000001; asc; 1: len 6; hex 0000054abd; asc J;; 2: len 7; hex ba00000e18011d; asc; 3: len 4; hex 00000001; asc; 4: len 4; hex 00000001; asc; 5: len 4; hex 00000000; asc; Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 00: len 4; hex 000003 Asc;; 1: len 6; hex 0000054abd; asc J; 2: len 7; hex ba00000e18012a; asc *; 3: len 4; hex 00000003; asc; 4: len 4; hex 00000003; asc;; 5: len 4; hex 0000000; asc;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 00: len 4; hex 000004; asc;; 1: len 6; hex 0000054abd; asc J;; 2: len 7; hex ba00000e180137 Asc 7; 3: len 4; hex 00000002; asc; 4: len 4; hex 00000002; asc; 5: len 4; hex 00000002; asc; Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 00: len 4; hex 000006; asc; 1: len 6; hex 000000054fb4; asc O; 2: len 7; hex 3300000c430b49; asc 3 C Iscape; 3: len 4; hex 000008; asc; 4: len 4; hex 000005 Asc;; 5: len 4; hex 0000007b; asc {;; Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 00: len 4; hex 00000007; asc; 1: len 6; hex 0000054fb4; asc O; 2: len 7; hex 3300000c430b6b; asc 3 C KTX; 3: len 4; hex 00000006; asc; 4: len 4; hex 00000006; asc; 5: len 4; hex 0000007b; asc { Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 00: len 4; hex 00000000a; asc; 1: len 6; hex 000000054fb4; asc O; 2: len 7; hex 3300000c430b8d; asc 3 C; 3: len 4; hex 0000000a; asc; 4: len 4; hex 00000004; asc; 5: len 4; hex 00007b; asc {

Let's not consider the table-level intention lock here, but only consider the lock structure printed here.

The row lock is: lock_mode X (LOCK_X) | LOCK_ORDINARY (next key lock)

At the same time, we noticed that 0: len 8; hex 73757072656d756d; asc supremum

So let's show it with a picture.

2.jpg

In fact, we can see from the figure that in RR mode, all the lines on the primary key are added with NEXT_KEY LOCK, so any other DML operation you do will lock.

What about the lock structure of the following statement?

Mysql > desc update T1 set c4x123 where c2 > = 8 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | UPDATE | T1 | NULL | range | c2 | c2 | 4 | const | 2 | 100.00 | Using where Using temporary | +-+- -+ 1 row in set (0.01 sec)

As follows:

-TRX NO:348661 LOCK STRUCT (1) (Add by gaopeng) TABLE LOCK table `test`.`t1` trx id 348661 lock mode IX-TRX NO:348661 LOCK STRUCT (1) (Add by gaopeng) RECORD LOCKS space id 532 page no 4 n bits 80 index c2 of table `test`.`t1` trx id 348661 lock_mode X (LOCK_X) Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; Info bits 00: len 4; hex 00000008; asc; 1: len 4; hex 00000006; asc; Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 0000000a; asc;; 1: len 4; hex 00000a; asc -TRX NO:348661 LOCK STRUCT (1) (Add by gaopeng) RECORD LOCKS space id 532 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 348661 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP) Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 00: len 4; hex 00000006; asc; 1: len 6; hex 00000551f5; asc Q; 2: len 7; hex 71000002700ad1; asc q p; 3: len 4 Hex 00000008; asc;; 4: len 4; hex 00000005; asc; 5: len 4; hex 0000007b; asc {;; Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 00: len 4; hex 00000a; asc;; 1: len 6; hex 0000000551f5; asc Q; 2: len 7; hex 71000002700af3; asc q p; 3: len 4; hex 00000000a; asc; 4: len 4; hex 00000004; asc; 5: len 4; hex 0000007b Asc {

We can clearly observe that INDEX c2 contains

Lock_mode X (LOCK_X) | LOCK_ORDINARY (next key lock)

Its line contains both C2:8/C1:6 C2:10/C2:10 and supremum

Also passed to the primary key PRIMARY lock structure is

Lock_mode X (LOCK_X) | rec but not gap (LOCK_REC_NOT_GAP)

That is, the primary key only locks the two lines of C1 6 C1VR 10, and it is not gap lock. If you need to draw a picture, it is as follows:

3.jpg

We can find that the scope of locking is much smaller, as shown in the following statement:

Select * from T1 where C1 = 7 for update

(I don't know if the c2o7 written by Mr. Ye here is wrong.)

It can be done because it does not fall within the lock range of PRIMARY.

Third, the locking mode in RC mode

Here is just a look at the locking structure of the RC mode as follows:

Mysql > desc update T1 set c4x123 where c2 > = 6 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | UPDATE | T1 | NULL | index | c2 | PRIMARY | 4 | NULL | 7 | 100.00 | Using where Using temporary | +-+- -- + 1 row in set (0.22 sec)-TRX NO:348596 LOCK STRUCT (1) (Add by gaopeng) RECORD LOCKS space id 532 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 348596 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP) Record lock Heap no 6 PHYSICAL RECORD: n_fields 6 Compact format; info bits 00: len 4; hex 00000006; asc; 1: len 6; hex 00000551b4; asc Q; 2: len 7; hex 3300000c430c03; asc 3 C; 3: len 4; hex 00000008; asc; 4: len 4; hex 00000005; asc; 5: len 4; hex 0000007b; asc {; Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 00: len 4; hex 00000007; asc; 1: len 6; hex 0000000551b4 Asc Q;; 2: len 7; hex 3300000c430c25; asc 3 C%; 3: len 4; hex 000006; asc; 4: len 4; hex 00000006; asc; 5: len 4; hex 0000007b; asc {; Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 00: len 4; hex 00000a; asc; 1: len 6; hex 00000000551b4; asc Q; 2: len 7; hex 3300000c430c47; asc 3 C Gpassport; 3: len 4; hex 00000a Asc;; 4: len 4; hex 00000004; asc; 5: len 4; hex 0000007b; asc {

We can clearly see that in RC mode, the corresponding line of PRIMARY is locked without considering the implicit lock:

Lock_mode X (LOCK_X) locks | rec but not gap (LOCK_REC_NOT_GAP)

Notice here, NOT GAP.

This is the end of the case study on the expansion of the scope of UPDATE locks in a RR mode in mysql. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.