关于mysql insert into...select锁的问题

最新推荐文章于 2025-08-16 14:19:33 发布
原创 最新推荐文章于 2025-08-16 14:19:33 发布 · 268 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文探讨了在MySQL5.6和5.7环境下，不同事务隔离级别（REPEATABLE-READ与READ-COMMITTED）对行锁产生的影响。在REPEATABLE-READ下，事务会逐步产生行锁；而在READ-COMMITTED下，则不会产生行锁。同时，两种操作都会产生元数据锁。

环境：

mysql 5.6和5.7

binlog_format = 'ROW';

1：当隔离级别为REPEATABLE-READ时，逐步产生行锁

---TRANSACTION 14970094, ACTIVE 34 sec fetching rows
mysql tables in use 2, locked 2
15510 lock struct(s), heap size 1384656, 136906 row lock(s), undo log entries 121400

2：当隔离级别为READ-COMMITTED时,不产生行锁

---TRANSACTION 14973455, ACTIVE 9 sec inserting
mysql tables in use 2, locked 1
2 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 97603

注：两种操作都会产生元数据锁

 

 

MySQL死锁记录(INSERT INTO ... SELECT.... )
qq_45872465的博客
03-19 3668
一、前言 在线上发布新功能后，追踪了日志记录，发现有死锁的报错记录。因业务复杂，仅简化问题，先将问题简化复原。特在此记录解决分析过程。 先抛出问题，后附解决方案； 事务A 事务B 1、 insert into user_01 (name,age) select name,age user_02 1、update user_02 set name = ‘lisi’ where id = ‘2’ 2、update user_02 set name = ‘lisi’ where id
mysql特殊语法insert into .. on duplicate key update ..使用详解
健身变秃，coding变强
10-21 2万+
mysql特殊语法insert into .. on duplicate key update ..使用详解
关于MySQL insert into ... select 的锁情况
weixin_34302561的博客
04-28 614
摘要：      一直以为"insert into tb select * from tbx" 这样的导入操作是会把tbx表给锁住的，在锁期间是不允许任何操作（保证一致性）。看完这篇写的之后，发现tbx表是会被锁住，但这个锁有2种情况，现在逐一进行分析： 分析环境： root@127.0.0.1 : test 02:10:40>select @@global.tx_isolation...
mysql insert锁机制
热门推荐
zhanghongzheng3213的专栏
12-02 2万+
一、前言 上周遇到一个因insert而引发的死锁问题，其成因比较令人费解。 于是想要了解一下insert加锁机制，但是发现网上介绍的文章比较少且零散，挖掘过程比较忙乱。 本以为只需要系统学习一个较完全的逻辑，但是实际牵扯很多innodb锁相关知识及加锁方式。我好像并没有那么大的能耐，把各种场景的加锁过程一一列举并加之分析；亦没有太多的精力验证网上的言论的准确性。 只好根据现在了
mysql insert select 锁表_insert into select * from 锁表
weixin_28520495的博客
01-28 230
mysql[192.168.11.187] processid[249] root@localhost in db[zjzc] hold transaction time 197112069858,250,insert into ClientActionTrack20151125(clientSn,ip,url,httpMethod,requestParams,requestHeader,pa...
MySQL insert into select锁表的问题（上）
llliarby的博客
12-02 9668
MySQL insert into select锁表
mssql insert into 和insert into select性能比较
12-15
根据提供的描述，一次插入1190条数据时，使用 `INSERT INTO...VALUES` 需要大约510毫秒，而使用 `INSERT INTO...SELECT` 只需约16毫秒，这表明在处理大量数据时，`INSERT INTO...SELECT` 的性能显著优于 `INSERT ...
MySQL高级讲座篇之：`INSERT INTO…SELECT…`：大数据批量导入的实现原理。
最新发布
weixin_41455464的博客
08-16 1188
掌握了这些技巧，你就可以轻松应对各种数据导入的需求，让数据在你的手中自由流动！如果目标表有自增主键，通常情况下，我们不需要指定自增主键的值，数据库会自动生成。这时候，我们可以采用分批导入的策略，把大数据分割成小批量的任务，逐步导入。在导入数据之前，可以禁用目标表的索引，导入完成后再重新启用。想象一下，你要导入几百万条数据，一条一条执行，得跑到猴年马月啊？在导入数据之前，可以创建一个错误日志表，用于记录导入过程中出现的错误。语句放在一个事务中，如果导入过程中出现错误，可以回滚事务，保证数据的一致性。
解析MySQL中INSERT INTO SELECT的使用
09-10
MySQL中的`INSERT INTO SELECT`语句是一种非常实用的数据库操作，它允许你从一个或多个数据源中选择数据，并将其插入到另一个表中。这种操作在数据迁移、数据整合或者生成汇总报告时非常常见。下面将详细介绍`INSERT...
mysql- insert select带来的锁问题
jolly10的专栏
04-26 1883
    当使用 insert...select...进行记录的插入时，如果select的表是innodb类型的，不论insert的表是什么类型的表，都会对select的表的纪录进行锁定。对于那些从oracle迁移过来的应用，需要特别的注意，因为oracle并不存在类似的问题，所以在oracle的应用中insert...select...操作非常的常见。例如：有时候会对比较多的纪录进行统计分析，然后...
理解MySQL insert into ... select 的锁情况
echojson的专栏
07-15 454
摘要： 一直以为"insert into tb select * from tbx" 这样的导入操作是会把tbx表给锁住的，在锁期间是不允许任何操作（保证一致性）。看完这篇写的之后，发现tbx表是会被锁住，但这个锁有2种情况，现在逐一进行分析： 分析 环境： root@127.0.0.1 : test 02:10:40>select @@global.tx_isolation,@@session.tx_isolation; +-----------------------+-----...
mysql中insert…select 死锁举例
chy2z的专栏
04-25 787
请先看下上一篇日志InnoDB的行锁模式及加锁方法。对于普通SELECT的语句InnoDB不会加锁，但是insert into table1 … select … from table2 语句会给table2加锁，加行还是表锁跟索引有关。测试表结构如下：account 表建立组合索引（status, merchantId, customerId）下面的数字（0-7）是测试事务sql语句的执行顺序。
MySQL insert ... select 语句锁表导致数据写不进去
10-11 1305
insert...select会对查询的表加共享锁
mysql- insert select带来的死锁DEADLOCK问题
zhang284021445的专栏
10-15 2632
转载 ：mysql- insert select带来的锁问题 https://blog.csdn.net/jolly10/article/details/80095443 转载：MySQL insert into select锁表的问题 https://blog.csdn.net/llliarby/article/details/78697327 参考上边两个帖子，感觉很有帮助，谢谢作者。...
如何优化用SQL语句INSERT INTO … SELECT插入数据时锁全表的问题
weixin_34228617的博客
05-27 564
1、binlog format 启用Row Based Replication（行复制）模式： SET GLOBAL binlog_format = 'ROW'; 如果你想永久的启用这个模式，请修改my.cnf 配置文件: [mysqld] binlog_format=ROW 2、在执行你的sql语句前，设置当前会话的隔离级别 SET SESSION TRANSAC...
Mysql insert into...select...不产生锁问题
weixin_34087503的博客
04-11 368
2019独角兽企业重金招聘Python工程师标准>>> ...
mysql inser into select 性能_Mysql 增加新数据，若存在则更新的问题
weixin_39722196的博客
12-23 413
P.S. 基于mysql 5.6，数据库引擎是 InnoDB解决方案1 、使用INSERT...ONDUPLICATEKEYUPDATEStatement语法；官网手册https://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html2、 使用REPLACE statement官网手册https://dev.mys...
mysql5.6基本sql语句介绍
weixin_41557069的博客
03-23 441
mysql数据库如何存放数据 mysql（DBMS） 一。数据库基本操作 1.创建库并设置字符集 mysql> create database db2 default charset gbk; 2.查看数据库创建信息 show create database db2; 3.更改数据库信息 更改数据库默认字符集 mysql> alter database db1 default char...
MYSQL锁机制 - 锁的简述 | 索引对行级锁的影响
假人一个的博客
08-17 1753
MYSQL锁机制 - 锁的简述 | 索引对行级锁的影响
mysql8.0.25 insert into select next-key锁
03-25
<think>嗯，用户想了解MySQL 8.0.25中使用INSERT INTO SELECT语句时涉及的next-key锁行为以及解决方法。我需要先回忆一下MySQL的锁机制，尤其是InnoDB的next-key锁。 首先，next-key锁是InnoDB在可重复读隔离级别下用来防止幻读的一种锁机制。它结合了记录锁和间隙锁，锁定一个范围。当执行INSERT INTO SELECT时，如果源表有数据被扫描，可能会对扫描到的范围加next-key锁，特别是当使用唯一索引或非唯一索引的时候。 然后，用户提到的版本是8.0.25，需要确认这个版本是否有相关的变化。根据引用[2]，MySQL 8.0.22开始，DML操作读取grant表时不加读锁，但这里讨论的是普通的表操作。可能需要查看MySQL官方文档关于锁的部分。 接下来，INSERT INTO SELECT在复制数据时，如果目标表有触发器或外键约束，或者源表有索引扫描，可能会导致更长时间的锁定，从而引发性能问题甚至死锁。比如，当从大表中SELECT数据插入到另一个表时，如果源表的索引范围被锁定，其他事务可能被阻塞。 解决方法可能包括调整事务隔离级别，比如使用读已提交来减少间隙锁的使用，或者优化索引，减少扫描范围。另外，分批处理数据可能有助于减少锁的持有时间。引用[3]提到MySQL 8.0提高了可靠性，可能在锁管理方面有所优化，但具体到8.0.25需要确认。 用户可能遇到的情况是，在执行INSERT INTO SELECT时，源表被长时间锁定，影响其他事务。需要解释这种情况下next-key锁的行为，并提供具体的解决方案，比如使用主键顺序扫描、降低隔离级别、拆分事务等。同时，结合用户提供的引用，可能引用相关版本文档来支持解答。</think>### MySQL 8.0.25中`INSERT INTO SELECT`的Next-Key锁行为分析 #### 1. Next-Key锁的基本机制 在InnoDB存储引擎中，`INSERT INTO SELECT`语句在**可重复读（REPEATABLE READ）**隔离级别下，若涉及索引范围扫描，可能触发Next-Key锁。Next-Key锁是记录锁（Record Lock）和间隙锁（Gap Lock）的组合，用于防止幻读（Phantom Read）[^2]。 例如： ```sql INSERT INTO target_table SELECT * FROM source_table WHERE indexed_column BETWEEN 100 AND 200; ``` 如果`source_table`的`indexed_column`是非唯一索引，InnoDB会对该范围内所有记录及间隙加锁，阻止其他事务插入符合此范围的新数据。 #### 2. 具体行为表现 - **主键/唯一索引扫描**：若`SELECT`条件使用主键或唯一索引精确匹配（如`WHERE id = 5`），仅加记录锁。 - **非唯一索引扫描**：若使用非唯一索引或全表扫描，会对扫描范围加Next-Key锁。 - **无索引扫描**：全表扫描时，可能对表中所有间隙加锁，导致严重锁竞争[^3]。 #### 3. 典型问题场景 ```sql -- 事务1（长时间运行） INSERT INTO orders_archive SELECT * FROM orders WHERE create_time >= '2023-01-01'; -- 事务2（被阻塞） INSERT INTO orders (create_time) VALUES ('2023-01-02'); ``` 若`orders.create_time`有非唯一索引，事务1会锁定`2023-01-01`之后的间隙，导致事务2被阻塞。 #### 4. 优化解决方案 ##### 方法1：调整隔离级别 将事务隔离级别改为**读已提交（READ COMMITTED）**，该级别下InnoDB不使用间隙锁： ```sql SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN; INSERT INTO target_table SELECT ...; COMMIT; ``` 注意：需评估幻读风险是否可接受。 ##### 方法2：索引优化 为`SELECT`条件添加高效索引，减少锁定范围： ```sql ALTER TABLE source_table ADD INDEX idx_column(column); ``` ##### 方法3：分批处理 使用主键范围分批操作，减少单次锁持有时间： ```sql INSERT INTO target_table SELECT * FROM source_table WHERE id BETWEEN 1 AND 1000; INSERT INTO target_table SELECT * FROM source_table WHERE id BETWEEN 1001 AND 2000; ``` ##### 方法4：使用`LIMIT`与循环 通过应用层控制分批逻辑： ```python last_id = 0 batch_size = 1000 while True: cursor.execute(f""" INSERT INTO target_table SELECT * FROM source_table WHERE id > {last_id} ORDER BY id LIMIT {batch_size} """) last_id = get_last_insert_id() if affected_rows < batch_size: break ``` #### 5. 监控锁状态 通过以下命令实时监控锁行为： ```sql -- 查看当前锁信息 SELECT * FROM performance_schema.data_locks; -- 查看阻塞关系 SELECT * FROM sys.innodb_lock_waits; ``` --- ###
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值