bigywater.tech
Java · 370

JDBC 连接池:复用连接也要重置状态

连接池让连接「复用」,也让连接上的状态跟着复用。这篇用真实的 HikariCP 看归还时重置了什么、没重置什么,耗尽与泄漏的表现,以及服务端杀掉连接之后连接池要多久才发现。

SET / schema / 锁
HikariCP 归还连接时不会重置:会话变量(SET)、PG 的 schema、MySQL 的 catalog、临时表、advisory lock / get_lock
300 ms vs 800 ms
连接被服务端杀掉后再借:500 ms 内(aliveBypassWindowMs)拿到坏连接,之后会验证并重建
1647 → 31 µs
每次新建物理连接与从池里借出 + 归还 + select 1 的耗时(PG,本机)

归还时重置了什么

实测 · 本机执行输出 
  ── PostgreSQL
  新借出的连接状态:autoCommit=true readOnly=false isolation=2 catalog=lab schema=public;物理连接 id 46596
  第一个使用者改成:autoCommit=false readOnly=true isolation=8 catalog=lab schema=public
  还回去再借(同一物理连接 true):autoCommit=true readOnly=false isolation=2 catalog=lab schema=public(Hikari 归还时重置了 autoCommit / readOnly / isolation)
  改 catalog 后归还,再借:(PG 不能切换 catalog)
      当前 catalog = lab
  setSchema(pg_catalog) 后归还,再借:getSchema = pg_catalog;current_schema = pg_catalog
  会话变量(SET)归还后再借:application_name = leaked_name;statement_timeout = 3s;search_path = pg_catalog(没有被重置,泄漏给下一个使用者)
  临时表归还后再借:1(临时表还在)
  advisory lock 归还后再借:pg_locks 里本会话持有的 advisory lock 数 = 1(锁跟着物理连接走,没有释放)
  未提交的 insert 之后 close() 归还,再借查 acct:0 行(Hikari 在归还时 rollback)
  ── MySQL
  新借出的连接状态:autoCommit=true readOnly=false isolation=4 catalog=lab schema=null;物理连接 id 10
  第一个使用者改成:autoCommit=false readOnly=true isolation=8 catalog=lab schema=null
  还回去再借(同一物理连接 true):autoCommit=true readOnly=false isolation=4 catalog=lab schema=null(Hikari 归还时重置了 autoCommit / readOnly / isolation)
  改 catalog 后归还,再借:
      当前 catalog = mysql(MySQL 上 Hikari 没有把 catalog 恢复成 lab;下一个使用者拿到的是被改过的 catalog)
  会话变量(SET)归还后再借:@leak = 42;wait_timeout = 77;sql_mode = “”(没有被重置,泄漏给下一个使用者)
  临时表归还后再借:1(临时表还在)
  get_lock 归还后再借:is_used_lock('lk') = 连接 10,当前连接 10(锁仍被持有)
  未提交的 insert 之后 close() 归还,再借查 acct:0 行(Hikari 在归还时 rollback)

池耗尽与代理连接

实测 · 本机执行输出 
  ── PostgreSQL
  池大小 2,已借出 2 个,第 3 次 getConnection:SQLTransientConnectionException(null): demo - Connection is not available, request timed out after 1011ms (total=2, active=2, idle=0, waiting=0)
      等待了 1028 ms(connectionTimeout = 1000 ms)
  MXBean:总 2 活跃 2 空闲 0 等待线程 0
  归还 1 个后再借:成功,耗时 9 µs
  已 close 的连接上再用:SQLException(null): Connection is closed
  重复 close():不报错
  isClosed / isValid(1):true / false
  c1 的类:com.zaxxer.hikari.pool.HikariProxyConnection;unwrap 物理连接类:PgConnection / PgConnection
  连接 close 后其 Statement 的状态:isClosed = true(Hikari 会替你关闭泄漏的 Statement)
  ── MySQL
  池大小 2,已借出 2 个,第 3 次 getConnection:SQLTransientConnectionException(null): demo - Connection is not available, request timed out after 1004ms (total=2, active=2, idle=0, waiting=0)
      等待了 1024 ms(connectionTimeout = 1000 ms)
  MXBean:总 2 活跃 2 空闲 0 等待线程 0
  归还 1 个后再借:成功,耗时 11 µs
  已 close 的连接上再用:SQLException(null): Connection is closed
  重复 close():不报错
  isClosed / isValid(1):true / false
  c1 的类:com.zaxxer.hikari.pool.HikariProxyConnection;unwrap 物理连接类:ConnectionImpl / ConnectionImpl
  连接 close 后其 Statement 的状态:isClosed = true(Hikari 会替你关闭泄漏的 Statement)

