Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes JDBC and the fly likes Does a Connection commit on close? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Does a Connection commit on close?" Watch "Does a Connection commit on close?" New topic
Author

Does a Connection commit on close?

Yuriy Zilbergleyt
Ranch Hand

Joined: Dec 13, 2004
Posts: 429
If a Connection with autoCommit = false is closed without commiting, do the changes get committed, lost, or is it vendor dependant behavior?

I'm talking both about the standard Connection and about the way ConnectionPools in J2EE servers are supposed to handle them.

Thank you,
Yuriy
[ October 04, 2005: Message edited by: Yuriy Zilbergleyt ]
Makarand Parab
Ranch Hand

Joined: Dec 10, 2004
Posts: 121
Hi
If autocommit is set to false in the code, then u will have to write conn.commit() in your java code, else you won't see the insert/update/delete changes in the database.

By default, in all app server and oracle db, autocommit is true. No matter you use connection pooling or direct connection[DriverManager] since commit is the operation on connection object it doesn't matter. BY DEFAULT autocommit is true everywhere unless you change that property in the app server setting or through your java code.

Let me know
Inputs and suggestions are welcomed

Regards
Makarand Parab
Yuriy Zilbergleyt
Ranch Hand

Joined: Dec 13, 2004
Posts: 429
Hmm, I just checked with some test code, and the connection committed after the test executed, even with connection.setAutoCommit(false) being called at the very beginning. The only way i can get it to drop the changes is by calling "connection.rollback()".

The connection was obtained through a DataSource deployed on Weblogic 8.1. The database is MySql 4.1.14.

Yuriy
Makarand Parab
Ranch Hand

Joined: Dec 10, 2004
Posts: 121
Hi
I think it means that MySQL doesn't support the autocommit = false property. U need to get into the specifications of the database.

Regards
Makarand Parab
Yuriy Zilbergleyt
Ranch Hand

Joined: Dec 13, 2004
Posts: 429
If I call setAutoCommit(true), it commits everything and I can't roll back. If I call setAutoCommit(false), it doesn't commit anything until I either tell it to, or the connection closes. Rollback works only if setAutoCommit(false) was called. Is that really "not supporting"?

Yuriy
Makarand Parab
Ranch Hand

Joined: Dec 10, 2004
Posts: 121
Hi
It means database is supporting the the autocommit property.
So here is what should happen

By default autocommit is true, it will commit as soon as sql is fired through java, whether you close the connection or not. Rollback is of no use in this case

If autocommit is set to false, only and only if you say conn.commit, the sql operation will take place or if you say rollback it will rollback the changes. i have no idea what will happen if you close the connection without saying commit but logically it should not commit the changes.

Thanks
Makarand Parab
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


I think it means that MySQL doesn't support the autocommit = false property. U need to get into the specifications of the database.

Remember that some of the database engines in MySQL don't support transactions at all. What kind of table type are you using?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Yuriy Zilbergleyt
Ranch Hand

Joined: Dec 13, 2004
Posts: 429
According to the MySQL Administrator, InnoDB is the default, is disabled, and is activated. Yes, at the same time. But in any case, since commmit() and rollback() work, I'm assuming transactions work.

Yuriy
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Yuriy,

This is an excellent question which I have investigated repeatedly and the answer is... there is no answer.

Personally I feel, as I think you do, that unless a specific commit statement is sent a transaction should be rolled-back if the connection is closed.

However I have gone through the JDBC spec in relation to this and there is NO default behaviour specified for this situation. I suspect the reason for this is that some DB's do it one way (commit) and some the other way (rollback) and so no agreement could be had on what the spec should say.

Again personally I find this sadly lacking. I believe this behaviour should be specified. As it is I find the entire transaction element of JDBC sadly lacking. I don't care which way it happens if my code just calls close instead of commit or rollback but I do care what happens if it is outside my control i.e. the connection socket is terminated because of a network failure.

So having said all that you best bet is to test and see what happens on your setup. (It seems to be commiting). Also be aware that behaviour with a pooled connection may be different. (I would suspect it will always commit in that circumstance)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Does a Connection commit on close?