排查
报错日志
org.springframework.dao.CannotAcquireLockException:
### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: delete from yc_data_storage where data_obj_id in ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) and `is_deleted` = 0 and DATE_FORMAT(data_time,%Y-%m-%d)=?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction ; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded;
try restarting transaction at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:258) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74) at
发生业务场景
- 用户上传文件后，触发某任务，该任务分为清洗和计算两步骤
- 并发时可能会触发该问题
- 如：用户A先上传，先清洗后计算，该计算需要花费长达数十分钟，计算过程中存在大量的DB数据读取和写入；在用户A的任务计算中，用户再上传跑新的任务，触发新的清洗工作，新的清洗工作会调用delete操作请求锁表（由于没走索引导致锁表请求直到超时——Lock wait timeout exceeded）
超时日志截图 （错误截图）
看了代码后，截图截错了，并不是超时错误；
该截图下很长一段代码没有主动logger，所以并没有日志打印；
具体的单个db操作很快，不足以超时。
看一下数据库的锁请求的超时时间
mysql 事务锁超时时间 innodb_lock_wait_timeout （网址: https://www.cnblogs.com/xiluhua/p/9063660.html）
# 查询全局等待事务锁超时时间
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
处理措施
预估下表的数据规模
当前两年的数据为2M行，未来十年预估在千万行以内。
问题不大。
增加联合索引，更改sql确定走了索引，重新Explain一下上面的超时的SQL
delete操作走了索引，每次delete更新就只更新索引树，不会再引起等待超时的问题了。
另一方面，加了索引后，存储和执行的速度得到有效提升；效果如下：
确保增删改查都走索引：
### SQL: delete from yc_data_storage where data_obj_id in ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) and `is_deleted` = 0 and DATE_FORMAT(data_time,%Y-%m-%d)=?
上面的这个异常log里的日志的sql使用了DATE_FORMAT，及时加了索引也会导致索引失效。
分区与索引的考虑
分区和索引可以一起加上。
data_time列为分区，分区可以有效缓解数据膨胀，索引可以在一定程度中提升索引速度。
