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

待我称王封你为后i 2024-04-20 11:40 77阅读 0赞

#### 亲身经历的间隙锁问题 ####

*  1、问题描述
 *  2、排查过程
 *  3、分析优化

## 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） — 唯一索引索引，唯一索引可以避免这个间隙锁问题