Detect open transaction not yet committed in JDBC connection

Question

How do I detect if a transaction remains open, still pending on a COMMIT or ROLLBACK on a JDBC Connection?

I'm getting my Connection objects via a connection pool. So I want to check the state of the connection before using it.

Using Postgres 9.x and Java 8.

Gardol

A correctly implemented connection pool should rollback() before returning the connection back into the pool. – Carnatic

I tried reading the source code to the Tomcat JDBC Connection Pool implementation but could not determine this behavior. Use of proxies, interceptors, and more makes that source code difficult to follow. – Gardol

@PaulWhite I suppose the Answer by heenenee is best; it should work (I've not yet tested) and is elegant enough. Its only downside is that is Postgres-specific, but apparently there is no general way. The Answer by sibnick is informative and clever (if I correctly deduced its application, per my comment), but is inelegant as I don't like the idea of ever-more incrementing the transaction counter only for the purpose of detecting an open transaction. I did not understand the remaining Answer by Andrei I. – Gardol

Answer 1

I'm not aware of any way to detect current transaction status on a Connection using only standard JDBC API methods.

However, for PostgreSQL specifically, there is AbstractJdbc2Connection.getTransactionState(), which you can compare against the constant ProtocolConnection.TRANSACTION_IDLE. PostgreSQL's JDBC4 Connection extends this class so you should be able to cast your Connection to get access to this property.

That constant is one of three values defined in the pgjdbc driver source code:

/**
 * Constant returned by {@link #getTransactionState} indicating that no
 * transaction is currently open.
 */
static final int TRANSACTION_IDLE = 0;

/**
 * Constant returned by {@link #getTransactionState} indicating that a
 * transaction is currently open.
 */
static final int TRANSACTION_OPEN = 1;

/**
 * Constant returned by {@link #getTransactionState} indicating that a
 * transaction is currently open, but it has seen errors and will
 * refuse subsequent queries until a ROLLBACK.
 */
static final int TRANSACTION_FAILED = 2;

Personate

Answer 2

As I understand, you use plain JDBC and this is why you have this problem. Because you told about the Tomcat's JDBC Connection Pool, you could use JDBCInterceptor.invoke(), where you could track what happens to each Connection. More details here.

Frangipane

Answer 3

The accepted Answer by heenenee is correct.

Example Code

This Answer posts the source code of a helper class. This source code is based on the ideas if that accepted Answer.

package com.powerwrangler.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.UUID;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.slf4j.LoggerFactory;

/**
 *
 * Help with database chores.
 *
 * © 2015 Basil Bourque 
 * This source code available under terms of the ISC License. http://opensource.org/licenses/ISC
 *
 * @author Basil Bourque.
 *
 */
public class DatabaseHelper
{

    static final org.slf4j.Logger logger = LoggerFactory.getLogger( DatabaseHelper.class );

    public enum TransactionState
    {

        IDLE,
        OPEN,
        FAILED;

    }

    /**
     * If using the Postgres database, and the official "org.postgresql" JDBC driver, get the current state of the
     * current transaction held by a Connection. Translate that state to a convenient Enum value.
     *
     * @param connArg
     *
     * @return DatabaseHelper.TransactionState
     */
    public DatabaseHelper.TransactionState transactionStateOfConnection ( Connection connArg ) {
        // This code is specific to Postgres.
        // For more info, see this page on StackOverflow:  https://mcmap.net/q/1447305/-detect-open-transaction-not-yet-committed-in-jdbc-connection/642706

        // Verify arguments.
        if ( connArg == null ) {
            logger.error( "Received null argument for Connection object. Message # 6b814e3c-80e3-4145-9648-390b5315243e." );
        }

        DatabaseHelper.TransactionState stateEnum = null;  // Return-value.

        Connection conn = connArg;  // Transfer argument to local variable.

        // See if this is a pooled connection.
        // If pooled, we need to extract the real connection wrapped inside.
        // Class doc: http://docs.oracle.com/javase/8/docs/api/javax/sql/PooledConnection.html
        // I learned of this via the "Getting the actual JDBC connection" section of the "Tomcat JDBC Connection Pool" project.
        // Tomcat doc: https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html#Getting_the_actual_JDBC_connection
        if ( conn instanceof javax.sql.PooledConnection ) {
            javax.sql.PooledConnection pooledConnection = ( javax.sql.PooledConnection ) conn;
            try { // Can throw java.sql.SQLException. So using a Try-Catch.
                // Conceptually we are extracting a wrapped Connection from with in a PooledConnection. Reality is more complicated.
                // From class doc: Creates and returns a Connection object that is a handle for the physical connection that this PooledConnection object represents.
                conn = pooledConnection.getConnection();
            } catch ( SQLException ex ) {
                // We could just as well throw this SQLException up the call chain. But I chose to swallow it here. --Basil Bourque
                logger.error( "Failed to extract the real Connection from its wrappings in a PooledConnection. Message # ea59e3a3-e128-4386-949e-a70d90e1c19e." );
                return null; // Bail-out.
            }
        }

        // First verify safe to cast.
        if ( conn instanceof org.postgresql.jdbc2.AbstractJdbc2Connection ) {
            // Cast from a generalized JDBC Connection to one specific to our expected Postgres JDBC driver.
            org.postgresql.jdbc2.AbstractJdbc2Connection aj2c = ( org.postgresql.jdbc2.AbstractJdbc2Connection ) conn; // Cast to our Postgres-specific Connection.

            // This `getTransactionState` method is specific to the Postgres JDBC driver, not general JDBC.
            int txnState = aj2c.getTransactionState();
            // We compare that state’s `int` value by comparing to constants defined in this source code:
            // https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/core/ProtocolConnection.java#L27
            switch ( txnState ) {
                case org.postgresql.core.ProtocolConnection.TRANSACTION_IDLE:
                    stateEnum = DatabaseHelper.TransactionState.IDLE;
                    break;

                case org.postgresql.core.ProtocolConnection.TRANSACTION_OPEN:
                    stateEnum = DatabaseHelper.TransactionState.OPEN;
                    break;

                case org.postgresql.core.ProtocolConnection.TRANSACTION_FAILED:
                    stateEnum = DatabaseHelper.TransactionState.FAILED;
                    break;

                default:
                    // No code needed.
                    // Go with return value having defaulted to null.
                    break;
            }
        } else {
            logger.error( "The 'transactionStateOfConnection' method was passed Connection that was not an instance of org.postgresql.jdbc2.AbstractJdbc2Connection. Perhaps some unexpected JDBC driver is in use. Message # 354076b1-ba44-49c7-b987-d30d76367d7c." );
            return null;
        }
        return stateEnum;
    }

    public Boolean isTransactionState_Idle ( Connection connArg ) {
        Boolean b = this.transactionStateOfConnection( connArg ).equals( DatabaseHelper.TransactionState.IDLE );
        return b;
    }

    public Boolean isTransactionState_Open ( Connection conn ) {
        Boolean b = this.transactionStateOfConnection( conn ).equals( DatabaseHelper.TransactionState.OPEN );
        return b;
    }

    public Boolean isTransactionState_Failed ( Connection conn ) {
        Boolean b = this.transactionStateOfConnection( conn ).equals( DatabaseHelper.TransactionState.FAILED );
        return b;
    }

}

Example usage:

if ( new DatabaseHelper().isTransactionState_Failed( connArg ) ) {
    logger.error( "JDBC transaction state is Failed. Expected to be Open. Cannot process source row UUID: {}. Message # 9e633f31-9b5a-47bb-bbf8-96b1d77de561." , uuidOfSourceRowArg );
    return null; // Bail-out.
}

Include JDBC Driver In Project But Omit From Build

The challenge with this code is that at compile-time we must address classes specific to a specific JDBC driver rather than generalized JDBC interfaces.

You might think, “Well enough, just add the JDBC driver jar file to the project”. But, no, in a web app Servlet environment we must not include the JDBC driver in our build (our WAR file/folder). In a web app, technical issues mean we should deposit our JDBC driver with the Servlet container. For me that means Apache Tomcat where we place the JDBC driver jar file into Tomcat’s own /lib folder rather than within our web app’s WAR file/folder.

So how to include the JDBC driver jar in our project during compile-time while excluding from the build of our WAR file? See this Question, Include a library while programming & compiling, but exclude from build, in NetBeans Maven-based project. The solution in Maven is the scope tag with a value of provided.

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.4-1201-jdbc41</version>
    <scope>provided</scope>
</dependency>

Gardol

Answer 4

You can retrieve txId from postgres select txid_current() and write it to log. This number is differ for different transactions.

Plasmagel

I don't see how this answers the question. That command returns a number both in auto-commit mode and in transaction mode. Since auto-commit does technically start a transaction, and end the transaction after executing the single statement, each call to select txid_current() returns an incrementing number. If inside an ongoing transaction, each call to select txid_current() returns the same number. I suppose a double-call to that command could be used to determine if a transaction is in effect (by whether same or different numbers are returned), I had hoped for a more elegant solution. – Gardol

Though not an Answer to my Question, let me mention that Postgres brings a new feature to learn the status of a recent transaction for recovery after a network connection loss or crash. See this article by Craig Ringer at 2nd Quadrant. – Gardol

Answer 5

I managed to do something with Statement.getUpdateCount() . The idea was that after each statement execution, I verify if updateCount > 0 . If it's true and that autocommit is disabled, it means the connection of that statement will need a commit or a rollback before being closed.

By wrapping Datasource, Connection, Statement, PreparedStatement, CallableStatement, it's possible to implement this verification at each call of execute(), executeUpdate(), executeBatch(), store the stack trace and the flag in the Connection wrapper. In connection close() you can then show the last statement execution with the stack , then rollback and throw an exception.

However I am not sure of the overhead of getUpdateCount(), and if it doesn't mess with the results. But the integration test cases are working to far.

We could check if getUpdateCount() >-1, but it would break ode that might already avoid commit if nothing was updated.

Simonson