search

Home >Database >Mysql Tutorial >Detailed explanation of row-level lock lock range of MySQL database InnoDB engine

Detailed explanation of row-level lock lock range of MySQL database InnoDB engine

小云云
小云云Original
2018-02-23 13:49:111962browse

Mysql database InnoDB engine supports row-level locking, which means that we can perform locking operations on certain rows of data in the table. The impact of the locking operation is: if one thing performs a locking operation on a row in the table, and another The transaction also needs to perform a lock operation on the same row, so the lock operation of the second transaction may be blocked. Once blocked, the second transaction can only wait until the first transaction is completed (commit or rollback) or times out.

This article mainly introduces the concepts related to row locks in InnoDB, focusing on the locking scope of row locks:

  • What kind of SQL statements will be locked?

  • What kind of lock should be added?

  • Which rows will the locking statement lock?

Background knowledge

We have briefly introduced InnoDB's row-level locks above. In order to understand the subsequent verification part, we need to add some background knowledge. If you know the corresponding knowledge very well, you can jump directly to the verification part.

1. Types of InnoDB locks

The InnoDB engine uses seven types of locks, they are:

  • Shared exclusive lock (Shared and Exclusive Locks)

  • Intention Locks

  • Record Locks

  • Gap Locks

  • ##Next-Key Locks

  • Insert Intention Locks

  • AUTO-INC Locks

This article mainly involves Shared and Exclusive Locks, Record Locks, Gap Locks, and Next-Key Locks. If you are interested in other types of locks, you can learn more about them yourself, and I will not go into details here.

1.1 Shared and Exclusive Locks

The concepts of shared locks (S locks) and exclusive locks (X locks) have appeared in many programming languages. First, let’s describe the impact of these two locks in MySQL:

  • If a transaction adds an S lock to a certain row of data, another transaction can also add an S lock to the corresponding row. lock, but X lock cannot be added to the corresponding row.

  • If a transaction adds an X lock to a certain row of data, another transaction can neither add an S lock nor an X lock to the corresponding row.

Use a classic matrix table to continue to illustrate the mutually exclusive relationship between shared locks and exclusive locks:

--SX01 ##X

In the figure, S represents a shared lock and X represents an exclusive lock. 0 represents lock compatibility and 1 represents lock conflict. Compatibility is not blocked and conflict is blocked. It can be seen from the table that once a transaction adds an exclusive lock, other transactions need to wait for any locks. Multiple shared locks will not block each other.

1.2 Record Locks, Gap Locks, and Next-Key Locks

These three types of locks all describe the scope of the lock, so they are explained together.

The following definitions are taken from MySQL official documentation

  • Record Locks: Record locks lock a record in the index.

  • Gap Locks: Gap locks either lock the value in the middle of the index record, or lock the value before the first index record or the value after the last index record.

  • Next-Key Locks: Next-Key lock is a combination of a record lock on the index record and a gap lock before the index record.

The index record is mentioned in the definition. Why? What is the relationship between row locks and indexes? In fact, InnoDB completes the locking operation by searching or scanning the index in the table. InnoDB will add a shared lock or exclusive lock to every index data it encounters. So we can call row-level locks (row-level locks) index-record locks (index-record locks), because row-level locks are added to the index corresponding to the row.

The locking ranges of the three types of locks are different and gradually expanded. Let's give an example to briefly explain the locking range of various locks. Assume that the index column in table t has four numerical values ​​​​3, 5, 8, and 9. According to the official document, the locking range of the three locks is determined as follows:

  • The locking scope of the record lock is a separate index record, which is the four rows of data 3, 5, 8, and 9.

  • The gap lock locks the gap in the row, which is represented by a set as (-∞,3), (3,5), (5,8), (8,9), (9,+∞).

  • Next-Key lock is a combination of index record lock and gap lock before index record lock. It is expressed as (-∞,3], (3, 5], (5,8], (8,9], (9,+∞).

