关于mysql:两个程序员为-insert-into-select-着迷一晚它到底有什么魔力

8次阅读

共计 5938 个字符，预计需要花费 15 分钟才能阅读完成。

明天遇到一个事件，两个程序员对 insert into select 着迷一晚，这到底是为神魔呢？上面就简略说说原由；

当天，在 MySql 数据库 中，程序员 A 应用 insert into select 对某张表中的一部分数据做备份，而后同时程序员 B 应用程序对这张表做数据插入操作，最初呈现了程序员 B 的插入操作失败的状况，而后他们就钻研了这个问题一早晨，最终找到了导致问题呈现的起因。

上面就通过简略的模仿场景复现问题，而后并钻研呈现问题的起因，最初再找到防止问题呈现的办法；

①、场景重现：模仿问题呈现的场景

②、问题求解：钻研问题呈现的起因

③、最终后果：防止问题呈现的办法

呈现问题时应用的 mysql 数据库的版本是 5.7.16-log；

不分明本人应用的 mysql 版本的能够应用上面的语句进行查问，只有版本差不多就能够；

select version();

留神：创立一个新的数据库，保障这个库只有本人操作，次要是为了更加直观的重现问题及钻研呈现问题的起因，防止其它的烦扰因素；

建表 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;

在 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();

通过下面三步曾经把呈现问题时的根本环境还原，而后就应用 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 没有索引，所以间接就是应用表锁喽；

然而大家须要晓得的是，网上的材料千千万万，存在谬误论断的也是不少的，所以说，咱们不能当拿来主义，须要本人在实际操作下验证真伪，并且这样也会加深本人的了解；

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

②、而后再开启一个查问界面，输出上面的 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;

字段解析：

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.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 读提交；

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

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

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

除了对 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 学习干货文章，您能够来瞧一瞧哟！

正文完

mysql

发表至： mysql

2021-05-15

0

关于mysql:InnoDB之锁类型

关于mysql:记一次Mysql大表清理操作

关于mysql:MySQL架构二-MySQL体系架构

关于mysql:数据库运维实操优质文章分享含OracleMySQL等-2023年6月刊

关于java:23种设计模式Java版第八篇

关于mysql:两个程序员为-insert-into-select-着迷一晚它到底有什么魔力

一、前言：

本文主线：

二、场景重现：模仿问题呈现的场景

1、保障环境的统一：

2、创立测试的表：

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

4、重现问题场景：

4.1、首先应用 navicat 可视化工具连贯上创立的数据库；

4.2、而后开启两个命令列介面：

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

4.4、而后再在第一个命令介面中执行上面 insert into select 这个备份 SQL 语句：

4.5、而后接着在另一个命令介面中执行上面这个新增数据的 SQL 语句：

三、问题求解：钻研问题呈现的起因

1、上面咱们就实际操作下，验证是否加的表锁：

2、执行后果如下：

3、执行后果解析：

3.1、trx_id 为 235430：

3.2、trx_id 为 235435：

3.3、论断 + 疑难？

四、最终后果：防止问题呈现的办法

1、防止问题呈现办法一：创立索引

2、防止问题呈现办法二：设置隔离级别为 RC

2.1、知识面扩大：

五、总结：

六、参考资料：

❤ 点赞 + 评论 + 转发哟

站内搜索

关于mysql:两个程序员为-insert-into-select-着迷一晚它到底有什么魔力

一、前言：

本文主线：

二、场景重现：模仿问题呈现的场景

1、保障环境的统一：

2、创立测试的表：

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

4、重现问题场景：

4.1、首先应用 navicat 可视化工具连贯上创立的数据库；

4.2、而后开启 两个 命令列介面：

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

4.4、而后再在第一个命令介面中执行上面 insert into select 这个备份 SQL 语句：

4.5、而后接着在另一个命令介面中执行上面这个新增数据的 SQL 语句：

三、问题求解：钻研问题呈现的起因

1、上面咱们就实际操作下，验证是否加的 表锁：

2、执行后果如下：

3、执行后果解析：

3.1、trx_id 为 235430：

3.2、trx_id 为 235435：

3.3、论断 + 疑难？

四、最终后果：防止问题呈现的办法

1、防止问题呈现办法一：创立索引

2、防止问题呈现办法二：设置隔离级别为 RC

2.1、知识面扩大：

五、总结：

六、参考资料：

❤ 点赞 + 评论 + 转发 哟

站内搜索

4.2、而后开启两个命令列介面：

1、上面咱们就实际操作下，验证是否加的表锁：

❤ 点赞 + 评论 + 转发哟