mysql中insert...select引发的死锁

最新推荐文章于 2025-06-22 22:48:08 发布
最新推荐文章于 2025-06-22 22:48:08 发布
阅读量6.6k 收藏 3
点赞数
CC 4.0 BY-SA版权
分类专栏： mysql 文章标签： mysql insert...select 死锁
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/sunmun/article/details/50088381
本文解析了MySQL Innodb引擎在高并发情况下因自增列产生的死锁现象，通过分析InnoDB源代码揭示了死锁判定规则，并针对不同autoinc_lock_mode设置下INSERT...SELECT语句对死锁的影响进行了探讨。

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

参考文章:

http://blog.chinaunix.net/uid-9950859-id-181376.html

http://blog.163.com/itjin45@126/blog/static/105107513201442102534166/

mysql5.6用户手册

http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html

 

作为mysql的新手,被最近在项目中碰到的死锁问题吓了一跳,赶紧记下来备忘.

使用show engine innodb status命令查看到的死锁相关信息如下:

(在navicat中执行该命令时会看到status列的值为空,此时别忘了右键全选-复制)

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-11-30 09:16:01 7f1e2c554700TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION

*** TRANSACTION:
TRANSACTION 649212, ACTIVE 0 sec setting auto-inc lock
mysql tables in use 2, locked 2
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 138630, OS thread handle 0x7f1e2c554700, query id 7715687 10.132.43.55 iapproxy Sending data
INSERT INTO tsp_agt_msg_send (
     c1,

     c2,

     c3,

       ... 

) 

SELECT 

          'c1',

          'c2', 
          'c3', 
          ...
*** WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `diap_proxy`.`tsp_agt_msg_send` trx id 649212 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 695842
Purge done for trx's n:o < 695818 undo n:o < 0 state: running but idle
History list length 3160
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 695841, not started
MySQL thread id 138912, OS thread handle 0x7f1e1f5d7700, query id 7846296 192.168.180.54 root cleaning up
---TRANSACTION 0, not started
MySQL thread id 138901, OS thread handle 0x7f1e17a62700, query id 7844770 10.132.137.206 root cleaning up
---TRANSACTION 0, not started
MySQL thread id 138875, OS thread handle 0x7f1e1feba700, query id 7846415 10.132.137.206 root init
show engine innodb status
---TRANSACTION 695828, not started
MySQL thread id 138874, OS thread handle 0x7f1e1dd77700, query id 7846253 192.168.180.54 root cleaning up
---TRANSACTION 692142, not started
MySQL thread id 138843, OS thread handle 0x7f1e1ee39700, query id 7846251 192.168.180.54 root cleaning up
---TRANSACTION 673389, not started

 

首先来看下这一行

2015-11-30 09:16:01 7f1e2c554700TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION

这里涉及到一个死锁判定的规则:

在innodb源代码lock/lock0lock.c文件中,定义了两个常量:

/* Restricts the length of search we will do in the waits-for

     graph of transactions */

#define LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK 1000000

 

/* Restricts the recursion depth of the search we will do in the waits-for

    graph of transactions */

#define LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK 200

然后在检查是否产生死锁的函数lock_deadlock_occurs()中有如下代码:

 

ret = lock_deadlock_recursive(trx, trx, lock, &cost, 0);

switch (ret) {

case LOCK_EXCEED_MAX_DEPTH:

        产生死锁
        ...

        break;

}

其中的lock_deadlock_recursive()函数是递归函数,它会检查自身递归深度,其中有如下代码:

 

ibool   too_far

   = depth > LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK

|| *cost > LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK;

...

if (too_far) {

                return(LOCK_EXCEED_MAX_DEPTH);

            }

因此innodb在检查是否产生死锁时调用lock_deadlock_occurs()检查,这个函数再会调用lock_deadlock_recursive()递归检查锁的数目(不知道这么说是否确切?)，当递归的深度depth大于了一开始介绍的常量LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK,或者cost(不清楚这个代表什么)大于一开始介绍的常量LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK时,就认为发生了死锁.

如上所述这个死锁并非数据库真正发生了死锁,mysql主观的认为发生了死锁.

下面继续分析死锁日志,注意如下两行,

*** WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `diap_proxy`.`tsp_agt_msg_send` trx id 649212 lock mode AUTO-INC waiting
从5.6的用户手册中查找到AUTO-INC的相关信息:

 InnoDB uses a special lock called the table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns. This lock is normally held to the end of the statement (not to the end of the transaction), to ensure that auto-increment numbers are assigned in a predictable and repeatable order for a given sequence of INSERT statements

