MySQL进阶系列：不同隔离级别下加锁情况
MySQL锁-不同隔离级别下加锁情况
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.21 |
+-----------+
1 row in set (0.01 sec)
前置设置和命令：
-- 查看当前隔离级别
select @@transaction_isolation;
-- 查看innodb相关的配置
show variables like '%innodb_status%';
-- 开启锁的日志：
set global innodb_status_output_locks=on;
-- 改成事务手动提交
set autocommit=0;
-- 查看innodb引擎的信息(包含锁的信息)
show engine innodb status\G;
-- 设置当前会话的隔离级别是RC
SET session TRANSACTION ISOLATION LEVEL READ COMMITTED;
锁模式的含义：
- 记录锁（LOCK_REC_NOT_GAP）: lock_mode X locks rec but not gap
- 间隙锁（LOCK_GAP）: lock_mode X locks gap before rec
- Next-key 锁（LOCK_ORNIDARY）: lock_mode X
- 插入意向锁（LOCK_INSERT_INTENTION）: lock_mode X locks gap before rec insert intention
- 意向排它锁（IX）：lock mode IX
隔离级别：
RR：REPEATABLE-READ(可重复读)
RC：READ-COMMITTED(读已提交)
1、RR隔离级别+表无显式主键和索引
创建表lock1，没有索引和主键，并插入测试数据
create table lock1(id int default null,name char(20) default null);
insert into lock1 values(10,'10'),(20,'20'),(30,'30');
手动开启事务，执行语句并采用for update方式（当前读）
begin;
select * from lock1 for update;
show engine innodb status\G;
从返回的信息中，可以看到对表添加了IX锁和4个记录锁，表中的三行记录上分别添加了Next-key Lock锁，防止有数据变化发生幻读，例如进行了更新、删除操作。
同时会出现“ 0: len 8; hex 73757072656d756d; asc supremum;;”这样的描述信息，此操作也是为了防止幻读，会将最大索引值之后的间隙锁住并用supremum表示高于表中任何一个索引的值，也就是锁的范围是(30,+∞)。
同表下，如果加上where条件之后，是否会产生Next-key Lock呢？执行如下语句：
begin;
select * from lock1 where id = 10 for update;
show engine innodb status\G;
从上述反馈信息中，可以发现跟不加where条件的加锁情况是一样的，会同时出现多个行的临键锁和supremum，这到底是为什么呢？
出现supremum的原因是：虽然where的条件是10，但是每次插入记录时所需要生成的聚簇索引Row_id还是自增的，每次都会在表的最后插入，所以就有可能插入id=10这条记录，因此要添加一个supremum防止数据插入。
出现其他行的临键锁的原因是：为了防止幻读，如果不添加Next-Key Lock锁，这时若有其他会话执行DELETE或者UPDATE语句，则都会造成幻读。
2、RR隔离级别+表有显式主键无索引
创建如下表并添加数据：
create table lock2(id int primary key not null,name char(20) default null);
insert into lock2 values(10,'10'),(20,'20'),(30,'30');
在此情况下要分为三种情况来进行分析，不同情况的加锁方式也不同：
1、不带where条件
begin;
select * from lock2 for update;
show engine innodb status\G;
通过上述信息可以看到，与之前的加锁方式是相同的。
2、where条件是主键字段
begin;
select * from lock2 where id = 10 for update;
show engine innodb status\G;
通过上述信息可以看到，只会对表中添加IX锁和对主键添加了记录锁（X locks rec but not gap）,并且只锁住了where条件id=10这条记录，因为主键已经保证了唯一性，所以在插入时就不会插入id=10这条记录。
3、where条件包含主键字段和非关键字段
begin;
select * from lock2 where id = 10 and name = '10' for update;
show engine innodb status\G;
通过看到，加锁方式与where条件是主键字段的加锁方式相同，因为根据主键字段可以直接定位一条记录。
在RR隔离级别+表有显式主键无索引：如果where条件使用主键而且能唯一确定一条记录就加记录锁，否则还是给所有记录加临键锁。
3、RR隔离级别+表无显式主键有索引
1、不带where条件
跟之前的情况类似，这里就不多说了
2、where条件是普通索引字段或者（普通索引字段+非索引字段）
创建如下表：
create table lock3(id int default null,name char(20) default null);
create index idx_id on lock3(id);
insert into lock3 values(10,'10'),(20,'20'),(30,'30');
执行如下语句：
begin;
select * from lock3 where id = 10 for update;
show engine innodb status\G;
通过上述信息可以看到，对表添加了IX锁，对id=10的索引添加了Next-Key Lock锁，区间是负无穷到10，对索引对应的聚集索引添加了X记录锁，为了防止幻读，对索引记录区间（10，20）添加间隙锁。
此时大家可以开启一个新的事务，插入负无穷到id=19的全部记录都会被阻塞，而大于等于20 的值不会被阻塞
3、where条件是唯一索引字段或者（唯一索引字段+非索引字段）
创建如下表：
create table lock4(id int default null,name char(20) default null);
create unique index idx_id on lock4(id);
insert into lock4 values(10,'10'),(20,'20'),(30,'30');
执行如下语句：
begin;
select * from lock4 where id = 10 for update;
show engine innodb status\G;
通过上述信息可以看到，此方式与where条件是主键字段的加锁情况相同，表无显式主键则会把唯一索引作为主键，因为是主键，所以不能再插入id=10这条记录，因此也不需要间隙锁。
4、RR隔离级别+表有显式主键和索引
此情况可以分为以下几种：
1、表有显式主键和普通索引
创建如下表：
create table lock5(id int not null,name char(20) default null,primary key(id),key idx_name(name));
insert into lock5 values(10,'10'),(20,'20'),(30,'30');
(1)不带where条件
begin;
select * from lock5 for update;
show engine innodb status\G;
通过上述信息可以看到，首先对表添加IX锁，然后对supremum添加临键锁，对name索引列添加临键锁，对主键索引添加X记录锁
(2)where条件是普通索引字段
begin;
select * from lock5 where name='10' for update;
show engine innodb status\G;
通过上述信息可以看到，首先对表添加IX锁，然后对name添加临键锁，对主键索引列添加X记录锁，为了防止幻读，对name的（10，20）添加间隙锁。
(3)where条件是主键字段
begin;
select * from lock5 where id=10 for update;
show engine innodb status\G
通过上述信息可以看到，对表添加了意向锁，对主键添加了记录锁。
(4)where条件同时包含普通索引字段和主键索引字段
begin;
select * from lock5 where id=10 and name='10' for update;
show engine innodb status\G
如图，和上面情况一致，需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是普通索引，那么跟普通字段是类似的，其实本质点就在于加锁的字段不同而已。
2、表有显式主键和唯一索引
创建如下表：
create table lock6(id int not null,name char(20) default null,primary key(id),unique key idx_name(name));
insert into lock6 values(10,'10'),(20,'20'),(30,'30');
(1)不带where条件
begin;
select * from lock6 for update;
show engine innodb status\G
通过上述信息可以看到，首先对表添加IX锁，然后对supremum添加临键锁，对name索引列添加临键锁，对主键索引添加X记录锁
(2)where条件是唯一索引字段
begin;
select * from lock6 where name='10' for update;
show engine innodb status\G
通过上述信息可以看到，首先对表添加IX锁，然后对name和主键添加行锁。
(3)where条件是主键字段
begin;
select * from lock6 where id=10 for update;
show engine innodb status\G
通过上述信息可以看到，首先对表添加IX锁，然后主键添加行锁
(4)where条件是唯一索引字段和主键字段
begin;
select * from lock6 where id=10 and name='10' for update;
show engine innodb status\G
此处需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是唯一索引，那么跟唯一索引字段是一样的，其实本质点就在于加锁的字段不同而已。
5、RC隔离级别+表无显式主键和索引
创建表lock7,没有索引和主键，并插入测试数据
create table lock7(id int default null,name char(20) default null);
insert into lock7 values(10,'10'),(20,'20'),(30,'30');
手动开启事务，执行语句并采用for update方式（当前读）
begin;
select * from lock7 for update;
show engine innodb status\G
通过上述信息可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（聚簇索引）
同表下，如果加上where条件之后，是否会产生Next-key Lock呢？执行如下语句：
begin;
select * from lock7 where id = 10 for update;
show engine innodb status\G
通过上述信息可以看到，首先对表添加IX锁，然后会对聚集索引添加记录锁，因为RC隔离级别无法解决幻读问题，所以不会添加临键锁。
6、RC隔离级别+表有显式主键无索引
创建如下表并添加数据：
create table lock8(id int primary key not null,name char(20) default null);
insert into lock8 values(10,'10'),(20,'20'),(30,'30');
在此情况下要分为三种情况来进行分析，不同情况的加锁方式也不同：
1、不带where条件
begin;
select * from lock8 for update;
show engine innodb status\G
通过上述信息可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（主键）
2、where条件是主键字段
begin;
select * from lock8 where id = 10 for update;
show engine innodb status\G
通过上述信息可以看到，首先对表添加IX锁，然后对表id=10的记录添加记录锁
3、where条件包含主键字段和非关键字段
begin;
select * from lock8 where id = 10 and name = '10' for update;
show engine innodb status\G
通过上述信息可以看到，首先对表添加IX锁，然后对表id=10的记录添加记录锁
7、RC隔离级别+表无显式主键有索引
创建如下表：
create table lock9(id int default null,name char(20) default null);
create index idx_id on lock9(id);
insert into lock9 values(10,'10'),(20,'20'),(30,'30');
1、不带where条件，跟之前的情况类似
begin;
select * from t9 for update;
show engine innodb status\G
通过上述信息可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（聚簇索引）
2、where条件是普通索引字段或者（普通索引字段+非索引字段）
执行如下语句：
begin;
select * from t9 where id = 10 for update;
show engine innodb status\G
通过上述信息可以看到，对表添加了IX锁，对id=10的索引添加了记录锁，对索引对应的聚集索引添加了记录锁。
3、where条件是唯一索引字段或者（唯一索引字段+非索引字段）
创建如下表：
create table lock10(id int default null,name char(20) default null);
create unique index idx_id on lock10(id);
insert into lock10 values(10,'10'),(20,'20'),(30,'30');
执行如下语句：
begin;
select * from lock10 where id = 10 for update;
show engine innodb status\G
通过上述信息可以看到，对表添加了IX锁，对id=10的索引添加了记录锁，对索引对应的聚集索引添加了记录锁。
8、RC隔离级别+表有显式主键和索引
此情况可以分为以下几种：
1、表有显式主键和普通索引
创建如下表：
create table lock11(id int not null,name char(20) default null,primary key(id),key idx_name(name));
insert into lock11 values(10,'10'),(20,'20'),(30,'30');
(1)不带where条件
begin;
select * from lock11 for update;
show engine innodb status\G
通过上述信息可以看到，首先对表添加IX锁，总共6个行锁，其中3个是对name索引列添加记录锁，另外3个是对主键索引添加X记录锁。
(2)where条件是普通索引字段
begin;
select * from lock11 where name='10' for update;
show engine innodb status\G
通过上述信息可以看到，首先对表添加IX锁，然后对name添加X记录锁，对主键索引列添加X记录锁
(3)where条件是主键字段
begin;
select * from lock11 where id=10 for update;
show engine innodb status\G
通过上述信息可以看到，对表添加了意向锁，对主键添加了记录锁。
(4)where条件同时包含普通索引字段和主键索引字段
begin;
select * from lock11 where id=10 and name='10' for update;
show engine innodb status\G
通过上述信息可以看到，和单独使用主键效果一样：对表添加了意向锁，对主键添加了记录锁。
此处需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是普通索引，那么跟普通字段是类似的，其实本质点就在于加锁的字段不同而已。
2、表有显式主键和唯一索引
创建如下表：
create table lock12(id int not null,name char(20) default null,primary key(id),unique key idx_name(name));
insert into lock12 values(10,'10'),(20,'20'),(30,'30');
(1)不带where条件
begin;
select * from lock12 for update;
show engine innodb status\G
通过上述信息可以看到，首先对表添加IX锁，然后对name索引列添加X记录锁，对主键索引添加X记录锁
(2)where条件是唯一索引字段
begin;
select * from lock12 where name='10' for update;
show engine innodb status\G
通过上述信息可以看到，首先对表添加IX锁，然后对name和主键分别添加记录锁。
(3)where条件是主键字段
begin;
select * from lock12 where id=10 for update;
show engine innodb status\G
通过上述信息可以看到，首先对表添加IX锁，然后主键添加记录锁。
(4)where条件是唯一索引字段和主键字段
begin;
select * from lock12 where id=10 and name='10' for update;
show engine innodb status\G
通过上述信息可以看到，和上面的加锁情况一样，首先对表添加IX锁，然后主键添加记录锁。
此处需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是唯一索引，那么跟唯一索引字段是一样的，其实本质点就在于加锁的字段不同而已。
简单总结：
在RR模式下有间隙锁和临键锁，可以解决快照读和当前读导致的幻读问题。
在RC模式下，此模式下不能解决幻读问题，所以只有记录锁，没有临键锁。添加的是主键的记录锁或者索引的记录锁
我是纪先生，用输出倒逼输入而持续学习，持续分享技术系列文章，以及全网值得收藏好文，欢迎关注公众号，做一个持续成长的技术人。
mysql进阶系列历史文章
3. MySQL进阶系列：mysql中MyISAM和InnoDB有什么区别；
4. MySQL进阶系列：mysql中表设计如何更好的选择数据类型；
5. MySQL进阶系列：数据库设计中的范式究竟该如何使用；
6. MySQL进阶系列：一文详解explain各字段含义；
7. MySQL进阶系列：为什么mysql使用B+作为索引的数据结构；
15. MySQL进阶系列：你需要了解的几种MySQL日志；
转载自:https://juejin.cn/post/7129344329830629389