关于mysql:Innodb间隙锁实战

49次阅读

共计 7069 个字符，预计需要花费 18 分钟才能阅读完成。

锁概念

InnoDB 存储引擎蕴含了三种行锁的算法，别离如下所示：

  • Record Lock：行锁，针对的是单行记录；
  • Gap Lock：间隙锁，锁定的是一个范畴，然而不蕴含记录自身；
  • Next-Key Lock：其实就是行锁 + 间隙锁，蕴含了记录自身和范畴；

为什么须要间隙锁

数据库个别都有四种隔离级别，其中最罕用的就是：已提交读 (Read committed) 和可反复读 (Repeatable read)；在已提交读隔离级别下会呈现不可反复读的景象，而在可反复读隔离级别下会呈现幻读(Phantom Read) 的景象；

幻读：同一事务下，间断执行两次同样的 SQL 可能会导致不同的后果；

Innodb 引擎在可反复读隔离级别下并不会呈现幻读的景象，这次要是因为 Innodb 提供了 多版本并发管制 MVCC和 间隙锁 ；常见的 快照读 其实就是应用的 MVCC，而 以后读 就应用了间隙锁；

以下实例有两点阐明：

  • Innodb 的可反复读隔离级别下，对以后读应用了间隙锁来解决幻读的问题，所以上面的实例都是基于默认隔离级别 RR；
  • Innodb 的锁机制都依赖索引，所以上面的实例围绕索引来开展；

实战

无索引的状况

首先创立一个无索引的表，并初始化数据：

mysql> create table t1(a int);
mysql> insert into t1 values(1),(3),(5);

启动事务 1，执行以后读：

mysql> begin;
mysql> select * from t1 where a=3 for update;

以上事务没有提交，再启动事务 2，以下语句都被阻塞：

mysql> select * from t1 where a=3 for update;
mysql> insert into t1 values(1);
mysql> insert into t1 values(2);
mysql> insert into t1 values(5);
mysql> insert into t1 values(7);

然而这时候去执行快照读还是能够的：

mysql> select * from t1 where a=3;

能够发现在没有索引的状况下，除了快照读什么都干不了，感觉像是表被锁住了，表锁分为读和写锁，在写锁的状况下快照读同样被锁住，而在读锁的状况下能够应用快照读，相似下面无索引的状况；

mysql> lock table t1 read;  ## 读锁
mysql> lock table t1 write;  ## 写锁
mysql> unlock tables;  ## 解锁

那是不是无索引的状况下就应用了表锁那，能够通过如下命令进行查看，首先看一下在表锁的状况下执行插入操作：

mysql> SHOW PROCESSLIST;
+-----+------+-----------------+------+---------+------+------------------------------+--------------------------+
| Id  | User | Host            | db   | Command | Time | State                        | Info                     |
+-----+------+-----------------+------+---------+------+------------------------------+--------------------------+
|  75 | ODBC | localhost:65316 | test | Query   |   98 | Waiting for table level lock | insert into t1 values(7) |
+-----+------+-----------------+------+---------+------+------------------------------+--------------------------+

能够发现状态一栏显示Waiting for table level lock，示意以后插入语句在期待表锁；再看一下无索引的状况：

mysql> SHOW PROCESSLIST;
+-----+------+-----------------+------+---------+------+--------+--------------------------+
| Id  | User | Host            | db   | Command | Time | State  | Info                     |
+-----+------+-----------------+------+---------+------+--------+--------------------------+
|  75 | ODBC | localhost:65316 | test | Query   |    6 | update | insert into t1 values(7) |
+-----+------+-----------------+------+---------+------+--------+--------------------------+

总结：以上状态并不是在期待表锁，其实 InnoDB 在没有任何索引的状况下，会应用 隐式的主键来进行锁定。

一般索引的状况

首先创立一个一般索引的表，并初始化数据：

mysql> create table t2 (a int,key(a));
mysql> insert into t2 values(1),(3),(5);

启动事务 1，执行以后读：

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

启动事务 2，以下语句都被阻塞：

