案例剖析:MySQL唯一索引并发插入导致死锁!

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 案例剖析:MySQL唯一索引并发插入导致死锁!

MySQL锁列表

共享与排他锁:

S 锁:共享锁,允许其他事务并行读;禁止其他事务持有排它锁

X 锁:排它锁,允许持有排它锁的事务对数据更新,禁止其他事务对数据持有共享锁或排它锁

注:普通的 select * from user 属于快照读,不加任何锁。

-- S锁
select * from user where id=1 lock in share mode;
-- X锁
select * from user where id=1 for update;
update user set name=‘zhangsan’ where id=1;
delete from user where id=1;
insert into user

意向锁:

在 MySQL 事务进行读写时,需要先对表加意向读写锁,意向锁也分为共享和排他锁,记为 IS、IX。

Innodb的意向锁为表级别的锁,IX,IS是表级锁,不会和行级的X,S锁发生冲突,只会和表级的X,S发生冲突。

主要有两种意向锁:

  • 意向共享锁(IS lock):事务想要获得一张表中某几行的共享锁,必须先获取该表的IS锁。
  • 意向排他锁(IX Lock):事务想要获得一张表中某几行的排他锁,必须先获得该表的IX锁。

记录锁:

文章内容收录到个人网站,方便阅读hardyfish.top/

即 Record 锁。对于主键和唯一索引(全部字段)的当前读,加 Record 锁,如下:

select * from table where id=1 lock in share mode;
select * from table where id=1 for update;
update table set name = 'zhangsan' where id = 1;
delete from table where id = 1;

间隙锁:

即 Gap 锁,区间锁, 仅仅锁住一个索引区间(开区间)。

在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身。

对于非唯一索引的当前读,会加 Gap 锁,如下:

-- seq_id 是非唯一索引
select * from table where seq_id=3 lock in share mode;
select * from table where seq_id=3 for update;
update table set name = 'zhangsan' where seq_id = 3;
delete from table where seq_id = 3;

Next-Key锁:

next-key lock = record + gap lock,左开右闭区间InnoDB使用next-key lock来避免幻读问题

举例来说:

假设 MySQL 表数据如下:

id seq_id
4 1
5 3
6 5
7 7
8 9

当执行下面的语句时:

select * from table where seq_id=3 lock in share mode;

加锁情况如下:

  • 在seq_id=3,id=5记录上加 Record 锁;
  • 在[1,4]~[3,5)区间加Gap锁
  • 在[3,5]~[5,6)区间加Gap锁

如下图:

image.png

插入意向锁

插入意向锁是一种间隙锁形式的意向锁,(区别于 IS、IX,他们是表级别的锁)。在真正执行 INSERT 操作之前设置。

insert会在insert的行对应的索引记录上加一个排它锁,这是一个X record lock,并没有gap,所以并不会阻塞其他session在gap间隙里插入记录。

不过在insert操作之前,还会加一种锁,官方文档称它为insertion intention gap lock,也就是意向的gap锁。

这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时,只要插入的记录不是gap间隙中的相同位置,则无需等待其他session就可完成,这样就使得insert操作无须加真正的gap lock。

假设有一个记录索引包含键值4和7,不同的事务分别插入5和6,每个事务都会产生一个加在4-7之间的插入意向锁,获取在插入行上的排它锁,但是不会被互相锁住,因为数据行并不冲突。

需要注意,对于insert操作来说,如果发生了唯一索引冲突,则需要对冲突的唯一索引加上 Share Record Lock 和 Gap Lock,(即使是RC事务隔离级别)。

这个在并发插入时容易导致死锁,后面会分析。

next-key锁和插入意向锁之间的兼容性:

是否兼容 gap insert intention record next-key
gap
insert intention
record
next-key

Insert 操作涉及到的锁:

INSERT操作,在插入行之前会设置一个插入意向锁。如果该间隙已被加上了 GAP 锁或 Next-Key 锁,则加锁失败进入等待;(注意:Gap锁是为了防止insert, 插入意向锁是为了insert并发更快,两者是有区别的 )

如果是简单INSERT操作,并且存在唯一主键,那么 next-key lock 退化为记录锁(即行锁)。

如果是INSERT...ON DUPLICATE KEY UPDAT会加上间隙锁。若再发生duplicate-key错误的时候则需要执行UPDATE操作,对重复的主键值设置排它记录锁,对重复的唯一键值设置排它临键锁,还会加一个共享记录锁(S)。

并发insert 唯一键冲突死锁示例

表和数据准备:

create table test(
  id int not null primary key auto_increment,
  a int not null ,
  unique key ua(a)
) engine=innodb;
 
