两个程序员为 insert into select 着迷一晚，它到底有什么魔力？

2022-09-18 16:35:25

前言

今天遇到一个事情，两个程序员对 insert into select 着迷一晚，这究竟是为神魔呢？先来简单说说原由；当天，在 MySql数据库 中，程序员A使用 insert into select 对某张表中的一部分数据做备份，然后同时程序员B使用程序对这张表做数据插入操作，最后出现了程序员B的插入操作失败的情况，然后他们就研究了这个问题一晚上，最终找到了导致问题出现的原因。

下面就通过简单的模拟场景复现问题，然后并研究出现问题的原因，最后再找到避免问题出现的方法；

本文主线：

场景重现：模拟问题出现的场景
问题求解：研究问题出现的原因
最终结果：避免问题出现的方法

场景重现：模拟问题出现的场景

1、保证环境的一致：

出现问题时使用的mysql数据库的版本是 5.7.16-log，如果你对自己使用的mysql版本不清楚，那么可以使用下面的语句进行查询，只要版本差不多就可以；

select version();

这里需要注意一点：我们创建一个新的数据库，保证这个库只有自己操作，主要是为了避免其它的干扰因素，可以更加直观的重现问题及研究出现问题的原因。

2、创建测试的表：

建表SQL如下：

日志表：

