mysql死锁定位

最新推荐文章于 2024-07-01 14:06:48 发布

1316901704@qq.com

最新推荐文章于 2024-07-01 14:06:48 发布

阅读量496

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_44399790/article/details/127011224

版权

本文介绍了如何通过查询`information_schema.innodb_trx`来查看MySQL中的锁状态，特别是针对RUNNING状态的SQL，揭示了可能由间隙锁导致的死锁问题。通过示例展示了如何使用`KILL`命令解除死锁，并分析了由于并发查询中索引命中导致的死锁情况。

摘要由CSDN通过智能技术生成

1、查看锁的相关信息

select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx;
查看trx_started = RUNNING 的trx_query

trx_state	trx_started	trx_mysql_thread_id	trx_query
LOCK WAIT	2022-09-23 14:52:40	9340526
LOCK WAIT	2022-09-23 14:52:40	9341570
LOCK WAIT	2022-09-23 14:52:40	9342466
LOCK WAIT	2022-09-23 14:52:40	9340851
RUNNING	2022-09-23 10:12:35	9312245	NULL
RUNNING	2022-09-23 14:52:40	9342015
RUNNING	2022-09-23 14:52:40	9342006	SELECT * FROM pdt_product_price a WHERE EXISTS ( SELECT 1 FROM ( SELECT product_id, max(batch_num) maxbatch FROM pdt_product_price WHERE deleted = 0 AND crowd_type = 0 AND start_time <= 1663915972750 AND end_time >= 1663915972750 AND varint_code = null GROUP BY product_id ) b WHERE a.product_id = b.product_id AND a.batch_num = b.maxbatch )
RUNNING	2022-09-23 14:52:40	9341844	SELECT * FROM pdt_product_price a WHERE EXISTS ( SELECT 1 FROM ( SELECT product_id, max(batch_num) maxbatch FROM pdt_product_price WHERE deleted = 0 AND crowd_type = 0 AND start_time <= 1663915972469 AND end_time >= 1663915972469 AND product_id IN ( 158114232664067 ) GROUP BY product_id,trade_attr ) b WHERE a.product_id = b.product_id AND a.batch_num = b.maxbatch ) and a.crowd_type = 0 LIMIT 10

2、解决死锁

KILL trx_mysql_thread_id；

例子 kill 9341844

3、分析

通过RUNNING状态的SQL可以看出都是select导致的死锁，

这时候可以联想出命中索引导致的间隙锁导致的死锁。

一、
#开启事务一
begin;

#事务一在1-5之间加间隙锁

二、
#开启事务二
begin;

#事务二在6-9之间加间隙锁

三、
#事务一在6-9之间插入数据

关注