insert into test(id,a) values(1,1),(5,4),(20,20),(25,12);

并发插入:

事务 1 事务 2 说明
事务 1 事务 2 说明
SET autocommit=0;SET SESSION innodb_lock_wait_timeout = 300000;SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 关闭事务自动提交增加事务超时时间为300s设置事务隔离级别为 RC
SET autocommit=0;SET SESSION innodb_lock_wait_timeout = 300000;SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
begin;
begin;
insert into test(id,a) values(26,10); 成功
insert into test(id,a) values(30,10); 阻塞等待加了(4,10) gap
insert into test(id,a) values(6,10); 成功
insert into test(id,a) values(40,9); 死锁

死锁分析

查看事务的锁情况:

SELECT*FROM INFORMATION_SCHEMA.data_locks;

利用 show engine innodb status; 命令来查看死锁日志.

关键:对于insert操作来说,若发生唯一约束冲突,则需要对冲突的唯一索引加上 Share Record Lock + Gap Lock。(即使是RC事务隔离级别)

我们从时间线维度分析:

  1. 事务T2 insert into t7(id,a) values(26,10) 语句 insert 成功,持有a=10 的 X 行锁(X locks rec but not gap) ;
  2. 事务T1 insert into t7(id,a) values(30,10),因为T2  的第一条insert已经插入a=10的记录,事务T1的 insert a=10 则发生唯一约束冲突,需要申请对冲突的唯一索引  a=10加上Share Record Lock + Gap Lock (也即是 lock mode S waiting )  这是一个间隙锁会申请锁住(4,10)之间的gap区域。从这里会发现,即使是RC事务隔离级别,也同样会存在Next-Key  Lock锁,从而阻塞并发。所以,此时事务 T1 持有(4,10)的 Gap Lock,并且等待 a=10上的 share lock。
  3. 事务T2 insert into t7(id,a) values(40,9) 该语句插入的 a=9 ,需要先获取插入意向Gap锁(4,10),的值在 事务T1申请的gap锁(4,10)之间,故需事务T2的第二条insert语句要等待事务T1的Gaplock锁释放,在日志中显示 lock_mode X locks gap before rec insert intention waiting。所以,此时事务 T2 持有a=10上的 X lock,并且等待(4,10)的插入意向Gap Lock。
  4. 综上,产生死锁。

解决:

死锁后,InnoDB会选择资源最小的那个事务进行回滚。另外一个事务会执行成功,目前的解决方案是:

  • 尽量不要有大事务,降低锁冲突的可能。
  • 死锁回滚后,记录下原始 SQL,手动处理。

死锁回滚记录原始 SQL:

try {
    // 事务代码
} catch (DataAccessException e) {
    if (e.getCause() instanceof MySQLTransactionRollbackException) {
        // 遇到 MySQL 死锁异常后,记录下 SQL,人工处理插入数据
        log.error("Caught MySQLTransactionRollbackException, manualSql={}", generateInsertSQL(records));
    }
}

参考:


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
20天前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
|
1月前
|
存储 关系型数据库 MySQL
MySQL MVCC全面解读:掌握并发控制的核心机制
【10月更文挑战第15天】 在数据库管理系统中,MySQL的InnoDB存储引擎采用了一种称为MVCC(Multi-Version Concurrency Control,多版本并发控制)的技术来处理事务的并发访问。MVCC不仅提高了数据库的并发性能,还保证了事务的隔离性。本文将深入探讨MySQL中的MVCC机制,为你在面试中遇到的相关问题提供全面的解答。
134 2
|
2月前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
1月前
|
SQL 算法 关系型数据库
面试:什么是死锁,如何避免或解决死锁;MySQL中的死锁现象,MySQL死锁如何解决
面试:什么是死锁,死锁产生的四个必要条件,如何避免或解决死锁;数据库锁,锁分类,控制事务;MySQL中的死锁现象,MySQL死锁如何解决
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
189 1
|
1月前
|
存储 关系型数据库 MySQL
MySQL MVCC深度解析:掌握并发控制的艺术
【10月更文挑战第23天】 在数据库领域,MVCC(Multi-Version Concurrency Control,多版本并发控制)是一种重要的并发控制机制,它允许多个事务并发执行而不产生冲突。MySQL作为广泛使用的数据库系统,其InnoDB存储引擎就采用了MVCC来处理事务。本文将深入探讨MySQL中的MVCC机制,帮助你在面试中自信应对相关问题。
118 3
|
2月前
|
关系型数据库 MySQL 数据库
一个 MySQL 数据库死锁的案例和解决方案
本文介绍了一个 MySQL 数据库死锁的案例和解决方案。
108 3
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
78 0
|
5天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
25天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
34 1