// 獲取HikariCP連接池狀態(tài)
HikariPoolMXBean pool = dataSource.getHikariPoolMXBean();
System.out.printf("連接池狀態(tài): 活躍=%d, 空閑=%d, 等待=%d, 總=%d%n",
    pool.getActiveConnections(),
    pool.getIdleConnections(),
    pool.getThreadsAwaitingConnection(),
    pool.getTotalConnections());

-- MySQL
SHOW STATUS LIKE 'Threads_connected';
SHOW PROCESSLIST;

-- PostgreSQL
SELECT count(*) FROM pg_stat_activity;
SELECT * FROM pg_stat_activity WHERE state = 'active';

-- Oracle
SELECT count(*) FROM v$session;
SELECT sid, serial#, username, status FROM v$session WHERE type = 'USER';

// HikariCP泄漏檢測(cè)配置(單位毫秒)
spring.datasource.hikari.leak-detection-threshold=60000

// Druid泄漏檢測(cè)
spring.datasource.druid.remove-abandoned=true
spring.datasource.druid.remove-abandoned-timeout=60

// 反例1：未關(guān)閉ResultSet/Statement
Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM table");
// 忘記調(diào)用rs.close(), stmt.close(), conn.close()

// 反例2：try-with-resources使用不當(dāng)
try (Connection conn = dataSource.getConnection()) {
    Statement stmt = conn.createStatement(); // 未包含在try中
    // ...
} // stmt不會(huì)被自動(dòng)關(guān)閉

-- MySQL長(zhǎng)事務(wù)查詢
SELECT trx_id, trx_started, TIMEDIFF(NOW(), trx_started) duration, 
       trx_query, trx_state 
FROM information_schema.INNODB_TRX 
ORDER BY trx_started ASC;

// 標(biāo)準(zhǔn)寫法
try (Connection conn = dataSource.getConnection();
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {
    // 處理結(jié)果集
} // 自動(dòng)關(guān)閉所有資源

// Spring事務(wù)正確用法
@Transactional
public void serviceMethod() {
    // 只包含數(shù)據(jù)庫操作
    repository.update(data);
    // 不包含: 文件IO、網(wǎng)絡(luò)請(qǐng)求、長(zhǎng)時(shí)間計(jì)算
}

# 推薦HikariCP配置
spring:
  datasource:
    hikari:
      maximum-pool-size: 20      # 根據(jù)DB負(fù)載能力調(diào)整
      minimum-idle: 5            # 可減少初始連接數(shù)
      connection-timeout: 30000  # 30秒獲取連接超時(shí)
      max-lifetime: 1800000      # 30分鐘連接最大存活
      idle-timeout: 600000       # 10分鐘空閑超時(shí)
      leak-detection-threshold: 60000 # 60秒泄漏檢測(cè)

-- 終止問題連接(MySQL示例)
KILL <process_id>;

-- 批量終止空閑連接
SELECT concat('KILL ', id, ';') 
FROM information_schema.processlist 
WHERE Command = 'Sleep' AND Time > 300;

<!-- SpotBugs插件檢測(cè)資源泄漏 -->
<plugin>
    <groupId>com.github.spotbugs</groupId>
    <artifactId>spotbugs-maven-plugin</artifactId>
    <version>4.7.3</version>
</plugin>

// 使用JMeter模擬并發(fā)場(chǎng)景
@Test
public void testConnectionPoolUnderLoad() {
    // 模擬100并發(fā)持續(xù)請(qǐng)求
    StressTestUtils.concurrentTest(100, () -> {
        service.processRequest(testData);
    });
}

# 監(jiān)控方法調(diào)用
watch com.example.service.*Service * '{params, returnObj, throwExp}' -n 5 -x 3

# 追蹤連接獲取
trace javax.sql.DataSource getConnection

# 生成線程dump
jstack <pid> > thread_dump.log

# 分析持有連接的線程
grep -A 30 "java.sql.Connection" thread_dump.log