title: Disabling autoCommit for Oracle UCP on Tomcat 6
body: <p>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).</p>
  
  <p>(I added edits to the end of this post because the situation changed after a few developments)</p>
  
  <p>I cannot get rid of autocommit though. I tried configuring the datasource like this in Tomcat's context.xml:</p>
  
  <pre><code>    &lt;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;"
  /&gt;
  </code></pre>
  
  <p>Note the semicolons. I added them because of <a href="http://mail-archives.apache.org/mod_mbox/tomcat-users/201108.mbox/%3CE50C0E4CEABD914197D9B9EC464A85511A71FC1DB0@USSEAMAIL1.corp.amdocs.com%3E" rel="nofollow">this post</a>. But it is not working, neither with semicolons nor without.</p>
  
  <p>In my application, I have some test code like this:</p>
  
  <pre><code>        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());
          }
      });
  </code></pre>
  
  <p>The output is:</p>
  
  <pre><code>Print all conn props:{AutoCommit=false}
  ################################### autocommit is true
  </code></pre>
  
  <p>I also tried to disable autocommit in Hibernate with hibernate.connection.autocommit=false, but that doesn't work either.</p>
  
  <p>edit: My configuration is as follows:</p>
  
  <pre><code>@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();
  }
  </code></pre>
  
  <p>and</p>
  
  <pre><code>@Configuration
  @EnableTransactionManagement
  @EnableScheduling
  @Import({ HibernateConfig.class, ... })
  @ComponentScan(basePackages = ...)
  public class ServerCommonsConfig {
      [...]
  }
  </code></pre>
  
  <p>and</p>
  
  <pre><code>@Configuration
  @EnableAspectJAutoProxy(proxyTargetClass = true)
  @Import({ ServerCommonsConfig.class, ... })
  public class ApplicationServerConfig {
      [...]
  }
  </code></pre>
  
  <p>hibernate.properties looks like this:</p>
  
  <pre><code># 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
  </code></pre>
  
  <p>edit2:</p>
  
  <p>Apparently there was a typo with the name of the autoCommit property. It must be </p>
  
  <pre><code>connectionProperties=";autoCommit=false;"
  </code></pre>
  
  <p>with a small "a" at the start. I used the capitalized version because I found that in <a href="http://javacolors.blogspot.de/2010/12/replace-your-c3p0-or-dbcp-by-oracle.html" rel="nofollow">one of the only examples of configuring UCP with autoCommit off on the net</a>.</p>
  
  <p>Now the test output is</p>
  
  <pre><code>Print all conn props:{autoCommit=false}
  false
  ################################### autocommit ist false
  </code></pre>
  
  <p>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</p>
  
  <pre><code>private void commitAndResetAutoCommit() throws SQLException {
      try {
          jdbcContext.connection().commit();
      }
      finally {
          toggleAutoCommit();
      }
  }
  </code></pre>
  
  <p>, but the database remains unfazed by that. No changes are committed.</p>
  
  <p>Data access code example (this is the service on the server that the client calls via Spring HTTP invoker) :</p>
  
  <pre><code>@Transactional
  @Component
  public class ServiceImpl implements Service {
      @Resource
      private SessionFactory sessionFactory;
  
      //added this method to test the autocommit issue
      @Override
      public List&lt;Stuff&gt; getStuff(Long id) {
          Query query = sessionFactory.getCurrentSession().createQuery(
                  "FROM Stuff p");
          @SuppressWarnings("unchecked")
          List&lt;Stuff&gt; 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;
      }
  }
  </code></pre>