Mysql索引不当引发死锁问题

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

Mysql索引不当引发死锁问题

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.兑奖 1.未兑奖', status tinyint(1) not null comment '0 正常', deleted tinyint(1) default 0 not null comment '逻辑删除 0.未删除 1.删除', 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)

2.1.2原因

锁等待原因:mysql对每个update增加排它锁,更新完成之后,释放锁,其他更新操作执行,mysql对于更新操作是串行的。

在大并发量的前提下,如果update语句慢,会造成排队现象。

这个时候我们不禁想问,为什么update语句这么慢呢?

看一下我们创建的索引

index idx_ins_staton t_award (pool_id, identifier,status, is_redeemed);

从执行计划中得知并没有走我们创建的索引,是什么原因呢?

Mysql索引不当引发死锁问题

索引失效了

什么原因导致索引失效呢?

(pool_id, identifier,status, is_redeemed) 索引中identifier 该字段允许为null,这会导致identifier后边的字段失效,从而导致整个联合索引会失效,看来是索引的问题

那为什么会死锁呢?

还记得pool_id是普通索引吗,在执行以下sql的时候

where pool_id=19021 and identifier is null and status=0 and is_redeemed=0 limit 1

pool_id加间隙锁,当互相持间隙锁的时候,就造成了死锁,看下图。

Mysql索引不当引发死锁问题

当出现死锁的时候,mysql会回滚其中一个事务,其他的会正常执行,如果偶尔出现一次死锁是可以接受的,但如果大面积的出现死锁,整个系统的性能就会下降

2.1.3解决方法

从上边分析的原因得知,造成死锁的原因是有大量并发的更新导致,如果想要解决死锁问题,那我们就要控制并发数量。

那如何控制并发量呢?

在单位时间内减少请求的数量,可以采用在程序中加锁的方式。但这种方式会导致系统的性能下降。

再次分析导致死锁的现象,我们发现在死锁出现之前有大量的锁等待,如果在单位时间内能减少锁等待的update语句数量,是不是可能会出现转机?

紧接着我们把优化的方向放到了 update语句上,怎样才让update语句执行的更快呢?

归根到底,还是索引问题

既然分析清楚了索引失效的原因,那就好解决了,调整一下索引创建顺序是不是就可以了。

在创建索引的时候,把identifier放到了最后,调整后的索引为(pool_id, status, is_redeemed, identifier);

本文转载自:https://juejin.cn/post/7368294440547156006

Apipost 私有化火热进行中

评论