Comparing implicit connection cache (ICC) pools and UCP it appears that in UCP the auto commit state persist even after connection is closed. This means if a connection changes the auto commit state and return it to the pool and same connection is checked out later, it will have the changed auto commit state and not the default value.
This behaviour was not seen on ICC pools (ICC is considered deprecated). Once the connection is returned to the pool and checked out again, it will have the default behaviour.
The test cases is given at the end of the post. Comment/uncomment either getUCPDS or getICCDS methods to run the desired test case. The pool size is set to 1 to keep things simple. But even if it's increased the test case would work. Below is an explanation of the output seen.
UCP test case output
First up is checking the default state of auto commit
Auto commit status after checking out of the pool 0 trueNext up the auto commit state is changed to opposite of the default. If default is true, then set to false and vice versa. Afterwards verify if the changed state is reflected on the connection. Since default auto commit state was true the opposite was false. As such now the auto commit state is reflected as false.
Setting auto commit to opposite 0 falseFollowing the auto commit state change the connection is closed and returned to the pool.
Connections closed and returned to the poolFinally the connection is checked out of the pool again and auto commit state is verified. In UCP the connection will have changed state of auto commit rather than the default value, even though this is a "new" connection checked out of the pool (pools never close the underlying physical connection. So new is a logical new rather than an actual new physical connection).
Auto commit status after checking out of the pool 0 : false
ICC test case output
The ICC test case output is same except for last step. It will also have a true state for default auto commit setting. The default state is changed to false and connection is returned to the pool.
Auto commit status after checking out of the pool 0 true Setting auto commit to opposite 0 false Connections closed and returned to the poolWhen the connection is checked out next time from the pool it will have the default state instead of the previously changed state (unlike UCP).
Auto commit status after checking out of the pool 0 : trueThese test were carried out using ojdbc10.jar and ucp.jar versions 19.6.
The behaviour seen on UCP is same even if the default auto commit state is changed on the pool level by setting the OracleConnection.CONNECTION_PROPERTY_AUTOCOMMIT to false. In such a case the connection checked out of the pool will have auto commit set to false. But any change to this will persist same as shown above.
Auto commit status after checking out of the pool 0 false Setting auto commit to opposite 0 true Connections closed and returned to the pool Auto commit status after checking out of the pool 0 : trueWhen a SR was raised with regard to the difference in behaviour, Oracle support stated "When connections are created and placed in the UCP pool the connection properties that are associated with each connection remain for the duration of the connection". However, this fact is not reflected on the UCP documentation but Oracle support insisted this has always been the case. Following that the same test case was tested against ojdbc6.jar and ucp.jar of 11.2.0.1 and showed the same test output. It seems rightly or wrongly the UCP had the same behaviour throughout.
However, this difference between ICC and UCP is not listed anywhere on the documentation. Any migration from ICC to UCP should watch out for this change in behaviour.
Related Posts
java.sql.SQLException: Could not commit with auto-commit set on When Using 12c JDBC Driver
Change in 12c JDBC Behavior - setDate & getDate Does Not Truncate Timestamp
JDBC Auto Commit and Log File Sync
UCP Connections Fail to Connect to DB in Mount Mode With ORA-12504 TNS:listener was not given the SID in CONNECT_DATA
TimesTen JDBC Connection Pool Using UCP (Universal Connection Pool)
Using Oracle Connection Pools with ActiveMQ
JDBC Client Failover in Data Guard Configuration with PDBs
Java Test Case
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleConnectionCacheManager;
import oracle.jdbc.pool.OracleDataSource;
import oracle.ucp.UniversalConnectionPoolException;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;
/**
*
* @author Asanga
*/
public class TestUCPAutoCommit {
static String URL = "jdbc:oracle:thin:@192.168.1.100:1521:enlt1";
static String username = "asanga";
static String password = "asanga";
static int POOL_SIZE = 1;
static DataSource ds;
public static void main(String[] args) throws SQLException, UniversalConnectionPoolException, InterruptedException {
ds = getUCPDS(); // UCP test case
// ds = getICCDS(); // ICC test case
Connection[] cons = new Connection[POOL_SIZE];
System.out.println("Auto commit status after checking out of the pool \n");
for (int i = 0; i < cons.length; i++) {
cons[i] = ds.getConnection();
System.out.println(i + " " + cons[i].getAutoCommit());
}
System.out.println("\n Setting auto commit to opposite \n");
for (int i = 0; i < cons.length; i++) {
cons[i].setAutoCommit(!cons[i].getAutoCommit());
System.out.println(i + " " + cons[i].getAutoCommit());
}
System.out.println("\nConnections closed and returned to the pool \n");
for (Connection conn : cons) {
conn.close();
}
System.out.println("Auto commit status after checking out of the pool \n");
for (int i = 0; i < cons.length; i++) {
cons[i] = ds.getConnection();
System.out.println(i + " : " + cons[i].getAutoCommit());
}
}
static DataSource getUCPDS() throws SQLException {
PoolDataSource ds = PoolDataSourceFactory.getPoolDataSource();
ds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
ds.setURL(URL);
ds.setUser(username);
ds.setPassword(password);
ds.setInitialPoolSize(POOL_SIZE);
ds.setMinPoolSize(POOL_SIZE);
ds.setMaxPoolSize(POOL_SIZE);
/* uncomment below to set default state of auto commit to false */
// Properties connProps = new Properties();
// connProps.put(OracleConnection.CONNECTION_PROPERTY_AUTOCOMMIT, "false");
// ds.setConnectionProperties(connProps);
return ds;
}
static DataSource getICCDS() throws SQLException {
OracleDataSource ds = new OracleDataSource();
ds.setURL(URL);
ds.setUser(username);
ds.setPassword(password);
Properties properties = new Properties();
properties.setProperty("MinLimit", POOL_SIZE + "");
properties.setProperty("MaxLimit", POOL_SIZE + "");
properties.setProperty("InitialLimit", POOL_SIZE + "");
ds.setConnectionCachingEnabled(true);
OracleConnectionCacheManager cache = OracleConnectionCacheManager.getConnectionCacheManagerInstance();
cache.createCache("asa", ds, properties);
return ds;
}
}
