HikariPool-1 - Connection is not available, request timed out after 30004ms.
报错 2024-04-11 11:01:00 [ERROR] org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[/].[dispatcherServlet] Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction] with root cause
java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30004ms.
问题复盘
接口并发测试，避免开启线程太多卡顿，不要开启太多。
测试工具采用Apifox
100线程并发测试上传报告接口，仅有极少部分正常运行，其余报错，ERROR:连接池连接无法获取，在30s后超时。
简单分析：线程池中线程持有数据库连接池的连接资源，但是阻塞在线程池中，进而导致出现请求超时问题，需要修改以避免线程池持有连接资源。
@PostMapping("/save")
public Result save(@Parameter(name = "report", description = "报告实体类", required = false) Report report) throws InterruptedException {
//保存Report
caseAudioUploadService.caseAudioUpload(report);
return Result.build(null, ResultCodeEnum.SUCCESS);
}线程池处理并发
@Resource
private ReportMapper reportMapper;
@Resource
private ReportAudioMapper audioMapper;
@Resource
private FilePathProperties filePathProperties;
@Transactional
@Override
public void caseAudioUpload(Report report) throws InterruptedException {
//根据用户id + 时间确定唯一报告编号
report.setIsGenerated(0);
//写入report
reportMapper.insertReport(report);
Long reportId = report.getId();
//写入report_audio
//Audio文件保存至磁盘,文件储存路径存入数据库
List<Audio> audioList = report.getAudioList();
if(audioList.isEmpty()) { return;}
DirectoryUtil.getInstance().createDirectory(filePathProperties.getAudioFilePath() + "/" + reportId.toString());
//遍历获取AV,PV,UV,TV
//读取不同音频并保存在磁盘中,并行保存至磁盘，不应该缓存，加快响应速度。
CountDownLatch countDownLatch = new CountDownLatch(audioList.size());
for(Audio audio : audioList) {
audio.setReportId(reportId);
AudioPoolUtil.getAudioPool().submit(() -> {
MultipartFile file = audio.getFile();
String originalFileName = file.getOriginalFilename();
assert originalFileName != null;
int startPointIndex = originalFileName.lastIndexOf(".");
String fileSuffix = originalFileName.substring(startPointIndex);
String filename = Strings.concat(audio.getType(), fileSuffix);
File dest = new File(filePathProperties.getAudioFilePath() + "/" + reportId + "/" + filename);
try {
file.transferTo(dest);
audio.setFilePath(dest.getAbsolutePath());
audioMapper.save(audio);
} catch (IOException e) {
throw new SpzxException(ResultCodeEnum.FILE_IO_ERROR);
} finally {
//确保出现异常也能释放资源，避免长时间阻塞引发系统崩溃
countDownLatch.countDown();
}
});
}
countDownLatch.await();
}业务分析
业务需求：上传一个待检测报告，包括用户基本信息（username，age，userId），以及待检测的1至4段音频（分别为AV、PV、UV、MV）。
当前业务逻辑是：报告基本字段存入数据库获取报告id，然后遍历Audio列表，通过AudioPoolUtil获取自定义线程池单例，提交任务：执行磁盘存储后，将该Audio单例相关字段存入数据库，利用CountDownLatch完成计数，等待所有磁盘加载线程执行完毕。考虑到保证Report表和Audio表这两个表存储数据的最终一致性，需要使用事务，这里就简单使用@Transactional注解来实现。
在10并发的情况下，该接口大概在150ms ~ 400ms之间波动，而不采用线程池直接遍历存入磁盘的方案，大概在200ms~500ms之间波动。这时候我们可以发现，并发处理磁盘加载后，并没有取得预期中四倍的时间效率，这是什么原因导致的呢？
仔细思考后，我们可以发现，在原有逻辑中，我们需要对Report表进行1次插入，对Audio表进行n次插入，而每一次插入都是需要与数据库通信的时间，并且由于audio表中采用自增主键id，每个audio实体都会对应一个id。而这个id自增又会与MySQL中的一个名为AUTO-INC锁的表锁相关。简单来说，由于表锁的存在，插入包含自增ID的语句不能并行执行。SQL语句执行成为当前逻辑的瓶颈，根据上传音频数量的不同粗略估算为2RTT ~ 5RTT，需要更改数据库，将对Audio表的插入操作合并，这样2RTT（Report + Audio）即可。
来自小林Coding：
AUTO-INC，表级锁，顾名思义就是锁定整张表，其他语句需要等待该锁释放后才能执行。
在插入数据时，AUTO_INCREMENT对应的字段加上轻量级锁，然后给该字段赋值，之后释放轻量级锁。
InnoDB存储引擎中与之相关的系统变量： innodb_autoinc_lock_mode
- 当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；
- 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
- 当 innodb_autoinc_lock_mode = 1：
- 普通 insert 语句，自增锁在申请之后就马上释放；
- 类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，主库将执行的语句发送给从库，由于语句执行数据的问题，在「主从复制的场景」中会发生数据不一致的问题，需要改用 binlog_format = row,将存储的数据的二进制格式发送给从库，这样就不会重新生成id，避免主从库不一致。
修改代码
将insert操作合并，过滤掉filePath为空的audio，这样也就不会因为线程池中部分任务阻塞在阻塞队列中，进而因为连接超时，而出现错误。同时，由于每个线程会单独修改其对应的audio的字段，不会操作同一个对象实例，没有线程安全隐患。
@Transactional
@Override
public void caseAudioUpload(Report report) throws InterruptedException {
......
CountDownLatch countDownLatch = new CountDownLatch(audioList.size());
for(Audio audio : audioList) {
AudioPoolUtil.getAudioPool().submit(() -> {
.......
try {
file.transferTo(dest);
audio.setFilePath(dest.getAbsolutePath());
//将save移出
} catch (IOException e) {
throw new SpzxException(ResultCodeEnum.FILE_IO_ERROR);
} finally {
countDownLatch.countDown();
}
});
}
countDownLatch.await();
//一次全部insert
audioMapper.saveAll(audioList.stream().filter(item -> item.getFilePath() != null).toList());
}测试结果
并发100
不采用进程池策略，平均2000ms。
采用进程池策略，平均500ms。
根据以上的测试结果，可以看到，使用线程池来处理上传能够显著提升上传速度。
并发1000
虽然在并发100的情况下，取得了当前还算比较满意的结果，但是在1000并发的情况下还是会存在一些问题，而这些问题还没有解决，需要之后再来完善，测试结果如下。
1000线程测试，CPU飙升，然后内存与磁盘接近99%，IDEA短暂黑屏。
在较高并发的场景下，单机已经不足以支撑业务正常运行了，接口的平均响应时间由500ms延迟到了5000ms，甚至出现了部分接口未测试的情况（不太明白，可能是因为丢包问题？）
完整报错
[ERROR] org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[/].[dispatcherServlet] Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction] with root cause
java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30004ms.
at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:181)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:146)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
…
