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

This article mainly introduces

Introduction to three kinds of row locks in 1.mysql

Why can next-key lock solve the problem of phantom reading in 2.RR mode

First, create a table:

Mysql > show create table test01\ G

* * 1. Row *

Table: test01

Create Table: CREATE TABLE `test01` (

`c1` bigint (20) NOT NULL AUTO_INCREMENT

`c2` int (11) DEFAULT NULL

`c3` varchar (20) DEFAULT NULL

PRIMARY KEY (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Initialization data:

Mysql > select * from test01

+-- +

| | C1 | c2 | c3 |

+-- +

| | 1 | 1 | a |

| | 2 | 2 | b | |

| | 3 | 3 | c | |

| | 4 | 4 | d | |

| | 5 | 5 | e | |

+-- +

5 rows in set (0.00 sec)

Record lock:

A lock added to the data row itself.

Session A

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select C1 from test01 where C1 for update

+-- +

| | C1 |

+-- +

| | 1 |

+-- +

1 row in set (0.00 sec)

Session B

Mysql > update test01 set C1 / 6 where C1 / 1;-- hang

Session B cannot be modified because there is a record lock on c1room1.

Mysql > SHOW ENGINE INNODB STATUS\ G

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

RECORD LOCKS space id 24 page no 3 n bits 72 index PRIMARY of table `ming`.`test01` trx id 1807 lock_mode X locks rec but not gap waiting

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

0: len 8; hex 8000000000000001; asc

1: len 6; hex 000000000708; asc

2: len 7; hex a80000011c0110; asc

3: len 4; hex 80000001; asc

4: len 1; hex 61; asc a

Gap lock:

A lock added to the gap, excluding the record itself.

Need to create a non-unique index, can not be a primary key or unique index, otherwise only record lock.

Mysql > create index idx_test01_c2 on test01 (c2)

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

Initialization data:

Mysql > select * from test01

+-- +

| | C1 | c2 | c3 |

+-- +

| | 1 | 1 | a |

| | 3 | 3 | c | |

| | 5 | 5 | e | |

| | 7 | 7 | e | |

+-- +

4 rows in set (0.00 sec)

Session A:

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select c2 from test01 where c2 > 3 and c2 insert into test01 select 4 hang

The gap between (3) and (5) is locked, so it cannot be inserted (4).

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

RECORD LOCKS space id 24 page no 4 n bits 72 index idx_test01_c2 of table `ming`.`test01` trx id 1925 lock_mode X locks gap before rec insert intention waiting

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

0: len 4; hex 80000005; asc

1: len 8; hex 8000000000000005; asc

At this point, the c2x3 and c2q5 rows of data can be changed, but the changed values cannot be in the current gap. For the gap of data rows, you can refer to the following example:, next-key lock

Locking both the data record itself and the gap is equivalent to record lock + gap lock.

Column c3 is useless, delete

Mysql > alter table test01 drop column c3

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

Initialization data:

Mysql > select * from test01

+-+ +

| | C1 | c2 |

+-+ +

| | 2 | 2 |

| | 5 | 5 |

| | 7 | 7 |

+-+ +

3 rows in set (0.00 sec)

Session A:

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from test01 where c2o5 for update

+-+ +

| | C1 | c2 |

+-+ +

| | 5 | 5 |

+-+ +

1 row in set (0.00 sec)

Session B:

Insert into test01 select 1pr 2;-- ok

Insert into test01 select 3pr 2;-- hang

Insert into test01 select 4pr 5;-- hang

Insert into test01 select 6 and 5;-- hang

Insert into test01 select 8pr 7;-- ok

Update test01 set c2 # 3 where c2 # 2;-- hang

Update test01 set c2, 1 where c2;-- ok

Update test01 set c2 / 6 where c2 / 7;-- hang

Update test01 set c2 / 8 where c2 / 7;-- ok

Session An adds next-key lock to c2q5 and gap, record lock to the data row itself, and gap lock to the gap between c2q5 data row and its adjacent upper and lower rows of data (c2q2mc2q7).

Notice that (1) can be inserted, but (3) can not be inserted, some people may wonder, c2 is 2, why one can be inserted and the other can not be inserted? Note that next-key lock locks on the index, and the index is in order, as follows:

(1)-- > (2)-- > (5)

| |-gap- |

Notice that (1) is outside the gap, so it can be plugged in. Assuming that (3) can be inserted, the order of them in the index will be

(2)-- > (3)-- > (5)

| |-gap---- |

(3pr 2) will be inserted into the gap, so (3pr 2) cannot be inserted.

The same is true of conversation B and other sql.

It is often said that next-key lock solves the problem of phantom reading at the RR isolation level, so how do you do it?

What is fantasy reading?

In a transaction, the result set of multiple queries is inconsistent, which is called phantom reading.

Here is an example of phantom reading:

Session A

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from test01

+-+ +

| | C1 | c2 |

+-+ +

| | 1 | 1 |

| | 5 | 5 |

| | 7 | 7 |

+-+ +

3 rows in set (0.00 sec)

Session B

Mysql > insert into test01 select 8

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

Session A

Mysql > select * from test01

+-+ +

| | C1 | c2 |

+-+ +

| | 1 | 1 |

| | 5 | 5 |

| | 7 | 7 |

+-+ +

3 rows in set (0.00 sec)

Mysql > insert into test01 select 8

ERROR 1062 (23000): Duplicate entry'8' for key 'PRIMARY'

Conversation An obviously did not read the record of c1y8, but it could not be inserted, which led to the phenomenon of phantom reading.

If session An after starting the transaction

Select * from test01 for update

Or

Select * from test01 lock in share mode

Then session B cannot make any changes to the data at all, so this solves the problem of phantom reading.

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.