mysql 间隙锁 作用,MySQL一例间隙锁分析

研究间隙锁的时候碰到一个问题1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18CREATE TABLE `t2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`a` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idx_a` (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> select * from t2;

+----+------+

| id | a |

+----+------+

| 1 | 2 |

| 3 | 4 |

| 6 | 5 |

| 8 | 5 |

| 10 | 5 |

| 13 | 11 |

+----+------+

T1：

1

2

3

4

5

6

7

8

9

10mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2 where a=4 for update;

+----+------+

| id | a |

+----+------+

| 3 | 4 |

+----+------+

1 row in set (0.00 sec)

T2：

1

2

3

4

5

6

7

8

9

10

11mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values (4, 5);

// 超时

mysql> insert into t2 values (7, 5);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values (null, 5);

Query OK, 1 row affected (0.00 sec)

问题：

为什么 T2 的第一个 insert 不能执行，间隙锁锁定难道不是区间 (4, 5] 吗？例子来自：

为什么 T2 第二个 insert 不能执行。第二个和第一个的 insert 只是因为 id 不同就最终的行为不同。第三条sql也可以执行。

表的索引结构是这样的：

间隙锁锁住的不是索引值的间隙，之前一直理解的是值的间隙。比如 where a=4，如果是值的间隙就是 (2, 4] 和 (4, 5]，这样理解不够完整。间隙锁住应该是索引的间隙，他锁住的是 B+ 树的节点，他的语义是「如果某两个索引项之间的间隙被锁住以后，这两个索引项之间在解锁前不能再写入新的索引项」，对照 t2 表在 a 上的索引，(a=2, id=1) 和 (a=5, id=6) 之间不能再写入新的索引项。

(id=4, a=5) 不能写入是因为他落在了间隙锁的间隙内了，他会被插到 (id=3, a=4) 和 (id=6, a=5) 之间。(id=7, a=5) 能写入是因为他会被插到 (id=6, a=5) 和 (id=8, a=5) 之间，这个区间不在间隙锁的间隙内，所以可以写入。 (id=null, a=5) 能写入和 (id=7, a=5) 的原因一样，id 列指定为 Null 时 mysql 会分配个自增的 id ，这个新的 id 显然不在间隙所的间隙内。

如果是 t2 没有主键：1

2

3

4

5

6

7

8

9

10mysql> CREATE TABLE `t_noid` (

-> `a` int(11) DEFAULT NULL,

-> KEY `idx_a` (`a`)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.02 sec)

mysql>

mysql> insert into t_noid values (1), (3), (5), (8), (11);

Query OK, 5 rows affected (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 0

T1：

1

2

3

4

5mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_noid where a=4 for update;

Empty set (0.00 sec)

T2：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_noid values (5);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t_noid values (2);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t_noid values (3);

^C^C -- query aborted

ERROR 1317 (70100): Query execution was interrupted

mysql> insert into t_noid values (4);

^C^C -- query aborted

ERROR 1317 (70100): Query execution was interrupted

没有主键的辅助索引是这样的：

insert 5 能执行是因为隐藏的 id 会递增 7，写入 (id=7, a=5) 不在间隙内。3 和 4 都在值的间隙内，无法写入。2 不在间隙内。

资料