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

关于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:

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=10 where id=0;

此时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：

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> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set d=100 where c=10;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

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.如果查询条件列没有索引，则锁全表