记一次MySQL死锁问题

云里有个皮皮
848 阅读7分钟

出现场景

年前，线上数据库报了一个死锁问题。 首先，表结构：

CREATE TABLE `miza_log`.`blind_date_user_info` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
    `blind_date_id` int(11) NOT NULL DEFAULT '0' COMMENT '本轮相亲id',
    `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
    `choose_id`  int(11) NOT NULL DEFAULT '0' COMMENT '用户选择的心仪对象id',
    `recv_gift` int(11) NOT NULL DEFAULT '0' COMMENT '用户本轮收礼总额',
    `send_gift` int(11) NOT NULL DEFAULT '0' COMMENT '用户本轮送礼总额',
    `hat_num` int(11) NOT NULL DEFAULT '0' COMMENT '带帽次数',
    `img_url`  varchar(256) NOT NULL DEFAULT '0' COMMENT '用户结果图片',
    `match_result` int(11) NOT NULL DEFAULT '0' COMMENT '0: 配对不成功 1: 配对成功 2: 配对成功且已经发送小秘书',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (`id`),
    KEY `blind_date_id` (`blind_date_id`),
    KEY `user_id` (`user_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户参与相亲信息';

id, blind_date_id 和 user_id 分别建了索引。

发生死锁的事务sql:

update blind_date_user_info set hat_num = hat_num + 1 where blind_date_id = 344 and  user_id = 2102142;

update blind_date_user_info set recv_gift = recv_gift + 0 where blind_date_id = 344  and user_id = 99264271;

sql的执行计划:

image.png

死锁日志:

=====================================
2021-02-01 16:32:45 0x7f385337f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 2 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1101094 srv_active, 0 srv_shutdown, 40 srv_idle
srv_master_thread log flush and writes: 1101061
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 566444
OS WAIT ARRAY INFO: signal count 2252846
RW-shared spins 0, rounds 2205774, OS waits 248328
RW-excl spins 0, rounds 36978240, OS waits 84670
RW-sx spins 1320303, rounds 11237389, OS waits 54800
Spin rounds per wait: 2205774.00 RW-shared, 36978240.00 RW-excl, 8.51 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-02-01 16:30:23 0x7f376cf43700
*** (1) TRANSACTION:
TRANSACTION 1928467447, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 10 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 481042, OS thread handle 139877068830464, query id 1006475452 172.17.0.83 root updating
update blind_date_user_info set hat_num = hat_num + 1 where blind_date_id = 344 and  user_id = 2102142
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12499 page no 66 n bits 280 index PRIMARY of table `miza_log`.`blind_date_user_info` trx id 1928467447 lock_mode X locks rec but not gap waiting
Record lock, heap no 186 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 4; hex 800020fc; asc     ;;
 1: len 6; hex 000072f213f6; asc   r   ;;
 2: len 7; hex 770000707a116f; asc w  pz o;;
 3: len 4; hex 80000158; asc    X;;
 4: len 4; hex 85eaa70f; asc     ;;
 5: len 4; hex 80000000; asc     ;;
 6: len 4; hex 80001e46; asc    F;;
 7: len 4; hex 800003e8; asc     ;;
 8: len 4; hex 8000001f; asc     ;;
 9: len 0; hex ; asc ;;
 10: len 4; hex 80000000; asc     ;;
 11: len 4; hex 6017b6b9; asc `   ;;
 12: len 4; hex 6017bc1f; asc `   ;;

*** (2) TRANSACTION:
TRANSACTION 1928467448, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
7 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 546874, OS thread handle 139876027873024, query id 1006475453 172.17.0.2 root updating
update blind_date_user_info set recv_gift = recv_gift + 0 where blind_date_id = 344  and user_id = 99264271
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 12499 page no 66 n bits 280 index PRIMARY of table `miza_log`.`blind_date_user_info` trx id 1928467448 lock_mode X locks rec but not gap
Record lock, heap no 186 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
 0: len 4; hex 800020fc; asc     ;;
 1: len 6; hex 000072f213f6; asc   r   ;;
 2: len 7; hex 770000707a116f; asc w  pz o;;
 3: len 4; hex 80000158; asc    X;;
 4: len 4; hex 85eaa70f; asc     ;;
 5: len 4; hex 80000000; asc     ;;
 6: len 4; hex 80001e46; asc    F;;
 7: len 4; hex 800003e8; asc     ;;
 8: len 4; hex 8000001f; asc     ;;
 9: len 0; hex ; asc ;;
 10: len 4; hex 80000000; asc     ;;
 11: len 4; hex 6017b6b9; asc `   ;;
 12: len 4; hex 6017bc1f; asc `   ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12499 page no 53 n bits 720 index blind_date_id of table `miza_log`.`blind_date_user_info` trx id 1928467448 lock_mode X locks rec but not gap waiting
