头图

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

木子雷
English

一、前言:

今天遇到一个事情,两个程序员对 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语句重现使两个程序员着迷的问题:

4.1、首先使用 navicat 可视化工具连接上创建的数据库;
4.2、然后开启 两个 命令列介面 :

4.3、然后先在第一个命令介面中手动开启一个事务,然后在另一个介面中也开启另一个事务:
start transaction ;
4.4、然后再在第一个命令介面中执行下面 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');
4.5、然后接着在另一个命令介面中执行下面这个新增数据的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、执行结果解析:

3.1、trx_id为 235430:

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

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

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

3.2、trx_id为 235435:

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

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

3.3、结论 + 疑问?

①、结论: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 读提交;

设置事务的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条件的行记录锁释放掉,最后如下所示:

2.1、知识面扩展:

其实目前大多数的互联网项目中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 具体的魔力时,也能明白本文中传递的一个重要内容,不要拿来主义,多实践;

还有就是大家在阅读本文时,也要跟着实操一遍,遇到与本文中描述的不一样的地方时,也要保持着怀疑态度,心想,这块是不是博主写错啦;

大家在查阅资料时,尽可能去官网查阅相关资料,因为官网的资料全且清晰正确;

六、参考资料:

①、INFORMATION_SCHEMA INNODB_TRX表

②、InnoDB Locking

③、INFORMATION_SCHEMA INNODB_LOCKS表

❤ 点赞 + 评论 + 转发 哟

如果本文对您有帮助的话,请挥动下您爱发财的小手点下赞呀,您的支持就是我不断创作的动力,谢谢啦!

您可以微信搜索 【木子雷】 公众号,大量Java学习干货文章,您可以来瞧一瞧哟!

阅读 518

Web后端码仔,记录生活,分享技术!

118 声望
259 粉丝
0 条评论
你知道吗?

Web后端码仔,记录生活,分享技术!

118 声望
259 粉丝
宣传栏