连接泄漏检测

实测 · 本机执行输出 
  ── PostgreSQL
  借出连接后 3 秒不归还(leakDetectionThreshold = 2000 ms;警告由 slf4j-simple 写到 stderr):
  归还后:总 2 活跃 0
WARN com.zaxxer.hikari.pool.ProxyLeakTask - Connection leak detection triggered for org.postgresql.jdbc.PgConnection@2f943d71 on thread main, stack trace follows
java.lang.Exception: Apparent connection leak detected
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:99)
	at Main.main(Main.java:50)
  ── MySQL
  借出连接后 3 秒不归还(leakDetectionThreshold = 2000 ms;警告由 slf4j-simple 写到 stderr):
  归还后:总 2 活跃 0
WARN com.zaxxer.hikari.pool.ProxyLeakTask - Connection leak detection triggered for com.mysql.cj.jdbc.ConnectionImpl@3bbc39f8 on thread main, stack trace follows
java.lang.Exception: Apparent connection leak detected
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:99)
	at Main.main(Main.java:50)

服务端杀掉连接之后

实测 · 本机执行输出 
  ── PostgreSQL
  物理连接被服务端杀掉后等 300 ms 再借(aliveBypassWindowMs 默认 500 ms):getConnection 成功(0 ms);第一个语句 → PSQLException(57P01): FATAL: terminating connection due to administrator command
  物理连接被服务端杀掉后等 800 ms 再借(aliveBypassWindowMs 默认 500 ms):getConnection 成功(11 ms);第一个语句 → 46687
  借出之后、使用中途被杀:PSQLException(57P01): FATAL: terminating connection due to administrator command
      之后 isValid(1) = false
  等 800 ms 后下一次借出:1(坏连接已被丢弃并重建)
WARN com.zaxxer.hikari.pool.ProxyConnection - demo - Connection org.postgresql.jdbc.PgConnection@2a70a3d8 marked as broken because of SQLSTATE(57P01), ErrorCode(0)
org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:356)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:341)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:317)
	at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:266)
	at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111)
	at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
	at Main.one(Main.java:10)
	at Main.lambda$main$10(Main.java:58)
	at Main.tryIt(Main.java:11)
	at Main.main(Main.java:58)
WARN com.zaxxer.hikari.pool.PoolBase - demo - Failed to validate connection org.postgresql.jdbc.PgConnection@10d59286 (This connection has been closed.). Possibly consider using a shorter maxLifetime value.
WARN com.zaxxer.hikari.pool.ProxyConnection - demo - Connection org.postgresql.jdbc.PgConnection@6d3af739 marked as broken because of SQLSTATE(57P01), ErrorCode(0)
org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:356)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:341)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:317)
	at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:266)
	at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111)
	at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
	at Main.one(Main.java:10)
	at Main.lambda$main$12(Main.java:64)
	at Main.tryIt(Main.java:11)
	at Main.main(Main.java:64)
  ── MySQL
  物理连接被服务端杀掉后等 300 ms 再借(aliveBypassWindowMs 默认 500 ms):getConnection 成功(0 ms);第一个语句 → CommunicationsException(08S01): Communications link failure
  物理连接被服务端杀掉后等 800 ms 再借(aliveBypassWindowMs 默认 500 ms):getConnection 成功(13 ms);第一个语句 → 20
  借出之后、使用中途被杀:CommunicationsException(08S01): Communications link failure
      之后 isValid(1) = false
  等 800 ms 后下一次借出:1(坏连接已被丢弃并重建)
WARN com.zaxxer.hikari.pool.ProxyConnection - demo - Connection com.mysql.cj.jdbc.ConnectionImpl@4b5d6a01 marked as broken because of SQLSTATE(08S01), ErrorCode(0)
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was 334 milliseconds ago. The last packet sent successfully to the server was 334 milliseconds ago.
	at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:165)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:55)
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1312)
	at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111)
	at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
	at Main.one(Main.java:10)
	at Main.lambda$main$10(Main.java:58)
	at Main.tryIt(Main.java:11)
	at Main.main(Main.java:58)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet successfully received from the server was 334 milliseconds ago. The last packet sent successfully to the server was 334 milliseconds ago.
	at java.base/jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:62)
	at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:502)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:486)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:52)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:95)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:140)
	at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:156)
	at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:592)
	at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:769)
	at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:708)
	at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:940)
	at com.mysql.cj.NativeSession.execSQL(NativeSession.java:817)
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1274)
	... 6 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
	at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:58)
	at com.mysql.cj.protocol.a.SimplePacketReader.readHeaderLocal(SimplePacketReader.java:72)
	at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:54)
	at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:36)
	at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:43)
	at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:32)
	at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:45)
	at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:35)
	at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:586)
	... 11 more
