wood burning stoves 2.0*
The moose likes JDBC and the fly likes autoCommit doubt Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

JavaRanch » Java Forums » Databases » JDBC
Bookmark "autoCommit doubt" Watch "autoCommit doubt" New topic

autoCommit doubt

Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 746
Hi guys! I was wondering... I've used this method before in Connection object.


//do some stuff


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!

SCJP 1.5
http://devpinoy.org/blogs/lamia/ - http://everypesocounts.com/
rohini gundu

Joined: Nov 16, 2006
Posts: 12
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.
autoCommit - true enables auto-commit; false disables auto-commit.
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

Joined: Sep 18, 2005
Posts: 746
Oh no... How can I possibly achieve atomicity here then? I need to rollback if an error ever occurs...
Peter Chase
Ranch Hand

Joined: Oct 30, 2001
Posts: 1970
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!

Betty Rubble? Well, I would go with Betty... but I'd be thinking of Wilma.
Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 746
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

Joined: Sep 18, 2005
Posts: 746
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...!?
Herman Schelti
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
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...

GeeCON Prague 2014
subject: autoCommit doubt