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

最新推荐文章于 2024-10-11 18:52:51 发布
原创 最新推荐文章于 2024-10-11 18:52:51 发布 · 256 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

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

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

环境：

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 3634
一、前言 在线上发布新功能后，追踪了日志记录，发现有死锁的报错记录。因业务复杂，仅简化问题，先将问题简化复原。特在此记录解决分析过程。 先抛出问题，后附解决方案； 事务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 select带来的死锁DEADLOCK问题
zhang284021445的专栏
10-15 2560
转载 ：mysql- insert select带来的锁问题 https://blog.csdn.net/jolly10/article/details/80095443 转载：MySQL insert into select锁表的问题 https://blog.csdn.net/llliarby/article/details/78697327 参考上边两个帖子，感觉很有帮助，谢谢作者。...
关于MySQL insert into ... select 的锁情况
weixin_34302561的博客
04-28 602
摘要：      一直以为"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 214
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 9634
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的使用
09-10
MySQL中的`INSERT INTO SELECT`语句是一种非常实用的数据库操作，它允许你从一个或多个数据源中选择数据，并将其插入到另一个表中。这种操作在数据迁移、数据整合或者生成汇总报告时非常常见。下面将详细介绍`INSERT...
mysql中insert与select的嵌套使用方法
12-15
在MySQL数据库中，`INSERT` 与 `SELECT` 的嵌套使用是一种强大的功能，它允许我们在一条SQL语句中从一个或多个数据源提取数据并将其插入到目标表中。这种技术尤其适用于需要合并不同表的数据或者创建数据备份的情况...
mysql- insert select带来的锁问题
jolly10的专栏
04-26 1852
    当使用 insert...select...进行记录的插入时，如果select的表是innodb类型的，不论insert的表是什么类型的表，都会对select的表的纪录进行锁定。对于那些从oracle迁移过来的应用，需要特别的注意，因为oracle并不存在类似的问题，所以在oracle的应用中insert...select...操作非常的常见。例如：有时候会对比较多的纪录进行统计分析，然后...
理解MySQL insert into ... select 的锁情况
echojson的专栏
07-15 443
摘要： 一直以为"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 774
请先看下上一篇日志InnoDB的行锁模式及加锁方法。对于普通SELECT的语句InnoDB不会加锁，但是insert into table1 … select … from table2 语句会给table2加锁，加行还是表锁跟索引有关。测试表结构如下：account 表建立组合索引（status, merchantId, customerId）下面的数字（0-7）是测试事务sql语句的执行顺序。
MySQL insert ... select 语句锁表导致数据写不进去
10-11 1223
insert...select会对查询的表加共享锁
如何优化用SQL语句INSERT INTO … SELECT插入数据时锁全表的问题
weixin_34228617的博客
05-27 552
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 358
2019独角兽企业重金招聘Python工程师标准>>> ...
mysql inser into select 性能_Mysql 增加新数据，若存在则更新的问题
weixin_39722196的博客
12-23 319
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 413
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 1650
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、付费专栏及课程。

余额充值