间隙锁（gap lock）个人实验（一）

我们都知道间隙锁会锁掉操作表上，可能被修改的数据。那如果修改时，使用了子查询，子查询上的数据该如何上锁呢。

以下是实验的表结构与索引结构

mysql> desc otb;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x     | int(11) | NO   | PRI | NULL    |       |
| y     | int(11) | YES  |     | NULL    |       |
| z     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.12 sec)


mysql> desc itb;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x     | int(11) | NO   | PRI | NULL    |       |
| y     | int(11) | YES  |     | NULL    |       |
| z     | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql> show index from otb;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| otb   |          0 | PRIMARY  |            1 | x           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.10 sec)


mysql> show index from itb;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| itb   |          0 | PRIMARY  |            1 | x           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| itb   |          1 | z        |            1 | z           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)


mysql> insert into otb values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0


mysql> insert into itb values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

session1开启事物，不提交

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


mysql> update otb set y=y+10 where x =(select x from itb where y=1);
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> 
----------------

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


mysql> update otb set y=y+10 where x=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> 

等待超时，事物2被事物1阻塞。这里只确定了子查询的该行记录被锁住了。

我们重置环境从新实验

session1依旧执行完update后不提交

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


mysql> update otb set y=y+10 where x =(select x from itb where y=1);
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> 
----------------------------
session2我们尝试修改到gap上
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> update itb set y=1 where x=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> 

结果是不行的，可见子查询上被加上了gap锁。

我开始以为在对修改行数据上X锁时，就会顺带锁掉子查询的相关记录

但是其实并不是这样

session1我们开启一个事物，并用for update锁住改行数据

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


mysql> select * from otb where x =(select x from itb where y=1) for update;
+---+------+------+
| x | y    | z    |
+---+------+------+
| 1 |    1 |    1 |
+---+------+------+
1 row in set (0.00 sec)

-------------------------------------
session2我们对子查询表进行操作，发现可以修改相关行的数据

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


mysql> update itb set y=1 where x=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--------------------------------------
session1再对改行数据进行update

mysql> select * from otb where x =(select x from itb where y=1) for update;
+---+------+------+
| x | y    | z    |
+---+------+------+
| 1 |    1 |    1 |
+---+------+------+
1 row in set (0.08 sec)


mysql> update otb set y=y+10 where x =(select x from itb where y=1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> 

可以看到session1事物先开启，反倒被session2的事物阻塞。

可见for update的情况下只加了record lock，即是说如果其他事物先其修改子查询相关行的数据，他将会被阻塞。

猜测：gap lock是在查询的过程中，对所有查询经过的或可能经过的数据进行上锁。