Recently we have done an environment setup upgradation/migration as mentioned below for our application: JDK 1.7 ---> JDK 1.8 Tomcat 7.0.47 ---> Tomcat 9.0.60 (Also using Apache HTTP Server along with Apache Tomcat for new setup) ojdbc7.jar ---> ojdbc8.jar Oracle 12c (12.1.0.2.0) ---> Oracle 19c (19.14.0.0)
We have been facing a JDBC autocommit issue since then in this new environment setup:
Issue: For the first time AutoCommit is true by default. We update the value of autocommit to false and manually commit the transaction. After that, we also close the connection. Now if we try to create another connection object with "DataSource.getConnection()" within 10 seconds (in debug mode), the previous connection object is getting assigned with autocommit value to false. But, we expect a new connection object to be assigned with autocommit value set to true.
During debugging, we found that in the "GenericObjectPool" class, inside the method "borrowObject", the line "p = idleObjects.pollFirst();" assigns (i) if within 10 seconds=> the value of p is the previous connection object; or, (ii) if after 10 seconds=> the value of p is null and thus one new connection object is created for p with autocommit value to true
public T borrowObject(final Duration borrowMaxWaitDuration) throws Exception { ........... ........... ........... while (p == null) { create = false; p = idleObjects.pollFirst(); if (p == null) { p = create(); if (p != null) { create = true; } } } ......... ......... ......... }
Our initial understanding is that this code segment is the reason behind such behavior. But, please help us understand the reason why it is behaving differently in the new environment setup. Also, please help us with the Java source code to create a new connection object with autocommit value to true for subsequent connection objects.
***Note: In the previous old environment setup mentioned before, the same source code works perfectly fine i.e. the autocommit is set to true by default every time we try to create a connection object with the same code "DataSource.getConnection()".
In Tomcat 7.0.47, the connection is retaining autocommit value to true also for second and any subsequent connections. But in Tomcat 9.0.60, autocommit value is true only for the first connection and autocommit value for any subsequent connections are false (if subsequent connections are created within 10 seconds of the first connection object creation).
