In my application, I use spring boot framework, and I choose "Tomcat JDBC Pool" as my connection pool, what I have configured is as follows. spring.datasource.type=org.apache.tomcat.jdbc.pool.DataSource spring.datasource.url=jdbc:postgresql://localhost:5432/test spring.datasource.tomcat.driverClassName=org.postgresql.Driver spring.datasource.tomcat.username=admin spring.datasource.tomcat.password=admin spring.datasource.tomcat.initialSize=15 spring.datasource.tomcat.min-idle=15 spring.datasource.tomcat.maxActive=30 spring.datasource.tomcat.max-idle=30 spring.datasource.tomcat.maxWait=300000 spring.datasource.tomcat.timeBetweenEvictionRunsMillis=30000 spring.datasource.tomcat.minEvictableIdleTimeMillis=60000 spring.datasource.tomcat.removeAbandoned=true spring.datasource.tomcat.removeAbandonedTimeout=900 spring.datasource.tomcat.logAbandoned=true spring.datasource.tomcat.testWhileIdle=true spring.datasource.tomcat.validationQuery=SELECT 1 spring.datasource.tomcat.validationInterval=60000 spring.datasource.tomcat.default-auto-commit=false Then I run my application, 60 seconds later, all the database connections become into "<IDLE> in transaction". Why does this happen? I look into the "PooledConnection.java" source code, and I get the reason. In the above configuration, I set the "testWhileIdle" to "true", so every connection in the pool will be tested to see if it is idle, using the specified SQL "SELECT 1". After executing the SQL, the connection does not call commit() or rollback(). We know that if the connection is auto committed, this is OK. But because I set the "defaultAutoCommit" to "false", so the connection will not be committed automatically, and so the connection will always stay in the status of "<IDLE> in transaction", and I think this is incorrect.
Created attachment 35232 [details] Attaching a simple patch to commit/rollback the connection.
Comment on attachment 35232 [details] Attaching a simple patch to commit/rollback the connection. diff --git a/org/apache/tomcat/jdbc/pool/PooledConnection.java b/org/apache/tomcat/jdbc/pool/PooledConnection.java index c833209..4615abf 100644 --- a/org/apache/tomcat/jdbc/pool/PooledConnection.java +++ b/org/apache/tomcat/jdbc/pool/PooledConnection.java @@ -541,6 +541,22 @@ public class PooledConnection { } if (stmt!=null) try { stmt.close();} catch (Exception ignore2){/*NOOP*/} + + try { + if(!connection.getAutoCommit()) { + connection.rollback(); + } + } catch (SQLException e) { + // do nothing + } + } finally { + try { + if(!connection.getAutoCommit()) { + connection.commit(); + } + } catch (SQLException e) { + // do nothing + } } return false; } //validate
Created attachment 35233 [details] use this patch instead Sorry, there are some mistakes in the previous patch, please use one instead.
admin=# select current_query from pg_stat_activity where usename='admin'; current_query ----------------------- <IDLE> in transaction <IDLE> in transaction <IDLE> in transaction <IDLE> in transaction <IDLE> in transaction <IDLE> in transaction <IDLE> in transaction <IDLE> in transaction <IDLE> in transaction <IDLE> in transaction <IDLE> in transaction <IDLE> in transaction <IDLE> in transaction <IDLE> in transaction <IDLE> in transaction (15 rows) The above is queried the database.
Thanks for the patch. The fix will be in : - 9.0.x for 9.0.0.M27 onwards - 8.5.x for 8.5.21 onwards - 8.0.x for 8.0.47 onwards - 7.0.x for 7.0.82 onwards