mysql> select * from t2 where a=3 for update;
mysql> insert into t2 values(1);
mysql> insert into t2 values(2);
mysql> insert into t2 values(4);

以下语句不会被阻塞：

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

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

总结 ：能够发现在应用一般索引的状况下，锁定蕴含了行锁和间隙锁，行锁就是a=3 这行，间隙锁蕴含了 (1,3) 和(3,5)，留神这里两边都是开区间，所以以后读 a=1 和a=5都能够胜利；然而在插入数据的时候能够发现 a=5 能够插入，然而 a=1 不能插入，这是因为对 Insert 操作会查看插入的 下一条记录是否被锁定，如果锁定则无奈执行，否则能够执行。

惟一索引的状况

首先创立一个惟一索引的表，并初始化数据：

mysql> create table t3 (a int,unique key(a));
mysql> insert into t3 values (1),(3),(5);

启动事务 1，执行以后读：

mysql> begin;
mysql> select * from t3 where a=3 for update;

启动事务 2，以下语句都被阻塞：

mysql> select * from t3 where a=3 for update;

以下语句不会被阻塞：

mysql> select * from t3 where a=1 for update;
mysql> insert into t3 values(2);
mysql> insert into t3 values(4);
mysql> insert into t3 values(7);

总结 ：能够发现当是惟一索引的时候，这时候会 降级为行锁，只会锁定以后记录，其余插入 SQL 都不会被阻塞；同样主键索引成果也是一样的。

无索引 + 一般索引的状况

首先创立一个领有无索引和一般索引的表，并初始化数据：

mysql> create table t4 (a int,b int,key(a));
mysql> insert into t4 values(1,1),(5,5),(9,9);

因为存在多个字段的状况，所以这时候的加锁状况就和查问的条件无关了：

条件为 a 字段

启动事务 1，执行以后读：

mysql> begin;
mysql>  select * from t4 where a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

同时查出了 a = 5 和 b = 5 这条记录，所以会依据无索引和一般索引的状况加锁；启动事务 2，以下语句都被阻塞：

mysql> select * from t4 where a=5 for update;
mysql> insert into t4 values(1,5);
mysql> insert into t4 values(2,5);
mysql> insert into t4 values(8,5);

mysql> select * from t4 where b=1 for update;
mysql> select * from t4 where b=2 for update;
mysql> select * from t4 where b=9 for update;
mysql> select * from t4 where b=11 for update;

以下语句不会被阻塞：

mysql> insert into t4 values(9,5);
mysql> insert into t4 values(10,5);

这里 a 上有一般索引，所以成果和间接应用一般索引相似；然而同时查出来的后果还有 b，因为 b 上是无索引的，所以对于 b 的一系列操作都被阻塞了；

条件为 b 字段

启动事务 1，执行以后读：

mysql> begin;
mysql> select * from t4 where b=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

启动事务 2，以下语句都被阻塞：

mysql> insert into t4 values(9,5);
mysql> insert into t4 values(10,5);

mysql> select * from t4 where a=1 for update;
mysql> select * from t4 where a=9 for update;

能够发现条件扭转后，原来 a=5 条件下能够插入的数据，这 b=5 条件下不能插入；并且 a 条件下的两个开区间 1 和 9 也无奈查问了；应该是应用了 隐式的主键来进行锁定，整个表被锁住了；

条件为 a + b 字段

启动事务 1，执行以后读：

mysql> begin;
mysql> select * from t4 where b=5 and a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

启动事务 2，阻塞的 SQL 同条件为 a 的状况，Mysql 执行打算会优先应用有索引的字段；以下 SQL 都能够执行：

mysql> insert into t4 values(9,5);
mysql> insert into t4 values(10,5);

通过下面的实例，咱们大略能够上面两件事：

  • 具体应用哪些锁和咱们应用的查问有很大关系；
  • 查出来记录中的多个字段都会启动锁机制，就算查问条件外面没有这个字段；

惟一索引 + 一般索引的状况

首先创立一个领有惟一索引和一般索引的表，并初始化数据：

mysql> create table t5 (a int,b int,unique key(a),key(b));
mysql> insert into t5 values(1,1),(5,5),(9,9);

