Mysql的加锁情况详解

最近在复习mysql的知识点，像索引、优化、主从复制这些很容易就激活了脑海里尘封的知识，但是在mysql锁的这一块真的是忘的一干二净，一点映像都没有，感觉也有点太难理解了，但是还是想把这块给啃下来，于是想通过这篇文章把复盘过程中所有的知识点给记录下来，特别是实操部分

一、mysql的锁介绍

mysql里有一堆锁，但是会存在一些概念上的交叉，所以这里只列举出常用的，或者说面试中常问的锁进行介绍，如下：

共享锁

共享锁又叫读锁，S锁，加了共享锁的数据还可以被其他事务加共享锁，但是不能被其他事务加排他锁，主要用于保证数据在读取过程中的一致性。可通过lock in share mode显示的给sql加上共享锁

select * from t lock in share mode;

排他锁

排他锁又叫写锁，X锁，加了排他锁的数据不能被其他事务再加排他锁或者共享锁，直到该排他锁被释放；可以通过for update显示的给sql加排他锁，当然，实际工作中不会使用for update，mysql在执行update、insert、delete等DML操作的时候会自动加排他锁；

select * from t for update;

记录锁(Record Locks)

记录锁也可以理解成行锁，作用在索引上，只锁某一行记录

间隙锁

间隙锁锁定的是索引记录锁之间的间隙，或者是索引最小值之前后者最大值之后的间隙，只在RR级别以上被使用；只阻止其他事务插入到间隙中，他们不阻止其他事务在同一个间隙上获得间隙锁

临键锁

临键锁=记录锁+间隙锁

二、加锁情况实操

约定：

1. 关闭事务自动提交，我们需要在事务里观察加锁情况：set autocommit=0
2. 使用show engine innodb status\G观察锁的情况
3. 查看mysql关于锁日志的输出是否打开(默认关闭)：show variables like ‘innodb_status_output%’;
4. 打开开关：set global innodb_status_output_locks=on; set global innodb_status_output=on;
5. 只演示mysql的默认隔离界别RR下锁的情况，RC隔离级别大部分加的都是行锁
6. mysql的表一定会有聚簇索引的，有主键使用主键作为聚簇索引，没主键使用唯一且非空列作为聚簇索引，否则使用自己维护的rowid作为聚簇索引
7. mysql的锁都是加在索引上的

1. 表无显式主键和索引

数据准备

create table t(id int default null,name char(20) default null);
insert into t values(10,'10'),(20,'20'),(30,'30');

不加任何查询条件的查询

• sql

begin;
select * from t for update;
show engine innodb status\G

• 图示
  
• 分析
  • 表t没有主键没有唯一非空列，所以会创建一条聚簇索引
  • 查询的是全表，没有限制任何条件，所以加的是聚簇索引的临键锁，由于聚簇索引是rowid，所以范围这里我们看不懂
  • 由于没有主键和索引，所以加上条件的查询和不加条件是一样的，这里就不再重复展示了

添加条件查询

• sql

begin;
select * from t where id = 10 for update;
show engine innodb status\G

• 图示同上
• 分析
  • 可以发现，从结果上课与不加条件加锁情况是一致的，都是加的聚簇索引的临键锁；
  • 限制了条件和全表查询一样是因为mysql锁是加在索引上的，并不是加在数据10上的；
  • 不加的话理论上在插入一条id=10的记录也是可以的，这一就出现幻读了

2. 表有显式主键无索引

数据准备

create table t2(id int primary key not null,name char(20) default null);
insert into t2 values(10,'10'),(20,'20'),(30,'30');

不带任何条件的查询

• sql

begin;
select * from t2 for update;
show engine innodb status\G

• 图示
  
• 分析
  • 结果与1中一样，区别在于这里的id是主键，聚簇索引就是id了，在图里就能显示的看到临建锁的范围了，虽然是16进制的，转换后就是表里对应的10，20，30

