Java SpringBoot 数据库连接池死锁生产故障排查实战：从连接耗尽到彻底解决的完整处理过程
技术主题：Java 编程语言
内容方向：生产环境事故的解决过程（故障现象、根因分析、解决方案、预防措施）
引言
数据库连接池死锁是Java应用生产环境中最隐蔽也最危险的故障类型之一。当连接池中的连接被错误使用或事务管理不当时，可能导致所有连接被占用无法释放，最终造成应用完全无法响应。我们团队维护的一个核心业务SpringBoot应用，在某次业务高峰期突然出现了连接池死锁故障：所有数据库连接被耗尽，新请求无法获取连接，系统陷入假死状态。经过6小时的紧急排查，我们发现是长事务嵌套和连接泄漏共同导致的问题。本文将详细记录这次故障的完整排查和解决过程。
一、故障现象与初步诊断
故障发生时间线
1
2
3
4
5
6
7
|
2024-09-27 13:45:00 [INFO] 业务高峰期开始，请求量正常增长
2024-09-27 14:15:30 [WARN] 数据库响应时间开始变慢，平均300ms
2024-09-27 14:20:15 [ERROR] 出现连接获取超时异常
2024-09-27 14:22:45 [CRITICAL] 连接池使用率达到100%
2024-09-27 14:25:00 [EMERGENCY] 应用完全无法响应，所有接口超时
2024-09-27 14:25:30 [ACTION] 启动紧急故障处理流程
|
关键错误日志分析
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
public class ErrorLogAnalysis {
public static final String[] ERROR_LOGS = {
"java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.",
"org.springframework.transaction.TransactionTimedOutException: Transaction timed out: deadline was 180s",
"com.zaxxer.hikari.pool.HikariPool - Connection leak detection triggered for connection",
"com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded"
};
public static final Map<String, String> ABNORMAL_METRICS = Map.of(
"连接池活跃连接数", "20/20 (100%使用率)",
"连接等待队列长度", "500+ 请求排队",
"平均连接获取时间", "从5ms增长到30000ms",
"数据库活跃会话数", "350+ (正常50-80)",
"长事务数量", "15个超过5分钟的事务",
"应用响应时间", "从200ms增长到超时"
);
}
|
二、故障排查过程
1. 连接池状态分析
首先通过JMX和监控工具分析HikariCP连接池状态：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
@Component
public class ConnectionPoolDiagnostics {
@Autowired
private HikariDataSource dataSource;
public ConnectionPoolStatus getPoolStatus() {
HikariPoolMXBean poolBean = dataSource.getHikariPoolMXBean();
ConnectionPoolStatus status = new ConnectionPoolStatus();
status.setTotalConnections(poolBean.getTotalConnections());
status.setActiveConnections(poolBean.getActiveConnections());
status.setIdleConnections(poolBean.getIdleConnections());
status.setThreadsAwaitingConnection(poolBean.getThreadsAwaitingConnection());
log.info("连接池状态: 总连接={}, 活跃={}, 空闲={}, 等待线程={}",
status.getTotalConnections(),
status.getActiveConnections(),
status.getIdleConnections(),
status.getThreadsAwaitingConnection());
return status;
}
public List<String> analyzePoolIssues() {
List<String> issues = new ArrayList<>();
ConnectionPoolStatus status = getPoolStatus();
if (status.getActiveConnections() >= dataSource.getMaximumPoolSize()) {
issues.add("连接池已耗尽：所有连接都在使用中");
}
if (status.getThreadsAwaitingConnection() > 10) {
issues.add("大量线程等待连接：" + status.getThreadsAwaitingConnection() + "个线程排队");
}
if (getAverageConnectionAcquisitionTime() > 1000) {
issues.add("连接获取时间过长：" + getAverageConnectionAcquisitionTime() + "ms");
}
return issues;
}
private long getAverageConnectionAcquisitionTime() {
return 5000;
}
@Data
public static class ConnectionPoolStatus {
private int totalConnections;
private int activeConnections;
private int idleConnections;
private int threadsAwaitingConnection;
}
}
|
2. 问题代码定位
通过线程转储和代码分析，发现了导致连接池死锁的问题代码：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
|
@Service
@Transactional
public class ProblematicOrderService {
@Autowired
private OrderMapper orderMapper;
@Autowired
private InventoryService inventoryService;
@Autowired
private PaymentService paymentService;
@Transactional(timeout = 300)
public void processLargeOrderBatch(List<OrderRequest> orders) {
log.info("开始处理批量订单，数量: {}", orders.size());
for (OrderRequest orderRequest : orders) {
try {
Order order = createOrder(orderRequest);
inventoryService.reserveInventory(order.getProductId(), order.getQuantity());
PaymentResult paymentResult = paymentService.processPayment(order);
if (paymentResult.isSuccess()) {
order.setStatus("PAID");
orderMapper.updateOrder(order);
} else {
throw new PaymentException("支付失败");
}
Thread.sleep(100);
} catch (Exception e) {
log.error("处理订单失败: {}", orderRequest.getOrderId(), e);
}
}
}
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void updateOrderStatus(Long orderId, String status) {
Order order = orderMapper.selectById(orderId);
order.setStatus(status);
orderMapper.updateOrder(order);
if ("COMPLETED".equals(status)) {
processOrderCompletion(order);
}
}
}
@Service
public class ProblematicReportService {
@Autowired
private DataSource dataSource;
public List<ReportData> generateLargeReport(ReportQuery query) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = dataSource.getConnection();
String sql = buildComplexQuery(query);
statement = connection.prepareStatement(sql);
setQueryParameters(statement, query);
resultSet = statement.executeQuery();
List<ReportData> results = new ArrayList<>();
while (resultSet.next()) {
results.add(mapResultToReportData(resultSet));
}
return results;
} catch (SQLException e) {
log.error("生成报表失败", e);
return Collections.emptyList();
} finally {
try {
if (resultSet != null) resultSet.close();
if (statement != null) statement.close();
} catch (SQLException e) {
log.error("关闭资源失败", e);
}
}
}
}
|
三、应急处理措施
1. 立即止血方案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
|
@Component
public class EmergencyConnectionPoolFix {
@Autowired
private HikariDataSource dataSource;
public void emergencyPoolReset() {
log.warn("执行紧急连接池重置...");
try {
dataSource.getHikariPoolMXBean().softEvictConnections();
Thread.sleep(5000);
log.info("连接池重置完成");
} catch (Exception e) {
log.error("连接池重置失败", e);
}
}
public void adjustPoolConfiguration() {
System.setProperty("spring.datasource.hikari.maximum-pool-size", "30");
System.setProperty("spring.datasource.hikari.connection-timeout", "10000");
System.setProperty("spring.datasource.hikari.leak-detection-threshold", "30000");
log.info("临时调整连接池配置完成");
}
}
@RestController
public class EmergencyController {
@Autowired
private ConnectionPoolDiagnostics diagnostics;
@GetMapping("/health/database")
public ResponseEntity<Map<String, Object>> checkDatabaseHealth() {
Map<String, Object> health = new HashMap<>();
try {
ConnectionPoolDiagnostics.ConnectionPoolStatus status = diagnostics.getPoolStatus();
health.put("status", "UP");
health.put("activeConnections", status.getActiveConnections());
health.put("totalConnections", status.getTotalConnections());
health.put("waitingThreads", status.getThreadsAwaitingConnection());
if (status.getActiveConnections() > status.getTotalConnections() * 0.8) {
health.put("warning", "连接池使用率过高，建议降级非关键功能");
}
} catch (Exception e) {
health.put("status", "DOWN");
health.put("error", e.getMessage());
return ResponseEntity.status(503).body(health);
}
return ResponseEntity.ok(health);
}
}
|
2. 临时配置调整
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
spring:
datasource:
hikari:
maximum-pool-size: 30
minimum-idle: 5
connection-timeout: 10000
leak-detection-threshold: 30000
max-lifetime: 1200000
validation-timeout: 3000
spring:
transaction:
default-timeout: 60
|
四、根本解决方案
1. 事务管理优化
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
|
@Service
public class ImprovedOrderService {
@Autowired
private OrderMapper orderMapper;
@Autowired
private InventoryService inventoryService;
@Autowired
private PaymentService paymentService;
public void processLargeOrderBatch(List<OrderRequest> orders) {
log.info("开始处理批量订单，数量: {}", orders.size());
int batchSize = 10;
for (int i = 0; i < orders.size(); i += batchSize) {
List<OrderRequest> batch = orders.subList(i,
Math.min(i + batchSize, orders.size()));
processSingleBatch(batch);
try {
Thread.sleep(100);
} catch (InterruptedException e) {
Thread.currentThread().interrupt();
break;
}
}
}
@Transactional(timeout = 30)
public void processSingleBatch(List<OrderRequest> batch) {
for (OrderRequest orderRequest : batch) {
try {
processSingleOrder(orderRequest);
} catch (Exception e) {
log.error("处理订单失败: {}", orderRequest.getOrderId(), e);
}
}
}
@Transactional(timeout = 15, propagation = Propagation.REQUIRED)
public void processingleOrder(OrderRequest orderRequest) {
Order order = createOrder(orderRequest);
CompletableFuture<Boolean> inventoryFuture = inventoryService.reserveInventoryAsync(
order.getProductId(), order.getQuantity());
CompletableFuture<PaymentResult> paymentFuture = paymentService.processPaymentAsync(order);
try {
Boolean inventoryReserved = inventoryFuture.get(5, TimeUnit.SECONDS);
PaymentResult paymentResult = paymentFuture.get(5, TimeUnit.SECONDS);
if (inventoryReserved && paymentResult.isSuccess()) {
order.setStatus("PAID");
} else {
order.setStatus("FAILED");
}
orderMapper.updateOrder(order);
} catch (TimeoutException | InterruptedException | ExecutionException e) {
log.error("订单处理超时或失败: {}", order.getOrderId(), e);
order.setStatus("PENDING");
orderMapper.updateOrder(order);
}
}
}
@Service
public class ImprovedReportService {
@Autowired
private JdbcTemplate jdbcTemplate;
public List<ReportData> generateLargeReport(ReportQuery query) {
try {
String sql = buildComplexQuery(query);
return jdbcTemplate.query(sql,
this::setQueryParameters,
this::mapResultToReportData);
} catch (Exception e) {
log.error("生成报表失败", e);
return Collections.emptyList();
}
}
public List<ReportData> generateReportWithManualConnection(ReportQuery query) {
String sql = buildComplexQuery(query);
try (Connection connection = jdbcTemplate.getDataSource().getConnection();
PreparedStatement statement = connection.prepareStatement(sql)) {
setQueryParameters(statement, query);
try (ResultSet resultSet = statement.executeQuery()) {
List<ReportData> results = new ArrayList<>();
while (resultSet.next()) {
results.add(mapResultToReportData(resultSet));
}
return results;
}
} catch (SQLException e) {
log.error("生成报表失败", e);
return Collections.emptyList();
}
}
}
|
2. 连接池监控体系
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
|
@Component
public class ConnectionPoolMonitoring {
@Autowired
private MeterRegistry meterRegistry;
@Autowired
private HikariDataSource dataSource;
@PostConstruct
public void setupConnectionPoolMetrics() {
HikariPoolMXBean poolBean = dataSource.getHikariPoolMXBean();
Gauge.builder("hikaricp.connections.active")
.register(meterRegistry, poolBean, HikariPoolMXBean::getActiveConnections);
Gauge.builder("hikaricp.connections.idle")
.register(meterRegistry, poolBean, HikariPoolMXBean::getIdleConnections);
Gauge.builder("hikaricp.connections.pending")
.register(meterRegistry, poolBean, HikariPoolMXBean::getThreadsAwaitingConnection);
Timer.builder("hikaricp.connections.acquire")
.register(meterRegistry);
}
@Scheduled(fixedRate = 30000)
public void monitorConnectionPoolHealth() {
HikariPoolMXBean poolBean = dataSource.getHikariPoolMXBean();
int activeConnections = poolBean.getActiveConnections();
int totalConnections = poolBean.getTotalConnections();
int waitingThreads = poolBean.getThreadsAwaitingConnection();
double usageRatio = (double) activeConnections / totalConnections;
if (usageRatio > 0.8) {
log.warn("连接池使用率过高: {}%, 活跃连接: {}/{}",
usageRatio * 100, activeConnections, totalConnections);
}
if (waitingThreads > 5) {
log.warn("连接池等待线程过多: {} 个线程等待连接", waitingThreads);
}
detectConnectionLeaks();
}
private void detectConnectionLeaks() {
}
}
|
五、修复效果与预防措施
修复效果对比
| 指标 |
故障期间 |
修复后 |
改善幅度 |
| 连接池可用性 |
0% |
99.5% |
完全恢复 |
| 平均连接获取时间 |
30秒超时 |
5ms |
提升99.9% |
| 应用响应时间 |
无响应 |
200ms |
完全恢复 |
| 数据库连接使用率 |
100% |
40-60% |
降低40-60% |
| 长事务数量 |
15个 |
0个 |
完全解决 |
预防措施体系
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
@Configuration
public class OptimalConnectionPoolConfig {
@Bean
@ConfigurationProperties("spring.datasource.hikari")
public HikariConfig hikariConfig() {
HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(10000);
config.setIdleTimeout(300000);
config.setMaxLifetime(1200000);
config.setLeakDetectionThreshold(30000);
config.setValidationTimeout(3000);
config.setConnectionTestQuery("SELECT 1");
return config;
}
}
|
总结
这次数据库连接池死锁故障让我们深刻认识到：正确的事务管理和连接使用是Java应用稳定性的基石。
核心经验总结：
- 事务要短小精悍：避免长事务占用连接，采用分批处理策略
- 连接管理要规范：优先使用JdbcTemplate，手动管理时必须正确释放
- 监控体系要完善：建立实时的连接池健康监控和告警
- 异步处理要合理：避免在事务中进行长时间的外部调用
预防措施要点：
- 建立连接池使用规范和代码审查checklist
- 实施完善的连接池监控和告警体系
- 定期进行连接池压力测试和泄漏检测
- 建立连接池故障的应急处理预案
实际应用价值：
- 系统稳定性从完全不可用恢复到99.5%可用性
- 连接获取时间从30秒超时优化到5ms正常响应
- 建立了完整的数据库连接管理最佳实践
- 为团队积累了宝贵的连接池故障处理经验
通过这次故障处理，我们不仅快速恢复了服务，更重要的是建立了一套完整的数据库连接管理体系，确保类似问题不再发生。
