mysql间隙锁小测

1、间隙锁是为了防止事务更新删除或者锁定数据时，别的事务dml符合事务1条件的数据，从而造成幻读，理解间隙锁前，先了解一下快照读和当前读，并非事务开启就开启快照读，而是第一条select语句执行才产生快照，当事务发生lock in share mode,for update,insert,update,delete操作时，会对修改的记录采取当前读，获取事务外对修改此记录已提交修改的数据
Table: a
Create Table: CREATE TABLE a (
id int(10) NOT NULL,
name int(10) NOT NULL,
PRIMARY KEY (id),
KEY idx_test (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
mysql> select * from a;
±—±-----+
| id | name |
±—±-----+
| 0 | 1 |
| 2 | 3 |
| 4 | 5 |
| 6 | 7 |
| 8 | 9 |
±—±-----+
5 rows in set (0.00 sec)
步骤1：transcation 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update a set name=33 where id<6 and id>2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
步骤2：transcation 2 （自动提交）
mysql> insert into a values(10,11);
Query OK, 1 row affected (0.02 sec)
步骤3：transcation 1(发现是能够读取到事务2的写入数据，如果没有间隙锁，事务2插入id为2和6之间的数据就会出现幻读)
mysql> select * from a;
±—±-----+
| id | name |
±—±-----+
| 0 | 1 |
| 2 | 3 |
| 6 | 7 |
| 8 | 9 |
| 10 | 11 |
| 4 | 33 |
±—±-----+
6 rows in set (0.00 sec)
步骤4：transcation 2
mysql> insert into a values(12,12);
Query OK, 1 row affected (0.01 sec)
步骤5：transcation 1 (发现此时查不到刚才事务2写入的数据，说明此时采用的快照读，而不是当前读，所以快照读还是当前读是由语句发起时根据语句的类型决定的，而不是事务启动的时候决定的，)
mysql> select * from a;
±—±-----+
| id | name |
±—±-----+
| 0 | 1 |
| 2 | 3 |
| 6 | 7 |
| 8 | 9 |
| 10 | 11 |
| 4 | 33 |
±—±-----+
6 rows in set (0.00 sec)
步骤6：transcation 1
mysql> update a set name=20 where id=8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from a;(发现还是查不到（12,12）的数据，但此时可以看到name=20的修改，此时对此记录采取的是当前读，对(12,12)的记录是采取快照读，如果你此时去update id=12的数据也是可以成功的，因为此时对update的记录是采用当前读，会读取已经提交的数据)
±—±-----+
| id | name |
±—±-----+
| 0 | 1 |
| 2 | 3 |
| 6 | 7 |
| 10 | 11 |
| 8 | 20 |
| 4 | 33 |
±—±-----+
6 rows in set (0.00 sec)
mysql> update a set name=100 where id=12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from a;
±—±-----+
| id | name |
±—±-----+
| 0 | 1 |
| 2 | 3 |
| 6 | 7 |
| 10 | 11 |
| 8 | 20 |
| 4 | 33 |
| 12 | 100 |
±—±-----+
7 rows in set (0.00 sec)
2、对于主键索引的等值锁定，不会有间隙锁
步骤1：transcation 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a where id=2 for update;
±—±-----+
| id | name |
±—±-----+
| 2 | 3 |
±—±-----+
1 row in set (0.00 sec)
步骤2：transcation 2 （自动提交）
mysql> insert into a values(1,1);
Query OK, 1 row affected (0.01 sec)
步骤3：transcation 1
mysql> select * from a;
±—±-----+
| id | name |
±—±-----+
| 0 | 1 |
| 1 | 1 |
| 2 | 3 |
| 4 | 5 |
| 6 | 7 |
| 8 | 9 |
±—±-----+
6 rows in set (0.00 sec)
3、对于主键索引范围锁定，会产生间隙锁，按锁是加在索引上的原理，所以锁住的范围是(2,6]，符合左开右闭原则，但对于非主键上的数据不产生间隙锁，只有闭区间此行数据对应的记录锁
步骤1：transcation 1

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

mysql> select * from a where id<5 and id>2 for update;
±—±-----+
| id | name |
±—±-----+
| 4 | 5 |
±—±-----+
1 row in set (0.00 sec)
步骤2：
（1）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update a set id=1 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
（2）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values(1,1);
Query OK, 1 row affected (0.00 sec)
（3）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values(3,1);
^CC – query aborted
ERROR 1317 (70100): Query execution was interrupted
（4）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values(5,5);
^CC – query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>
（5）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values(1,4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from a;
±—±-----+
| id | name |
±—±-----+
| 0 | 1 |
| 2 | 3 |
| 1 | 4 |
| 4 | 5 |
| 6 | 7 |
| 8 | 9 |
±—±-----+
6 rows in set (0.00 sec)
（6）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update a set id=7 where id=6;
^CC – query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>
（7）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update a set name=10 where name=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
（8）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update a set name=10 where name=7;
^CC – query aborted
ERROR 1317 (70100): Query execution was interrupted
4、二级索引等值锁定,首先在二级索引name上会锁住间隙（3,7)，然后锁住符合条件记录，只要写入的数据不在此间隙则可以写入，写入的数据是先按二级索引顺序排列后再按主键顺序决定写入的位置，所以只要数据不是在此间隙即可
步骤1：transcation 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a where name=5 for update;
±—±-----+
| id | name |
±—±-----+
| 4 | 5 |
±—±-----+
1 row in set (0.00 sec)
步骤2：transcation 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values(3,3);
^CC – query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values(3,1);
Query OK, 1 row affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values(1,3);
Query OK, 1 row affected (0.00 sec)
(2)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values(10,6);
^CC – query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values(5,12);
Query OK, 1 row affected (0.00 sec)
(3)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update a set id=1 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(4)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update a set id=7 where id=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(5)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update a set name=10 where name=7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
（6）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values(5,7);
^CC – query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values(7,7);
Query OK, 1 row affected (0.00 sec)
5、二级索引范围锁定,除了锁定符合记录(4,5)还会锁定name上的间隙(3,5),(5,7]，所以name上锁定的范围是(3,7]，至于主键和name的值根据name排列后不出现在此间隙就可以写入
步骤1：transcation 1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a where name>3 and name<7 for update;
±—±-----+
| id | name |
±—±-----+
| 4 | 5 |
±—±-----+
1 row in set (0.00 sec)
步骤2：transcation 2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update a set name=2 where name=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
步骤3：transcation 2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update a set name=10 where name=7;
^CC – query aborted
ERROR 1317 (70100): Query execution was interrupted
步骤4：transcation 2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values(3,3);
^CC – query aborted
ERROR 1317 (70100): Query execution was interrupted
步骤5：transcation 2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values(1,3);
Query OK, 1 row affected (0.00 sec)
步骤6：transcation 2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values(5,7);
^CC – query aborted
ERROR 1317 (70100): Query execution was interrupted
步骤7：transcation 2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values(7,7);
Query OK, 1 row affected (0.00 sec)
6、二级索引update一条不存在的数据，锁定的范围是name的间隙，不包括前后的记录
步骤1: transcation 1:
MySQL [wjz]> begin;
Query OK, 0 rows affected (0.00 sec)
MySQL [wjz]> update a set id=11 where name=6;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
步骤2: transcation 2:
MySQL [wjz]> update a set id=3 where name=5; ----说明name=5的记录并没有被锁
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MySQL [wjz]> update a set id=11 where name=5;
^CCtrl-C – query killed. Continuing normally.
ERROR 1317 (70100): Query execution was interrupted

MySQL [wjz]> update a set id=7 where name=7; ----说明name=7的记录并没有被锁
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MySQL [wjz]> update a set id=5 where name=7; ----说明在name(5,7)的间隙被锁了
^CCtrl-C – query killed. Continuing normally.
ERROR 1317 (70100): Query execution was interrupted

7、主键索引update一条不存在的记录，最终锁也只是锁在主键的间隙上(8,15)
步骤1: transcation 1
insert into a values (15,15);
MySQL [wjz]> select * from a;
±—±-----+
| id | name |
±—±-----+
| 0 | 1 |
| 2 | 3 |
| 4 | 5 |
| 6 | 7 |
| 8 | 9 |
| 15 | 15 |
±—±-----+
6 rows in set (0.00 sec)
begin;
MySQL [wjz]> update a set name =17 where id=12;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0

步骤2: transcation 2
begin;
MySQL [wjz]> insert into a values(11,11); 说明间隙无法插入
^CCtrl-C – query killed. Continuing normally.
ERROR 1317 (70100): Query execution was interrupted

MySQL [wjz]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [wjz]> update a set id=17 where id=15; 说明15的记录没有加锁
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MySQL [wjz]> update a set name=8 where id=8; 说明8的记录没有加锁
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0