通过id查询

• sql

begin;
select * from t2 where id = 10 for update;
show engine innodb status\G

• 图示
  
• 分析
  • 可以看到，此情况只加了id的记录锁，因为id就是聚簇索引，锁住这一行记录就可以了

3. 表无显式主键有索引

数据准备

create table t3(id int default null,name char(20) default null);
create index idx_id on t3(id);
insert into t3 values(10,'10'),(20,'20'),(30,'30');

不带任何条件的查询

与1.2类似，加的都是聚簇索引的临键锁

带普通索引ID的查询

• sql

begin;
select * from t3 where id = 10 for update;
show engine innodb status\G

• 图示
  
• 分析
  • 如图，会加聚簇索引的记录所，id的临建锁，id的间隙锁，锁的范围如上图，由此可见我们此时可以插入30之后的数据，不能插入10之前的数据，如下图：
    

带唯一索引的id查询

• sql

create table t4(id int default null,name char(20) default null);
create unique index idx_id on t4(id);
insert into t4 values(10,'10'),(20,'20'),(30,'30');
begin;
select * from t4 where id = 10 for update;
show engine innodb status\G

• 图示
  
• 分析
  • 因为是唯一索引，且可以为空，所以mysql还会创建聚簇索引，所以此时加的是唯一索引id的记录锁，聚簇索引

4.表有显式主键和普通索引

数据准备

create table t5(id int not null,name char(20) default null,primary key(id),key idx_name(name));
insert into t5 values(10,'10'),(20,'20'),(30,'30');

不带任何条件的查询

• sql

begin;
select * from t5 for update;
show engine innodb status\G

• 图示
  

• 分析

  • 主键id的记录锁(锁表里的每行记录)，普通索引name的临键锁

where条件是普通索引字段

• sql

begin;
select * from t5 where name='10' for update;
show engine innodb status\G

• 图示
  
• 分析
  • 主键id的记录锁(锁id=10一条记录)、普通索引name的临键锁，范围：(-∞,10]，普通索引name的间隙锁：(10,20)

where条件是主键字段

• sql

begin;
select * from t5 where id=10 for update;
show engine innodb status\G

• 图示
  

• 分析

  • 查询条件是主键的话就简单了，直接加id的记录锁即可

where条件同时包含普通索引字段和主键索引字段

• sql

begin;
select * from t5 where id=10 and name='10' for update;
show engine innodb status\G

• 分析
  • 与单查主键一致，都只是上主键id的记录锁

5.表有显式主键和唯一索引

数据准备

create table t6(id int not null,name char(20) default null,primary key(id),unique key idx_name(name));
insert into t6 values(10,'10'),(20,'20'),(30,'30');

不带条件查询

• sql

begin;
select * from t6 for update;
show engine innodb status\G

• 图示
  
• 分析
  与普通索引类似，会加主键的记录锁，锁定表里的每条记录，然后加唯一索引的临键锁，范围是：(-∞,10],(10,20],(20,30],(30,10)

where条件是唯一索引字段

• sql

begin;
select * from t6 where name='10' for update;
show engine innodb status\G

• 图示
  
• 分析
  • 两个行锁，分别是主键索引的行锁和唯一索引的行锁

where条件是主键

• sql

begin;
select * from t6 where id=10 for update;
show engine innodb status\G

• 分析
  • 只会加主键的行锁

where条件是主键和唯一索引

• sql

begin;
select * from t6 where id=10 and name='10' for update;
show engine innodb status\G

• 分析
  • 与只加主键一致，还是只会加主键的行锁；一般涉及到主键的查询都会只锁主键的行锁

总结

至此，mysql加锁的分析就结束了，其实挺绕的，实际总是与自己想的不一致，但是要秉持一个原则，就是mysql的锁除了保证并发事务的情况下对数据的安全读写外，还会用来解决幻读问题；有时候从幻读的角度出发，那么也就理解了为什么会这样加锁了