mysql innodb 记录锁和插入意向锁如何协同工作? [英] how mysql innodb record lock and insert intention lock works together?
问题描述
InnoDB 的锁指令很多，但在我的测试中，有些东西还是让我困惑.MySQL 版本为 5.7，具有可重复读隔离级别.
There are many of InnoDB locks instrunction, but in my test, something still confusing me. MySQL version is 5.7 with Repeatable Read isolation level.
drop table if exists t1;
create table if not exists t1(id float,name varchar(20),key idx_id(id));
insert into t1 values (1,'a'),(3,'c'), (4,'d'), (10,'f');
-- session1:
START TRANSACTION;
insert into t1 values (5,'a');
-- session 2:
update t1 set name='a2' where id > 4 and id < 5; -- wating for lock
-- session3:
update t1 set name='a2' where id > 5 and id < 10; -- no wait,executed.
据我所知，session1 持有插入意向锁 (4,10)，session2 正在等待 X 锁 (4,5)，因此它被 session 1 阻塞，这是合理的.Session3 可以等待 session 1 释放锁，但实际上 session3 只是被执行了.这是怎么发生的?
To My understanding, session1 hold insert intention lock (4,10), session2 is waiting for X lock (4,5) so it is blocked by session 1, that is rantional. Session3 could wait for session 1 to release lock, but the truth is session3 just be executed. How this happened?
推荐答案
就在会话 1 执行之后 START TRANSACTION;insert into t1 values(5,'a');，select * from performance_schema.data_locks;的输出为:
Just after session 1 executes START TRANSACTION; insert into t1 values (5,'a');, the output of select * from performance_schema.data_locks; is:
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 139675093921992:1063:139675020906192 | 2117 | 52 | 31 | test | t1 | NULL | NULL | NULL | 139675020906192 | TABLE | IX | GRANTED | NULL |
+--------+--------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
就在会话 2 执行之后 update t1 set name='a2' where id >4 和 id <5;，输出为:
Just after session 2 executes update t1 set name='a2' where id > 4 and id < 5;, the output is:
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+----------------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+----------------------------+
| INNODB | 139675093921144:1063:139675020900048 | 2118 | 49 | 29 | test | t1 | NULL | NULL | NULL | 139675020900048 | TABLE | IX | GRANTED | NULL |
| INNODB | 139675093921144:2:5:6:139675020897056 | 2118 | 49 | 29 | test | t1 | NULL | NULL | idx_id | 139675020897056 | RECORD | X | WAITING | 0x0000A040, 0x000000000208 |
| INNODB | 139675093921992:1063:139675020906192 | 2117 | 52 | 31 | test | t1 | NULL | NULL | NULL | 139675020906192 | TABLE | IX | GRANTED | NULL |
| INNODB | 139675093921992:2:5:6:139675020903280 | 2117 | 49 | 29 | test | t1 | NULL | NULL | idx_id | 139675020903280 | RECORD | X,REC_NOT_GAP | GRANTED | 0x0000A040, 0x000000000208 |
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+----------------------------+
会话 3 执行后 update t1 set name='a2' where id >5 和 id <10;，输出为:
After session 3 executes update t1 set name='a2' where id > 5 and id < 10;, the output is:
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+---------------+-------------+----------------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+---------------+-------------+----------------------------+
| INNODB | 139675093922840:1063:139675020912224 | 2119 | 50 | 30 | test | t1 | NULL | NULL | NULL | 139675020912224 | TABLE | IX | GRANTED | NULL |
| INNODB | 139675093922840:2:5:5:139675020909312 | 2119 | 50 | 30 | test | t1 | NULL | NULL | idx_id | 139675020909312 | RECORD | X | GRANTED | 0x00002041, 0x000000000203 |
| INNODB | 139675093922840:2:4:5:139675020909656 | 2119 | 50 | 30 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 139675020909656 | RECORD | X,REC_NOT_GAP | GRANTED | 0x000000000203 |
| INNODB | 139675093921144:1063:139675020900048 | 2118 | 49 | 29 | test | t1 | NULL | NULL | NULL | 139675020900048 | TABLE | IX | GRANTED | NULL |
| INNODB | 139675093921144:2:5:6:139675020897056 | 2118 | 49 | 29 | test | t1 | NULL | NULL | idx_id | 139675020897056 | RECORD | X | WAITING | 0x0000A040, 0x000000000208 |
| INNODB | 139675093921992:1063:139675020906192 | 2117 | 52 | 31 | test | t1 | NULL | NULL | NULL | 139675020906192 | TABLE | IX | GRANTED | NULL |
| INNODB | 139675093921992:2:5:6:139675020903280 | 2117 | 49 | 29 | test | t1 | NULL | NULL | idx_id | 139675020903280 | RECORD | X,REC_NOT_GAP | GRANTED | 0x0000A040, 0x000000000208 |
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+---------------+-------------+----------------------------+
所以，session 2 被阻塞的原因是:它请求 next-key lock (4, 5] 也就是 (4, 5) gap lock 加上 5 index-record lock，但是session 1持有5 index-record lock，session 3没有被阻塞的原因是:它请求了next-key lock(5, 10] 即(5, 10) gap lock 加上10 index-record lock，没有人持有10 index-record锁.
So, the reason why session 2 is blocked is: it requests next-key lock (4, 5] which is (4, 5) gap lock plus 5 index-record lock, but session 1 is holding 5 index-record lock. And the reason why session 3 is not blocked is: it requests next-key lock (5, 10] which is (5, 10) gap lock plus 10 index-record lock, and nobody is holding 10 index-record lock.
这篇关于mysql innodb 记录锁和插入意向锁如何协同工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
