两万字详解InnoDB的锁( 五 ) _InnoDB

文章插图

3.4 查询条件列无索引+RC隔离级别如果id没有加索引，只是一个常规的列，在RC（读提交隔离级别下），又加了什么锁呢？

若id列上没有索引，MySQL会走聚簇索引进行全表扫描过滤。每条记录都会加上X锁。但是，为了效率考虑，MySQL在这方面进行了改进，在扫描过程中，若记录不满足过滤条件，会进行解锁操作。同时优化违背了2PL原则。

初始化下表结构和数据

create table t4 (name varchar(16),id int,primary key (name));insert into t4 values('a',1),('c',3),('b',6),('e',6),('d',9);

加锁示意图图下：

文章插图

验证流程如下，先开启事务会话A，先执行以下操作：
begin;//删除id=6的这条记录delete from t4 where id = 6;接着开启事务会话B

begin;//可以执行，MySQL因为效率问题，解锁了update t4 set name='f' where id=3;//阻塞等待update t4 set name='f' where id=6;

验证结果如下：

文章插图

3.5 查询条件是主键+RR隔离级别给定SQL：delete from t1 where id = 6;，如果id是主键的话，在RR隔离级别下，跟RC隔离级别，加锁是一样的，也都是在id = 10这条记录上加上X锁。大家感兴趣可以照着3.1小节例子，自己验证一下哈。
3.6 查询条件是唯一索引+RR隔离级别给定SQL：delete from t1 where id = 6;，如果id是唯一索引的话，在RR隔离级别下，跟RC隔离级别，加锁也是一样的哈，加了两个X锁，id唯一索引满足条件的记录上一个，对应的主键索引上的记录一个。
3.7 查询条件是普通索引+RR隔离级别如果查询条件是普通的二级索引，在RR（可重复读的隔离级别下），除了会加X锁，还会加间隙Gap锁。Gap锁的提出，是为了解决幻读问题引入的，它是一种加在两个索引之间的锁。
假设有表结构和数据如下：

CREATE TABLE t5 ( id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL, PRIMARY KEY (id), KEY c (c)) ENGINE=InnoDB;insert into t5 values(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

如果一条更新语句update t5 set d=d+1 where c = 10，加锁示意图如下：

文章插图

我们来验证一下吧，先开启事务会话A，先执行以下操作：
begin;update t5 set d=d+1 where c = 10;接着开启事务会话B
begin;insert into t5 values(12,12,12);//阻塞等待，最后超时释放锁了验证流程图如下：

文章插图

为什么会阻塞呢？因此c=10这个记录更新时，不仅会有两把X锁，还会把区间（10,15）加间隙锁，因此要插入（12,12,12）记录时，会阻塞。
3.8 查询条件无索引+RR隔离级别如果查询条件列没有索引呢？又是如何加的锁呢？
假设有表结构和数据如下：

CREATE TABLE t5 ( id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL, PRIMARY KEY (id)) ENGINE=InnoDB;insert into t5 values(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

给定一条更新语句update t5 set d=d+1 where c = 10，因为c列没有索引，加锁示意图如下：

文章插图

如果查询条件列没有索引，主键索引的所有记录，都将加上X锁，每条记录间也都加上间隙Gap锁。大家可以想象一下，任何加锁并发的SQL，都是不能执行的，全表都是锁死的状态。如果表的数据量大，那效率就更低。

在这种情况下，MySQL做了一些优化，即semi-consistent read，对于不满足条件的记录，MySQL提前释放锁，同时Gap锁也会释放。而semi-consistent read是如何触发的呢：要么在Read Committed隔离级别下；要么在Repeatable Read隔离级别下，设置了
innodb_locks_unsafe_for_binlog参数。但是semi-consistent read本身也会带来其他的问题，不建议使用。

我们来验证一下哈，先开启事务会话A，先执行以下操作：
begin;update t5 set d=d+1 where c = 20;接着开启事务会话B
begin;insert into t5 values(16,16,16);//插入阻塞update t5 set d=d+1 where c = 16;//更新阻塞我们去更新一条不存在的c=16的记录，也会被X锁阻塞的。验证如下：