13. Row lock range_lock analysis_a bit complicated

Directory

1 premise

1.1 Knowledge reserve

1.2 Related background of the lock:

1.3 What are the row lock granularities?

1.4 Locking rules

1.5 Index in range query

1.4 Data preparation

2. Case 1: Equivalence query gap lock

2.1 Effect

2.2 Principle

3. Case 2: non-unique index equivalent lock

3.1 Effect

3.2 Description

4. Case 2: non-unique index equivalent lock

4.1 Effect

4.2 Principle

5. Case 2: non-unique index equivalent lock

5.1 Effect

5.2 Principle

6 Case 3: primary key index range lock

6.1 Effect

6.2 Principle

7. Case 3: primary key index range lock

7.1 Effect

7.2 Principle

8. Case 4: Non-unique index range lock

8.1 Effect

8.2 Principle


Row lock in Mysql — lock analysis

1 prerequisite

1.1 Knowledge reserve

Knowledge reserve: the principle of indexing

1.2 Factors affecting the scope of locks:

“What locks are on a certain SQL statement” needs to be considered in combination with the following situations:
– the isolation level of the transaction
– The index used when the statement is executed (such as clustered index, unique secondary index, ordinary secondary index)
– Query conditions (such as =, =<, >=, etc.)
– The specific execution statement type (select, insert)
– order by impact
– limit impact

1.3 What are the row lock granularities

– record lock: record lock, only add lock on the record
– gap lock: gap lock n=1 (0,3), which refers to the range. (Prevent phantom reading, gap insertion is not allowed)
– next-key lock: n=1 (0,3] record lock + gap lock , gap lock + row lock (next-key lock) (open before and then close interval)

1.4 Locking Rules

– Principle 1: The basic unit of locking is next-key lock. Note that it is a front-opening and back-closing interval.
– Principle 2: Only objects accessed during the search process will be locked. This sentence is very important and needs to be understood. 1. It means that if the object is locked, then these are accessed. 2. As long as the object is accessed, the above will be locked.

– Optimization 1: equivalent query on the index,
Hitting a unique index degenerates into a row lock.
Hit the normal index, GAP Lock + Record Lock on the left and right.
– Optimization 2: In the equivalent query on the index, when traversing to the right and the last value does not meet the equivalence condition, the next-key lock degenerates into a gap lock. After opening, open the interval.

1.5 Index in range query

Indexed in range queries:
– Equivalence and range are judged separately.
– When the index is in range query, it will always access the first value in the interval that does not meet the condition.
– If reverse sorting is used, after reverse sorting, an additional GAP is added to the right of the search range.
– Which direction still has the equivalent judgment of the hit, and then expand the interval of opening outside and closing inside in the same direction.

1.6 Data Preparation

CREATE TABLE `t` (
`id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`)
) ENGINE=InnoDB;


insert into t values (0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25 );

2. Case 1: Equivalent query gap lock

2.1 Effects

session A session B session C
begin;
update t set d = d + 1 where id = 7;
mysql> insert into t values(8,8,8);
ERROR 1205 (HY000): Lock wait timeout exceeded;
mysql> update t set d=d + 1 where id=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

2.2 Principles

The locking unit is next-key lock, and the locking range of session A is (5,10];
This is an equivalent query (id=7), and the rightmost id=10 does not satisfy the query condition, and the next-key lock degenerates into a gap lock, so the final locked range is (5,10). Also note that although id has a unique index, but id=7 does not have this value and does not hit the unique index, so it cannot degenerate into a row lock.
Therefore, if session B inserts the record with id=8 into this gap, it will be locked, but it is possible for session C to modify the row with id=10.

3. Case 2: non-unique index equivalent lock_covering index

3.1 Effects

Case 2: Non-unique index Equivalent lock
session A session B session C
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select id from t where c=5 lock in share mode;
+ —- +
| id |
+ —- +
| 5 |
+ —- +
1 row in set (0.01 sec)

mysql> update t set d=d + 1 where id= 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert into t values(7,7,7);
ERROR 1205 (HY000): Lock wait timeout exceeded;

3.2 Description

