亲身经历的间隙锁问题以及优化过程

1、问题描述

我负责了一个数据量及并发量比较高的项目，其中有一个对外接口，高峰时并发（tps）在50左右，
这个接口会向一个表a插入1条数据，并在最后更新这条数据的状态，这张表数据量在8000w左右
突然有一天，偶发报错数据库这张表存在deadlock，一天大概出现了3、到5次报错
错误日志：

{index=ylog_9, message=2024-01-09 21:07:03.716||http-nio-8080-exec-4||ERROR||TID:f710574d-0org.springframework.dao.DeadlockLoserDataAccessException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ### The error may exist in com/FnaaaDao.java (best guess) ### The error may involve com.insertSelective-Inline
### The error occurred while setting parameters ### SQL: INSERT INTO table1  ( ............t ) VALUES( ?,?,?,?,?,........?,?,?,?,? ) ### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:267)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
	at com.sun.proxy.$Proxy172.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:271)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)

2、排查过程

首先介绍一下表table1的结构
id（int） — 自增主键
a （varchar） — 普通索引，但是每一个值在表里面是唯一的
c （varchar） — 普通索引，数据有重复的情况

经过查看代码，这个接口里面针对表table1数据操作的逻辑如下
1）插入1条数据
2）根据c select for update
3）根据a修改当前数据

3、分析优化

分析：
1）第2步select for update会导致多个间隙锁
2）第3步根据a修改当前数据，当前数据可能是数据库最新的一条数据，虽然a的值在业务上是唯一的，但是还是会导致间隙锁，锁住当前记录和正无穷

优化：
1）分析使用场景后，将其改为普通的select，删除for update
2）将a （varchar） — 普通索引 改为 a （varchar） — 唯一索引索引，唯一索引可以避免这个间隙锁问题