MySQL45讲读书笔记讲读书笔记 40讲讲insert语句的锁为什么这么多语句的锁为什么这么多
一一 序序
本文属于极客时间MySQL45讲读书笔记系列，本文老师主要讲了特殊的insert 情况。
MySQL对自增主键锁做了优化，尽量在申请到自增id以后，就释放自增锁。还有些insert语句是属于“特殊情况”的，在执行过程中需要给其他资源加锁，或者无法在申请到自增id以后
就立马释放自增锁。
二二 insert … select 语句语句
我们先从昨天的问题说起吧。表t和t2的表结构、初始化数据语句如下，今天的例子我们还是针对这两个表展开。
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
现在，我们一起来看看为什么在可重复读隔离级别下，binlog_format=statement时执行：
insert into t2(c,d) select c,d from t;
这个语句时，需要对表t的所有行和间隙加锁呢？
其实，这个问题我们需要考虑的还是日志和数据的一致性。我们看下这个执行序列：
图1 并发insert场景
实际的执行效果是，如果session B先执行，由于这个语句对表t主键索引加了(-∞,1]这个next-key lock，会在语句执行完成后，才允许session A的insert语句执行。
但如果没有锁的话，就可能出现session B的insert语句先执行，但是后写入binlog的情况。于是，在binlog_format=statement的情况下，binlog里面就记录了这样的语句序列：
insert into t values(-1,-1,-1);
insert into t2(c,d) select c,d from t;
这个语句到了备库执行，就会把id=-1这一行也写到表t2中，出现主备不一致。
insert 循环写入循环写入
当然了，执行insert … select 的时候，对目标表也不是锁全表，而是只锁住需要访问的资源。
如果现在有这么一个需求：要往表t2中插入一行数据，这一行的c值是表t中c值的最大值加1。
此时，我们可以这么写这条SQL语句 ：
insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1);
这个语句的加锁范围，就是表t索引c上的(4,supremum]这个next-key lock和主键索引上id=4这一行。
它的执行流程也比较简单，从表t中按照索引c倒序，扫描第一行，拿到结果写入到表t2中。
因此整条语句的扫描行数是1。
这个语句执行的慢查询日志（slow log），如下图所示：
图2 慢查询日志--将数据插入表t2
通过这个慢查询日志，我们看到Rows_examined=1，正好验证了执行这条语句的扫描行数为1。
那么，如果我们是要把这样的一行数据插入到表t中的话：
insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);
语句的执行流程是怎样的？扫描行数又是多少呢？
这时候，我们再看慢查询日志就会发现不对了。
图3 慢查询日志--将数据插入表t
可以看到，这时候的Rows_examined的值是5。
我在前面的文章中提到过，希望你都能够学会用explain的结果来“脑补”整条语句的执行过程。今天，我们就来一起试试。
如图4所示就是这条语句的explain结果。
评论0
最新资源