InnoDB在为自增列产生值的时候,使用一种叫做AUTO_INC的表级锁来做控制.这种锁是作用于语句的而不是事务(即语句执行完了锁就会被释放).使用这种锁是为了确保自增列的值的可预见性和可重复性.可预见性是说当一条insert语句作用于多行时,这些行的自增列基于第一行来说是可预见的;可重复执行是指基于语句的复制在slave重放时自增列的值与master的一致.

mysql提供参数innodb_autoinc_lock_mode来控制在产生自增列时锁的行为,可取值为0,1,2默认为1

0:对于每一个insert操作,都加AUTO_INC锁来为自增列分配值.

1:对于简单的insert操作,不加AUTO_INC锁,而使用一个轻量级的mutex,分配完毕后立即释放,不需要等到语句结束.

对于批量的insert操作,加AUTO_INC锁.当简单的insert操作检测到其他的事务持有AUTO_INC锁时,也需要等待AUTO_INC锁.

2.从不加AUTO_INC锁,并发性最好,但基于语句的复制和恢复将有问题.

 

insert操作有INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA.(凡是能产生新的一行的语句都是)

简单的insert语句:

在语句正式执行前,所作用的行数是确定的.比如insert子句,不包含子查询的replace子句.

 

批量插入的sql语句:
包括INSERT ... SELECT, REPLACE ... SELECT和LOAD DATA

混合型的sql语句:

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');即包含指定的又包含需要mysql帮忙自增的.


从以上分析可以得出,在innodb_autoinc_lock_mode=1的情况下insert...select语句会导致自增列的赋值需要加AUTO_INC锁.当在并发作insert的情况下会导致上述死锁.

总结: 在高并发的情况下对有自增列的表做插入操作应避免使用批量insert语句.
 


 

 

