• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Does a Connection commit on close?

 
Ranch Hand
Posts: 429
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 429
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 429
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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?
 
Yuriy Zilbergleyt
Ranch Hand
Posts: 429
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 381
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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)
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic