文档章节

mysql并发insert死锁问题——gap、插入意向锁冲突

hebaodan
 hebaodan
发布于 2018/06/26 20:27
字数 825
阅读 6868
收藏 17

问题描述

线上出现MySQL死锁报警,通过show engine innodb status命令查看死锁日志,结合异常代码,还原发生死锁的事务场景如下:

环境: mysql5.7,事务隔离级别REPEATABLE-READ

表结构

CREATE TABLE `ta` (
  `id` int AUTO_INCREMENT,
  `a` int,
  `b` int ,  `c` int ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_a_b` (`a`,`b`)
) ENGINE=InnoDB
数据:
mysql> select * from ta;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 |    1 |   10 |  100 |
|  2 |    3 |   20 |   99 |
|  3 |    5 |   50 |   80 |
+----+------+------+------+

并发事务

T1 T2
begin; begin
delete from ta where a = 4;//ok, 0 rows affected
delete from ta where a = 4; //ok, 0 rows affected
insert into ta(a,b,c) values(4, 11, 3),(4, 2, 5);//wating,被阻塞
insert into ta(a,b,c) values(4, 11, 3),(4, 2, 5); //ERROR 1213 (40001): Deadlock found when trying to get lock;
T1执行完成, 2 rows affected

从上面可以看出,并发事务都成功执行delete后(影响行数为0),执行insert出现死锁。

死锁分析

等待锁分析

查看死锁日志,显示事务T1的insert语句在等待插入意向锁,lock_mode X locks gap before rec insert intention waiting;事务T2持有a=4的gap lock,同时也在等待插入意向锁。另外,T1能执行delete,说明它也拿到了gap lock,所以,两个事务都持有gap lock,导致循环等待插入意向锁而发生死锁。

加锁分析

  1. delete的where子句没有满足条件的记录,而对于不存在的记录 并且在RR级别下,delete加锁类型为gap lock,gap lock之间是兼容的,所以两个事务都能成功执行delete;关于gap lock可以参考文章加锁分析。这里的gap范围是索引a列(3,5)的范围。
  2. insert时,其加锁过程为先在插入间隙上获取插入意向锁,插入数据后再获取插入行上的排它锁。又插入意向锁与gap lock和 Next-key lock冲突,即一个事务想要获取插入意向锁,如果有其他事务已经加了gap lock或 Next-key lock,则会阻塞。
  3. 场景中两个事务都持有gap lock,然后又申请插入意向锁,此时都被阻塞,循环等待造成死锁。

锁兼容矩阵

锁兼容矩阵

死锁解决

方案如下几种选择:

  1. 不采用事务包装这部分逻辑,本文实际业务场景中可以不需要事务,所以直接取消事务包装即可,采用insert ON DUPLICATE KEY UPDATE的方式
  2. 调整事务隔离级别为read commit,RC级别不会产生gap lock
  3. 利用分布式锁

附:排查过程

  1. 查看死锁日志,注意这里并不会包含整个事务的相关sql,仅仅会把等待锁的SQL打印出来,死锁日志内容含义参考 :http://blog.itpub.net/22664653/viewspace-2145133/
  2. 根据服务异常log定位到具体事务执行代码,找出该事务相关的sql
  3. 根据积累的经验知识分析加锁、锁等待情况,找出死锁原因

参考

insert加锁分析,参考文章http://www.aneasystone.com/archives/2017/12/solving-dead-locks-three.html

© 著作权归作者所有

hebaodan
粉丝 9
博文 25
码字总数 30036
作品 0
朝阳
程序员
私信 提问
加载中

评论(1)

mark35
mark35
一了百了的解决方案,换掉mysql
故障分析 | MySQL Insert 加锁与死锁分析

原创: 胡呈清 前 言 本文是由爱可生运维团队出品的「MySQL专栏」系列文章,内容来自于运维团队一线实战经验,涵盖MySQL各种特性的实践,优化案例,数据库架构,HA,监控等,有扫雷功效。 爱...

爱可生
07/03
67
0
MySQL · 引擎特性 · InnoDB 事务锁系统简介

前言 本文的目的是对 InnoDB 的事务锁模块做个简单的介绍,使读者对这块有初步的认识。本文先介绍行级锁和表级锁的相关概念,再介绍其内部的一些实现;最后以两个有趣的案例结束本文。 本文所...

阿里云RDS-数据库内核组
2016/01/02
0
0
mysql批量插入死锁问题分析(正序VS逆序)

https://dev.mysql.com/doc/refman/5.5/en/innodb-locking.html#innodb-insert-intention-locks An insert intention lock is a type of gap lock set by operations prior to row insertion......

tantexian
2018/10/15
1K
0
深入理解 MySQL ——锁、事务与并发控制

深入理解 MySQL ——锁、事务与并发控制 目录 MySQL 服务器逻辑架构 MySQL 锁 事务 隔离级别 并发控制 与 MVCC MySQL 死锁问题 1、MySQL 服务器逻辑架构 (图片来源MySQL官网) 每个连接都会...

优惠券活动
03/14
0
0
抱歉,没早点把这么全面的InnoDB锁机制发给你

作者介绍 崔亚盟,阿里高级开发工程师,目前在研究InnoDB相关技术,乐于专研学道,是技术分享的践行者。 数据事务设计遵循ACID的原则: 原子性(Atomicity)、一致性(Consistency)、隔离性...

崔亚盟
03/18
0
0

没有更多内容

加载失败,请刷新页面

加载更多

nginx学习笔记

中间件位于客户机/ 服务器的操作系统之上,管理计算机资源和网络通讯。 是连接两个独立应用程序或独立系统的软件。 web请求通过中间件可以直接调用操作系统,也可以经过中间件把请求分发到多...

码农实战
今天
5
0
Spring Security 实战干货:玩转自定义登录

1. 前言 前面的关于 Spring Security 相关的文章只是一个预热。为了接下来更好的实战,如果你错过了请从 Spring Security 实战系列 开始。安全访问的第一步就是认证(Authentication),认证...

码农小胖哥
今天
12
0
JAVA 实现雪花算法生成唯一订单号工具类

import lombok.SneakyThrows;import lombok.extern.slf4j.Slf4j;import java.util.Calendar;/** * Default distributed primary key generator. * * <p> * Use snowflake......

huangkejie
昨天
12
0
PhotoShop 色调:RGB/CMYK 颜色模式

一·、 RGB : 三原色:红绿蓝 1.通道:通道中的红绿蓝通道分别对应的是红绿蓝三种原色(RGB)的显示范围 1.差值模式能模拟三种原色叠加之后的效果 2.添加-颜色曲线:调整图像RGB颜色----R色增强...

东方墨天
昨天
11
1
将博客搬至CSDN

将博客搬至CSDN

算法与编程之美
昨天
13
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部