【MySQL】MySQL Insert into select 大量锁表导致无法插入
九师兄
07-20 8705
1.概述 原文：因用了Insert into select语句，同事被开除了！ Insert into select 请慎用，同事因为使用了 Insert into select 语句引发了重大生产事故，最后被开除。 某天 xxx 接到一个需求，需要将表 A 的数据迁移到表 B 中去做一个备份。他本想通过程序先查询查出来然后批量插入，但 xxx 觉得这样有点慢，需要耗费大量的网络 I/O，决定采取别的方法进行实现。 通过在某度的海洋里遨游，他发现了可以使用 insert into select 实现，这.
mysql- insert select带来的死锁DEADLOCK问题
zhang284021445的专栏
10-15 2563
转载 ：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 …select …带来的死锁问题
muzizhuben的专栏
06-17 1万+
mysql中 insert …select …带来的问题 当使用insert...select...进行记录的插入时，如果select的表是innodb类型的，不论insert的表是什么类型的表，都会对select的表的纪录进行锁定。 对于那些从oracle迁移过来的应用，需要特别的注意，因为oracle并不存在类似的问题，所以在oracle的应用中insert...selec
INSERT语句的“锁之谜“：为什么一个简单的插入能引发这么多锁？
最新发布
weixin_54726354的博客
06-22 908
《MySQL INSERT语句的锁机制深层解析》 摘要：看似简单的INSERT语句背后隐藏着复杂的锁机制。文章通过虚拟面试场景引出问题，深入剖析了四种典型场景下的锁行为：1）普通INSERT仅加排他锁；2）INSERT...SELECT会对源表加读锁以保证主备一致性；3）同表INSERT...SELECT需要全表扫描并加锁；4）唯一键冲突会申请共享锁，可能引发死锁。文章还揭示了ON DUPLICATE KEY UPDATE的特殊锁机制，并总结了不同场景下的加锁策略。通过mermaid图表直观展示了锁的获取流
mysql insert锁机制
热门推荐
zhanghongzheng3213的专栏
12-02 2万+
一、前言 上周遇到一个因insert而引发的死锁问题，其成因比较令人费解。 于是想要了解一下insert加锁机制，但是发现网上介绍的文章比较少且零散，挖掘过程比较忙乱。 本以为只需要系统学习一个较完全的逻辑，但是实际牵扯很多innodb锁相关知识及加锁方式。我好像并没有那么大的能耐，把各种场景的加锁过程一一列举并加之分析；亦没有太多的精力验证网上的言论的准确性。 只好根据现在了
mysql insert into select 死锁_sql insert into .. select..死锁解决办法
weixin_39710462的博客
02-05 182
sql insert into .. select..死锁解决办法sql insert into .. select..死锁解决办法方法一insert into a select * from b with(xlock) where form_no=@form_no方法二，sp_who --sql2000及以上dbcc inputbuffer(spid)--用于查看具体的语句kill spid -...
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数据库my.cnf配置文件
04-22
#MySQL的查询缓冲大小（从4.0.1开始，MySQL提供了查询缓冲机制）使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中， # 今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户...
mysql先删后增并发时出现死锁_MySQL死锁案例分析一(先delete，再insert，导致死锁)...
weixin_39899021的博客
02-10 2966
一、死锁案例MySQL版本：Percona MySQL Server 5.7.19隔离级别：可重复读(RR)业务逻辑：并发下按某个索引字段先delete记录，再insert记录比如：begin;deletefromtbwhereorder_id=xxx;insertintotb(order_id)values(xxx);commit;二、MySQL锁基本概念S：共享锁(行级锁)X...
新版 MySQL DBA 高级视频 基于MySQL 5.7 MySQL 8.0版本.rar
07-17
│ 9_MySQL Insert课堂练习和Update命令.mp4 │ ├─新版MySQL DBA综合实战班 第03天 │ 1_课堂作业讲解.mp4 │ 2_MySQL Delete语法讲解.mp4 │ 3_MySQL Select语法讲解.mp4 │ 4_MySQL Select多表连接讲解.mp4 │ ...
MySQL中的死锁预防和解决
常备不懈
04-18 2044
死锁是数据库管理系统中常见的问题，特别是在高并发的应用场景下。MySQL数据库中的死锁会导致事务处理速度减慢，甚至完全停止，因此理解并预防死锁至关重要。本文将详细介绍如何预防MySQL中的死锁，包括常用的策略和技巧。
Mysql insert … select 语句 锁的问题
存在,即合理
09-22 1048
并发 insert 场景实际的执行效果是，如果 session B 先执行，由于这个语句对表 t 主键索引加了 (-∞,1] 这个 next-key lock，会在语句执行完成后，才允许 session A 的 insert 语句执行。但如果没有锁的话，就可能出现 session B 的 insert 语句先执行，但是后写入 binlog 的情况。
mysql insert into select 死锁_mysql insert导致死锁
weixin_28543661的博客
02-07 1002
两个insert语句发生死锁的案例。一. 准备数据CREATETABLE`t1`(`a`int(11)NOTNULL,PRIMARYKEY(`a`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;insertintot1values(1);mysql>insertintot1values(1);QueryOK,1rowaf...
mysql insert into select 死锁_sqlinsertinto..select..死锁解决办法
weixin_35474542的博客
02-07 392
sql insert into .. select..死锁解决办法sql insert into .. select..死锁解决办法方法一insert into a select * from b with(xlock) where form_no=@form_no方法二，sp_who --sql2000及以上dbcc inputbuffer(spid)--用于查看具体的语句kill spid -...
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写入数据出现deadlock_MySQL唯一键insert时候的DeadLock异常
weixin_28875235的博客
02-03 521
create table test_innodb_insert_lock(id int not null primary key auto_increment,uk int not null unique key) engine = innodb;开启三个终端执行窗口，模拟三个sessionsession_1,insert成功session_2,等待...session_3,等待Mysql(当前问...
mysql insert into ..select 发生死锁问题。
纯洁的明依
08-30 4547
mysql 对于业务较为复杂的功能，我们常常使用存储过程进行编辑数据库的操作，当我们在进行数据库的操作时，进行着函数的嵌套或者是数据的算法较为复杂时， 我们怎么样在一个事物中进行数据的处理呢。 一:错误的方案: insert into ..select 嵌套复杂的算法  from .. INSERT INTO `inp_bill_detail`             (`id`
insert into ... select 由于SELECT表引起的死锁情况分析
数据与算法架构提升之路专栏
08-05 1060
Insert into select请慎用。这天xxx接到一个需求，需要将表A的数据迁移到表B中去做一个备份。本想通过程序先查询查出来然后批量插入。但xxx觉得这样有点慢，需要耗费大量的网络I/O，决定采取别的方法进行实现。通过在Baidu的海洋里遨游，她发现了可以使用insert into select实现，这样就可以避免使用网络I/O，直接使用SQL依靠数据库I/O完成，这样简直不要太棒了。然后她就被开除了。 事故发生的经过。 由于数据数据库中order_today数据量过大，当时好像有700.
mysql- insert select带来的锁问题
jolly10的专栏
04-26 1852
    当使用 insert...select...进行记录的插入时，如果select的表是innodb类型的，不论insert的表是什么类型的表，都会对select的表的纪录进行锁定。对于那些从oracle迁移过来的应用，需要特别的注意，因为oracle并不存在类似的问题，所以在oracle的应用中insert...select...操作非常的常见。例如：有时候会对比较多的纪录进行统计分析，然后...
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、付费专栏及课程。

余额充值