死锁问题排查

线上得到：
org.springframework.dao.CannotAcquireLockException: JDBC exception executing SQL [delete from ad_monetization where id<=? and advertising_network=? and config_key_id=? and day between ? and ?] [Deadlock found when trying to get lock; try restarting transaction] [n/a]; SQL [n/a]

由于没有开启死锁日志记录：

SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF   |
+----------------------------+-------+

只能通过SHOW ENGINE INNODB STATUS查看最后一次死锁的信息。
SHOW ENGINE INNODB STATUS命令的输出太长，输出到文件里看：

mysql -h域名 -u用户名 -p密码 -P端口 xxx_prod -e "SHOW ENGINE INNODB STATUS\G" > innodb_status.txt

从最近一次死锁部分开始分页看：

grep -A 3000 "LATEST DETECTED DEADLOCK" innodb_status.txt | less

分析死锁信息

识别事务：确定是哪几个事务（通常是两个）参与了死锁。
识别 SQL 语句：查看每个事务在死锁发生时正在执行或等待执行的 SQL 语句是什么。
识别资源和锁：

看事务 A 持有了哪些资源（记录锁、间隙锁等）。
看事务 A 正在等待哪些事务 B 持有的资源。
看事务 B 持有了哪些资源。
看事务 B 正在等待哪些事务 A 持有的资源。
形成一个 A 等待 B，B 等待 A 的循环等待关系。

本次死锁由两个删除事务导致。
delete from ad_monetization where id<=12719708 and advertising_network='Mintegral' and config_key_id=5 and day between '2025-03-29' and '2025-03-31';
和
delete from ad_monetization where id<=12719708 and advertising_network='Facebook' and day between '2025-03-29' and '2025-03-31';

由于要支持按key回滚数据，删除时增加了config_key_id条件，所以把原来advertising_network、day联合索引改成了advertising_network、config_key_id、day联合索引。

 show index from ad_monetization;
+-----------------+------------+-----------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table           | Non_unique | Key_name        | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------------+------------+-----------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| ad_monetization |          0 | PRIMARY         |            1 | id                  | A         |     1464725 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| ad_monetization |          1 | idx_an_ckid_day |            1 | advertising_network | A         |          11 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| ad_monetization |          1 | idx_an_ckid_day |            2 | config_key_id       | A         |          13 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| ad_monetization |          1 | idx_an_ckid_day |            3 | day                 | A         |        1215 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-----------------+------------+-----------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

已改造的渠道Mintegral等，走idx_an_ckid_day回滚数据：

explain delete from ad_monetization where id<=12719708 and advertising_network='Mintegral' and config_key_id=5 and day between '2025-03-29' and '2025-03-31';
+----+-------------+-----------------+------------+-------+-------------------------+-----------------+---------+-------------------+------+----------+-------------+
| id | select_type | table           | partitions | type  | possible_keys           | key             | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-----------------+------------+-------+-------------------------+-----------------+---------+-------------------+------+----------+-------------+
|  1 | DELETE      | ad_monetization | NULL       | range | PRIMARY,idx_an_ckid_day | idx_an_ckid_day | 1044    | const,const,const | 3093 |   100.00 | Using where |
+----+-------------+-----------------+------------+-------+-------------------------+-----------------+---------+-------------------+------+----------+-------------+

而还未改造的渠道Facebook等，走主键索引删除数据：

explain delete from ad_monetization where id<=12719708 and advertising_network='Facebook' and day between '2025-03-29' and '2025-03-31';
+----+-------------+-----------------+------------+-------+-------------------------+---------+---------+-------+--------+----------+-------------+
| id | select_type | table           | partitions | type  | possible_keys           | key     | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+-----------------+------------+-------+-------------------------+---------+---------+-------+--------+----------+-------------+
|  1 | DELETE      | ad_monetization | NULL       | range | PRIMARY,idx_an_ckid_day | PRIMARY | 8       | const | 733365 |   100.00 | Using where |
+----+-------------+-----------------+------------+-------+-------------------------+---------+---------+-------+--------+----------+-------------+

导致未改造渠道删除事务加锁范围过大，形成锁竞争。

解决方法

未改造渠道，加个config_key_id is null删除条件，让它走idx_an_ckid_day索引删除数据。

explain delete from ad_monetization where id<=12719708 and advertising_network='Facebook' and day between '2025-03-29' and '2025-03-31' and config_key_id is null;
+----+-------------+-----------------+------------+-------+-------------------------+-----------------+---------+-------------------+------+----------+-------------+
| id | select_type | table           | partitions | type  | possible_keys           | key             | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-----------------+------------+-------+-------------------------+-----------------+---------+-------------------+------+----------+-------------+
|  1 | DELETE      | ad_monetization | NULL       | range | PRIMARY,idx_an_ckid_day | idx_an_ckid_day | 1044    | const,const,const |    1 |   100.00 | Using where |
+----+-------------+-----------------+------------+-------+-------------------------+-----------------+---------+-------------------+------+----------+-------------+