autocommit and catalog config properties

[Mark Woon]

I'm using HikariCP 1.3.8 with Hibernate 4.3.5 and connecting to an Oracle database (using the 12c driver).  I'm setting the autocommit and catalog properties but it doesn't seem to be making any difference.

In hibernate.cfg.xml:

    <property name="connection.provider_class">com.zaxxer.hikari.hibernate.HikariConnectionProvider</property>

    <property name="hibernate.hikari.dataSourceClassName">oracle.jdbc.pool.OracleDataSource</property>

    <property name="hibernate.hikari.dataSource.url">xxx</property>

    <property name="hibernate.hikari.dataSource.user">xxx</property>

    <property name="hibernate.hikari.dataSource.password">xxx</property>

    <property name="hibernate.hikari.autoCommit">true</property>

    <property name="hibernate.hikari.catalog">Preview</property>

When I check the Connection underlying the Session I get from Hibernate, I see that autocommit is false and catalog is null.

Am I using these config properties incorrectly?  Or is there something else I need to fix?

Thanks,

-Mark

[Brett Wooldridge]

Are you in a position where you can attach a debugger and set some breakpoints in the HikariCP code?  From everything I've looked at, those properties should be working.  Here is what I've analyzed:

When a connection is added to the pool...

private boolean addConnection() {
   ...
    IHikariConnectionProxy pc = ProxyFactory.getProxyConnection(this, connection, ... isAutoCommit, isReadOnly, catalog);     
    pc.resetConnectionState();
    ...
  }

When a connection getProxyConnection() creates a new proxy...

 protected ConnectionProxy(... boolean defaultAutoCommit, boolean defaultReadOnly, String defaultCatalog) {
  this.defaultAutoCommit = defaultAutoCommit;
  this.defaultCatalog = defaultCatalog;
  ...
  isCatalogDirty = true;
  isAutoCommitDirty = true;
 }

And when the resetConnectionState() is called...

public final void resetConnectionState() throws SQLException {
 ...
 if (isAutoCommitDirty) {
  delegate.setAutoCommit(defaultAutoCommit);
  isAutoCommitDirty = false;
 }

 if (isCatalogDirty && defaultCatalog != null) {
  delegate.setCatalog(defaultCatalog);
  isCatalogDirty = false;
 }
}

So assuming the properties are making it into the Hikari config correctly, it is difficult to see how they could fail to be set.  Maybe put a breakpoint in the ConnectionProxy constructor and check defaultAutoCommit and defaultCatalog, and put a breakpoint in ConnectionProxy.resetConnectionState() can verify that the delegate is being called to set those properly.


I can't see anything wrong with your configuration, so a little debugging assistance would be appreciated.

[Mark Woon]

After a bit of digging I found that Oracle just ignores setCatalog().  I has assumed I could use this to change the default schema, but was mistaken.

But autocommit should still be configurable.  I added the breakpoints as you suggested and can confirm that HikariCP is doing the right thing.  I also checked right before getConnection() returns a Connection, and it looks good.

But the moment I get the Connection from Hibernate, I'm getting autocommit == false even though it was true the before it left HikariCP.  I'm guessing there's something else that's changing this value in Hibernate.

Hibernate does have a hibernate.connection.autocommit property, but that doesn't seem to make a difference either.

While I think it's odd that I can't set autocommit to true, this isn't an issue for me since I want it to be false anyway.  I was just testing different values and stumbled across this problem.

-Mark

[Brett Wooldridge]

It sounds like an issue with the Hibernate configuration.  I know that Hibernate can be set to honor autocommit=false, but the configuration varies based on the container (Spring, JBoss, etc.).  For example, I've seen both:

<property name="hibernate.connection.autocommit" value="false"/>

and

<property name="hibernate.connection.autocommit">false</property>

...styles of configuration.  Also, different class transactional annotations can affect how Hibernate sets the autocommit behavior.  But I'm nearly 100% certain that Hibernate is capable of autocommit=false behavior, otherwise a signification number of applications would be affected.  Even though you don't need it, you might want to ask over in the Hibernate support forums just so you understand what might be wrong with your configuration attempts.

[Mark Woon]

I guess I wasn't clear, but I _want_ autocommit to be false, and that's what I'm getting.

While testing the catalog property, I decided to test the autoCommit property as well, and noticed that I couldn't change autoCommit to true.

You're probably right in that there's some other Hibernate config that's forcing autocommit to false, and I'm ok with that.