记一次Update语句死锁
业务背景
订单服务和消息盒子服务使用典型的生产者消费者模式，
订单状态变更, 产生消息, 发往SQS队列,
消息盒子服务通过订阅SQS队列消费消息，更新DB中订单消息的状态。
出现死锁问题
消息盒子服务多个线程消费订单状态消息, update DB中对应的订单记录, 出现死锁。
update语句如下:
update msgbox_message set record_status = -1 where record_status = 0 and gmt_create >= now() - INTERVAL 3 MONTH and msg_key = ‘SO146213662’ and target = ‘201307438’.
看起来是很普通的update语句, 不应该出现死锁。
msgbox_message 表结构如下 (精简版)
CREATE TABLE msgbox_message (
id BIGINT ( 20 ) UNSIGNED AUTO_INCREMENT,
gmt_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
record_status TINYINT ( 3 ),
msg_key VARCHAR ( 64 ),
target VARCHAR ( 32 ) COMMENT 'uid',
target_type TINYINT ( 3 ),
url VARCHAR ( 255 ),
PRIMARY KEY (id),
KEY target(target, target_type),
KEY msg_key(msg_key),
KEY gmt_create(gmt_create)
) ENGINE = INNODB
死锁日志分析
show engine innodb status的显示结果如下(删除了不必要的部分):
LATEST DETECTED DEADLOCK
2019-09-26 15:20:13
(1) TRANSACTION: TRANSACTION 149683649, thread id 659442, query id 956107029 IP1 ops_write updating
update msgbox_message set record_status = -1 where record_status = 0 and gmt_create >= now() - INTERVAL 3 MONTH and msg_key = ‘SO146213662’ and target = ‘201307438’
WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 118 page no 1399014 n bits 640 index target_idx of table cf_msgbox.msgbox_message;
trx id 149683649 lock_mode X locks rec but not gap waiting Record lock,
heap no 539 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ——(索引树的叶子节点数据)
0: len 9; hex 323031333037343338; asc 201307438;;
1: len 1; hex 00; asc ;;
2: len 8; hex 0000000004a135cb; asc 5 ;; ——(4a135cb是 16 进制的主键 ID 值77673931)
3: len 4; hex 5d8af714; asc ] ;;
..
(2) TRANSACTION: TRANSACTION 149683648, thread id 660492, query id 956107024 IP2 ops_write updating
update msgbox_message set record_status = -1 where record_status = 0 and gmt_create >= now() - INTERVAL 3 MONTH and msg_key = ‘SOxxxxxxx’ and target = ‘201307438’
HOLDS THE LOCK(S):
RECORD LOCKS space id 118 page no 1399014 n bits 640 index target_idx of table cf_msgbox.msgbox_message;
trx id 149683648 lock_mode X locks rec but not gap Record lock,
heap no 539 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ——(索引树的叶子节点数据)
0: len 9; hex 323031333037343338; asc 201307438;;
1: len 1; hex 00; asc ;;
2: len 8; hex 0000000004a135cb; asc 5 ;; ——(4a135cb是 16 进制的主键 ID 值77673931)
3: len 4; hex 5d8af714; asc ] ;;
WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 118 page no 1936298 n bits 152 index PRIMARY of table cf_msgbox.msgbox_message;
trx id 149683648 lock_mode X locks rec but not gap waiting Record lock,
heap no 13 PHYSICAL RECORD: ——(索引树的叶子节点数据)
n_fields 18; compact format; info bits 0
0: len 8; hex 0000000004a135cb; asc 5 ;;—— (4a135cb是 16 进制的主键 ID 值77673931)
1: len 4; hex 5d8af714; asc ] ;;
…….. 省略一些字段
WE ROLL BACK TRANSACTION (2)
我把部分关键的信息重点标注了出来，其中thread id是mysql的两个内部线程,
两个IP地址就是消息盒子服务机器的IP地址, 两条update语句只有where条件中的msg_key值不同。
- 事务 1 在等待 target_idx 索引树 (index target_idx) 中叶子节点 (heap no 539 PHYSICAL RECORD) 的 X 锁(互斥锁)—标记为 1 号锁🔒；
- 事务 2 已持有 1 号锁🔒, 在等待 PRIMARY 索引树 (index PRIMARY) 中叶子节点 (heap no 13 PHYSICAL RECORD) 的 X 锁—我们标记为 2 号锁🔒。
仅凭上面的日志, 好像没有死锁。死锁产生的必要条件是互相等待对方持有的锁。
唯一的解释是, 事务1的日志没有全部输出(不知道为啥), 事务1 必然持有了 2号锁🔒。
死锁---> 事务1 拿了2号锁🔒 并等待 1号锁；事务2 拿了 1号锁🔒 并等待 2号锁🔒
怎么回事
二级索引树(target_idx)和主键索引树(PRIMARY) 的锁🔒 都指向了id为77673931行记录。
msgbox_message表中 id=77673931的结果如下:
| id | gmt_create | record_status | msg_key | target | target_type | url |
|---|---|---|---|---|---|---|
| 77673931 | 2019-09-25 05:11:48 | 0 | SO146213662 | 201307438 | 0 | /orders#/SO146213662 |
msg_key列的值是 SO146213662, target列的值是201307438
事务1 中的where条件 利用target列的找到了该叶子节点,并等待该叶子节点的X锁(1号锁🔒),这是合理的。
事务2 中的where条件 利用target列的找到了该叶子节点,拿到了该叶子节点的X锁(1号锁🔒),这也是合理的。
但是
事务1 中的where条件 利用msg_key列可以查到 该行记录, 它拿到了该行叶子节点的锁(2号锁🔒)的合理的。
事务2 中的where条件 利用msg_key列是查不到 该行记录的, 它去等待 该行叶子节点的锁(2号锁🔒) 是不合理的。
结论
explain该update语句的结果如下：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | UPDATE | msgbox_message | p1910 | index_merge | target,msg_key,gmt_create | target,msg_key | 131,259 | NULL | 1 | 100.00 | Using intersect(target,msg_key); Using where |
type 列标记为了 index_merge(即索引合并方式), key和Extra列说明
使用了target 和msg_key这两个列的索引。关于 索引合并, 请移步官档。
explain的结果表明, innodb同时根据二级索引 target_idx和msg_key,
查找到二级索引树叶子节点中的主键ID值后,再根据主键ID索引查找行记录。
那么索引合并为什么就引起了死锁呢?
引起死锁的过程
两种可能
- 查找到即加锁。
先根据 msg_key 索引查找，对 msg_key 二级索引节点加 X 锁， 然后对相应的主键节点加 X 锁； 再根据 target 索引查找，然后对相应的主键节点加 X 锁;（msg_key 的索引选择性总是高于 target 索引）。这种方式 对于 造成死锁 逻辑上是成立的。
叶子节点 事务 1 事务 2 叶子节点 msg_key 的索引节点 1 号锁 (主键对应行记录 a) X 锁 1 成功 . . . . X 锁 2 成功 msg_key 的索引节点 2 号锁 ( 主键对应行记录 行记录 a 4 号锁 X 锁 4 成功 . . . . X 锁 5 成功 行记录 b 5 号锁 . . X 锁 3 成功 target 的索引节点 3(对应行记录 a) 3 号锁 . . 等待 X 锁 4 . target 的索引节点 3(对应行记录 a) 3 号锁 等待 X 锁 3 . .
- 查找，合并，加锁
根据 msg_key 和 target 索引对查找到的二级索引节点和主键节点 合并, 依次加锁。由于合并后的结果是无序的，两个事务 交叉对结果进行加锁造成死锁。 逻辑上是成立的，也包含了 上 面的情况。
好心办坏事
mysql 查询优化器 对于上述 sql 语句，其实不应该使用 index merge, 因为 msg_key 索引的选择性很高, 完全不需要再使用 target 列的索引。这也说明查询优化器并不是总能给出最优的结果。多次 explain 的结果表明，mysql 查询优化器 有时候给出使用 msg_key 索引及 index pushdown 方式 (最优的结果)。
另外，为什么要使用 索引合并 ? 大多数情况下，索引合并可以减少 数据页的 IO 访问次数。查询的时候可以直接根据索引合并后的结果集再去做 where 条件的过滤。但是不巧的是，update 的时候，根据索引合并的无序结果，加 X 锁，导致了并发时死锁（个人感觉这种问题属于 bug）。
解决办法
- 一个是 sql 语句中 强制使用 单个索引列 (最优解)
msg_key 列索引的选择性高于 target 列, 那么 sql 指定索引
update msgbox_message set record_status = -1 **force index(msg_key)**
where record_status = 0 and gmt_create >= now() - INTERVAL 3 MONTH and msg_key = 'SO146213662' and target = '201307438'.
- 另外一个, 业务代码中先 select 查出主键 ID 值， 再根据主键 ID 值去 update(处理速度肯定会慢一些)
