index_merge导致死锁案例分析

已于 2022-02-15 11:46:20 修改
阅读量753 收藏 1
点赞数 1
分类专栏： MySQL 文章标签： mysql
于 2020-06-26 15:29:07 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_37692493/article/details/106970386
版权

一、死锁现象描述

1.1 基本环境信息

1、数据库版本以及隔离级别

mysql>select version();
+---------------------+
| version()           |
+---------------------+
| 5.7.26-log          |
+---------------------+
返回行数：[1]，耗时：3 ms.

mysql>show variables like '%iso%';
+-------------------------+-----------------+
| Variable_name           | Value           |
+-------------------------+-----------------+
| transaction_isolation   | READ-COMMITTED  |
| tx_isolation            | READ-COMMITTED  |
+-------------------------+-----------------+
返回行数：[2]，耗时：7 ms.

1.2 模拟测试准备

1、数据准备

CREATE TABLE `deadlock_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `info_uuid` varchar(50) DEFAULT NULL,
  `view_date` date DEFAULT NULL,
  `view_count` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_view_date` (`view_date`),
  KEY `idx_info_uuid` (`info_uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=30000 DEFAULT CHARSET=utf8

mysql> select * from deadlock_test where info_uuid = 'aa381' and VIEW_DATE = '2020-05-04';
+-------+-----------+------------+------------+
| id    | info_uuid | view_date  | view_count |
+-------+-----------+------------+------------+
| 13908 | aa381     | 2020-05-04 |         77 |
+-------+-----------+------------+------------+
1 row in set (0.02 sec)

mysql> select * from deadlock_test where info_uuid = 'aa269' and VIEW_DATE = '2020-05-04';
+-------+-----------+------------+------------+
| id    | info_uuid | view_date  | view_count |
+-------+-----------+------------+------------+
| 29033 | aa269     | 2020-05-04 |          5 |
+-------+-----------+------------+------------+
1 row in set (0.05 sec)

2、模拟死锁产生场景方案

高并发下，多个客户端并发请求更新deadlock_test表的view_count记录，更新SQL主要如下：

update deadlock_test set view_count=view_count+1 where info_uuid = 'aa269' and VIEW_DATE = '2020-05-04';
update deadlock_test set view_count=view_count+1 where info_uuid = 'aa381' and VIEW_DATE = '2020-05-04';

与update获取锁资源相对应的，我们在进行模拟的时候使用select … for update进行代替进行加锁模拟。

二、模拟死锁故障

2.1 模拟步骤以及现象

1、会话执行情况

Session ASession B
begin;-
select id from deadlock_test force index(idx_info_uuid) where info_uuid = ‘aa269’ and VIEW_DATE = ‘2020-05-04’ for update;begin;
·select id from deadlock_test force index(idx_info_uuid) where info_uuid = ‘aa381’ and VIEW_DATE = ‘2020-05-04’ for update;
select id from deadlock_test force index(idx_view_date) where info_uuid = ‘aa269’ and VIEW_DATE = ‘2020-05-04’ for update; --被hang住-
·select id from deadlock_test force index(idx_view_date) where info_uuid = ‘aa381’ and VIEW_DATE = ‘2020-05-04’ for update; --死锁发生

2、死锁日志查看

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-05-14 22:31:06 0x70000fc2f000
*** (1) TRANSACTION:
TRANSACTION 39033, ACTIVE 30 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 58 lock struct(s), heap size 8400, 4 row lock(s)
MySQL thread id 5145, OS thread handle 123145565908992, query id 183203 localhost root Sending data
select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa269' and VIEW_DATE = '2020-05-04' for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 291 page no 65 n bits 488 index PRIMARY of table `db1`.`deadlock_test` trx id 39033 lock_mode X locks rec but not gap waiting
Record lock, heap no 324 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80003654; asc   6T;;
 1: len 6; hex 000000009857; asc      W;;
 2: len 7; hex 2a000001ed2e34; asc *    .4;;
 3: len 5; hex 6161333831; asc aa381;;
 4: len 3; hex 8fc8a4; asc    ;;
 5: len 4; hex 8000004d; asc    M;;

*** (2) TRANSACTION:
TRANSACTION 39034, ACTIVE 18 sec fetching rows
mysql tables in use 1, locked 1
58 lock struct(s), heap size 8400, 3 row lock(s)
MySQL thread id 5153, OS thread handle 123145566744576, query id 183204 localhost root Sending data
select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa381' and VIEW_DATE = '2020-05-04' for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 291 page no 65 n bits 488 index PRIMARY of table `db1`.`deadlock_test` trx id 39034 lock_mode X locks rec but not gap
Record lock, heap no 324 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80003654; asc   6T;;
 1: len 6; hex 000000009857; asc      W;;
 2: len 7; hex 2a000001ed2e34; asc *    .4;;
 3: len 5; hex 6161333831; asc aa381;;
 4: len 3; hex 8fc8a4; asc    ;;
 5: len 4; hex 8000004d; asc    M;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 291 page no 37 n bits 1272 index idx_view_date of table `db1`.`deadlock_test` trx id 39034 lock_mode X locks rec but not gap waiting
Record lock, heap no 692 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 8fc8a4; asc    ;;
 1: len 4; hex 80003654; asc   6T;;

*** WE ROLL BACK TRANSACTION (2)

2.2 锁等待分析

为了分析锁等待的情况，我们把innodb_deadlock_detect参数关闭，MySQL不做死锁检测，这时候我们就可以查看相应的一些锁等待信息

1、Session A 执行通过idx_view_date索引扫描被hang住时刻的锁等待信息

可以看到Session A的第二步已经被SessionB的第一步hang住，此时需要获取的锁资源就是primary key索引上id=13908的行锁。

mysql> SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\G
*************************** 1. row ***************************
 waiting_trx_id: 39036
 waiting_thread: 5145
  waiting_query: select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa269' and VIEW_DATE = '2020-05-04' for update
blocking_trx_id: 39037
blocking_thread: 5153
 blocking_query: NULL
1 row in set, 1 warning (0.00 sec)


mysql> select * from information_schema.innodb_lock_waits ;                                                                                                                                            +-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 39036             | 39184:291:65:324  | 39037           | 39185:291:65:324 |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from information_Schema.innodb_locks;
+------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+-----------+
| lock_id          | lock_trx_id | lock_mode | lock_type | lock_table            | lock_index | lock_space | lock_page | lock_rec | lock_data |
+------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+-----------+
| 39184:291:65:324 | 39036       | X         | RECORD    | `db1`.`deadlock_test` | PRIMARY    |        291 |        65 |      324 | 13908     |
| 39185:291:65:324 | 39037       | X         | RECORD    | `db1`.`deadlock_test` | PRIMARY    |        291 |        65 |      324 | 13908     |
+------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.01 sec)

2、Session B 执行执行通过idx_view_date索引扫描被hang住时刻的锁等待信息

可以看到，两个事务锁与被锁形成了一个死锁闭环，若MySQL死锁检测打开的情况下，会立刻对其中权重较低的事务进行回滚。Session B通过idx_view_date扫描时需要获取的锁资源为(1034404, 13908),但是该资源已经被Session A持有。

mysql> SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\G
*************************** 1. row ***************************
 waiting_trx_id: 39037
 waiting_thread: 5153 (rollback)
  waiting_query: select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa381' and VIEW_DATE = '2020-05-04' for update
blocking_trx_id: 39036
blocking_thread: 
 blocking_query: select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa269' and VIEW_DATE = '2020-05-04' for update
*************************** 2. row ***************************
 waiting_trx_id: 39036
 waiting_thread: 5145
  waiting_query: select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa269' and VIEW_DATE = '2020-05-04' for update
blocking_trx_id: 39037
blocking_thread: 5153
 blocking_query: select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa381' and VIEW_DATE = '2020-05-04' for update
2 rows in set, 1 warning (0.00 sec)

mysql> select * from information_schema.innodb_lock_waits ;                                                                                                                                            +-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 39037             | 39037:291:37:692  | 39036           | 39036:291:37:692 |
| 39036             | 39036:291:65:324  | 39037           | 39037:291:65:324 |
+-------------------+-------------------+-----------------+------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> select * from information_Schema.innodb_locks;
+------------------+-------------+-----------+-----------+-----------------------+---------------+------------+-----------+----------+----------------+
| lock_id          | lock_trx_id | lock_mode | lock_type | lock_table            | lock_index    | lock_space | lock_page | lock_rec | lock_data      |
+------------------+-------------+-----------+-----------+-----------------------+---------------+------------+-----------+----------+----------------+
| 39037:291:37:692 | 39037       | X         | RECORD    | `db1`.`deadlock_test` | idx_view_date |        291 |        37 |      692 | 1034404, 13908 |
| 39036:291:37:692 | 39036       | X         | RECORD    | `db1`.`deadlock_test` | idx_view_date |        291 |        37 |      692 | 1034404, 13908 |
| 39036:291:65:324 | 39036       | X         | RECORD    | `db1`.`deadlock_test` | PRIMARY       |        291 |        65 |      324 | 13908          |
| 39037:291:65:324 | 39037       | X         | RECORD    | `db1`.`deadlock_test` | PRIMARY       |        291 |        65 |      324 | 13908          |
+------------------+-------------+-----------+-----------+-----------------------+---------------+------------+-----------+----------+----------------+
4 rows in set, 1 warning (0.00 sec)

2.3 整体分析与处理

1、会话加锁步骤分析

Session A:(5145)

  1. select id from deadlock_test force index(idx_info_uuid) where info_uuid = ‘aa269’ and VIEW_DATE = ‘2020-05-04’ for update; 锁资源：idx_info_uuid索引的(idx_info_uuid=‘aa269’,id=29033)获取成功,primary索引的(id=29033)获取成功

  2. select id from deadlock_test force index(idx_view_date) where info_uuid = ‘aa269’ and VIEW_DATE = ‘2020-05-04’ for update; 锁资源：idx_view_date索引的(view_date=‘2020-05-04’,id=13908)获取成功，primary索引的(id=13908)获取 被Session B的2）被hang住 --view_date索引从头到尾扫描，扫描到该记录时被hang住。

Session B:(5153) – rollback

  1. select id from deadlock_test force index(idx_info_uuid) where info_uuid = ‘aa381’ and VIEW_DATE = ‘2020-05-04’ for update; 锁资源：idx_info_uuid索引的(idx_info_uuid=‘aa381’,id=13908)获取成功,primary索引的(id=13908)获取成功

  2. select id from deadlock_test force index(idx_view_date) where info_uuid = ‘aa381’ and VIEW_DATE = ‘2020-05-04’ for update; 锁资源：扫描idx_view_date索引的(view_date=‘2020-05-04’,id=13908)获取被SessionA的3)hang住 --view_date索引从头到尾扫描，扫描到该记录时被hang住

2、处理方法

基于以上的分析，我们可以推断MySQL在使用index_merge进行扫描加锁的期间，一定程度上会扩大锁的范围。为了避免MySQL使用index_merge，我们可以将其调整为复合索引来进行优化。

三思呐三思
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle死锁原因解决办法
07-01
在任何数据库中发生死锁都是不愉快的，即使是在一个特殊的情况下发生也是如此，它们会减小应用程序的接受程度(ACCEPTANCE)，因此避免并正确解释死锁是非常重要的。
线上mysql死锁排查分析---索引index merge导致死锁
爆裂无球的程序空间
08-07 1525
线上mysql死锁排查分析---索引index merge导致死锁
记一次实遇的 MySQL--index merge 死锁历程
Park33的博客
04-13 640
本文仅描述了由于 index_merge 优化导致的死锁，讲述了死锁产生的原因以及解决办法，并顺便介绍了 MySQL 加锁机制。事实上死锁的原因还有很多，比如操作数据顺序不一致、长事务等，就不一一展开了。
mysql使用了index merge（索引合并）造成死锁分析总结
阿钟小哥的博客
07-07 3843
mysql数据库更新时造成死锁，经查看执行计划发现是使用到了index merge索引合并算法，那个这个索引合并是什么呢?为什么会用到索引合并呢？使用index merge为什么会造成死锁呢？怎么避免或解决死锁呢？接下来通过这篇文章带你一探究竟。
【线上死锁分析】由index_merge引发的死锁事件
Prior_SX的博客
03-27 437
index_merge 引发的死锁事件
重新认识php array_merge函数
12-18
今天因一个Bug重新审视了下array_merge()这个函数。 定义：array_merge — 合并一个或多个数组 规范：array array_merge(array $array1 [, array $…]) 说明： 1. 将一个或多个数组的单元合并起来，一个数组中的值...
PHP合并数组函数array_merge用法分析
12-19
合并数组是把一个数组追加到另一个数组中，主要应用array_merge()函数实现 语法如下： array array_merge(array array1,array array2[,array...]); 注： 在合并时，如果输入的数组中有相同的字符串键名，则后面的值...
PHP中array_merge和array相加的区别分析
12-19
//如果键名为字符，且键名相同，array_merge()后面数组元素值会覆盖前面数组元素值print_r(array_merge($arr1,$arr2)); //Array ( [a] => JAVA )//如果键名为字符，且键名相同，数组相加会将最先出现的值作为结果...
Image-merge.rar_Image.merge_image merge应用_merge image
09-14
本文将深入探讨“Image Merge”这一主题，它涉及到如何将两幅图像合并成一幅新的图像。我们将主要关注`image.merge`功能以及其在实际应用中的使用。 在OpenCV库中，`merge`函数是一个关键的图像处理操作，它允许...
MergeList_Sq.zip_MergeList Sq_MergeList_Sq
09-20
这个问题在排序算法和数据处理中非常常见，例如在数据库查询和数据分析中。 标题“MergeList_Sq.zip_MergeList Sq_MergeList_Sq”暗示了我们正在处理一个与线性表合并相关的项目，可能是一个程序或代码库，其中包含...
MySQL的index merge(索引合并）导致数据库死锁分析与解决方案 | 京东云技术团队
最新发布
JDDTechTalk的博客
10-13 969
index merge(索引合并)：该数据库查询优化的一种技术，在mysql 5.1之后进行引入，它可以在多个索引上进行查询，并将结果合并返回。该死锁问题是因为优化器使用了合并索引问题导致的，最终通过新建一个联合索引来解决这个问题。参考文档：1作者：京东工业 李小辉来源：京东云开发者社区 转载请注明来源。
记一次ORA-00060: Deadlock detected的场景
Jalen备忘录
07-09 880
1. 查看被锁的表 select object_name, machine, s.sid, s.serial# from v$locked_object l, dba_objects o, v$session s where l.object_id = o.OBJECT_ID and l.session_id = s.sid; 2. 强制解锁: sid -> 277, serial# -> 1817, --需要dba权限，如果不是dba的话在客户端强制断开查询也会解锁恢.
mysql自动合并索引(index merge)查询导致死锁问题
AlgoRain的博客
03-17 627
mysql自动合并索引(index merge)查询导致死锁问题 虽然在生产环境上加了分布式锁，但还是会出现某一个事务未结束，而下一个事务进入来修改数据，这时就会陷入等待，最后等待超时，事务进行了回滚，在运行几个月后第一次出现这种情况，发生死锁的是两条update语句，当sql语句的where语句中使用两个索引时，mysql的优化器可能会对这两个索引进行合并，使用explain分析会显示Using intersect(index1,index2); 表示将index1和index2合并来查询。该表中只有in
mysql死锁报错循环_【MYSQL死锁问题】Deadlock found when trying to get lock;
weixin_34566436的博客
01-19 541
问题：在多访问的情况下，一个删除计划的操作会出现死锁现象，报错如下：### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transac...
Mysql索引合并（index merge）导致的死锁问题
zhibin的程序日记
09-13 1763
在mysql5.0之前，一个表仅仅能使用一个索引，从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。 可以使用explain来查看index merge Using intersect(idx_brand_id,idx_i_sort_id); 以上查询，使用了2个索引。大家也知道，mysql innodb引擎使用索引来实现行级别的锁，以下描...
【网易云商】记一次实遇的 MySQL--index merge 死锁历程
netease_im的博客
03-28 232
以 select * from table where name='ccc' 为例，MySQL 对 SQL 进行解析后发现 name 字段有索引可用，于是先在二级索引（图2）上根据 name='ccc' 找到主键 id=27，然后根据主键 27 到主键索引上（图1）上找到需要的记录。在实际业务开发过程中，MySQL 会由于许多原因造成死锁，本文就是基于网易云商的一次实遇死锁经验，讲述了一次由于 Index Merge 优化导致的死锁案例，并分享处理本次死锁所运用的方法。2. 使用主键索引进行更新。
Mysql因为索引造成死锁DEADLOCK
Sail__的专栏
05-27 2094
近日生产发生死锁，日志和表索引如下。 分析是 因为索引的原因，在循环对数据进行Update的时候，因为查询的结果集颗粒度很大，造成多个线程因为索引INX_ACCOUNT_ID产生了行级锁。 解决办法是，缩小查询结果集的颗粒度，就是缩小查询的结果范围，在循环Update的时候不会造成资源竞争。 循环如下 //新的循环 List<Map<String,Object>&gt...
深入分析MySQL索引优化导致的死锁问题
Java_Mike的博客
06-14 1326
死锁是我们在大学时就接触过的概念，由于鄙人资历尚浅，毕业后的开发过程中几乎没有碰到过死锁问题。然而常在河边走，哪有不湿鞋，最近就碰到了一个诡异的死锁问题，于是就抓住这次难得机会，对这次的死锁问题好好研究了一番。于是就总结出了这篇博客。 发现问题 某一天，我一如既往地对一个批量并发操作相关的业务进行自测，在自测过程中发现有两个线程执行失败了，通过日志发现是更新状态时导致的死锁问题。第一眼见到这个问题，我有点纳闷，明明更新的不是同一行记录，为什么会出现死锁呢？于是带着这个问题在网上找了一些大佬的文章，发现是索引
oracle行锁 merge,merge into引发死锁
weixin_29752191的博客
04-04 3036
create or replace procedure up_mid_66880002(v_rq number, --计算日期rc1out bssys.tp_cursor,rc2out bssys.tp_cursor)asnb_rq number;nb_ExecTime date;check_rq date;nb_rows number(10);v_status...
index_merge
07-28
index_merge是一种优化技术，它通过对两个独立的索引进行过滤，将过滤后的结果合并在一起，然后返回结果集。这种技术可以减少磁盘读取和CPU负载，从而提高查询性能。在某些情况下，index_merge可以使用index intersect merge算法或index union merge算法来优化查询。然而，需要注意的是，index_merge不适用于全文索引。在未来的MySQL版本中，计划扩展index_merge以支持全文索引。\[1\]\[2\]\[3\] #### 引用[.reference_title] - *1* [MySQL 优化之 index_merge （索引合并）](https://blog.csdn.net/qq_33095135/article/details/122188017)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* *3* [MySQL 优化之 index merge(索引合并)](https://blog.csdn.net/GarfieldEr007/article/details/106365350)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值