Here session A needs to add a read lock to the row c=5 on index c.
This is an ordinary index, which requires GAP Lock + Record Lock on the left and right sides, so the range is (5,10]
According to optimization 2: equivalence judgment, traversing to the right, the last value does not satisfy the equivalence condition of c=5, so it degenerates into a gap lock (5,10).
According to principle 2, only the accessed objects will be locked. This query uses the covering index and does not need to access the primary key index, so there is no lock on the primary key index, which is why the update statement of session B can be executed.
If session C wants to insert a (7,7,7) record, it will be locked by session A’s gap lock (5,10).

4. Case 2: non-unique index equivalent lock_back table

4.1 Effects

session A session B < strong>session C
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where c=5 lock in share mode;
+ —- + —— + —— +
| id | c | d |
+ —- + —— + —— +
| 5 | 5 | 5 |
+ —- + —— + —— +
1 row in set (0.00 sec)

mysql> update t set d=d + 1 where id= 5;
ERROR 1205 (HY000): Lock wait timeout exceeded;
mysql> insert into t values(7,7,7);
ERROR 1205 (HY000): Lock wait timeout exceeded;

4.2 Principles

Here session A needs to add a read lock to the row c=5 on index c.
This is a normal index that requires GAP Lock + Record Lock on the left and right sides, so the range is (5,10].
According to optimization 2: equivalence judgment, traversing to the right, the last value does not satisfy the equivalence condition of c=5, so it degenerates into a gap lock (5,10).
According to principle 2, only the accessed objects will be locked. This query needs to lock the primary key of the queried record. So session B will be locked at this time.
If session C wants to insert a (7,7,7) record, it will be locked by session A’s gap lock (5,10).

5. Case 2: non-unique index equivalent lock_write

5.1 Effects

session A session B < strong>session C
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select id from t where c=5 for update;
+ —- +
| id |
+ —- +
| 5 |
+ —- +
1 row in set (0.00 sec)