Record lock, heap no 621 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000158; asc    X;;
 1: len 4; hex 800020fc; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

分析

从死锁日志可以知道，当前有两个事务正在执行，而且使用了同一个索引(blind_date_id)。事务1锁住的资源为：

RECORD LOCKS space id 12499 page no 66 n bits 280 index PRIMARY of table `miza_log`.`blind_date_user_info` trx id 1928467447 lock_mode X locks rec but not gap waiting
Record lock, heap no 186 PHYSICAL RECORD: n_fields 13; compact format; info bits 0

值得注意的是：locks res but not gap 代表锁住的是一个索引，不是一个范围

同理，事务2锁住的资源：

RECORD LOCKS space id 12499 page no 66 n bits 280 index PRIMARY of table `miza_log`.`blind_date_user_info` trx id 1928467448 lock_mode X locks rec but not gap
Record lock, heap no 186 PHYSICAL RECORD: n_fields 13;

可以看到，事务2也在和事务1竞争相同的资源。而且两个事务都使用了 X Lock(排他锁，在 《mysql-innodb技术内幕》 锁的章节讲到，X 锁与任何的锁都不兼容)，出现相互等待。

事务1中锁住4行记录，事务2锁住3行记录。

问题 1、是什么场景下导致了两个事务并发？ 2、事务1和事务2中是更新不同的行，为什么要对索引进行加锁呢？

第一个问题需要我们回到业务场景中去寻找，在业务代码中找到调用这两个sql语句的代码：

image.png

在业务中，使用了两个Goroutine去执行了两个语句。所以出现并发的情况并不奇怪。

回到问题2,需要在理解mysql在执行这个语句之前都做了写什么。

首先，在mysql有三种行锁算法：

  • Record Lock: 单个行记录上锁
  • Gap Lock: 间隙锁，锁定一个范围，但不包含记录本身
  • Next-Key Lock: Gap Lock + Record Lock 锁定一个范围，并且锁定记录本身。

本次涉及的锁是第一种锁Record Lock, 所以本文还是主要针对问题进行分析，至于其他锁和原理，后面会写相应的博客分析。值得注意的是Rcord Lock总是会去锁住索引记录。

innodb的行锁是通过给索引上的索引项加锁实现的，也就是说只有通过索引条件检索数据，innodb才是用行级锁，否则innodb将是用表锁，会把所有扫描过的行都锁定。 在这个案例中，update时使用了两个索引blind_date_id 和 user_id。根据explian语句分析sql可以知道，两个语句都是范围查找，而且最终的使用了user_id这个索引。根据extra标识为using where, innodb 会去扫描返回的行数，然后进行加锁。 具体解释为，事务1为例，首先 innodb 会首先查找 user_id = 99264271的行记录并返回，然后再根据blind_date_id = 344进行扫描进行过滤。在这个过程中，所有扫描的数据都会被锁定。 同理，事务2也是一样，都要对两个索引进行加锁，并在扫描过程也会加锁。最终就导致了相同的行数被两个事务同时加锁，就造成了死锁。

处理方法
经过分析可以知道死锁是由于innodb在扫描中出现的，所以有两个方法： 1.可以对blind_date_id和user_id捡一个联合索引，这样就可以在执行事务过程中使用using index确定到具体的行, 从而避免死锁。 2.

select id form blind_date_user_info where where blind_date_id = 344 and  user_id = 2102142;
update blind_date_user_info set hat_num = hat_num + 1 where id in (....);

这样采用先select后update的方法。注意，这里select 不能加for update, 不然还是会造成死锁。

方法1: 直接添加一个索引就可以解决;
方法2: 会出现幻读的情况，但是由于本身业务对于数据的精确度不高，所以最终解决方法采用了第二种。同时只是用一个goroutine执行两个事务。

参考：

《MySQL 技术内幕-InnoDB 存储引擎》第二版，作者：姜承尧
cloud.tencent.com/developer/a… yq.aliyun.com/articles/33… dev.mysql.com/doc/refman/… www.fanyilun.me/2017/04/20/…

才疏学浅，如有错误，请评论指正，不胜感激!
avatar
文章
阅读
粉丝