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之间插入数据