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!
