Does Connection.close() result in commit or rollback ?

Earlier today I saw a transaction question targeted for a completely different audience pop up as the first headline news item on a well known java news site. Besides giving me and my colleagues a good laugh about bugs and transactions it also touched upon one of the questions that have given me a couple of free beers in bar bets and been mind-boggling for students during trainings. The question relates to the following (simplified) code:

Connection con = DriverManager.getConnection(url, name, pwd);
con.setAutoCommit(false);
Statement st = con.prepareStatement("delete VITAL_DATA");
st.executeUpdate();
con.close();

Assuming that VITAL_DATA contains data before we execute this code, will it still contain those data after the call to con.close()?

Yes or No ?

The answer is: It depends!

If this code were executed against an Oracle database VITAL_DATA will no longer contain data since Oracle implicitly calls commit() if your connection has left over changes.

It is about here people starts arguing with me and say I'm crazy! No way that is possible, because all developers who believe in the goodness of transactions and its ACID properties would state that nothing should be committed to a database without an explicit call to commit() when not running in auto-commit mode - anything else would be a sin.

Well, I guess Oracle like to be sinful, and it is even documented.

/Page 3-14, Oracle9i JDBC Developers Guide and Reference/ contains the following text:

If auto-commit mode is disabled and you close the connection
without explicitly committing or rolling back your last changes,
then an implicit <code>COMMIT</code> operation is executed.

I heard from an Oracle tech-guy that this behavior is a left over from how the old OCI library worked - whether that is true or not I don't know; but it sure is a surprise for most developers I have shown this too (including my self the first time I bumped into this issue).

After discovering this a couple of years back I went to look in the JDBC spec to see who is to blame for this behavior.

The fun part is that I have not been able to find anything about the behavior of close() in JDBC besides the following text from the /JDBC 4.0/ spec:

When auto-commit is disabled, each transaction must be explicitly committed by
calling the Connection method commit or explicitly rolled back by calling the
Connection method rollback, respectively.
The javadoc for close() states:

Releases this Connection object's database and JDBC resources

immediately instead of waiting for them to be automatically released

From a naive ACID believing person I would say Oracle are wrong on this, but notice how the specification only mentions how the transaction behaves ? It does not explicitly state that close() is not allowed to commit the data, only that it should release resources (which it does!)

Thus from my perspective Oracle is walking on an edge here, but apparently without breaking the spec. Note that it might also occur on other databases, but it has never occurred for me on the other major databases I have worked with.

Lessons learned ? Always explicitly (or declaratively) commit or rollback your transactions!

 

 

=======================

Does Java Connection.close rollback into a finally block?.

I know .Net SqlConnection.close does it.

With this I could make try/finally blocks without catch...

Example:

try{
    conn.setAutoCommit(false);ResultSet rs = executeQuery(conn,...);....
    executeNonQuery(conn,...);....

    conn.commit();}finally{
   conn.close();}
--------
According to the javadoc, you should try to either commit or roll back before calling the close method. The results otherwise are implementation-defined.

In any database system I've worked with, there is no harm in doing a rollback right after the commit, so if you commit in the try block, and rollback in the finally, things get committed, whereas if an exception or early return causes the commit to be missed, the rollback will rollback the transaction. So the safe thing to do is

try{
    conn.setAutoCommit(false);ResultSet rs = executeQuery(conn,...);....
    executeNonQuery(conn,...);....

    conn.commit();}finally{
   conn.rollback();
   conn.close();}


Oracle's JDBC driver commits on close() by default. You should not rely on this behaviour if you intend to write multi-platform JDBC code.

The behavior is completely different between different databases. Examples:

Oracle

The transaction is committed when closing the connection with an open transaction (as @Mr. Shiny and New 安宇 stated.

SQL Server

 

Calling the close method in the middle of a transaction causes the transaction to be rolled back.