MySQL KILL QUERY and HikariCP validation: can the pool detect a transaction left by a cancelled statement?

Answer

No, HikariCP's default validation cannot detect a transaction left open by a KILL QUERY. The default validation query (typically SELECT 1) only verifies that the network connection to the database is alive; it does not inspect the session's transactional state. Because KILL QUERY aborts the statement but not the surrounding transaction, the connection remains valid from the pool's perspective, allowing the next borrower to inherit an uncommitted transaction.

Technical Explanation

The behavior stems from how MySQL and connection pools handle session state:

• Lock Release: When a DML statement is terminated via KILL QUERY, the InnoDB storage engine must roll back any partial changes made by that specific statement. Row locks are not released instantly; they are held until the internal rollback process completes.
• Transaction Persistence: KILL QUERY is designed to stop the current execution, not to terminate the session or roll back the entire transaction. The transaction remains ACTIVE.
• Validation Gap: HikariCP's connectionTestQuery (or the JDBC 4 isValid() call) simply checks for a successful round-trip. Since the session is still active and capable of executing a simple SELECT, the pool considers the connection healthy.

Implementation Steps for Detection

1. Implement a State-Aware Query: Replace the default validation with a query that returns a value only if no transaction is active. While SELECT @@transaction_isolation is often used, a more explicit check involves querying the transaction ID or using a custom function.
2. Configure HikariCP: Set the connectionTestQuery property to your state-aware SQL. If the query fails or returns an unexpected result, HikariCP will evict the connection from the pool.
3. Manual Cleanup: As a fail-safe, ensure your application code explicitly calls connection.rollback() in a finally block or uses try-with-resources to ensure the connection is returned to the pool in a clean state.

Verification Method

To verify this behavior in a staging environment (assuming MySQL 8.0+ and InnoDB):

-- Session 1: Start a transaction and a heavy update
START TRANSACTION;
UPDATE large_table SET status = 'processing' WHERE id < 1000000;

-- Session 2: Kill the query
SHOW PROCESSLIST;
KILL QUERY [thread_id];

-- Session 1: Check transaction status
-- This will still show as active/uncommitted
SELECT @@transaction_isolation; 

Missing Diagnostic Detail

To determine if a custom validation query is the most efficient path, please confirm if you are using InnoDB or a different storage engine, as rollback timing and lock release semantics vary by engine.