条件为 a 字段

mysql> begin;
mysql> select * from t5 where a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

这种状况根本和应用惟一索引一样，以下 SQL 都能够执行：

mysql> insert into t5 values(2,2);
mysql> insert into t5 values(10,2);
mysql> select * from t5 where a=1 for update;

阻塞的 SQL 次要蕴含如下：

mysql> select * from t5 where a=5 for update;
mysql> select * from t5 where b=5 for update;

条件为 b 字段

mysql> begin;
mysql>  select * from t5 where b=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

这种状况根本和应用一般索引一样，以下 SQL 会阻塞：

mysql> insert into t5 values(2,3);
mysql> insert into t5 values(2,7);
mysql> insert into t5 values(2,1);

以下插入 SQL 不会阻塞：

mysql> insert into t5 values(2,9);

条件为 a + b 字段

mysql> begin;
mysql> select * from t5 where b=5 and a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

此种状况根本和应用条件 a 一样，优先应用惟一索引，能够查看执行打算：

mysql> explain select * from t5 where b=5 and a=5 for update;
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | t5    | const | a,b           | a    | 5       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+

以上实例咱们发现在多索引的状况下，要看执行打算具体应用的什么索引，才会应用什么类型的锁；

组合索引的状况

首先创立领有组合索引的表，并初始化数据：

mysql> create table t6 (a int,b int,key(a,b));
mysql> insert into t6 values(1,1),(5,5),(9,9);

条件为 a 字段

首先开启事务一，执行如下 SQL：

mysql> begin;
mysql> select * from t6 where a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

启动事务 2，以下语句都被阻塞：

mysql> insert into t6 values(1,2);
mysql> insert into t6 values(2,2);
mysql> insert into t6 values(7,2);
mysql> insert into t6 values(9,2);

除了 (9,2) 这条记录其余和应用一般索引没有差异，这里 (9,2) 被阻塞的起因是因为组合索引的存在，导致 (9,9) 记录在 (9,2) 记录的前面，索引会被锁住，同理如果是 (9,10) 是不会被锁住的；

mysql> insert into t6 values(9,10);
mysql> insert into t6 values(10,2);

条件为 b 字段

首先开启事务一，执行如下 SQL：

mysql> begin;
mysql> select * from t6 where b=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

启动事务 2，以下语句都被阻塞：

mysql> insert into t6 values(9,2);
mysql> insert into t6 values(9,10);
mysql> insert into t6 values(10,2);

这个其实也好了解，因为应用的是组合索引，会依照最左匹配准则，这时候条件 b = 5 其实是无奈应用索引的，所以这时候和无索引的状况是一样的，以上语句都会被锁住；

条件为 a + b 字段

首先开启事务一，执行如下 SQL：

mysql> begin;
mysql> select * from t6 where b=5 and a=5 for update;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
+------+------+

启动事务 2，阻塞和不阻塞的状况基本一致，执行打算会走组合索引；

总结

通过以上的实例测试大抵做了以下几点总结：

  • 无索引的状况会应用隐式的主键来进行锁定，成果就是整个表被锁住了；
  • 一般索引的状况下会应用间隙锁，对以后值前后增加开区间的间隙锁，并且对以后值增加行锁；对 Insert 操作会查看插入的下一条记录是否被锁定；
  • 惟一索引的状况，间接应用行锁，只对以后行加锁；
  • 具体应用哪些锁和咱们应用的查问有很大关系；
  • 查出来记录中的多个字段都会启动锁机制，就算查问条件外面没有这个字段；
  • 对于查问条件有多个的状况，会依据执行打算抉择索引，而后抉择对应的锁机制。

正文完
mysql
发表至： mysql
2021-08-23
 0
关于mysql:Cant-Connect-to-MySQL-Server-on-IP-Address-10061-错误的解决方案
关于mysql:记一次通过id关联查询时过滤失效问题实际上是varchar和bigint转换问题
关于mysql:MySQL数据库DDLDML详解
关于mysql:统计科学之讲讲OC曲线是什么