MySQL Lock--Index intersect导致的死锁
涉及表结构
CREATE TABLE `am_friend_send_link` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`task_id` int(11) NOT NULL COMMENT '任务id',
`robot` int(11) DEFAULT NULL COMMENT '机器人logicId',
`wechat_username` varchar(30) DEFAULT NULL COMMENT '用户微信id',
`has_send` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否发送链接 0未发送/1已发送',
`has_join` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已进群 0未加入/1已加入',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'create_time',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update_time',
`send_type` tinyint(1) unsigned DEFAULT '0' COMMENT '发送类型 0：二维码 1：短链',
PRIMARY KEY (`id`),
KEY `idx_create_time` (`create_time`),
KEY `idx_task_id` (`task_id`),
KEY `idx_wechat_username` (`wechat_username`)
) ENGINE=InnoDB AUTO_INCREMENT=1137187 DEFAULT CHARSET=utf8mb4 COMMENT='加粉号-发送链接统计'
涉及SQL
update um.am_friend_send_link
set has_join = true
where task_id in (154)
and wechat_username = 'user0002';
update um.am_friend_send_link
set has_join = true
where task_id in (154)
and wechat_username = 'user0001';
三种执行计划（可能）
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: am_friend_send_link
type: index_merge
possible_keys: idx_task_id,idx_wechat_username
key: idx_wechat_username,idx_task_id
key_len: 123,4
ref: NULL
rows: 1
Extra: Using intersect(idx_wechat_username,idx_task_id); Using where; Using temporary
1 row in set (0.00 sec)
*************************** 1. row ***************************
id: 1
select_type: UPDATE
table: am_friend_send_link
partitions: NULL
type: range
possible_keys: idx_task_id,idx_wechat_username
key: idx_wechat_username
key_len: 123
ref: const
rows: 1
filtered: 100.00
Extra: Using where
1 row in set (0.00 sec)
*************************** 1. row ***************************
id: 1
select_type: UPDATE
table: am_friend_send_link
partitions: NULL
type: range
possible_keys: idx_task_id,idx_wechat_username
key: idx_task_id
key_len: 123
ref: const
rows: 1
filtered: 100.00
Extra: Using where
1 row in set (0.00 sec)
死锁信息
LATEST DETECTED DEADLOCK
------------------------
2020-06-15 23:00:28 7efcc9d29700
*** (1) TRANSACTION:
TRANSACTION 206352932923, ACTIVE 0.005 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 122 lock struct(s), heap size 30248, 4 row lock(s)
LOCK BLOCKING MySQL thread id: 746657886 block 696343511
MySQL thread id 696343511, OS thread handle 0x7efc98463700, query id 287972470324 172.16.1.176 um Searching rows for update
update um.am_friend_send_link set has_join = true where task_id in (154) and wechat_username = 'user0002'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11663 page no 11196 n bits 272 index `PRIMARY` of table `um`.`am_friend_send_link` trx id 206352932923 lock_mode X locks rec but not gap waiting
Record lock, heap no 169 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
0: len 4; hex 0011397e; asc 9~;; ## id=1128830
1: len 6; hex 00300b951d5d; asc 0 ];;
2: len 7; hex aa000100390110; asc 9 ;;
3: len 4; hex 8000009a; asc ;; ## task_id=154
4: len 4; hex 8002bfcd; asc ;;
5: len 11; hex 6861696c692d7a68616e67; asc user0001;;
6: len 1; hex 80; asc ;;
7: len 1; hex 80; asc ;;
8: len 5; hex 99a69f6e92; asc n ;;
9: len 5; hex 99a69f6e92; asc n ;;
10: len 1; hex 00; asc ;;
*** (2) TRANSACTION:
TRANSACTION 206352932938, ACTIVE 0.002 sec fetching rows, thread declared inside InnoDB 3894
mysql tables in use 3, locked 3
118 lock struct(s), heap size 30248, 3 row lock(s)
MySQL thread id 746657886, OS thread handle 0x7efcc9d29700, query id 287972470345 172.16.1.176 um Searching rows for update
update um.am_friend_send_link set has_join = true where task_id in (154) and wechat_username = 'user0001'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 11663 page no 11196 n bits 272 index `PRIMARY` of table `um`.`am_friend_send_link` trx id 206352932938 lock_mode X locks rec but not gap
Record lock, heap no 169 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
0: len 4; hex 0011397e; asc 9~;; id=1128830
1: len 6; hex 00300b951d5d; asc 0 ];;
2: len 7; hex aa000100390110; asc 9 ;;
3: len 4; hex 8000009a; asc ;; task_id=154
4: len 4; hex 8002bfcd; asc ;;
5: len 11; hex 6861696c692d7a68616e67; asc user0001;; ## wechat_username='user0001'
6: len 1; hex 80; asc ;;
7: len 1; hex 80; asc ;;
8: len 5; hex 99a69f6e92; asc n ;;
9: len 5; hex 99a69f6e92; asc n ;;
10: len 1; hex 00; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11663 page no 9224 n bits 552 index `idx_task_id` of table `um`.`am_friend_send_link` trx id 206352932938 lock_mode X locks rec but not gap waiting
Record lock, heap no 481 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000009a; asc ;; task_id=154
1: len 4; hex 0011397e; asc 9~;; id=1128830
*** WE ROLL BACK TRANSACTION (2)
加锁顺序(猜测):
1、事务206352932938按照wechat_username='user0001'在索引idx_wechat_username(wechat_username,id)上找到索引记录('user0001',1128830)并加锁成功。
2、事务206352932938按照id=1128830在主键索引Primary Key(id)上找到索引记录(1128830)并加锁成功。
3、事务206352932923按照task_id=154在索引idx_task_id(task_id,id)上找到索引记录(154,1128830)并加锁成功。
4、事务206352932923按照id=1128830在主键索引Primary Key(id)上找到索引记录(1128830)并尝试加锁，该锁资源已被事务206352932938持有，加锁失败并等待锁资源。
5、事务206352932938按照task_id=154在索引idx_task_id(task_id,id)上找到索引记录(154,1128830)并尝试加锁，但该锁资源已被事务206352932923持有，加锁失败并等待锁资源，触发死锁检测机制。
6、死锁检测机制发现死锁环路，回滚事务206352932938。
问题思考
在MySQL官方文档中对intersect操作描述如下：
The Index Merge intersection algorithm performs simultaneous scans on all used indexes and produces the intersection of row sequences that it receives from the merged index scans.
intersect操作需要对所有涉及到的索引进行范围扫描，然后将有序的扫描结果求交集，如本例中对idx_task_id和idx_wechat_username分布按照task_id=154 和wechat_username='user0001' 进行范围扫描，得到的索引记录在id列上是有序的，能快速执行intersect操作，再按照intersect操作后的id值做Primary key lookup操作。intersect操作主要用于减少Primary key lookup次数。
在本例中事务206352932938已经对主键索引加锁成功，即已经读取到id=1128830的数据记录，可以在数据记录上进行task_id=154的条件过来，为何还会对索引idx_task_id上的记录进行范围扫描和加锁？
