关于RR和间隔锁的一些实验

分手后的思念是犯贱 2023-07-25 14:15 5阅读 0赞

关于RR和间隔锁的一些实验

测试表如下：

mysql> show create table test\G
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `id` int(11) NOT NULL,
      `c` int(11) DEFAULT NULL,
      `d` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx` (`c`),
      KEY `idx_d` (`d`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

案例1：  
session 1:

mysql> select * from test;
    +-----+------+------+
    | id  | c    | d    |
    +-----+------+------+
    |   0 |    0 |    0 |
    |   5 |    5 |    5 |
    |  10 |   10 |   10 |
    |  15 |   15 |   15 |
    |  20 |   10 |   20 |
    | 100 |  100 |  100 |
    +-----+------+------+
    6 rows in set (0.00 sec)
    
    mysql> 
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> update test set c=100 where c=10;
    Query OK, 2 rows affected (0.00 sec)
    Rows matched: 2  Changed: 2  Warnings: 0
    
    mysql> select * from test;
    +-----+------+------+
    | id  | c    | d    |
    +-----+------+------+
    |   0 |    0 |    0 |
    |   5 |    5 |    5 |
    |  10 |  100 |   10 |
    |  15 |   15 |   15 |
    |  20 |  100 |   20 |
    | 100 |  100 |  100 |
    +-----+------+------+
    6 rows in set (0.00 sec)

session 2:

此时session2被堵塞的原因是，session1在c的索引上存在next-key锁(5,10\]，(10,15)，而session2要将c改为10，也就是要把id=0这一行的c=0改为c=10，索引C上的操作是将c=0，id=0这一行索引标记为删除，然后插入c=10,id=0这一行索引，所以会被session1的next-key锁堵塞。  
顺便分析一下，session1都拿了什么锁：  
1.MDL读锁  
2.索引C上的next-key锁(5,10\]，(10,15)  
3.主键索引上id=10和id=20的行锁

案例2：表结构与上面一致  
session1：

session2:

mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into test values(1000,1000,10);
    Query OK, 1 row affected (0.00 sec)

这里插入d=10为什么不被堵塞呢？session1在修改id=10这一行时，由于d列有索引，那不应该在索引d上的d=10附近维护一个next-key锁吗？  
尝试分析一下，其实session1的执行流程是从索引C定位到所有c=10的主键id，然后回表读取数据行，从中拿出d列，这里根本不需要访问索引d，所以索引d上是没有锁的  
所以session1的拿锁情况是：  
1.MDL读锁  
2.索引C上的next-key锁  
3.主键索引上行锁

总结一下MySQL上锁的规则：  
1.只对访问到的索引上锁，这个索引包括主键索引，二级索引；  
2.如果查询条件列没有索引，则锁全表