I have a Sybase 11.9.2 connection pooled (DBCP 1.1 RC1) datasource set up and bound to JNDI (completely outside of Hibernate), with autocommit = false.

Outside of Hibernate (by looking up JNDI to get the Datasource, and then getting a connection directly from the datsdatasource), I can see that autocommit = false (correct), and more importantly, that @@TRANCHAINED = 1 and @@trancount = 1 (ie, autocommit is false, so a transaction has been opened via Sybase "chained mode"), which is exactly what I'd expect.

However, when I try to use this datasource using Hibernate, I get errors related to the fact that chained mode is OFF (when it should be still ON). The transaction that *should* be open (because autocommit is off) has been incorrectly closed somewhere (either using a commit/rollback, or via connection.setAutoCommit(true) ).

Does anyone know why / when this is happening?? I suspect Hibernate is doing something funny with the connection before it uses it, but having looked at the Hibernate code, I can't see it.


My config:
I have turned *off* connection pooling in Hibernate (to prevent it conflicting with DBCP), and I am using the JDBCTransactionFactory instead of the JTATransactionFactory. I am also using the Hibernate Transaction API rather than the J2EE UserTransaction variety.

My hibernate.properties:



The sql I am using to see that the problem is definitely with the connections opened by Hibernate (and not with the connections opened outside of Hibernate) is the following:



(gets processes for "myusername" on the current database). Note that if a tran is open on the connection, the "tran_name" value will be NOT null. Null implies that there is NO transaction open, which is what I am seeing using Hibernate on the same Datasource.

I just had a thought on this:

I am NOT setting the default isolation level (for connections) on the Datasource before binding it to JNDI for Hibernate to access, so is it possible that when Hibernate gets the data source (and subsequently the connection), and picks up the hibernate.connection.isolation=1 value from the hibernate.properties file, it might be issuing somthing to set the isolation level to 1, where that change also sets autocommit to true, sets chained mode to off, and closes the transaction???

Am in the process of testing this..

1) Remove hibernate.connection.isolation setting from hibernate.properties
2) Set default isolation level on the datasource instead.
3) re-run

will post an update as soon as I have one.

A few things have become clearer, but it still doesn't work!

I am attempting to use DBCP 1.1RC1, whereas Hibernate bundles DBCP 1.0.. so a potential conflict exists there...

I tried anyway, setting the default isolation level on the DataSource (DBCP 1.1 specific.. not available on 1.0), and then have Hibernate pick up the Datasource from JNDI, without the hibernate.connection.isolation option being set in the hibernate.properties file.. but guess what.. the connection attributes are still not what I specified.. (chained mode =on, autocommit=false, transaction open)

Is it possible that Hibernate is picking up properties from some other properties file other than hibernate.properties?

What does Hibernate do on the connection once it gets it, assuming that the following are the only options set in the Hibernate.properties file???



Is there some other datasource (other than the DBCP SharedPoolDataSource) that I can use to create the Datasource in JNDI that Hibernate will have less issues with??

What does everyone else do for manually created DataSources???

To see exactly where the issue lay, I tried a different driver, namely the DataDirect Sybase driver (rather than the jTDS Sybase driver).. this new driver behaves slightly differently, in that, while it does NOT turn on chained mode, it does begin a transaction when autocommit = false, which is fine.

The issue thus looks like its in the interaction between Hibernate and the DataSource set up using the jTDS Sybase driver.. (The jTDS driver behaves as expected in this respect when used outside of Hibernate).

Bizarre...