Mysql索引不当引发死锁问题

1. 前言

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

2.遇到的问题

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

sql

代码解读

复制代码

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)

2.1.2原因

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

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

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

看一下我们创建的索引

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

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

索引失效了

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

(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会回滚其中一个事务，其他的会正常执行，如果偶尔出现一次死锁是可以接受的，但如果大面积的出现死锁，整个系统的性能就会下降

2.1.3解决方法

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

那如何控制并发量呢？

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

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

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

归根到底，还是索引问题

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

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