• 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

autoCommit doubt

 
Ranch Hand
Posts: 751
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi guys! I was wondering... I've used this method before in Connection object.

conn.setAutoCommit(false);

//do some stuff

conn.commit();
conn.setAutoCommit(true);


This was possible with a single Connection alive. But what if you're using a Connection pool? And between the setAutoCommit(false) and the commit() method, we can't be sure if there would be any other Connection object being used. Is Connection's autoCommit() effective only on the object you called it in? Or would it be safe to use it for the rest of the transaction, even if other Connection objects are used? Thanks!
 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think its only for that connection object on which the auto commit has been called. Because by default if we get a new connection its in auto commit mode.
In the sun specs its given as follows:

public void setAutoCommit(boolean autoCommit)
throws SQLExceptionSets this connection's auto-commit mode. If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions. Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback. By default, new connections are in auto-commit mode. The commit occurs when the statement completes or the next execute occurs, whichever comes first. In the case of statements returning a ResultSet, the statement completes when the last row of the ResultSet has been retrieved or the ResultSet has been closed. In advanced cases, a single statement may return multiple results as well as output parameter values. In these cases the commit occurs when all results and output parameter values have been retrieved.
Parameters:
autoCommit - true enables auto-commit; false disables auto-commit.
Throws:
SQLException - if a database access error occurs



Hope you have understood.

[changed code tag to quote so doesn't wrap page]
[ February 21, 2007: Message edited by: Jeanne Boyarsky ]
 
Timothy Sam
Ranch Hand
Posts: 751
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Oh no... How can I possibly achieve atomicity here then? I need to rollback if an error ever occurs...
 
Ranch Hand
Posts: 1970
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
To use database transactions, you have to do all the operations of the transaction on a single Connection object. No other threads should try to use that Connection at the same time, unless they want to participate in the same transaction.

Once that transaction has finished (rollback or commit), you can use the same Connection for another transaction, and for efficiency, you would often wish to do so, rather than close that Connection and open another.

If you're using connection pools from javax.sql, the PooledConnection.getConnection() method is supposed to return you "a temporary handle" to refer to the real physical Connection underneath. You are supposed to be able to call close() on this Connection, without necessarily closing the underlying real physical Connection. The implementation of the temporary Connection ought to ensure that it behaves as if it were a real physical Connection, from the client code's perspective; this should include transactional behaviour. If the pool is properly implemented, you shouldn't find your code getting messed-up by transactions that some previous temporary Connection performed on the same real physical Connection.

That said, the noddy pool that I just wrote doesn't quite achieve these things. Don't buy a connection pool from me!
 
Timothy Sam
Ranch Hand
Posts: 751
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks! Hmmm... I think we're in a lot of trouble now...

rohini gundu, could you please try to edit your post(the java doc part) so it would be easier to read? Thanks!
 
Timothy Sam
Ranch Hand
Posts: 751
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So I where should I be getting connection from the pool? I did this for every method in my DAO.



As you can see, for every method I try to acquire a Connection if there's not already one available.




It seems like, based on your point... My idea of doing something like this in the controller is wrong...



Should I get a reference to a Connection first, in case I have to make multiple transactions? Like this?





Awwwww... Were we doing it all wrong all these time...!?
 
Ranch Hand
Posts: 387
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi Timothy,

I've used the Spring-framework for situations like yours:

-it has classes to get and automatically release connections, statements en resultsets.
-you can define that a method is part of a transaction instead of having to code commits or rollbacks yourself.
-Spring's Exception handling is better than standard SQLExceptions

It will save you a lot of coding...

Herman
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic