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.

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;

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.