Disabling autoCommit for Oracle UCP on Tomcat 6

Question

I am using Hibernate 3.3.2 and Spring 3.2.3 on Tomcat 6. I want to use a connection pool and decided to test Oracle's UCP (for Oracle 11.2.0.4).

(I added edits to the end of this post because the situation changed after a few developments)

I cannot get rid of autocommit though. I tried configuring the datasource like this in Tomcat's context.xml:

    <Resource
    name="jdbc/datasource"
    auth="Container"
    type="oracle.ucp.jdbc.PoolDataSource"
    [snip]
    initialPoolSize="3"
    minPoolSize="3"
    maxPoolSize="10"
    factory="oracle.ucp.jdbc.PoolDataSourceImpl"
    connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"
    connectionProperties=";AutoCommit=false;"
/>

Note the semicolons. I added them because of this post. But it is not working, neither with semicolons nor without.

In my application, I have some test code like this:

        PoolDataSourceImpl pds;
    try {
        pds = (PoolDataSourceImpl) hc.dataSource();
        System.out.println("Print all conn props:" + pds.getConnectionProperties().toString());
    } catch (NamingException e) {
        e.printStackTrace();
    }

    sessionFactory.getCurrentSession().doWork(new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {
            System.err.println("################################### autocommit is " + connection.getAutoCommit());
        }
    });

The output is:

Print all conn props:{AutoCommit=false}
################################### autocommit is true

I also tried to disable autocommit in Hibernate with hibernate.connection.autocommit=false, but that doesn't work either.

edit: My configuration is as follows:

@Configuration
public class HibernateConfig {

@Bean
LocalSessionFactoryBean hibernateSessionFactory() throws Exception {
    LocalSessionFactoryBean factory = new LocalSessionFactoryBean();
    factory.setMappingResources(new String[] { "mapping.hbm.xml" });
    factory.setHibernateProperties(hibernateProperties());
    factory.setDataSource(dataSource());
    return factory;
}

@Bean
public DataSource dataSource() throws NamingException {
    return (DataSource) new InitialContext().lookup("jdbc/datasource");
}

@Bean
public HibernateTransactionManager transactionManager() throws Exception {
    HibernateTransactionManager manager = new HibernateTransactionManager();
    manager.setSessionFactory(hibernateSessionFactory().getObject());
    return manager;
}

@Bean
Properties hibernateProperties() throws IOException {
    PropertiesFactoryBean bean = new PropertiesFactoryBean();
    bean.setLocation(new ClassPathResource("hibernate.properties"));
    bean.afterPropertiesSet();
    return bean.getObject();
}

and

@Configuration
@EnableTransactionManagement
@EnableScheduling
@Import({ HibernateConfig.class, ... })
@ComponentScan(basePackages = ...)
public class ServerCommonsConfig {
    [...]
}

and

@Configuration
@EnableAspectJAutoProxy(proxyTargetClass = true)
@Import({ ServerCommonsConfig.class, ... })
public class ApplicationServerConfig {
    [...]
}

hibernate.properties looks like this:

# Hibernate Properties

#hibernate.bytecode.provider=cglib
hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
hibernate.show_sql=true
hibernate.format_sql=true

hibernate.cache.provider_class=org.hibernate.cache.NoCacheProvider
hibernate.cache.use_second_level_cache=false
hibernate.cache.use_query_cache=false
hibernate.jdbc.batch_size=200
hibernate.connection.autocommit=false
connection.autocommit=false

edit2:

Apparently there was a typo with the name of the autoCommit property. It must be

connectionProperties=";autoCommit=false;"

with a small "a" at the start. I used the capitalized version because I found that in one of the only examples of configuring UCP with autoCommit off on the net.

Now the test output is

Print all conn props:{autoCommit=false}
false
################################### autocommit ist false

which is all fine and dandy, but now nothing gets committed. I see Hibernate writing DELETE statements when I flush, and after the @Transactional method ends, I can debug to Spring's TransactionAspectSupport and HibernateTransactionManager.doCommit() and finally JDBCTransaction.commitAndResetAutoCommit() where it says

private void commitAndResetAutoCommit() throws SQLException {
    try {
        jdbcContext.connection().commit();
    }
    finally {
        toggleAutoCommit();
    }
}

, but the database remains unfazed by that. No changes are committed.

Data access code example (this is the service on the server that the client calls via Spring HTTP invoker) :

@Transactional
@Component
public class ServiceImpl implements Service {
    @Resource
    private SessionFactory sessionFactory;

    //added this method to test the autocommit issue
    @Override
    public List<Stuff> getStuff(Long id) {
        Query query = sessionFactory.getCurrentSession().createQuery(
                "FROM Stuff p");
        @SuppressWarnings("unchecked")
        List<Stuff> list = query.list();

        for (Stuff stuff : list) {
            sessionFactory.getCurrentSession().delete(stuff);
        }
        //this flush used to commit the changes
        //instead, now nothing gets committed
        sessionFactory.getCurrentSession().flush();
        return null;
    }
}

Answer 1

Spring automatically manages the connection for you (assuming you use Spring controlled transactions and a datasource from the spring configuration). So you shouldn't need to mess around with the read-only settings of the connection.

Also if you use spring to inject the datasource into hibernate your hibernate.connection.* properties are useless.