MySQL update use index merge(Using intersect) increase chances for deadlock

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介:

昨天一同事发现线上系统在并发更新的时候出现了死锁,通过排查定位于update更新使用了两个索引导致,死锁信息如下:

*** (1) TRANSACTION:
TRANSACTION 29285454235, ACTIVE 0.001 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 6 lock struct(s), heap size 1184, 4 row lock(s)
MySQL thread id 6641616, OS thread handle 0x2b165c4b1700, query id 28190427937 10.103.180.86 test_ebs Searching rows for update
UPDATE test SET is_deleted = 1 WHERE group_id = 1332577 and test_id = 4580605
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 132 page no 37122 n bits 352 index `PRIMARY` of table `testdriver`.`test` trx id 29285454235 lock_mode X locks rec but not gap waiting
Record lock, heap no 179 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
*** (2) TRANSACTION:
TRANSACTION 29285454237, ACTIVE 0.001 sec fetching rows, thread declared inside InnoDB 4980
mysql tables in use 3, locked 3
5 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 6639213, OS thread handle 0x2b1694cc2700, query id 28190427939 10.103.180.113 test_ebs Searching rows for update
UPDATE test SET is_deleted = 1 WHERE group_id = 1332577 and test_id = 4212859
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 132 page no 37122 n bits 352 index `PRIMARY` of table `testdriver`.`test` trx id 29285454237 lock_mode X locks rec but not gap
Record lock, heap no 179 PHYSICAL RECORD: n_fields 8; compact format;

表结构:
CREATE TABLE `test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
`test_id` bigint(20) DEFAULT NULL,
`group_id` bigint(20) DEFAULT NULL COMMENT ‘Id,对应test_group.id’,
`gmt_created` datetime DEFAULT NULL COMMENT ‘创建时间’,
`gmt_modified` datetime DEFAULT NULL COMMENT ‘修改时间’,
`is_deleted` tinyint(4) DEFAULT ‘0’ COMMENT ‘删除。’,
PRIMARY KEY (`id`),
KEY `idx_testid` (`test_id`),
KEY `idx_groupid` (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7429111 ;

SQL执行计划:
mysql>explain UPDATE test SET is_deleted = 1 WHERE group_id = 1332577 and test_id = 4212859
+—-+————-+—————+————-+———————–+———————–+———+—–+——+———
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+—————+————-+———————–+———————–+———+—–+——+———
| 1 | SIMPLE | test | index_merge | idx_testid,idx_groupid | idx_testid,idx_groupid | 9,9 | | 1 | Using intersect(idx_testid,idx_groupid); Using where; Using temporary |
+—-+————-+—————+————-+———————–+———————–+———+—–+——+———

所以第一个事务先根据group_id索引,已经锁住primary id,然后再根据test_id索引,锁定primary id;
第二个事务先根据test_id索引,已经锁住primary id,然后再根据group_id索引,去锁primary id;
所以这样并发更新就可能出现死索引。

MySQL官方也已经确认了此bug:https://bugs.mysql.com/bug.php?id=77209

解决方法有两种:

第一、添加test_id+group_id的组合索引,这样就可以避免掉index merge;

第二、将优化器的index merge优化关闭;

建议选择第一种方法来避免此问题的发生。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
8月前
|
存储 关系型数据库 MySQL
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
478 10
|
11月前
|
SQL 关系型数据库 MySQL
MySQL异常一之: You can‘t specify target table for update in FROM clause解决办法
这篇文章介绍了如何解决MySQL中“不能在FROM子句中指定更新的目标表”(You can't specify target table for update in FROM clause)的错误,提供了错误描述、需求说明、错误做法和正确的SQL写法。
2184 0
|
分布式计算 DataWorks 关系型数据库
DataWorks产品使用合集之ODPS数据怎么Merge到MySQL数据库
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
191 1
|
数据采集 关系型数据库 MySQL
大数据-业务数据采集-FlinkCDC The MySQL server is not configured to use a ROW binlog_format
大数据-业务数据采集-FlinkCDC The MySQL server is not configured to use a ROW binlog_format
109 1
|
存储 SQL 关系型数据库
MySQL中的update操作与锁机制
本文探讨MySQL中`UPDATE`操作的自动加锁机制及其对数据一致性的保障作用。尤其在InnoDB存储引擎下,系统会在涉及索引的更新操作中加行锁或间隙锁,防止多事务并发修改同一条记录。通过福利码兑换系统的实例展示,当线程A开启事务更新库存时,线程B试图更新相同记录会被阻塞,直至线程A提交。此外,文章还介绍了乐观锁及版本号控制等策略进一步提升并发性能的方法。作者:小明爱吃火锅,来源:稀土掘金。
631 2
|
关系型数据库 MySQL 测试技术
MySQL 报错 ERROR 1709: Index column size too large
MySQL 报错 ERROR 1709: Index column size too large
557 4
|
缓存 关系型数据库 MySQL
MySQL数据库——InnoDB引擎-架构-内存结构(Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer)
MySQL数据库——InnoDB引擎-架构-内存结构(Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer)
206 3
|
负载均衡 关系型数据库 MySQL
MySQL PXC集群多个节点同时大量并发update同一行
如本文标题,MySQL PXC集群多个节点同时大量并发update同一行数据,会怎样? 为此,本人做了一个测试,来验证到底会怎样!
125 0
|
存储 关系型数据库 MySQL
MySQL的优化利器⭐️Multi Range Read与Covering Index是如何优化回表的?
本文以小白的视角使用通俗易懂的流程图深入浅出分析Multi Range Read与Covering Index是如何优化回表
|
供应链 关系型数据库 MySQL
MySQL的`FOR UPDATE`详解
MySQL的`FOR UPDATE`详解
798 0

推荐镜像

更多