Dear all,

First I'd like to quote our famous Gavin ;-)


The default JDBCTransaction behavior is as follows:



(cfr. http://forum.hibernate.org/viewtopic.php?t=925718)

Unfortunately, this behaviour depends how your database/driver is configured (autocommit on or off).

Would be nice if we could have a property in the Hibernate.properties configuration that would set the autocommit accordingly.

This way you are always sure of its value, whatever database/driver brand/version you are using.

Any comment ?

I don't get it,
If the driver is set to autocommit=true, then it's changed and restored
If the driver is set to autocommit=false, then it's fine, nothing to do.
What am I missing ?

Hmm.. I wasn't clear enough ;-)

The problem is my application REQUIRES autocommit to be false - otherwise my transaction strategy may fail.

Suppose the JDBC driver is set to autocommit=true:
The JDBCTransaction will turn it off until commit or rollback, and restore it to true after.
In our system, a session may still be open after a transaction rollback (which is done at the first exception thrown by a service). A this point, the JDBCTransaction will set autocommit=true again, causing changes made after the rollback to be flushed and persisted when the session is closed.
In this case, these changes are not covered by any transaction anymore - and I wont to be sure they wont get to the database.
The easiest solution was to set autocommit=false, so they will be discarded by the database itself.

My point was there is no *explicit* item in the Hibernate configuration file that let the user configure the autocommit behaviour.
This seems important to me since it may have big impact on how well the application behaves.

Moreover, it seems that many people get caught with the same kind of problem (cfr. search forum on autocommit). An explicit configuration item in hibernate.properties may be an additional hint to developper and may help to reduce mistakes.

Finally, we have been caught by this issue when switching to another database having different default settings regarding the autocommit... And you don't notice it until you start having dirty records in your database!

Is my explanation better this time (made great efforts to improve my English ;)

I'm French: it may explain.

What you want basically is the possibility to set conenction autocommit to true or false on hibernate retrieving ?
Isn't it possible using hibernate.connection.<propertyName> ?

Maybe - but AFAIK these properties are passed asis to the underlying driver.
Again, this will not be database independent.

In the meantime, here is what we did:
We are using the ThreadLocal pattern for session management. When creating a new session, we always turn off autocommit on the underlying connection:

Wondering if Hibernate could do it itself based on a special property in its configuration file.

I know this won't provide much new feature to Hibernate - but again, having such entry in the template config file may help people to be aware of the issue.


I'm Belgian - maybe that's why we don't understand each other ;-)

Hibernate sets autocommit to false if it is enabled. If the JDBC driver reports autocommit as off (if it is on), Hibernate will not do anything. This is a problem of the JDBC driver and adding a configuration option doesn't help. This behavior is consistent and as simple as possible.

You should never re-use a Session after an exception occurs, but immediately close it.

s/(if its on)/(even if its on)

I know - but your statement should be:
You CANNOT re-use a Session after an exception

Even when using transactions!

Before having the problem, I thought (stupid) that nothing will get to the database after my rollback. This is simply *NOT* true if autocommit is true.

That's why I found the current behaviour of JDBCTransaction dangerous - since it actually depends on the settings of your JDBC driver - which are NOT set in your hibernate properties but eventually somewhere else.

And even if you can specify this property in your url connection, you may forget it when switching to another DB...

To me, it looks as important as the connection Isolation level - which is actually defined in the hibernate properties. So why not the autocommit ?

(last attempt to make my point ;-)

Of course. This is quite clear in the documentation.



Yes.



Show a simple snippet of code where this happens.

If I give you a sample of code, you will say: of course! you *cannot* reuse the session!!!

The fact is that it is easy to guarantee that all developpers will always follow the guideline. The only way I found it to turn the autocommit to false - if it is true, then nasty code can put garbage in your database.

The following scenario explains it:

1/
Session is opened by a servlet filter and stored in the TLS using the ThreadLocal pattern.

2/
At the same time, a new transaction is started, and stored in the TLS as well (same as for the session)

3/
The request goes down the chain for processing.
Our framework is such that a Transaction is automagically rollback when an exception is thrown by one of the services/components.

4/
The caller should handle this exception and pass it to the upper layer.
Unfortunately, one can catch the exception and never throw it back to the upper level - layers above are therefore not aware of the exception.
They may go further, thinking everything went ok -> possibly making other changes to objects already in the Hibernate session.

5/
Finally, when the thread comes back to the servlet filter, nothing is done for the transaction initially opened - since it has already been rolledback.
Then the session is closed.

At this time, if autocommit was set to TRUE, the session.close() will persist dirty entities. But you are not covered by any transaction anymore, and autocommit is TRUE ==> you get dirty data in your DB.

If autocommit was FALSE, Hibernate would issue the statements to update the database, but not commit would follow ==> your data are safe whatever happens.


This description just to explain why it is so important to:
- not reuse in any ways a session after an exception - thrown by Hibernate our your application;
- disable autocommit

Voila.


Note: I wasn't reporting any kind of problem or asking for information - just sharing my experience ;-)

Your experience is broken exception handling in an application. I don't see a particular good reason to change Hibernate because of this.

You missed my point: I wasn't asking to change Hibernate in any ways - it does exactly what it should.

Basically, I was just telling people that turning off the autocommit is a very good idea - as already said Gavin - and will help to keep your data safe in case of broken exception handling

It is a kind of insurance - better safe than sorry. Having nothing in my database is always better than having bad data.

Yes, autocommit should not be turned on by default. Unfortunately, it is in some databases.

Sorry to come back on this issue but I noticed the following:

The following Hibernate connection provider set autoCommit=false before returning the Connection:
- C3P0ConnectionProvider
- DBCPConnectionProvider
- DriverManagerConnectionProvider

At the same time they also set the Transaction Isolation.

The others (Datasource/UserSupplied/Proxool) don't... (yet).

So my request to have the ability to specify the autoCommit from the Hibernate configuration file is not relevant - Hibernate already does it thru the ConnectionProviders. Unfortunately their behavior is not consistent.

This lead to a more important question: what is the impact if the provider doesn't set the transaction isolation, is it set in another area ?

ProxoolCP should behave the same as the other three.


DatasourceCP and UserSuppliedCP should behave differently.