MySQL InnoDB——锁（欢迎探讨）

MySQL InnoDB的7种锁

参看：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

1. 自增锁（AUTO-INC Lock）
2. 共享排他锁（Shared & Exclusive Lock）
3. 意向锁（Intention Lock）
4. 插入意向锁（Insert Intention Lock）
5. 记录锁（Record Lock）
6. 间隙锁（Gap Lock）
7. 临键锁（Next-Key Lock）

自增锁

小测试：自增id知多少？

§ 表： tb_user (id auto_inc pk , name)

§ 执行语句：

§ i nsert into tb_user values(1, “ 小刘 ”);

§ i nsert into tb_user (name) values(“ 张三 ”), (“ 李四 ”);

§ d elete from tb_user ;// 使用 delete 清空表

§ i nsert into tb_user values(0, “ 零号 ”);

§ insert into tb_user values(1, “ 小刘 ”);

§ insert into tb_user values(10, “ 张三 ”);

§ insert into tb_user (name) values (“ 李四 ”);

§ 请问最终“零号”、“小刘”、“李四”的 id 分别是多少？

A: 0,1,4     B:0,1,11    C:4,1,5    D:4,1,11

 

 答案：D

备注：

• delete清空表，自增主键不会重置（请复习truncate、drop的用法，进行对比）
• 默认情况下，自增id插入0、null或未指定，会生成新的自增值。（此处应特别注意的是0的插入，并没有跟自己预想的一样插入0。如果非得插入0，请参看sql_mode=NO_AUTO_VALUE_ON_ZERO）
• 自增值从最大值后边开始新增。（

  1. InnoDB引擎的自增值，在MySQL5.7及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+步长作为这个表当前的自增值: select max(ai_col) from table_name for update;

  2. 在MySQL8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值） 

思考：多个客户端并发请求生成自增id会阻塞吗？ 

 

还是刚才的表：

打开两个会话，两个事务并发执行：

Transaction A	Transaction B
begin;	begin;
insert into tb_user(name) values("zhangsan");
	insert into tb_user(name) values("zs");//阻塞？？
insert into tb_user(name) values("lisi");	rollback;
select * from tb_user where id > 11;
rollback;

 

通过实验可以发现并发执行的事务并没有相互阻塞生成自增id，那自增id的生成是怎么做到并发的呢？？

思考：实际使用中也经常发现，事务回滚，但是自增id并没有回滚，又是为何呢？

下面开始介绍今天要讲的第一个锁——自增锁

• 自增锁是一种特殊的表级别锁（table-level lock），专门针对插入AUTO_INCREMENT类型的列。
• 自增锁并不是一个事务锁，而是每次使用完就马上释放，以便允许别的事务再申请。
• 5.1.22版本之前，自增锁的范围是语句级别，一个语句申请了自增锁，会等语句执行结束后才释放。
• 5.1.22版本引入新策略，轻量级互斥量机制，新增参数：innodb_autoinc_lock_mode
  • =0：表示采用之前的策略，即语句执行结束后才释放锁。
  • =1：默认。简单insert，申请后马上释放；类似insert … select/load data这种批量插入，插入前不能确定行数，还是要等语句执行结束后才释放（语句结束前一直持有锁）。
  • =2：所有插入操作都是申请后就释放锁，性能最高。（存在问题：值可能不连续，Statement-Base复制会出现问题，使用这种模式，需要使用Row-Base复制）

上文介绍中的模式=1中简单insert和批量insert对锁的使用情况是不一样的，这里做个说明，对插入进行分类，引用《MySQL技术内幕》中的：

其中：

• 对于根据语句就能确定行数的简单插入：在模式1和模式2，均直接使用互斥量对于申请的语句直接返回自增值；
• 对于不能确定行数的批量插入：模式1仍然使用自增锁，语句结束后，再释放。模式2完全放弃了锁，就会出现语句执行后生成的id不连续的问题，从而引起Statement-Base复制出现问题。

下面讲解下模式2在批量插入情况下为啥会出现问题：

仿照刚才的表tb_user创建表t2，执行insert ... select插入，如下图所示：

 

请问第5条数据的id是多少？？？

如果你认为第5条数据的id=5，那就说明接下来讲的对你还有点用。

先看下结果哈：

 

为啥id=8呢？？

因为，对于批量插入且不能确定行数的语句，MySQL有一个申请自增id的策略：分批申请。

• 语句执行过程中，第一次申请，会分配1个id
• 1个不够用，这个语句第二次申请，会再分配2个（此时总共生成了1+2个）
• 3个还不够用，还是这个语句，第三次申请，会再分配4个（此时总共生成了1+2+4个）
• 依次类推，同一个语句去申请，每次申请得到的id数都是上一次的两倍，直到够用，不再申请为止。