mysql> update t set d=d + 1 where id= 5;
ERROR 1205 (HY000): Lock wait timeout exceeded;
mysql> insert into t values(7,7,7);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values(4,4,4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t values(4,5,6);
ERROR 1205 (HY000): Lock wait timeout exceeded;

5.2 Principles

Here session A needs to add a write lock to the line c=5 on index c.
This is a normal index that requires GAP Lock + Record Lock on the left and right sides, so the range is (5,10].
According to optimization 2: equivalence judgment, traversing to the right, the last value does not satisfy the equivalence condition of c=5, so it degenerates into a gap lock (5,10).
When executing for update, the system will think that you will update the data next, so it will add row locks to the rows that meet the conditions on the primary key index by the way. So it is locked by session A
If session C wants to insert a (7,7,7) record, it will be locked by session A’s gap lock (5,10).

6 Case 3: primary key index range lock_equivalent

6.1 Effect

session A session B < strong>session C
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id=10 for update;
+ —- + —— + —— +
| id | c | d |
+ —- + —— + —— +
| 10 | 10 | 10 |
+ —- + —— + —— +
1 row in set (0.00 sec)

mysql> insert into t values(8,8,8) ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(13,13,13);
Query OK, 1 row affected (0.01 sec)

mysql> update t set d=d + 1 where id=15;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

6.2 Principle

At the beginning of execution, the first row with id=10 should be found, so it should be next-key lock(5,10].
According to optimization 1, the equivalence condition on the primary key id degenerates into a row lock, and only the row lock of the row id=10 is added.

7. Case 3: primary key index range lock_range

7.1 Effect

session A session B < strong>session C
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id>=10 and id < 11 for update;
+ —- + —— + —— +
| id | c | d |
+ —- + —— + —— +
| 10 | 10 | 10 |
+ —- + —— + —— +
1 row in set (0.00 sec)

mysql> insert into t values(8,8,8) ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(13,13,13);
ERROR 1205 (HY000): Lock wait timeout exceeded;

mysql> update t set d=d + 1 where id=15;
ERROR 1205 (HY000): Lock wait timeout exceeded;

7.2 Principles

Range Search Continue to search later, find the line with id=15 and stop, so you need to add next-key lock(10,15], because it is a range match, not an equivalent match, and the far right is not Convert closed intervals to open intervals.
Therefore, the scope of session A’s lock at this time is the primary key index, row lock id=10 and next-key lock(10,15]. In this way, you can understand the results of session B and session C.
When session A locates the row with id=10 for the first time, it is judged as an equivalent query, and when it scans to the right to id=15, it uses a range query to judge. Which direction still has the equivalent judgment of the hit, and then expand the interval of opening outside and closing inside in the same direction.

8. Case 4: Non-unique index range lock

8.1 Effect

session A session B < strong>session C
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where c>=10 and c < 11 for update;
+ —- + —— + —— +
| id | c | d |
+ —- + —— + —— +
| 10 | 10 | 10 |
+ —- + —— + —— +
1 row in set (0.00 sec)

mysql> insert into t values(8,8,8) ;
ERROR 1205 (HY000): Lock wait timeout exceeded;
mysql> insert into t values(13,13,13);
ERROR 1205 (HY000): Lock wait timeout exceeded
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set d=d + 1 where c=15;
ERROR 1205 (HY000): Lock wait timeout exceeded

8.2 Principles

When using c=10 to locate the record for the first time, after the next-key lock (5,10] is added to the index c, since the index c is a non-unique index, there is no optimization rule, that is to say, it will not change into a row lock , so the final locks added by session A are the two next-keylocks (5,10] and (10,15] on the index c. Because it is a range match, not an equivalent match, the rightmost will not transfer the closed interval into an open interval.
So from the results, sesson B is blocked when it wants to insert the insert statement of (8,8,8), and it is reasonable to scan until c=15, because InnoDB needs to scan to c=15 15, I realized that there is no need to continue to look for it.

9. Case 5: An example of “equivalent” on a non-unique index

insert the same value

mysql> insert into t values(30,10,30);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
 + ---- + ------ + ------ +
| id | c | d |
 + ---- + ------ + ------ +
| 0 | 0 | 0 |
| 5 | 5 | 5 |
| 10 | 10 | 10 |
| 15 | 15 | 15 |
| 20 | 20 | 20 |
| 25 | 25 | 25 |
| 30 | 10 | 30 |
 + ---- + ------ + ------ +
7 rows in set (0.00 sec)

9.1 Effect

session A session B < strong>session C

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

mysql> delete from t where c=10;
Query OK, 2 rows affected (0.01 sec)

mysql> insert into t values(8, 8,8);
ERROR 1205 (HY000): Lock wait timeout exceeded;
mysql> insert into t values(13,13,13);
ERROR 1205 (HY000): Lock wait timeout exceeded;
mysql> insert into t values(50,10,16);
ERROR 1205 (HY000): Lock wait timeout exceeded;

mysql> update t set d=d + 1 where c=15;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update t set d=d + 1 where c=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

9.2 Principle

The locking range of the statement on index c is the part covered by the blue area in the figure below

What is the “gap” for the same index value?

When session A traverses, it first visits the first record with c=10
According to principle 1, the next-key lock from (c=5,id=5) to (c=10,id=10) is added here. Then, session A searches to the right until the line (c=15,id=15) is encountered, and the loop ends.
According to optimization 2, this is an equivalent query, and rows that do not meet the conditions are found to the right, so it will degenerate into a gap lock from (c=10,id=10) to (c=15,id=15).

10. Case 6: Limit statement lock

10.1 Effects

session A session B

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

mysql> delete from t where c=10 limit 2;
Query OK, 2 rows affected (0.01 sec)

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

mysql> insert into t values(50,10,16);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values(13,13,13);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values(8,8,8);
ERROR 1205 (HY000): Lock wait timeout exceeded;

10.2 Principle

The delete statement explicitly adds a limit of limit 2, so after traversing to the line (c=10, id=30), there are already two statements that satisfy the condition, and the loop ends. Therefore, the locking range on the index c is It becomes an interval from (c=5, id=5) to (c=10, id=30) which is open and closed

The guiding significance of practice is to increase the limit as much as possible when deleting data, which can not only control the number of deleted data, make the operation safer, but also reduce the scope of locking