Mysql索引不当引发死锁问题

宅哥聊构架数据库 2024-08-16

1. 前言

在并发量很低的情况下，mysql的响应时延一切正常，一旦并发量上去了，mysql就会出现死锁的情况，你有没有遇到过？到底是是什么原因导致的呢，让我们一起看看真实的案例。

2.遇到的问题

先介绍一下我们的库表结构，数据库表中的数据为500wsql

 代码解读
复制代码
create table t_award
(
    id            bigint(30)            not null primary key,
     award_no     varchar(30)           not null comment '奖券',
    award_pwd     varchar(100)          not null comment '奖券密码',
    pool_id    int(20)    default 0  not null comment 'poolId',
    is_redeemed      tinyint(1) default 0  not null comment '0.兑奖　１.未兑奖',
    status        tinyint(1)            not null comment '0 正常',
    deleted      tinyint(1) default 0  not null comment '逻辑删除　０．未删除　１．删除',
    identifier      varchar(100)          null,
    identifier_type    varchar(20)           null comment '身份类型',
    constraint award_no
        unique (award_no),
    constraint uniq_ins_identifier
        unique (pool_id, identifier, identifier_type)
)
    engine = InnoDB
    charset = utf8;

create index identifier
    on t_award (identifier);

create index idx_pool
    on t_award (pool_id);
   
create index idx_ins_stat
    on t_award (pool_id, identifier,status, is_redeemed);

唯一索引： unique (award_no) 和unique (pool_id, identifier, identifier_type)

普通索引：identifier，pool_id ，index(pool_id, identifier,status, is_redeemed)

根据业务场景，需要从抽奖池中获取一个没有兑换过奖的奖券，执行的sql为csharp

 代码解读
复制代码
select id from t_award where pool_id=? and identifier is null and status=0 and is_redeemed=0 limit 1;

2.1 问题1: 死锁

2.1.1现象

从压测的第30s开始，QPS一下从1000骤降到100，紧接着就是十几了，响应时延TP95从10+ms上升到1s

从mysql的监控上看，有一堆像这样的sql语句排队等待更新js

 代码解读
复制代码
update t_award 
set identifier=?, identifierType=? 
where pool_id=? and identifier is null and status=0 and is_redeemed=0 limit 1;

紧接着出现了死锁的情况js

 代码解读
复制代码
trascation 1

WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 70 page no 699223 n bits 144 index PRIMARY of table `test`.`t_ward` trx id 79626302 lock_mode X locks rec but not gap waiting Record lock, heap no 74 PHYSICAL RECORD: n_fields 37; compact format; info bits 0 0: len 8;
update t_award 
set identifier=?, identifierType=? 
where pool_id=19021 and identifier is null and status=0 and is_redeemed=0 limit 1

trascation2 79626303 HOLDS THE LOCK(S)