Finally, three points need to be added about the gap lock:

  1. Gap locks prevent other transactions from concurrently inserting gap data, which can effectively solve the phantom problem (Phantom Problem). Because of this, not all transaction isolation levels use gap locks#. ##, MySQL InnoDB engine only uses gap locks at the Repeatable Read (default) isolation level.

    2. ##The role of gap locks is only to prevent other transactions from inserting data in gaps. It does not. Will prevent other transactions from having the same gap lock. This means that
  2. except the insert statement, other SQL statements can add gap locks to the same row without being blocked

    .

    3. For the locking behavior of the unique index, the gap lock will be invalid. At this time, only the record lock will work

  3. ##2. Locking. Statement

    We have already introduced that InnoDB implements locking by scanning index records during the execution of SQL statements. Which statements will be locked? What kind of locks will be added? Let’s describe them one by one:

select ... from statement: The InnoDB engine uses multi-version concurrency control (MVCC) to implement non-blocking reading, so for ordinary select reading statements, InnoDB does not lock [Note 1]

    ##select ... from lock in share mode statement: The difference between this statement and the ordinary select statement is that lock in share is added at the end. mode, we can guess from the literal meaning that this is a locked read statement, and the lock type is a shared lock (read lock). InnoDB will add next-key locks to all index records searched, but if the only index scanned is The only row, next-key is downgraded to an index record lock.

    • select ... from for update statement: Like the above statement, this statement adds an exclusive lock (write lock). ). InnoDB will add next-key locks to all index records searched, but if the unique row of a unique index is scanned, next-key will be downgraded to an index record lock

  • #update ... where ... statement: InnoDB will add next-key locks to all index records searched, but if the unique row of a unique index is scanned, next-key is downgraded to an index record lock. [Note 2]

  • delete ... where ... statement:. InnoDB will add next-key locks to all index records searched, but if a unique row of a unique index is scanned, next-key is downgraded to an index record lock.

  • insert statement: InnoDB will only set an exclusive index record lock on the row to be inserted.

  • Finally two points to add:

  • If a query uses an auxiliary index and adds an exclusive lock to the index record, InnoDB will The corresponding aggregate index record is locked.

