insert操作加锁场景分析

实验环境：

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t

insert...select

在RR隔离级别下，binlog为statement。执行以下批量插入语句时需要给t表的所有行记录和间隙都上锁。

insert into t2(c,d) select c,d from t; 

假设不给t表上锁，考虑这样一个时序：

1. A线程开启事务，此时是快照读，因此视图不会改变。
2. B线程开启事务，往t表插入一条数据：insert into t ()。并提交事务，记录binlog
3. A线程执行insert into t2 select t ，因为是RR隔离级别，因此插入时select看不到t表中B线程新插入的数据
4. A事务提交，记录binlog

备库会根据事务提交记录的binlog顺序进行恢复，先执行B事务往t表插入当行数据，然后执行A事务向t2批量插入，此时因为没有MVCC支持，对于t2来说t表新插入的数据也是可见的，所以这条数据也会被插入t2表。导致主备不一致、

💡加锁保证了事务提交的顺序，而事务提交顺序决定了binlog写入顺序。主库在执行事务时可见性会受到MVCC的制约；而备库应用binlog恢复时没有mvcc，因此可见性只由事务的提交顺序决定。

insert 循环写入

需求：需要往t2表中插入一行数据，这一行的c值时表t中c值的最大值加1：

insert into t2(c,d) (select c+1,d from t force index(c) order by c desc limit 1);

这条语句的加锁范围就是c索引上next-key lock(c=4，c=supremum]和主键上id=4的行锁。

而如果是循环写入问题，也就是读t表的数据，并且插入到t表中，SQL语句如下：

insert into t(c,d) (select c+1,d from t force index(c) order by c desc limit 1);

1. 首先创建一个临时表，表中有字段c和d(临时表是为了防止insert插入t表后会干扰select的查询结果)
2. 从索引C逆序取出4个id，然后回表取出四条记录，将每条记录的c和d写入临时表。此时Rows_examined=4
3. 此时limit 1，所以会从临时表取出第一行放到表t中。此时Rows_examined加1变成5

也就是说即使c字段上存在索引，也还是需要对t表进行全表扫描。此时C字段上所有间隙都会加上nect-key-lock(目的是为了防止binlog造成的主备不一致)。

✨优化方法：先将select的结果insert into到内存临时表，此时因为C字段上建立了索引，因此取到c最大值对应的id后，只需要回表扫描一行记录。最后再把内存临时表的最终结果插入到t表。

create temporary table temp_t i(c int,d int) engine=memory;
insert into temp_t (select c+1,d from t force index(c) order by c desc limit 1);
insert into t select * from temp_t;
drop table temp_t;

insert 唯一键冲突

共享锁：同一条记录允许持有多把共享读锁，读操作需要获取共享锁。

排他锁：同一条记录只允许持有一把排他写锁，写操作需要获取排他锁。排他锁和共享锁是互斥的。

执行insert语句插入的表存在唯一性约束时，逻辑如下：

1. 首先InnoDB会查看唯一性字段是否存在重复key值：
   • 如果存在则在唯一索引字段上添加重复值的next-key-lock(共享s锁，读间隙锁)，注意持有这个共享锁的是整个表(可以理解为永不会释放，从而保障唯一键上插入重复值就会报错)。
   • 如果不存在，则不加锁。
2. 执行insert插入数据的表更新操作：
   • 如果存在共享S锁，则出现读写锁冲突被阻塞。
   • 不存在共享读锁，则直接插入该条新的行记录，并添加排他写锁。

C字段上存在唯一索引约束，一种出现死锁的情况如下：

1. 线程A先执行insert语句，在C索引c=5直接添加排他写锁(唯一索引next-key-lock会退化)
2. 线程B执行时出现唯一键冲突(通过该条记录的最新版本号)，因此加上读锁；同样线程C也会加上读锁❓
3. 线程A回滚释放写锁。线程B和线程C都等待对方释放读锁，再执行插入操作。因此会造成死锁。

另外null值会使唯一性约束失效。

insert into ... on duplicate key update

假设表t中存在行记录(10,10,10)，(11,11,11)C字段建立唯一索引约束，然后执行以下语句：

insert into t values(11,10,10) on duplicate key updaet d=100;

首先给C字段的(5,10]添加排他的next -key lock写锁，然后执行后面更新操作。on duplicate key 指插入一行数据后，如果遇上唯一键约束，那么执行后面的更新语句。

💡如果插入的数据存在多个列字段的唯一性冲突，那么修改的行记录只会修改"第一个索引"冲突的行(按照索引排序)。在上面例子中，插入的记录同时导致id=11冲突和c=10冲突，因此id的索引位置更前面，因此只会修改(11,11,11)变为(11,100,11)。