锁--07---insert 造成死锁

已于 2023-12-06 22:15:19 修改
阅读量2.9k 收藏 19
点赞数 5
分类专栏： Mysql高级 文章标签： mysql 数据库 database
于 2022-01-09 18:45:15 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48052161/article/details/121941478
版权

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

案例1----简单insert

  • 简单insert之间的死锁

表结构

CREATE TABLE `emp1` (
   `id` int(16) NOT NULL,
   `name` varchar(16) DEFAULT NULL,
   `age` int(16) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

在这里插入图片描述
在这里插入图片描述

设置环境

  1. 常看当前数据库的事务隔离级别： RR级别
SHOW VARIABLES LIKE ‘tx_isolation’;

在这里插入图片描述

  1. 设置: 手动提交事务
set autocommit = 0
  1. 打开3个会话
    在这里插入图片描述

会话1，执行insert，不提交

BEGIN;

INSERT INTO emp1 (id,NAME,age) VALUES (4,'rose',28);

在这里插入图片描述

会话2，执行insert，由于id=4已被会话1上了行锁，所以被堵塞

BEGIN;

INSERT INTO emp1 (id,NAME,age) VALUES (4,'rose',28);

在这里插入图片描述

会话3，执行insert，由于id=4已被会话1上了行锁，所以被堵塞

BEGIN;

INSERT INTO emp1 (id,NAME,age) VALUES (4,'rose',28);

在这里插入图片描述

此时的事务锁等待信息：

SHOW VARIABLES LIKE ‘%innodb%’;
SET GLOBAL innodb_status_output_locks=ON;

在这里插入图片描述

SHOW ENGINE INNODB STATUS

在这里插入图片描述

继续------会话1执行rollback

会话2，显示insert成功

在这里插入图片描述

会话3，显示出现死锁
Deadlock found when trying to get lock; try restarting transaction

在这里插入图片描述

insert 插入前,会话需要先去拿S读锁,检查唯一性

分析死锁原因

  1. 会话1持有id=4的X锁 写锁；
  2. 会话2插入前，需要拿S锁检查唯一性，但是id=4已经上了X锁，所以等待；
  3. 会话3插入前，需要拿S锁检查唯一性，但是id=4已经上了X锁，所以等待；
  4. 当会话1回滚，id=4上的X锁释放，那么会话2和会话3同时获得了S锁，判断唯一性；
  5. 由于会话2和会话3都拿着id=4的读锁，所以各自都无法在id=4上面获取X锁，死锁出现。

有两个需要注意的点：

  1. 只有拿到行锁，才能检查唯一约束，例如当会话1拿着X锁时，会话2就没办法进行唯一约束判断。
  2. 死锁信息中，或者show engine innodb status\G的事务信息中，不一定会把事务持有锁的情况都显示，例如上面的死锁信息就没有显示第一个事务持有S锁，但实际是有的；

那么为什么最后 会显示会话2 插入成功了呢?

在这里插入图片描述

  1. Innodb检测到死锁之后,判断会话3 事务较小,让其回滚,释放S锁
  2. 此时会话2,拿到X写锁,插入成功

案例2----- on duplicate key update

表结构

mysql> select * from sam;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  3 |    3 |    3 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> desc sam;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| col1  | int(11) | YES  | UNI | NULL    |       |
| col2  | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

会话1

  • 执行insert into sam values (4,3,3) on duplicate key update col1=4，不提交事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sam values (4,3,3) on duplicate key update col1=4;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from sam;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  3 |    4 |    3 |
+----+------+------+
3 rows in set (0.00 sec)

会话2

  • 执行insert into sam values (4,4,3) on duplicate key update col1=5，被会话1堵塞
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sam values (4,4,3) on duplicate key update col1=5;

会话3

  • 执行insert into sam values (5,4,3) on duplicate key update col1=5，被会话2堵塞
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sam values (5,4,3) on duplicate key update col1=5;

此时的事务锁等待信息：

---TRANSACTION 69999, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 140302983333632, query id 262 localhost sam update
insert into sam values (5,4,3) on duplicate key update col1=5
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 5 n bits 80 index uk_c1 of table `sam`.`sam` trx id 70007 lock_mode X waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 4; hex 80000003; asc     ;;

------------------
---TRANSACTION 69998, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140302983603968, query id 260 localhost sam update
insert into sam values (4,4,3) on duplicate key update col1=5
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 5 n bits 80 index uk_c1 of table `sam`.`sam` trx id 70006 lock_mode X waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 4; hex 80000003; asc     ;;

------------------

会话1执行rollback

  • 会话1执行rollback
  • 会话2，显示insert成功
  • 会话3，显示出现死锁，
    事务被回滚ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

死锁信息：

LATEST DETECTED DEADLOCK
------------------------
2020-04-28 22:47:21 0x7f9ad57b8700
*** (1) TRANSACTION:
TRANSACTION 69998, ACTIVE 40 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140302983603968, query id 250 localhost sam update
insert into sam values (4,4,3) on duplicate key update col1=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 5 n bits 80 index uk_c1 of table `sam`.`sam` trx id 69998 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 69999, ACTIVE 9 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 140302983333632, query id 252 localhost sam update
insert into sam values (5,4,3) on duplicate key update col1=5
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 76 page no 5 n bits 80 index uk_c1 of table `sam`.`sam` trx id 69999 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 5 n bits 80 index uk_c1 of table `sam`.`sam` trx id 69999 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

分析死锁过程

  1. 会话1持有主键上id=3的X锁，col1上唯一索引的id=3和id=4的X锁；
  2. 会话2在等col1上唯一索引col1=4的next-key锁；
  3. 会话3在等col1上唯一索引col1=4的next-key锁；
  4. 会话1回滚，所有X锁释放；
  5. 本来会话2和会话3都要拿到col1唯一索引上(3,4]这一个next-key锁，但会话1回滚后，col1=4就没了，所以会话2和会话3的锁就扩大了，变成(3，+∞]
  6. 会话2和会话3各自的next-key锁堵塞了各自的insert操作

这里也是需要注意两点：

  1. 死锁信息同样显示不全，应该是两个事务都持有lock_mode X，即(3,+∞]的next-key锁，但死锁信息只显示一个事务持有；
  2. insert into sam values (5,4,3) on duplicate key update col1=5，为什么在insert之前，不需要在col1的唯一索引上先拿S锁来检测唯一性呢？这里我猜由于这种insert语句比较特殊，本身就带有on duplicate来处理唯一键冲突，所以直接拿X锁在执行过程中来将唯一冲突处理掉。

案例3 ----- Replace into在这里插入图片描述

案例4-----间隙锁导致死锁

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

分析死锁原因

在这里插入图片描述

那么为什么最后 会显示会话2 插入成功了呢?

在这里插入图片描述

在这里插入图片描述

知行合一。。。
关注
  • 5
    点赞
  • 踩
  • 19
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
INSERT语句引发的死锁
weixin_64314555的博客
01-14 4514
两条一样的INSERT语句竟然引发了死锁，这究竟是人性的扭曲，还是道德的沦丧，让我们不禁感叹一句：卧槽！这也能死锁，然后眼中含着悲催的泪水无奈的改起了业务代码。 好的，在深入分析为啥两条一样的INSERT语句也会产生死锁之前，我们先介绍一些基础知识。 准备一下环境 为了故事的顺利发展，我们新建一个用了无数次的hero表： CREATE TABLE hero ( number INT AUTO_INCREMENT, name VARCHAR(100), country var
操作系统-死锁.pptx
11-14
操作系统-死锁.pptx
分享insert into select遇到的死锁问题（项目实战）
辰兮要努力
06-20 6703
本篇分享分享insert into select遇到的死锁问题！欢迎打卡、沟通、交流！
主键冲突，失败的insert变成共享锁，导致死锁，经典案例随时复现
songjiweiliu的博客
06-09 2538
具体来说，当我们使用INSERT语句插入数据时，MySQL会根据数据表的唯一索引来创建一条排他锁，这样可以确保其他事务不能同时修改该数据。在MySQL中，当我们使用INSERT语句向表中插入数据时，如果插入的数据与已有的数据主键或唯一索引发生冲突，就会产生唯一键冲突错误（Duplicate Key Error）。总之，当INSERT语句产生唯一键冲突时，MySQL会将产生的排他锁转换为共享锁，以避免出现死锁等问题，保证数据的隔离性和一致性。需要注意的是，该操作只是在发生唯一键冲突时才会执行。
Mysql insert也会发生死锁？
最新发布
taoerchun的专栏
10-07 1728
今天给大家分享我们前几天线上遇到的一个Mysql死锁的案列
故障分析 | 从 Insert 并发死锁分析 Insert 加锁源码逻辑
ActionTech的博客
04-23 1332
死锁，作为数据库一个常见的并发问题。此类问题：1.触发原因往往与应用的逻辑相关，参与的事务可能是两个、三个、甚至更多；2.由于不同数据库的锁实现机制几乎完全不同、实现逻辑复杂，还存在多种锁类型；3.数据库发生死锁后，会立即终止部分事务，事后无法看到死锁前的等待状态。即，死锁问题具有业务关联、机制复杂、类型多样等特点，导致当数据库发生死锁问题时，不是那么容易分析。
mysql insert 时出现Deadlock死锁场景分析
huangdi1309的博客
04-21 7688
案例一： 有一张表A，先更新，如果影响行数为0，则执行INSERT插入数据。很常见的场景，在生产上也跑了很久，没有出现什么问题。但是有一次在测试环境做压测时居然出现了死锁，Deadlock found when trying to get lock; try restarting transaction 因为对mysql锁不熟悉，为什么insert也会死锁，不是一般在update的时候会死锁吗？...
操作系统-死锁概述.pptx
11-14
操作系统-死锁概述.pptx
p264 - p276 死锁
05-15
p264 - p276 死锁
操作系统实验报告-死锁的检测与解除.wps
11-17
操作系统实验报告-死锁的检测与解除.wps
进程调度与死锁-作业
12-18
进程调度与死锁-作业 帮你复习所学的东西
MySQL并发insert导致的dead lock解决方法
hannnnn_的博客
02-08 4627
线上某业务最近经常会出现dead lock，相关信息如下： 2016-06-15 20:28:257f72c0043700InnoDB: transactions deadlock detected, dumping detailed information. 2016-06-15 20:28:25 7f72c0043700 *** (1) TRANSACTION: TRANSACTION 151506716, ACTIVE 30 sec inserting mysql tables in...
insert唯一索引死锁问题-1
weixin_42002747的博客
02-06 404
innodb 死锁
Java高级工程师必备知识！为什么你的insert就死锁了
m0_56144365的博客
08-04 616
A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record. By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable.
oracle 死锁问题
zj0910
09-17 377
我个人对SQL锁也不是非常关注，但是毕竟也是IT内的一小角色；也不免要跟数据库打交道，我所谓说的打交道只是使用基本的操作，不像那些DB  manger ； 更别提什么优化什么游标和表的索引了。对锁也是了解没有进一步关注，但是最近因为一些需要所以去查阅资料和网络及书籍也只是了解一
oracle插入数据死锁,insert语句导致死锁，请大神指点原因
weixin_31207855的博客
04-08 1390
昨天数据库发现死锁，根据trace文件内提示的object id查询为表的主键索引，下面是部分trace文件的内容。请大神指定原因：[Transaction Deadlock]The following deadlock is not an ORACLE error. It is adeadlock due to user error in the design of an application...
Oracle insert数据的新方法 及死锁解决办法
qq_42740899的博客
09-26 1427
在开发过程中，发现了一串代码，如下： <insert id="insertAccInvoicePrintListBatch" parameterClass="list"> INSERT INTO acc_invoice_print_list (instance_id, invoice_id, charge_name,currency,unit_price,unit_num,am...
insert into死锁分析
[email protected]
05-24 3163
不管事务管理级别如何设置，只要存在唯一性约束并且可能insert/delete/update同一key值的记录，死锁都将无法100%的避免，只能是概率降低 唯一键冲突回滚造成的死锁, 不管是主键冲突,唯一键冲突,还是唯一联合索引冲突都会造成死锁, 但是加锁方式不同 在插入之前，会先在插入记录所在的间隙加上一个插入意向gap锁 成功插入后, insert会对插入成功的行加上排它锁 关于意向g...
MySQL死锁记录(INSERT INTO ... SELECT.... )
qq_45872465的博客
03-19 3464
一、前言 在线上发布新功能后，追踪了日志记录，发现有死锁的报错记录。因业务复杂，仅简化问题，先将问题简化复原。特在此记录解决分析过程。 先抛出问题，后附解决方案； 事务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
mybatis-plus实现表锁
09-16
Mybatis-Plus并没有提供直接实现表锁的功能，但是可以通过使用Mybatis的注解和原生SQL来实现表锁。 下面是一个使用Mybatis注解实现表锁的示例： ```java @Mapper public interface UserMapper { @Select("SELECT * FROM user WHERE id = #{id} FOR UPDATE") User selectForUpdate(Long id); } ``` 在上面的示例中，使用了`FOR UPDATE`语句来锁定查询结果，从而实现了表锁。 如果需要使用原生SQL来实现表锁，可以通过在Mapper XML文件中编写相应的SQL语句来实现。例如： ```xml <select id="selectForUpdate" resultType="user"> SELECT * FROM user WHERE id = #{id} FOR UPDATE </select> ``` 使用时，直接调用Mapper中的相应方法即可实现表锁。 需要注意的是，表锁可能会对系统性能造成一定的影响，因此需谨慎使用。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值