Question
Tomcat JDBC Pool: testOnBorrow vs. testWhileIdle for safe rollback after a schema change
Ira River
0 reputation · 01 Dec 2024, 17:59 UTC
17K views1
Loading…
0 reputation · 01 Dec 2024, 17:59 UTC
After running DDL (for example, adding a column) against a database fronted by a Tomcat JDBC Pool, existing pooled connections can become stale. The pool offers two documented validation strategies: testOnBorrow with a validationQuery, which checks each connection when it is borrowed and discards it on failure, and testWhileIdle with the periodic eviction runner, which proactively removes invalid idle connections before they are handed out.
The constraint is rollback safety: with the default autoCommit=false, an in-flight transaction on a connection that fails validation must be rolled back rather than partially committed when the connection is discarded. There is also a documented interaction risk between the validation timeout and maxWait — if validation runs longer than maxWait, borrow attempts can fail even though a fresh, valid connection could be created after the schema change.
Assuming Tomcat 9/10 with the built-in pool and a lightweight query such as SELECT 1:
1. For minimizing both stale-connection errors and uncommitted-transaction risk after DDL, is borrow-time validation alone sufficient, or should idle eviction also be enabled?
2. How should the validation timeout be sized relative to maxWait so that post-DDL borrows fail over to new connections instead of throwing to the application?
3. Is there a documented guarantee that a failed validation triggers rollback of the open transaction before the connection is dropped?
31775 reputation · 01 Dec 2024, 19:16 UTC
To minimize both stale-connection errors and the risk of uncommitted transactions after a schema change, testOnBorrow should be used as the primary safeguard, but it is most effective when paired with testWhileIdle.
While testOnBorrow ensures that no application thread receives a connection that is already dead or invalidated by the database, it does not proactively clean the pool. Relying solely on borrow-time validation means the application must endure the latency of a failed validation and a subsequent reconnection attempt during the request cycle. Enabling testWhileIdle (with a configured timeBetweenEvictionRunsMillis) allows the pool to prune stale connections in the background, reducing the number of "failed borrow" cycles the application encounters after a DDL operation.
To ensure that post-DDL borrows fail over to new connections rather than throwing an exception to the application, the validation timeout must be strictly lower than maxWait.
If the validation query hangs or takes longer than maxWait, the pool will throw a NoSuchElementException or a timeout exception before it can decide to discard the stale connection and attempt to create a new one. For a lightweight query like SELECT 1, the following sizing is recommended:
There is no documented guarantee in the Tomcat JDBC Pool that a failed validation triggers a formal ROLLBACK of an open transaction before the connection is dropped. Validation queries are typically executed outside the context of the application's current transaction state.
If a connection is discarded because testOnBorrow failed, the pool simply closes the physical connection. Depending on the database engine (e.g., PostgreSQL, MySQL, Oracle), the database server will typically detect the connection loss and perform an automatic server-side rollback of any uncommitted transaction associated with that session. However, this is a database-level behavior, not a pool-level guarantee.
To verify this behavior in a staging environment, you can use the following configuration pattern:
tomcattestOnBorrow=true
validationQuery="SELECT 1"
testWhileIdle=true
timeBetweenEvictionRunsMillis=30000
validationInterval=5000
maxWait=10000Perform an ALTER TABLE operation and monitor the logs for connection eviction events to confirm the pool is rotating connections without application-level crashes.
Diagnostic Detail Needed: To refine the rollback recommendation, please specify the database vendor and version, as the handling of "orphaned" transactions upon connection loss varies by engine.
Use comments to ask for clarification. Post a solution as an answer.
31,775 reputation · 02 Dec 2024, 00:31 UTC
The Tomcat JDBC pool itself does not issue a ROLLBACK when a connection is discarded. It simply closes the JDBC connection, and the driver performs the rollback if it implements the standard behavior. Most mainstream drivers (MySQL Connector/J, PostgreSQL JDBC, H2) roll back any active transaction on close(), but drivers that deviate from this contract can leave uncommitted changes. Therefore, rely on the driver’s documentation or test the behavior in your environment.
testWhileIdle Isn’t a ReplacementtestOnBorrow for safety.Set validationQueryTimeout to at least twice the average round‑trip time of your lightweight query (e.g., SELECT 1). Keep maxWait larger than that timeout so the pool can discard the failed connection and establish a fresh one before the borrow times out.