WARN com.zaxxer.hikari.pool.PoolBase - demo - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@35ef1869 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
WARN com.zaxxer.hikari.pool.ProxyConnection - demo - Connection com.mysql.cj.jdbc.ConnectionImpl@1dde4cb2 marked as broken because of SQLSTATE(08S01), ErrorCode(0)
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was 318 milliseconds ago. The last packet sent successfully to the server was 318 milliseconds ago.
	at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:165)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:55)
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1312)
	at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111)
	at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
	at Main.one(Main.java:10)
	at Main.lambda$main$12(Main.java:64)
	at Main.tryIt(Main.java:11)
	at Main.main(Main.java:64)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet successfully received from the server was 318 milliseconds ago. The last packet sent successfully to the server was 318 milliseconds ago.
	at java.base/jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:62)
	at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:502)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:486)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:52)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:95)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:140)
	at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:156)
	at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:592)
	at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:769)
	at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:708)
	at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:940)
	at com.mysql.cj.NativeSession.execSQL(NativeSession.java:817)
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1274)
	... 6 more
Caused by: java.net.SocketException: Connection reset
	at java.base/sun.nio.ch.NioSocketImpl.implRead(NioSocketImpl.java:318)
	at java.base/sun.nio.ch.NioSocketImpl.read(NioSocketImpl.java:346)
	at java.base/sun.nio.ch.NioSocketImpl$1.read(NioSocketImpl.java:796)
	at java.base/java.net.Socket$SocketInputStream.read(Socket.java:1099)
	at com.mysql.cj.protocol.ReadAheadInputStream.fill(ReadAheadInputStream.java:91)
	at com.mysql.cj.protocol.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:130)
	at com.mysql.cj.protocol.ReadAheadInputStream.read(ReadAheadInputStream.java:157)
	at java.base/java.io.FilterInputStream.read(FilterInputStream.java:119)
	at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:55)
	at com.mysql.cj.protocol.a.SimplePacketReader.readHeaderLocal(SimplePacketReader.java:72)
	at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:54)
	at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:36)
	at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:43)
	at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:32)
	at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:45)
	at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:35)
	at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:586)
	... 11 more

池的收益

实测 · 本机执行输出 
  ── PostgreSQL
  每次新建物理连接(200 次)1647 µs / 次;池里借出+归还+select 1(2000 次)31 µs / 次;同一连接只 select 1 30 µs / 次
  16 个线程共用 8 个连接的池,各 500 次借出 + select 1:总 153 ms,吞吐 52207 次/秒
  ── MySQL
  每次新建物理连接(200 次)1223 µs / 次;池里借出+归还+select 1(2000 次)37 µs / 次;同一连接只 select 1 34 µs / 次
  16 个线程共用 8 个连接的池,各 500 次借出 + select 1:总 168 ms,吞吐 47740 次/秒

分步演示

连接池
本步的对象

    本文用到的工具与边界

    • 本机 macOS 27 arm64,JDK 21.0.12;HikariCP 6.2.1(slf4j-simple 1.7.36 输出日志);PostgreSQL 16.15 与 MySQL 26.7.0(自己初始化的隔离实例,端口 9501 / 9601);驱动 PostgreSQL JDBC 42.7.4、MySQL Connector/J 9.1.0;用第三个连接 pg_terminate_backend / kill 模拟服务端断开
    • 没有验证:其它连接池(Tomcat JDBC / DBCP / c3p0 / Druid)的重置行为、Hikari 的 connectionInitSql 与 connectionTestQuery 配置对泄漏状态的作用、maxLifetime 与 idleTimeout 的实际回收时机(本机没有跑到数十分钟)、PgBouncer 等外部连接池的 session / transaction 模式、网络层断开(丢包而非 RST)时的检测时间
    京ICP备2026067502号-1京公网安备11010502063487号