DROP TABLE IF EXISTS `t_log`;
CREATE TABLE `t_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `log` varchar(1024) DEFAULT NULL COMMENT '日志内容',
  `createts` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

日志备份表：

DROP TABLE IF EXISTS `t_log_back`;
CREATE TABLE `t_log_back` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `log` varchar(1024) DEFAULT NULL COMMENT '日志内容',
  `createts` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3、构造用于测试备份的数据：

在 t_log 表中新增 20 万条数据用于备份时使用；使用存储过程在表中插入数据；

# 如果存储过程存在则删除 
DROP PROCEDURE IF EXISTS proc_initData;

DELIMITER $
# 创建存储过程
CREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT  DEFAULT 1;
    WHILE i<=200000 DO   # 循环遍历插入的次数，循环遍历20万次
        INSERT INTO t_log ( log ) VALUES ( CONCAT('测试日志log', i) ); #执行的插入sql语句
        SET i = i+1;
    END WHILE;
END $

# 调用存储过程
CALL proc_initData();

4、重现问题场景：

现在我们已经通过上面三步把出现问题时的基本环境复原了，然后来使用SQL语句重现让两个程序员着迷的问题：

① 首先使用 navicat 可视化工具连接上创建的数据库；

② 然后开启两个命令列介面：

③ 先在第一个命令介面中手动开启一个事务，然后在另一个介面中也开启另一个事务：

start transaction ;

④ 然后再在第一个命令介面中执行下面 insert into select 这个备份SQL语句：

注意：createts 创建时间筛选条件值需要改为自己表中对应的数据；

INSERT INTO t_log_back  SELECT * FROM t_log WHERE CREATETS > STR_TO_DATE('2021-05-11 12:28:16','%Y-%m-%d %H:%i:%s') and CREATETS < STR_TO_DATE('2021-05-11 12:35:33','%Y-%m-%d %H:%i:%s');

⑤ 接着在另一个命令介面中执行下面这个新增数据的SQL语句：

insert into t_log (log) values('insert into select test log');

此时会发现，insert 语句会被阻塞，直至超时插入失败，为什么会这样呢？

可能有对这方面有些了解的同学，或许知道这是由于你在第一个事务中使用 insert into select 进行备份时，没有commit 提交事务，导致 t_log 表一直处于 被锁住 的状态，所以其它事务再进行写操作时被阻塞；

答案确实是由于第一个事务没有提交导致其它事务的写操作被阻塞，这也跟两个程序员出现的问题情形一样，程序员A使用 insert into select 备份数据时，备份的数据量太大，导致事务长时间没有完成，最终导致程序员B的插入操作超时失败；

所以这也是网上很多资料都说尽量避免长事务的原因，因为会阻塞其它并发执行的写事务；大家看到这里会不会有两个疑问？

疑问一：insert into log 执行时导致锁表，这个时候是直接使用的表锁实现的锁表吗？

注意： innodb 存储引擎支持行锁的，并且大家在网上查阅资料时应该都会查到 “innodb的行锁是基于索引实现的：只有where条件存在索引时，InnoDB才使用行级锁，否则，InnoDB将直接使用表锁！” ，这句话真的对吗？；

疑问二：在不同的事务隔离级别下，表锁、行锁是否存在区别？

存在区别，主要是行锁在RC读提交、RR可重复读隔离级别下存在区别，具体区别下文有说明；

问题求解：研究问题出现的原因

通过上面的场景重现，已经将问题复现了，并且知道了是由于存在长事务导致表被锁住了，进而导致了其它事务的写操作阻塞超时失败；知道原因后，再研究下，到底是不是直接使用的表锁将表锁住的？

通过上面提到的网上查阅的资料，如果 where条件中不存在索引 ，那么就会直接使用表锁，那么按这么说的话在 insert into select 中使用 createts 创建时间字段进行查询数据时，由于 createts 没有索引，所以直接就是使用表锁喽；

但是大家需要知道的是，网上的资料千千万万，也不是所有的都是正确的，所以说我们不能做拿来主义，需要在自己的实践操作下验证真伪，并且这样也可以加深自己的理解；

1、下面我们就实践操作下，验证是否加的是表锁：

① 首先重新执行下重现问题场景的那五步；

② 然后再开启一个查询界面，查询当前事务中所持有的锁的信息，SQL语句如下：

select trx.trx_id, trx.trx_state, trx.trx_started, trx.trx_query, locks.lock_id, locks.lock_mode, locks.lock_type, locks.lock_table, locks.lock_index, trx.trx_rows_locked, trx.trx_isolation_level
from information_schema.INNODB_TRX trx, INFORMATION_SCHEMA.INNODB_LOCKS locks where trx.trx_id = locks.lock_trx_id

注意事项：

只有在SQL语句处于阻塞中时，执行下面的SQL语句才会得到锁的数据，所以执行完那五步后，需要立刻执行上面这个语句；
除了执行上面的SQL语句得到事务、锁信息外，还可以直接查询下innodb存储引擎的状态，在其中的事务模块得到需要的信息，语句如下：

show engine innodb status;

2、执行结果如下：

字段解析：

trx_id 事务id
trx_state 事务状态 LOCK_WAIT：阻塞等待，RUNNING：运行中
trx_started 事务开始时间
trx_query 事务中执行的SQL语句
lock_id 事务所持有的锁的id
lock_mode 锁的模式 X ：排他锁（写锁），S：共享锁（读锁）
lock_type 锁的类型 RECORD：行锁（记录锁），TABLE：表锁
lock_table 被加锁的表
lock_index 行锁使用到的索引，表锁时为 null
trx_rows_locked 此事务锁定的 大概数目或行数 。该值可能包括物理上存在但对事务不可见的带有删除标记的行。
trx_isolation_level 此事务使用的隔离级别

3、执行结果解析：

① trx_id为 235430：

通过执行结果得知，事务id为 235430 是执行 insert into select 的事务，由于此事务中SQL已执行完毕，所以在 trx_query 字段没有展示出具体SQL语句；

此事务持有的是行锁，通过全遍历 聚簇索引 将表中全部的行记录加行锁，锁类型是共享读锁，trx_rows_locked 字段展示的值大概就是lock_table 被加锁的表中全部的数据量；

此事务使用的隔离级别是 REPEATABLE READ 可重复读 ；

② trx_id为 235435：

事务id为 235435 是执行 insert 新增数据的事务，此事务被阻塞，当进行插入数据时，也是通过遍历 聚簇索引 加行锁，锁类型是排它锁；

此事务使用的隔离级别是 REPEATABLE READ 可重复读 ；

③ 结论 + 疑问？

结论：innodb的行锁是基于索引的，但是如果where条件中不存在索引的话，它会直接根据聚簇索引加行锁，每个表中都有主键，即使没有手动添加主键，mysql也会自动生成一个rowid（自增的）作为默认主键的；

所以不是直接加的表锁，是加的行锁；

疑问：如果是对表中现有数据加行锁，那么 insert 新增数据时，是在最后面顺序插入数据，按理说不应该被阻塞的呀；

解答：需要知道当前事务使用的隔离级别是 RR可重复读 ，在RR隔离级别下是存在 间隙锁 的，在聚簇索引中除了加行锁之外，还会默认加上间隙锁，通过 行锁+间隙锁 实现锁表；具体加锁如下图展示：

在mysql中行锁+间隙锁构成一种新的锁，叫做 Next-Key Locks，它除了可以在RR隔离级别下实现锁表，也用来避免幻读的发生；

注意：间隙锁是不存在于 Read Uncommited(RU) 读未提交、Read Committed (RC) 读提交 隔离级别下的；

那要是间隙锁在RC读提交隔离级别下不存在，是不是在RC下就不会锁表了呢？并且也就不会导致文中说的问题啦？我们下面来研究下。

最终结果：避免问题出现的方法

我们通过上面的介绍，可以得出两种可能能够避免问题出现的方法，但是需要去实践下：

RR隔离级别下，在 createts 创建时间字段上创建二级索引；
将当前数据库的事务隔离级别设置为 RC 读提交；

1、避免问题出现方法一：创建索引

RR隔离级别下，在 createts 字段上创建索引，重新执行下 重现问题场景 的那五步，发现insert新增数据时事务没有被阻塞；

因为innodb的行锁是基于索引的，所以如果where条件字段存在索引的话，会先根据二级索引筛选出主键值，然后再回表到聚簇索引中对筛选出的主键值对应的记录加行锁，以及加行锁的记录之间默认加上间隙锁；

通过下图展示，发现最后面的间隙没有上锁，所以insert操作就不会被阻塞了，加锁展示如下：

2、避免问题出现方法二：设置隔离级别为RC

除了对where条件字段创建索引外，还可以直接将当前数据库事务的隔离级别设置为 RC 读提交。SQL语句如下：

SET  global  TRANSACTION  ISOLATION  LEVEL  Read committed;

设置完事务的RC隔离级别后，重新执行下 重现问题场景 的那五步，发现insert新增数据时事务没有被阻塞，说明此方式也是可行的；

并且通过查阅官网资料得知：使用 RC 隔离级别时，MySQL评估WHERE条件后，将会释放不匹配行的记录锁；具体如下所示：

在RC隔离级别下执行下面这个SQL语句：

INSERT INTO t_log_back  SELECT * FROM t_log WHERE CREATETS > STR_TO_DATE('2021-05-11','%Y-%m-%d') and CREATETS < STR_TO_DATE('2021-05-13','%Y-%m-%d');

先扫描聚簇索引加行锁：

然后MySql进行评估优化后，将不满足where条件的行记录锁释放掉，最后如下所示

知识面扩展：

其实目前大多数的互联网项目中mysql事务隔离级别都是使用的RC读提交，一是因为在大多数场景下使用RC都是可以的，二是像Oracle数据库默认的事务隔离级别也是 RC 读提交，大家在使用Oracle数据库时也是直接使用，没有去修改过其隔离级别；

那mysql为什么将默认的事务隔离级别设置为 RR 呢？

主要是因为mysql一个遗留的历史问题导致，因为在RC隔离级别下，使用 statement 格式的 binlog 进行主从同步时，会导致主从数据不一致；但是后面binlog提供了 row 等格式，这时在RC下就可以避免数据不一致问题了。所以如果将事务的隔离级别设置为 RC 读提交的话，并且当前也需要使用binlog 进行主从同步的话，需要将binlog日志的格式改为 row；

查看当前数据库的binlog的格式的SQL语句：

show variables like 'binlog_format'

修改当前数据库的binlog的格式的SQL语句：

set globle binlog_format='ROW'

总结

至此，本文已经将 insert into select 具有的魔力聊完了；

读完本文后，希望大家在明白了 insert into select 具体的魔力时，也能明白本文中传递的一个重要内容，多实践，不要做“拿来主义”。大家在阅读时，也可以跟着实操一遍，在遇到与本文中描述的不一样的地方时，也要保持着怀疑态度，心想，这个帅比博主在这块是不是写错啦；而一旦你通过自己的实践证明这个怀疑的对错，就可以让你一直处于成长的状态！

最后，记得一键三连哟，如果本文对您有帮助的话，请挥动下您爱发财的小手点下呀，您的支持就是我不断动力，谢谢啦！

码农公寓

前言