登录
首页 >  数据库 >  MySQL

Innodb间隙锁实战

来源:SegmentFault

时间:2023-01-25 08:19:45 489浏览 收藏

小伙伴们有没有觉得学习数据库很有意思?有意思就对了!今天就给大家带来《Innodb间隙锁实战》,以下内容将会涉及到MySQL、Java,若是在学习中对其中部分知识点有疑问,或许看了本文就能帮到你!

锁概念

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操作会检查插入的下一条记录是否被锁定;
  • 唯一索引的情况,直接使用行锁,只对当前行加锁;
  • 具体使用哪些锁和我们使用的查询有很大关系;
  • 查出来记录中的多个字段都会启动锁机制,就算查询条件里面没有这个字段;
  • 对于查询条件有多个的情况,会根据执行计划选择索引,然后选择对应的锁机制。

好了,本文到此结束,带大家了解了《Innodb间隙锁实战》,希望本文对你有所帮助!关注golang学习网公众号,给大家分享更多数据库知识!

声明:本文转载于:SegmentFault 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>
评论列表