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?
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.
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
S | X | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
S | 01 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
1 | 1 |
Steps | client 1 | client 2 |
---|---|---|
begin; | -- | |
SELECT * FROM user where name='e' for update; | -- | |
-- | begin; | |
-- | INSERT INTO `user` (`id`, `name`) VALUES ( | 10, #{name}); |
rollback; | -- | |
-- | rollback; |
Execution result | |
---|---|
a | No blocking |
b | No blocking |
Blocking | |
Blocking | |
Blocking | ##h |
Not blocking | i |
No blocking | Observing the results, we found that the SQL statement |
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:
Steps
client 2 | ||
---|---|---|
-- | 2 | |
- - | 3 | |
SELECT * FROM user where name=#{name} for update; | 5 | |
-- | 6 | |
rollback; | Replace the value of name in step 5 and observe the result: |
e | |
##f | |
Because the gap lock will only block the insert statement, so for the same index data, the insert statement is blocked and | select for update
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:
##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:
client 1
#client 2
##1 | begin; | -- | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
SELECT * FROM user where name='e' for update; | -- | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- | #begin; | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- | INSERT INTO `user ` (`id`, `name`) VALUES (#{id}, #{name}); | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
rollback; | -- | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
-- | rollback; |
The value of id | name=c | Execution results | Value of id | name=g | Execution result |
---|---|---|---|---|---|
-- | -- | -- | -3 | g | Group plug |
-- | -- | -- | -2 | g | Blocking |
##-1 | cNot blocking | -1 | gBlocking | ||
1 | cNot blocking | 1 | gNot blocking | ||
2 | cNot blocking | 2 | gBlocking | ||
c | Not blocking | 3 | g | Not blocking | |
c | Blocking | 4 | g | Blocking | |
c | Blocking | 5 | g | Blocking | |
c | Blocking | 6 | g | Blocking | |
c | Not blocking | 7 | g | Not blocking | |
c | Blocking | 8 | g | Not blocking | |
c | Not blocking | 9 | g | No blocking | ##10 |
c | Blocking10 | g | Not blocking11 | ||
c | Blocking-- | - | 12 | ||
c | Blocking-- | - | ##By observing the above execution results, we found that the result of the | insert
id=5, we find a pattern:
When
name=c## When #,
When
name=e
, name=e
corresponds to the id aggregate index data record of
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.
Template for executing SQL statements:
Steps
1 | begin; | |
---|---|---|
2 | SELECT * FROM user where name='e' for update; | -- |
3 | -- | SELECT * FROM user where id=#{id} for update; |
5 | rollback; | -- |
-- | rollback; | |
Replace the value of id in step 5 and observe the result: | The value of id |
Not blocking | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
4 | Not blocking | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
5 | Blocking | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
6 | No blocking | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
7 | No blocking | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
通过观察执行结果可知, 最终结论:
上面我们将对辅助索引加锁的情况介绍完了,接下来我们测试一下对聚合索引和唯一索引加锁。 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语句的模板:
替换步骤5中name的值,观察结果:
由测试结果可知,只有 通过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语句的模板:
替换步骤5中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) 如果你的结果与上面不同先执行一下 通过观察SQL语句的执行计划我们发现,语句使用了 接下来我们再制造一组数据。 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'); 这张表和前表的区别是多了一列非索引列 我们再执行一下同样的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) 是不是和第一次执行结果不同了, 因为
接下来我们逐一测试: 首先测试验证了next-key锁范围,执行SQL语句的模板:
替换步骤5中name的值,观察结果:
下面验证next-key锁中哪部分是间隙锁,哪部分是索引记录锁,执行SQL语句的模板:
替换步骤5中name的值,观察结果:
接下来验证对
替换步骤5中id的值,观察结果:
最后我们验证
替换步骤5中id的值,观察结果:
注意7和9是索引记录锁记录锁。 观察上面的所有SQL语句执行结果,可以验证 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'); 和场景三表唯一不同是 SQL语句 我们通过SQL验证我们的结论,执行SQL语句的模板:
Replace the value of name in step 5 and observe the result:
Steps
Execution result
client 1 client 2
Steps client 2
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.
The locking range for scanned auxiliary index records is multiple index records next -Superposition combination of key range.
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:
|
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!