MySQL 一些容易迷惑的加锁例子

以下实验基于这个表，当然隔离级别是RR，不然RC下也没那么多复杂的加锁情况了：

mysql> select * from sam;
+----+------+------+
| id | c2   | c3   |
+----+------+------+
|  1 |    1 |    1 |
|  5 |    5 |    5 |
| 10 |   10 |   10 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> desc sam;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| c2    | int(11) | YES  | MUL | NULL    |       |
| c3    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

1.非唯一二级索引范围扫描末尾边界相同值之间的gap锁
会话1，执行mysql> select * from sam where c2<5 for update

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sam where c2<5 for update;
+----+------+------+
| id | c2   | c3   |
+----+------+------+
|  1 |    1 |    1 |
+----+------+------+
1 row in set (0.00 sec)

那么我们预测加锁c2列索引上应该是(-∞，1],(1,5]，这里注意的是，即使是<5，也需要在c2=5的列上加锁。以及虽然需要遍历到c2=10才知道是c2=5在哪里结束，但是并不需要加(5,10)。

会话2，插入(6,6,6)成功，但在c2=5上加S/X锁都会被堵住，验证了上面的加锁情况。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sam values (6,6,6);
Query OK, 1 row affected (0.00 sec)

mysql> select * from sam where c2=5 for update;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from sam where c2=5 lock in share mode;

但存在一种容易让人迷惑的情况：
会话2继续在上面同一个事务内，执行以下insert，会有“矛盾”的现象

mysql> insert into sam values (3,5,5);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> 
mysql> insert into sam values (7,5,5);
Query OK, 1 row affected (0.00 sec)

我们说，c2=5列上已经被会话1持有X锁，那么当插入(3,5,5)，由于c2=5冲突，所以被堵住；
那么为什么插入(7,5,5)，由没有因为c2=5冲突而被堵呢？

实际上，会话1确实在c2列索引上的c2=5这一行索引上加了X锁。上面两个insert的不同，是因为gap锁的原因。
会话1在c2列锁上加的gap锁，是从-∞，到c2=5,id=5这一行索引之间，当插入(3,5,5)，也就是需要插入c2=5,id=3这一行索引到c2=5,id=5这一行索引的前面，这时候就被gap锁堵住了；
而插入(7,5,5)，是需要插入c2=5,id=7这一行索引到c2=5,id=5这一行索引的后面，所以这时候并不会被gap锁堵住。

2.范围扫描末尾边界加锁问题
还是继续上面的例子，我们知道当范围扫描是c2<5，则最后的加锁范围是(1,5]，但是当我们的条件是c<=5，又不一样了。
会话1，执行mysql> select * from sam where c2<=5 for update

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sam where c2<=5 for update;
+----+------+------+
| id | c2   | c3   |
+----+------+------+
|  1 |    1 |    1 |
|  5 |    5 |    5 |
+----+------+------+
2 rows in set (0.00 sec)

会话2，再插入(7,5,5)，此时就会跟上面实验不一样，会被堵住；同样(7,6,5)，(7,10,10)都会被堵住，直到插入的c2=11，才能成功：

mysql> insert into sam values (7,5,5);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into sam values (7,6,5);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into sam values (7,10,10);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into sam values (7,11,10);
Query OK, 1 row affected (0.01 sec)

这是为什么呢？
原因是c2<=5，就需要往右继续遍历到第一个不满足c2=5的索引行，我们的例子中是10，还需要加一个(5,10]的next-key锁。

3.范围扫描起始边界加锁问题
上面第二个实验涉及到范围扫描是否包含边界值，会有不同的加锁情况。
同样的，对于起始边界值，包含与不包含也是不一样的：
会话1执行select * from sam where c2>5 for update

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sam where c2>5 for update;
+----+------+------+
| id | c2   | c3   |
+----+------+------+
| 10 |   10 |   10 |
+----+------+------+
1 row in set (0.00 sec)

会话2可以对c2=5的行加X锁：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sam where c2=5 for update;
+----+------+------+
| id | c2   | c3   |
+----+------+------+
|  5 |    5 |    5 |
+----+------+------+
1 row in set (0.00 sec)

但如果会话1的范围条件是c2>=5，那么会话2就不能对c2=5的行加锁。

4.非唯一二级索引范围扫描起始边界相同值之间的gap锁
范围扫描不包含起始边界：
会话1，执行select * from sam where c2>5 for update

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sam where c2>5 for update;
+----+------+------+
| id | c2   | c3   |
+----+------+------+
| 10 |   10 |   10 |
+----+------+------+
1 row in set (0.00 sec)

会话2可以插入(3,5,5)，不能插入(6,5,5)

mysql> insert into sam values (3,5,5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into sam values (6,5,5);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

这是因为会话1在c2列索引上加的是从c2=5,id=5到cd2=10,id=10的next-key锁，而-∞到c2=5,id=5之间没有gap锁。
插入(3,5,5)，也就是插入c2=5,id=3，是在-∞到c2=5,id=5之间，可以插入；插入(6,5,5)，是插入c2=5,id=6，是在c2=5,id=5和cd2=10,id=10之间，会被堵住。

范围扫描包含起始边界：
会话1，执行select * from sam where c2>=5 for update

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sam where c2>=5 for update;
+----+------+------+
| id | c2   | c3   |
+----+------+------+
|  5 |    5 |    5 |
| 10 |   10 |   10 |
+----+------+------+
2 rows in set (0.00 sec)

会话2,不能插入(3,5,5)，也不能插入(6,5,5)，此时是因为，-∞到c2=5,id=5之间也加入了gap锁。