[SOLVED] tomcat jdbc pool has some different behaviour about connection reuse

tomcat jdbc pool has some different behaviour about connection reuse

I have configured Tomcat JDBC pool in my web application and added maven dependency with version 8.0.38 which is my tomcat version also. Now I get connection from that pool and check value of autoCommit property and it is "true" then I set that connection's autoCommit to "false". Then I commit the transaction, then close that connection. Now I get another connection from pool and check the value of autoCommit property and it was "false". But I was expecting it as true. I also use Apache Common DBCP2 pooling library, and that has not this kind of behaviour. Whenever I get connection from common DBCP2 pool, it return connection with autoCommit set to "true". I have tested and seen this behaviour of tomcat jdbc pool.

Connection con1 = basicDataSourceWrite.getConnection();
con1.setAutoCommit(false);
System.out.println(con1.getAutoCommit()+ " con1  " );
con1.commit();
con1.close();

Connection con2 = basicDataSourceWrite.getConnection();
System.out.println(con2.getAutoCommit()+ " con2  " );

Output of above code is

false con1
false con2

<bean id="basicDataSourceWrite" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="${mysqlEndpointWrite}" />
    <property name="username" value="${mysqlUserWrite}" />
    <property name="password" value="${mysqlPasswordWrite}" />
    <property name="defaultAutoCommit" value="true" />
    <property name="initialSize" value="4" />
    <property name="maxActive" value="5" />
</bean>

here setting defaultAutoCommit to "true" even not work for me. it always return connection with autoCoomit false.

So I want to know how common DBCP2 manage this and how to achieve this in tomcat JDBC pool?

Solution

This is a known bug (or rather a feature since it hasn't been fixed) of Tomcat JDBC.

Setting the value of defaultAutoCommit isn't enough, you also need to enable interceptors which will actually enforce those settings. This will make the defaultAutoCommit value affect the connections.

<property name="jdbcInterceptors" value="ConnectionState" />