回到刚才的执行过程：

insert … select，实际上往表t2中插入了4行数据。但是，这四行数据是分三次申请:

第一次:id=1;

第二次被分配了id=2、3;

第三次被分配到id=4~7;

由于这条语句实际上只有4条语句，只需要用到4个id，所以id=5~7就被浪费掉了（但是并不会再退回去，给了就给了，别的客户端再申请，就接着往后给。思考：如果复制一个表数据的时候，使用这种自动生成id的方式，表数据量很大时，将可能浪费很多id，还是带着id一起复制插入比较好。）

因此，执行单条插入的第5条数据，只能获取到id=8。

 

到这里，模式2可能存在的问题就呼之欲出了：

假如有两个session：

A执行：insert into t2(name) select name from tb_user;

B执行：insert into t2(name) values (“我是5”);

 

因为在模式2，是申请完立即释放，sessionA申请按批申请，假如刚申请两次，即总共拿到了id=1,2,3；此时sessionB执行，拿到id=4；那么sessionA再申请只能拿到5~8。

对于A执行的这一条插入语句来说，id不连续。

 

但是，binlog里面对表t2的更新日志只有两种情况：要么先记A的，要么先记录B的。假如先记录A的，这个binlog拿到从库执行、或者用来恢复临时实例、备库里面，语句是串行执行的，A这个语句执行出来，生成的结果里面，id都是连续的。这时，这个库就和主库发生了数据不一致。

 

那这个问题有解决方案吗？

也是有的，如果配置模式2，binlog_format需要配置成Row-Base复制，即把整行数据，包括id一起复制。（因此，使用默认的1模式即可，在需要批量插入的时候，一般也不会去并发执行别的插入，因此没必要那么高的并发，批量插入使用自增锁锁住就是了，不会影响业务）

 

以上，就是自增锁相关的全部内容了。

 

共享/排他锁

InnoDB 实现了两种标准的行级锁：

• 共享锁（S）：事务拿到该锁才可以读取这一行；
• 排他锁（X）：事务拿到该锁才可以修改或者删除这一行。

共享排他锁的互斥关系如下：

	S	X
S	兼容	互斥
X	互斥	互斥

其中排他锁不与任何锁兼容，那存在的潜在问题：不能充分并行。

例如：事务A使用排他锁锁定了一行数据r，要进行修改或者删除，那么事务B想进行读取，必须等A释放排他锁才可以。

解决方案：MVCC非锁定一致性读（快照读）

底层通过undo log回滚段来实现。如果事务A 要对行r的数据进行更新的话，那么事务A要得到行的X锁，并把这一行之前的样子记录在undo log里面，这样一来如果事务A rollback 了就可以通过undo log 来恢复；说白了非锁定的一致性读就是读的该行undo log 中的内容，因此无需上锁，充分并行。

注意：不同隔离级别，读的快照也是差别很大的。

 

快照读差异（扩展）

这里先引用《MySQL技术内幕InnoDB引擎》一书中对此做的解释：

即：

• RC级别：总是读最新一份快照（已提交事务修改写入的）
• RR级别：总是读取事务开始时的行数据版本。

下面通过几个例子来验证下：

准备初始数据，RR级别：