If your SQL statement cannot use an index, MySQL must scan the entire table to process the statement. The result is that each row of the table will be locked and other users will be prevented from accessing it. All inserts into the table.

  1. SQL statement verification
  2. Without further ado, let’s move on to the key SQL statement verification part of this article.

    1. Test environment

    Database: MySQL 5.6.35
    Transaction isolation level: Repeatable read
    Database access terminal: mysql client

    2. Verification scenario

    2.1 Scenario 1

    Create a table: 

    CREATE TABLE `user` (
 `id` int(11) NOT NULL,
 `name` varchar(8) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    Insert data: 

    INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

    First we execute the template of the SQL statement:

S
1 1
##1begin;--2SELECT * FROM user where name='e' for update;--3--begin;4--INSERT INTO `user` (`id`, `name`) VALUES (5rollback;--6--rollback;
Steps client 1 client 2
10, #{name});
Replace the value of name in step 5 and observe the result:

The value of nameExecution result##dBlockingeBlocking##f##hiObserving the results, we found that the SQL statement SELECT * FROM user where name='e' for update
a No blocking
b No blocking
Blocking
Not blocking
No blocking

Locks a total of three rows of records in the index name. The (c, e] interval should be next-key lock and the (e, h) interval is the index record. The gap behind e.
Next we determine which part of the next-key lock is the index record lock and which part is the gap lock.
Template for executing the SQL statement:

Stepsclient 11begin;SELECT * FROM user where name='e' for update;--rollback;--Replace the value of name in step 5 and observe the result:
client 2
-- 2
- - 3
SELECT * FROM user where name=#{name} for update; 5
-- 6
rollback;

The value of name Execution result##d##No blockingBlockingNot blocking select for updateThe statement that does not block is a gap lock. If both statements are blocked, it is an index record lock.
e
##f
Because the gap lock will only block the insert statement, so for the same index data, the insert statement is blocked and
Observing the execution results, we can see that d and f are gap locks, and e is the index record lock.

Conclusion: Through two SQLs, we determined that the locking range for the auxiliary index name when the query condition is where name='e' is (c,e],(e ,g), Among them:

Add index record lock [e] to the index record e scanned by the SQL statement.

locks the front of e. The gap, the data (c,e) between c and e is added with gap lock

  • The first two constitute the next-key lock (c,e].

  • It is worth noting that the gap (e, g) behind e is also locked.

  • ##The careful readers mentioned here may have discovered our test data. There is no gap in the boundary data c and g. Next, we test the gap boundary value:

    • Template for executing the SQL statement:

    • #Step

client 1

#client 2

23456

Replace the values ​​of id and name in step 5, and observe the results:

##1 begin; --
SELECT * FROM user where name='e' for update; --
-- #begin;
-- INSERT INTO `user ` (`id`, `name`) VALUES (#{id}, #{name});
rollback; --
-- rollback;
cgcgcg##3Not blocking3Not blocking4Blocking4Blocking5 Blocking5Blocking6Blocking6Blocking##789##10BlockingNot blocking11Blocking12Blockinginsert statement when name is equal to c and e With different id values ​​one moment it is locked and another moment it is not locked. It must be that the ID column is locked to cause such a result.
The value of id name=c Execution results Value of id name=g Execution result
-- -- -- -3 g Group plug
-- -- -- -2 g Blocking
##-1 Not blocking -1 Blocking
1 Not blocking 1 Not blocking
2 Not blocking 2 Blocking
c g
c g
c g
c g
c Not blocking 7 g Not blocking
c Blocking 8 g Not blocking
c Not blocking 9 g No blocking
c 10 g
c -- -
c -- - ##By observing the above execution results, we found that the result of the
If we don’t look at the results of the row of data

id=5, we find a pattern:

When name=c## When #,

name=c
    corresponds to the gaps (3,5), (5,7), (7,9), (7,9), ( 9,∞) are all locked.

  • When name=e, name=e corresponds to the id aggregate index data record of

    id=7
    • The gaps (5,7), (3,5), (1,3), and (-∞,1) are all locked.

  • We can use the select * from user where id = x for update; statement to determine that the locks added to the above gaps are all gap locks.

    • Next, let’s explain the locking situation of
  • id=5

    Template for executing SQL statements:

Steps

client 1

client 2--##6--rollback;The value of idExecution result
1 begin;
2 SELECT * FROM user where name='e' for update; --
3 -- SELECT * FROM user where id=#{id} for update;
5 rollback; --
Replace the value of id in step 5 and observe the result:

##3Not blocking
4 Not blocking
5 Blocking
6 No blocking
7 No blocking

通过观察执行结果可知，id=5的聚合索引记录上添加了索引记录锁。根据MySQL官方文档描述，InnoDB引擎在对辅助索引加锁的时候，也会对辅助索引所在行所对应的聚合索引（主键）加锁。而主键是唯一索引，在对唯一索引加锁时，间隙锁失效，只使用索引记录锁。所以SELECT * FROM user where name='e' for update;不仅对辅助索引name=e列加上了next-key锁，还对对应的聚合索引id=5列加上了索引记录锁。

最终结论：  
对于SELECT * FROM user where name='e' for update;一共有三种锁定行为:

  1. 对SQL语句扫描过的辅助索引记录行加上next-key锁（注意也锁住记录行之后的间隙）。

  2. 对辅助索引对应的聚合索引加上索引记录锁。

  3. 当辅助索引为间隙锁“最小”和“最大”值时，对聚合索引相应的行加间隙锁。“最小”锁定对应聚合索引之后的行间隙。“最大”值锁定对应聚合索引之前的行间隙。

上面我们将对辅助索引加锁的情况介绍完了，接下来我们测试一下对聚合索引和唯一索引加锁。

2.2 场景二

建表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意与场景一表user不同的是name列为唯一索引。

插入数据：

INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

首先我们执行SQL语句的模板：

步骤 client 1 client 2
1 begin; --
2 SELECT * FROM user where name='e' for update;
3 -- begin;
4 -- INSERT INTO `user` (`id`, `name`) VALUES (10, #{name});
5 rollback; --
6 -- rollback;

替换步骤5中name的值，观察结果：

name的值 执行结果
a 不阻塞
b 不阻塞
c 不阻塞
d 不阻塞
e 阻塞
f 不阻塞
g 不阻塞
h 不阻塞
i 不阻塞

由测试结果可知，只有name='e'这行数据被锁定。

通过SQL语句我们验证了，对于唯一索引列加锁，间隙锁失效，

2.3 场景三

场景一和场景二都是在查询条件等于的情况下做出的范围判断，现在我们尝试一下其他查询条件，看看结论是否一致。

借用场景一的表和数据。

建表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `user` (`id`, `name`) VALUES ('1', 'a');
INSERT INTO `user` (`id`, `name`) VALUES ('3', 'c');
INSERT INTO `user` (`id`, `name`) VALUES ('5', 'e');
INSERT INTO `user` (`id`, `name`) VALUES ('7', 'g');
INSERT INTO `user` (`id`, `name`) VALUES ('9', 'i');

执行SQL语句的模板：

步骤 client 1 client 2
1 begin; --
2 SELECT * FROM user where name>'e' for update; --
3 -- begin;
4 -- INSERT INTO `user` (`id`, `name`) VALUES ('10', #{name});
5 rollback; --
6 -- rollback;

替换步骤5中name的值，观察结果：

name的值 执行结果
a 阻塞
b 阻塞
c 阻塞
d 阻塞
e 阻塞
f 阻塞
g 阻塞
h 阻塞
i 阻塞

这个结果是不是和你想象的不太一样，这个结果表明where name>'e'这个查询条件并不是锁住'e'列之后的数据，而锁住了所有name列中所有数据和间隙。这是为什么呢？

我们执行以下的SQL语句执行计划：

 explain select * from user where name>'e' for update;

执行结果：

+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | index_name    | index_name | 26      | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

如果你的结果与上面不同先执行一下OPTIMIZE TABLE user;再执行以上语句。

通过观察SQL语句的执行计划我们发现，语句使用了name列索引，且rows参数等于5，user表中一共也只有5行数据。SQL语句的执行过程中一共扫描了name索引记录5行数据且对这5行数据都加上了next-key锁，符合我们上面的执行结果。

接下来我们再制造一组数据。  
建表：

CREATE TABLE `user` (
 `id` int(11) NOT NULL,
 `name` varchar(8) NOT NULL,
 `age` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');

这张表和前表的区别是多了一列非索引列age。

我们再执行一下同样的SQL语句执行计划：

 explain select * from user where name>'e' for update;

执行结果：

+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | index_name    | index_name | 26      | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

是不是和第一次执行结果不同了，rows参数等于2，说明扫描了两行记录，结合SQL语句select * from user where name>'e' for update;执行后返回结果我们判断这两行记录应该为g和i。

因为select * from user where name>'e' for update;语句扫描了两行索引记录分别是g和i，所以我们将g和i的锁定范围叠就可以得到where name>'e'的锁定范围：

  1. 索引记录g在name列锁定范围为(e,g],(g,i)。索引记录i的在name列锁定范围为(g,i],(i,+∞)。两者叠加后锁定范围为(e,g],(g,i],(i,+∞)。其中g,i为索引记录锁。

  2. g和i对应id列中的7和9加索引记录锁。

  3. 当name列的值为锁定范围上边界e时，还会在e所对应的id列值为5之后的所有值之间加上间隙锁，范围为(5,7),(7,9),(9,+∞)。下边界为+∞无需考虑。

接下来我们逐一测试：

首先测试验证了next-key锁范围，执行SQL语句的模板：

步骤 client 1 client 2
1 begin; --
2 SELECT * FROM user where name>'e' for update; --
3 -- begin;
4 -- INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10', #{name},'18');
5 rollback; --
6 -- rollback;

替换步骤5中name的值，观察结果：

name的值 执行结果
a 不阻塞
b 不阻塞
c 不阻塞
d 不阻塞
f 阻塞
g 阻塞
h 阻塞
i 阻塞
j 阻塞
k 阻塞

下面验证next-key锁中哪部分是间隙锁，哪部分是索引记录锁，执行SQL语句的模板：

步骤 client 1 client 2
1 begin; --
2 SELECT * FROM user where name>'e' for update; --
3 -- SELECT * FROM user where name=#{name} for update;
5 rollback; --
6 -- rollback;

替换步骤5中name的值，观察结果：

name的值 执行结果
e 不阻塞
f 不阻塞
g 阻塞
h 不阻塞
i 阻塞
j 不阻塞

接下来验证对id列加索引记录锁，执行SQL语句的模板：

步骤 client 1 client 2
1 begin; --
2 SELECT * FROM user where name>'e' for update; --
3 -- SELECT * FROM user where id=#{id} for update;
5 rollback; --
6 -- rollback;

替换步骤5中id的值，观察结果：

id的值 执行结果
5 不阻塞
6 不阻塞
7 阻塞
8 不阻塞
9 阻塞
10 不阻塞

最后我们验证name列的值为边界数据e时，id列间隙锁的范围，执行SQL语句的模板：

步骤 client 1 client 2
1 begin; --
2 SELECT * FROM user where name>'e' for update; --
3 -- begin;
4 -- INSERT INTO `user` (`id`, `name`,`age`) VALUES (#{id}, 'e','18');
5 rollback; --
6 -- rollback;

替换步骤5中id的值，观察结果：

id的值 执行结果
-1 不阻塞
1 不阻塞
2 不阻塞
3 不阻塞
4 不阻塞
5 不阻塞
6 阻塞
7 阻塞
8 阻塞
9 阻塞
10 阻塞
11 阻塞
12 阻塞

注意7和9是索引记录锁记录锁。

观察上面的所有SQL语句执行结果，可以验证select * from user where name>'e' for update的锁定范围为此语句扫描name列索引记录g和i的锁定范围的叠加组合。

2.4 场景四

我们通过场景三验证了普通索引的范围查询语句加锁范围，现在我们来验证一下唯一索引的范围查询情况下的加锁范围。有了场景三的铺垫我们直接跳过扫描全部索引的情况，创建可以扫描范围记录的表结构并插入相应数据测试。

建表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(8) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `user` (`id`, `name`,`age`) VALUES ('1', 'a','15');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('3', 'c','20');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('5', 'e','16');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('7', 'g','19');
INSERT INTO `user` (`id`, `name`,`age`) VALUES ('9', 'i','34');

和场景三表唯一不同是name列为唯一索引。

SQL语句select * from user where name>'e'扫描name列两条索引记录g和i。如果需要只对g和i这两条记录加上记录锁无法避免幻读的发生，索引锁定范围应该还是两条数据next-key锁锁的组合：(e,g],(g,i],(i,+∞)。其中g,i为索引记录锁。

我们通过SQL验证我们的结论，执行SQL语句的模板：

步骤 client 1 client 2
1 begin; --
2 SELECT * FROM user where name>'e' for update; --
3 -- begin;
4 -- INSERT INTO `user` (`id`, `name`, `age`) VALUES ('10', #{name},'18');
5 rollback; --
6 -- rollback;

Replace the value of name in step 5 and observe the result:

##fghi
The value of name Execution result
a Not blocking
b Not blocking
c ##Not blocking
d No blocking
# #blocking
blocking
##Blocking
Blocking ##j
Blocking k
Blocking Let’s verify which part of the next-key lock is the gap lock and which part is the index record lock, execute SQL Statement template:

Steps

client 1client 21begin;----##3--SELECT * FROM user where name=#{name} for update;5rollback;--6 --rollback;name value
##2 SELECT * FROM user where name>'e' for update;
Replace the value of name in step 5 and observe the result:

Execution result

No blocking##fNo blockinggBlockinghNot blockingiBlockingSteps
e
##j No blocking
Through the verification results of the above two SQL statements, we have proved that the locking range trend of our g and i is both next-key overlay combination. Next we verify the lock transfer to the aggregate index after locking the auxiliary index, and execute the template of the SQL statement:

client 1

client 2

1begin;--2SELECT * FROM user where name>'e' for update;--3 --SELECT * FROM user where id=#{id} for update;5rollback;--6--rollback;Value of idExecution result
Replace the value of id in step 5 and observe the result:

No blockingnamename, one is a unique index, One is a normal index.
5
6 Not blocking
7 ##Blocking
8 No blocking
9 Blocking
##10 Not blocking
It can be seen from the results that index record locks are added to columns 7 and 9 in the aggregate index id corresponding to the g and i columns in the auxiliary index . So far, all the experimental results are exactly the same as scenario three, which is also easy to understand. After all, scenario four and scenario three are only different index types of the auxiliary index
Finally verify the intention, next-key locks the boundary data e, and see that the conclusion is the same as scenario three.

Template for executing SQL statements:

Stepsclient 1

client 2

1begin;--2SELECT * FROM user where name>'e' for update;--3--begin;4--INSERT INTO `user` (`id`, `name`,`age`) VALUES (#{id}, 'e','18');rollback;## 6--rollback;

Replace the value of id in step 5 and observe the result:

##5
--
##Not blocking9##10. It can be seen from the results that when
Value of id Execution result
-1 Not blocking
##1 Not blocking
2 Not blocking
3 No blocking
4 Not blocking
5 Not blocking
6 No blocking
7 Blocking
8
##Blocking
Not blocking ##11
Not blocking 12
No blocking ##Note that 7 and 9 are index record locks Record lock
name

is listed as the upper boundary e of the index record, there is no locking behavior on the id, which is different from scenario three. Range queries for unique indexes are similar to range queries for ordinary indexes. The only difference is that when the auxiliary index is equal to the boundary value of the upper and lower ranges, gap locks will not be added to the primary key.

Unique index range query locking range:

The locking range for scanned auxiliary index records is multiple index records next -Superposition combination of key range.

  • For the lock range of the aggregate index (primary key), index record locks will be added to the aggregate index columns corresponding to multiple auxiliary indexes.

  • ConclusionThe InnoDB engine will add corresponding locks to the index records it has scanned. Through "Scenario 1" we have made it clear that scanning a common The locking range of index records. Through "Scenario 3" we can infer the locking range of any number of index records for scanning ordinary indexes. Through "Scenario 2" we determined the locking range for scanning a unique index record (or primary key). Through "Scenario 4" we can infer the locking range of any number of scan index records (or primary keys). It can be used flexibly in actual applications to determine whether two SQL statements are locked to each other. It should also be noted here that the query conditions of the index cannot be taken for granted. They are often not what we understand. It is necessary to judge the number of records ultimately scanned by the index based on the execution plan, otherwise it will cause deviations in the understanding of the locking range.

Remarks

Note 1: When the transaction isolation level is SERIALIZABLE, the ordinary select statement will also add indexes scanned during the execution of the statement. next-key lock. If the statement scans a unique index, the next-key lock is downgraded to an index record lock.
Note 2: When the update statement modifies the aggregate index (primary key) record, an implicit locking operation will be performed on the affected auxiliary index. When a duplicate check scan is performed before a new secondary index record is inserted and when a new secondary index record is inserted, the update operation also adds shared locks on the affected secondary index records.
Related recommendations:


mysql reports an error when executing the sql file Error: Unknown storage engine'InnoDB How to solve


MySQL starts What to do if the InnoDB engine is disabled

Comparison between MySQL storage engine MyISAM and InnoDB

The above is the detailed content of Detailed explanation of row-level lock lock range of MySQL database InnoDB engine. For more information, please follow other related articles on the PHP Chinese website!

sql mysql for select Error auto delete 并发 table 数据库
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：MysqL security strategy sharingNext article：MysqL security strategy sharing

Related articles

See more