死锁分析-（using intersect多个索引引发死锁）
MySQL: 8.0.32
参考：https://www.modb.pro/db/646651
线上有一个SQL偶然报出死锁信息，是一类根据唯一ID和status进行更新的SQL。
age是唯一字段，理论上来说根据唯一字段更新不应该出现死锁，但在update执行计划中发现，并不止使用了age索引，还使用了status索引。【Using intersect(uni_age,idx_name); Using where; Using temporary】，猜想高并发更新时，因为status字段原因，导致死锁
示例：
update t2 set status='success' where age=7 and status='c'; update t2 set status='success' where age=8 and status='c';
1、创建测试环境
CREATE TABLE `t2` (
`id` int NOT NULL,
`status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`age` int NOT NULL,
PRIMARY KEY (`id`,`age`) USING BTREE,
UNIQUE KEY `uni_age` (`age`) USING BTREE,
KEY `idx_status` (`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `t2` (`id`, `status`, `age`) VALUES (1, 'a', 1);
INSERT INTO `t2` (`id`, `status`, `age`) VALUES (4, 'a', 4);
INSERT INTO `t2` (`id`, `status`, `age`) VALUES (2, 'b', 2);
INSERT INTO `t2` (`id`, `status`, `age`) VALUES (5, 'b', 5);
INSERT INTO `t2` (`id`, `status`, `age`) VALUES (3, 'c', 3);
INSERT INTO `t2` (`id`, `status`, `age`) VALUES (6, 'c', 6);
INSERT INTO `t2` (`id`, `status`, `age`) VALUES (7, 'c', 7);
INSERT INTO `t2` (`id`, `status`, `age`) VALUES (8, 'c', 8);
INSERT INTO `t2` (`id`, `status`, `age`) VALUES (9, 'c', 9);
mysql> select * from t2 order by 1;
+----+--------+-----+
| id | status | age |
+----+--------+-----+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
| 4 | a | 4 |
| 5 | b | 5 |
| 6 | c | 6 |
| 7 | c | 7 |
| 8 | c | 8 |
| 9 | c | 9 |
+----+--------+-----+
9 rows in set (0.00 sec)
2、执行计划，测试环境中并不是这样显示的，手动修改后只为还原当时线的场景。
mysql> explain update t2 set status='success' where age=7 and status='c'; +----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-----------------------+ | 1 | UPDATE | t2 | NULL | range | uni_age,idx_status | uni_age,idx_status | 4 | const | 1 | 100.00 | Using intersect(uni_age,idx_status); Using where; Using temporary +----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+---------------------------------------+ 1 row in set, 1 warning (0.00 sec)
3、猜测死锁形成原因
事务1： update t2 set status='success' where age=7 and status='c'; 事务2： update t2 set status='success' where age=8 and status='c';
猜测 mysql 内部加锁流程如下
T1、事务1持有age=7行锁
T2、事务2持有age=8行锁
T3、事务1申请并持持有 [status='c'，primary=7]的行锁，但因为status不是唯一索引，它还需要继续向后扫描 [status='c'，primary=8]，但primary=8 行锁已经被事务2持有，只能等待。
T4、事务2申请 [status='c',primary=8]的行锁，但因为status不是唯一索引，先开始扫描[status='c',primary=6]，不是这条，继续扫描下一条[status='c'，primary=7]，这条记录primary上有锁，所以等待。 死锁条件产生：事务1等事务2的 primary key=8，事务2等事务1的 [status='c'，primary=7]行锁。
写的有点乱，问题发生在T3 T4时间点上。
4、复现流程：
T1-session1:
mysql> begin;select * from t2 where age=7 for update; +----+--------+-----+ | id | status | age | +----+--------+-----+ | 7 | c | 7 | +----+--------+-----+ 1 row in set (0.01 sec)
T2-session2:
mysql> begin;select * from t2 where age=8 for update; +----+--------+-----+ | id | status | age | +----+--------+-----+ | 8 | c | 8 | +----+--------+-----+ 1 row in set (0.00 sec)
T3-session1:
select * from t2 force index(idx_status) where status='c' and age=7 for update; --锁等待
T4-session2:
mysql> select * from t2 force index(idx_status) where status='c' and age=8 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction --死锁发生
下面在T3，session1锁等待时刻，看下 data_locks 表
mysql> select * from performance_schema.data_locks; +--------+--------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+--------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+ | INNODB | 5138815200:1674:4989188376 | 394384 | 217 | 68 | ceshi | t2 | NULL | NULL | NULL | 4989188376 | TABLE | IX | GRANTED | NULL | | INNODB | 5138815200:499:5:8:4991752216 | 394384 | 217 | 68 | ceshi | t2 | NULL | NULL | uni_age | 4991752216 | RECORD | X,REC_NOT_GAP | GRANTED | 8, 8 | | INNODB | 5138815200:499:4:11:4991752560 | 394384 | 217 | 68 | ceshi | t2 | NULL | NULL | PRIMARY | 4991752560 | RECORD | X,REC_NOT_GAP | GRANTED | 8, 8 | | INNODB | 5138815992:1674:4989189400 | 394383 | 218 | 86 | ceshi | t2 | NULL | NULL | NULL | 4989189400 | TABLE | IX | GRANTED | NULL | | INNODB | 5138815992:499:5:9:4991756824 | 394383 | 218 | 86 | ceshi | t2 | NULL | NULL | uni_age | 4991756824 | RECORD | X,REC_NOT_GAP | GRANTED | 7, 7 | | INNODB | 5138815992:499:4:10:4991757168 | 394383 | 218 | 86 | ceshi | t2 | NULL | NULL | PRIMARY | 4991757168 | RECORD | X,REC_NOT_GAP | GRANTED | 7, 7 | | INNODB | 5138815992:499:6:9:4991757512 | 394383 | 218 | 87 | ceshi | t2 | NULL | NULL | idx_status | 4991757512 | RECORD | X,REC_NOT_GAP | GRANTED | 'c', 7, 7 | | INNODB | 5138815992:499:6:10:4991757512 | 394383 | 218 | 87 | ceshi | t2 | NULL | NULL | idx_status | 4991757512 | RECORD | X,REC_NOT_GAP | GRANTED | 'c', 8, 8 | | INNODB | 5138815992:499:4:11:4991757856 | 394383 | 218 | 87 | ceshi | t2 | NULL | NULL | PRIMARY | 4991757856 | RECORD | X,REC_NOT_GAP | WAITING | 8, 8 | +--------+--------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+ 9 rows in set (0.00 sec)
218 是session1: 已经持有 uni_age primary这两个索引=7的行锁。并且还持有了 idx_status 索引上的 【c,7】 【c,8】 两个行锁，目前在等待 primary 索引=8 的行锁。
217是session2: 已经持有 uni_age primary这两个索引=8的行锁