-- 创建表
CREATE TABLE `read_view` (
  `txt` varchar(100) NOT NULL COMMENT '文本'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入一条语句
insert into read_view values(‘init’);

CASE 1：先查询，后插入

-- 两个会话开始事务：
session A：begin
session B：begin

-- session A执行一个查询，查任何表均可，这个查询的目的是创建一个当前时间点的快照。START TRANSACTION WITH CONSISTENT SNAPSHOT;也可以达到同样的效果。
session A：select * from tb_user;

-- session B插入一条记录并提交
insert into read_view values('after A select');
commit;

session A：select * from read_view;//查询不到B插入的记录，因为快照在B插入前创建。
session A：commit;
seesion A：select * from read_view;//可以查到

下面是具体的执行过程：

 

Case 2：A先开始但不查询，B插入数据并提交

-- 两个会话开始事务
session A:begin;
session B:begin;

-- session B在A查询前插入数据，并提交。
insert into read_view values(‘before A select’);
commit

-- A执行查询操作
session A：select * from read_view;//请问能查到B的数据吗？？？
session A：commit;
session A：select * from read_view;

请问A能查到B的数据吗？？

可以先看A虽然先开始事务，但是第一次查询是在B插入提交之后，因此，查询到了B插入的数据，说明快照不是事务开始时的（注意和书中所说不一致）

Case 3：

session A：START TRANSACTION WITH CONSISTENT SNAPSHOT;//开始事务并创建快照
session B：begin;

session A：select * from read_view;
-- 插入一条数据B
session B：insert into read_view values('B');
-- 更新init为大写
session B：update read_view set txt='INIT' where txt='init';
session B：commit;

session A：select * from read_view;//跟上一步查询结果一样

session A：update read_view set txt='#B#' where txt='B';//更新自己没有查到的行

session A：select * from read_view;//你们猜会查到什么？？
session A：commit;

A更新自己没有查到的，B刚刚插入进去的数据，此时查询到的结果是什么呢？？

 

请着重看事务A第三次查询的结果，发现有部分数据没变，有部分数据是自己没有查询到但是更新的。

这里事务A同一条查询语句，查询出来不同的结果，是否违反了可重复读级别呢？？

这里做个解释：这里并没有违法MVCC的非锁定一致性读，因为update操作属于当前读，可以查询查询到快照读没有查询到的数据并更新。更新的那行数据事务id已经换成了自己的id号，所以根据MVCC的规则是可以查询出来的（可以专门看下MVCC对于事务的读取规则）。

此种情况当成一种特殊情况看待即可，可以认为是RR级别下一种特殊的“幻读”。

 

意向锁

InnoDB支持多粒度锁，它允许行锁与表锁共存，实际应用中，InnoDB使用的是意向锁。

意向锁是一个表级别锁，指未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。虽然是表级锁，但是不与行级锁冲突。

• 意向共享锁（IS）：预示着，事务有意向对表中的某些行加S锁。如：select … lock in share mode;
• 意向排他锁（IX）：预示着，事务有意向对表中某些行加X锁。如：select … for update;

意向锁协议非常简单：

• 事务要想获得某些行的S锁，必须先获得表的IS锁；
• 事务要想获得某些行的X锁，必须先获得表的IX锁。

其互斥关系如下：

	IS	IX
IS	兼容	兼容
IX	兼容	兼容

What？你心里肯定有一个大大的问号，既然意向锁之间都相互兼容，那其意义在哪里呢？？？

 

那是因为：意向锁会与表级的共享排他锁互斥：

	S	X
IS	兼容	互斥
IX	互斥	互斥

当需要加一个表级锁时，不需要再每一行数据确认共享排他锁，只需要通过意向锁判断一次即可（切记：意向锁是表级的这点！！！）

事务A先获取了某一行排他锁，并未提交：

select * from tb_user where id = 10 for update;

此时：事务A获取了tb_user表的意向排他锁；同时获取了id=10的数据行上的排他锁。

 

之后，事务B想要获取tb_user表的共享锁：

lock tables tb_user read;

此时：事务B检测到A持有表的意向排他锁，因为意向排他锁和表级的共享锁互斥，B请求阻塞。

 

然后，事务C也想获取tb_user表的某一行排他锁：

select * from tb_user where id=20 for update;

此时：事务C申请tb_user表的意向排他锁，因为意向锁之间均兼容，可成功；因为id=20的行上并没有排他锁，因此事务C可以顺利获取该行排他锁，无需等待。

根据上述分析可发现，意向锁像一个桥梁，实现了表锁和行锁的共存。

 

排他锁是很强的锁，不与其他类型锁兼容。（这也很好理解，修改或删除某一行数据，必须加强互斥X锁，禁止并发操作该行数据，保证数据一致性）

对已有数据行的修改与删除，必须加强X锁，那么对于数据的插入，是否还需要加这么强的锁来实施互斥呢？？

 

 插入意向锁

插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。

RR级别下，有表：t_idx(id auto PK，name， age KEY);

注意age是普通索引。

此时执行如下操作：

-- 开始两个事务
session A:begin;
session B:begin;


-- 事务A先执行，在age4~10之间插入一行，不提交：
insert into t_idx(age,name) values(6,"six");

-- 事务B后执行，也在age4~10之间插入一行：
insert into t_idx(age,name) values(7,"seven");//会因为间隙锁的存在而阻塞吗？？

 

事务B会因为索引age上存在的间隙锁，被阻塞吗？？

 

后边会专门讲间隙锁，但是根据实际业务使用，大家知道在RR级别下，为了防止幻读，会对索引加间隙锁，那为什么这个示例中的操作并没有阻塞呢，因为此时使用的是：插入意向锁。

上图是官方文档的介绍，大致翻译下就是：

该锁用以表示插入意向，当多个事务在同一区间（gap）插入位置不同的多条数据时，事务之间不需要互相等待。假设存在两条值分别为 4 和 7 的记录，两个不同的事务分别试图插入值为 5 和 6 的两条记录，每个事务在获取插入行上独占的排他锁前，都会获取（4，7）之间的间隙锁，但是因为数据行之间并不冲突，所以两个事务之间并不会产生冲突（阻塞等待）。

总结来说，插入意向锁特性分成两部分：

① 插入意向锁是一种 特殊的间隙锁 —— 间隙锁可以锁定开区间内的部分记录 。

② 插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要 记录本身（主键、唯一索引）不冲突 ，那么事务之间就不会出现冲突等待。

 

将age换成唯一索引执行上诉相同的操作：

 

换成唯一索引后，同区间不同值，不阻塞；如果相同值会阻塞。

 

着重强调下：

虽然插入意向锁中含有意向两个字，但是它并不属于意向锁而属于间隙锁，因为意向锁是表锁而插入意向锁是行锁。

 

记录锁

记录锁，顾名思义，锁定对应的索引记录。

例如：select * from tb_user where id=1 for update;

会在id=1的索引记录上加锁，以阻止其他事务更新、删除id=1的这一行。

 

需要说明：select * from tb_user where id=1;属于快照读，并不会加锁。（但是串行化级别会隐式加lock in share mode）

 

 

间隙锁

间隙锁，InnoDB特有，它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

如果隔离级别降为读提交(Read Committed, RC)，间隙锁会自动失效。

锁住不存在的记录：

 

 

临键锁

临键锁，是Record锁与Gap锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。更具体的，临键锁会封锁索引记录本身，以及索引记录之前的区间。

 

准备初始数据：

-- 建表
CREATE TABLE `next_key`(
`id` int(11) NOT NULL AUTO_INCREMENT,   
`num` int(11) NOT NULL,   
PRIMARY KEY (`id`),   
KEY `idx_num` (`num`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入数据
insert into next_key(num) values(1),(4),(7),(10);

执行如下操作：

session A：begin;
session A：select * from next_key;
session A：select * from next_key where num=7 for update;

session B：begin;
session B：insert into next_key(num) values(2);
session B：insert into next_key(num) values(11);
session B：insert into next_key(num) values(4);
session B：insert into next_key(num) values(5);//被阻塞
session B：insert into next_key(num) values(9);//被阻塞
session B：insert into next_key(num) values(10);
session B：commit;

session A：select * from next_key;
session A：commit;

执行结果如下：

注意，如果将num改成唯一索引，则Next-Key将将降级为Record Lock，执行相同语句，发现只有7会被阻塞：

 

 

总结：各类SQL加锁分析

• 普通select：在RU、RC、RR级别下，使用快照读，不加锁。串行化级别下，升级为select…lock in share mode
• 加锁select：主要是指select … for update和select…lock in share mode.在唯一索引上使用唯一的查询条件，会使用记录锁，不使用gap锁和next-key锁。其他查询条件和普通索引，会使用gap锁和next-key锁，锁索引范围。
• update和delete：和加锁select类似，如果是唯一索引唯一查询条件，只加记录锁；否则，符合查询条件的索引记录之前都会加排他临键锁。
• insert：同样是写操作，insert和update、delete不同，它会用排他锁封锁被插入的记录，而不会封锁记录之前的范围。
  同时，会在插入区间加插入意向锁(insert intention lock)，但这个并不会真正封锁区间，也不会阻止相同区间的不同key插入。 (非唯一索引，无需加锁)

 

事务与锁

并发事务存在以下三个问题：

① 脏 读（读了未提交的数据）

② 不可重复读（同一事务相同查询，得到了不同的结果，重点在修改删除）

③ 幻 读（重点在新增）

 

InnoDB通过不同的锁策略来实现不同的隔离级别，分别解决以上问题：

• 读未提交（RU）

该级别下select语句不加锁，可能出现“脏读”，并发最高，一致性最差（不用）

• 串行化（Serializable）

所有select隐式转化为select … lock in share mode; 一致性最好，并发最差（不用）

• 读提交（RC）

常用，普通读是快照读，加锁select、update、delete等，只使用记录锁（外键约束检查以及重复键检查时会封锁区间）。

• 可重复读（RR）

InnoDB默认，普通读是快照读，加锁select、update、delete等，如果在唯一索引上使用了唯一的查询条件会使用记录锁，范围查询或者普通索引上，会使用gap锁和next-key锁，避免范围内插入记录。（注意insert操作的插入意向锁）

 

加锁小测试

问：下面两条简单的SQL，他们加了什么锁？

1：select * from t_user where id = 10;

2：delete from t_user where id = 10;

 

答：通过以上的学习，发现要回答以上问题，其实是很复杂的一件事情，因为不同的前提条件，得出的结论是不同的，直接回答加了某种锁，是不专业的。

 

例如：隔离级别是什么？

因为串行化下，连普通读也会隐式加lock in share mode，因此第一条语句在串行化下也会加锁。直接回答说快照读，不加锁，就是不对的。

还有，id是主键吗？不是主键的话，是索引吗？唯一索引还是普通索引？这些都将影响加锁的类型。