See the code which I have taken from a java tutorials. I dont understand the need of rollback statement in the last try catch loop. As we had already set the auto commit to false then even if some error occurs one satement is not going to get commited. Then why to use rollback.


import java.sql.*;
     
public class TransactionPairs {

public static void main(String args[]) {

String url = "jdbc:mySubprotocol:myDataSource";
Connection con = null;
Statement stmt;
PreparedStatement updateSales;
PreparedStatement updateTotal;
String updateString = "update COFFEES " +
"set SALES = ? where COF_NAME like ?";

String updateStatement = "update COFFEES " +
"set TOTAL = TOTAL + ? where COF_NAME like ?";
String query = "select COF_NAME, SALES, TOTAL from COFFEES";

try {
Class.forName("myDriver.ClassName");

} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}

try {

con = DriverManager.getConnection(url,
"myLogin", "myPassword");

updateSales = con.prepareStatement(updateString);
updateTotal = con.prepareStatement(updateStatement);
int [] salesForWeek = {175, 150, 60, 155, 90};
String [] coffees = {"Colombian", "French_Roast",
"Espresso", "Colombian_Decaf",
"French_Roast_Decaf"};
int len = coffees.length;
con.setAutoCommit(false);
for (int i = 0; i < len; i++) {
updateSales.setInt(1, salesForWeek[i]);
updateSales.setString(2, coffees[i]);
updateSales.executeUpdate();

updateTotal.setInt(1, salesForWeek[i]);
updateTotal.setString(2, coffees[i]);
updateTotal.executeUpdate();
con.commit();
}

con.setAutoCommit(true);

updateSales.close();
updateTotal.close();

stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);

while (rs.next()) {
String c = rs.getString("COF_NAME");
int s = rs.getInt("SALES");
int t = rs.getInt("TOTAL");
System.out.println(c + " " + s + " " + t);
}

stmt.close();
con.close();

} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
if (con != null) {
try {
System.err.print("Transaction is being ");
System.err.println("rolled back");
con.rollback();
} catch(SQLException excep) {
System.err.print("SQLException: ");
System.err.println(excep.getMessage());
}
}
}
}
}

This is simply to leave a clean database connection! In this special case, it might not be required, but think of code where you want to so something else with the same connection later... (I must admit I don't know if there's an automatic rollback in case the connection is from a pool and therefore only virtually closed)

Reagrds

DJ

I guess a bug let this post get to the site's main page...

PLUS...
A) Code is not formatted.
B) Code lacks comments
C) code not syntax colored (Eclipse can preserve colorings when it's cut-pasted sometimes)
D) dude, you should log, not use System.err

I thought I was out of my league on this site. And this gets posted?

because, mainly, if you are into a transaction, depending on your database, maybe you are locking the resources.
Some dblinks with legacy systems keep the rows locked even if your program thread ends.

Other good reason is: if your connection is passed TO or FROM, you have 1 place where you are going to commit/rollback it.

Oh dear! I was trying to come up with a witty and harsh - but only appropriate - reply and failed miserably in doing so because the Motörhead's "Whorehouse blues" kept playing in my head ever since I've passed the 3rd, umm, phrase.

See the code which I have taken from a java tutorials. I dont understand the need of rollback statement in the last try catch loop. As we had already set the auto commit to false then even if some error occurs one satement is not going to get commited. Then why to use rollback.

And whatever makes you think that closing a connection will not commit the transaction anyway? Sigh!

Karl, ignoring the unwarranted sarcasm to a valid question ...

I would be surprised if closing a connection without calling commit would automatically commit the data unless of course AutoCommit was turned on.

Karl, ignoring the unwarranted sarcasm to a valid question ...I would be surprised if closing a connection without calling commit would automatically commit the data unless of course AutoCommit was turned on.

Why? AutoCommit is for committing after each statement. This has nothing to do with the handling of the connection.

Connection.close() will close the connection and freeing any resources associated with it. Nothing else is specified as far as I know.

I sort of remember that with various databases you could configure if a connection would commit or rollback once the connection was closed.

The short answer is that while you have AutoCommit turned off the database needs to keep a record of what data you have updated until you either commit or rollback.
Calling rollback tells the database that you have finished and that it can throw away the data.

The more subtle question is "What happens if you close the connection without committing?". I would guess that this would have the same effect as calling rollback.

I think that rollback is only there to be nice to the database if you plan to keep the connection open, or to be explicit that you want your data discarded before closing the connection.

The more subtle question is "What happens if you close the connection without committing?". I would guess that this would have the same effect as calling rollback.I think that rollback is only there to be nice to the database if you plan to keep the connection open, or to be explicit that you want your data discarded before closing the connection.

Auto-commit means "each statement is a transaction". Without this each statement since the last commit or rollback belongs to the same transaction.
Behaviour on abandoned open transactions is dependant on:
- the database server (in case the connection is interrupted on a lower lever than the driver)
- the driver
Take iBATIS for example; you're required to use a
try { connection::begin; connection::commit or connection::rollback; }
catch { connection::rollback; }
finally { connection::end; }
paradigm, where "end" is a no-op if you have called either commit or rollback and a rollback otherwise.

Closing an uncommited transaction without rolling back will have different outcomes with different databases.

With Oracle, if you have added or changed a lot of data, you will end up in an inconsistent state where some of the data makes it in and some does not.

Try doing a batch insert of a few hundred thousand records and close the connection.

If auto-commit mode has been disabled, a transaction will not terminate until the method commit or rollback is called explicitly. See 2.1.7 Transactions in http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/connection.html

With Oracle, if you have added or changed a lot of data, you will end up in an inconsistent state where some of the data makes it in and some does not.

I seriously doubt that. You sure you didn't mean mysql ?
If that does indeed happen it can only be a bug. A serious one.

With Oracle, if you have added or changed a lot of data, you will end up in an inconsistent state where some of the data makes it in and some does not.

In many systems closing connection by default commits all pending changes, to prevent data loss and ... because it's far easier than rolling back :-)

I would be surprised if closing a connection without calling commit would automatically commit the data unless of course AutoCommit was turned on.

I tried the following test case...

Testcase #1 :A standalone Java application using Oracle JDBC Thin Driver 9.x version...

conn.setAutoCommit(false);
...
doSomeUpdate(...)
...
conn.close();

The Result is : The Transaction was COMMITTED.

Testcase #2 :
Try the same operation with pooled connection supplied by BEA Weblogic Application Server...

The Result is : The Transaction is NEITHER committed NOR rolledback. The rows that were updated were in a LOCKED state, not allowing any more updates to these rows.

The worse case is, the next time, if the same connection instance were to be returned by the Weblogic Connection Pool, and by default, when a connection is given from the pool, the AutoCommit is ENABLED. Any any SQL update would end up committing the previous Transaction, thereby releasing the DB Lock.

Why should the driver and the App Server's pool implementation have such differences...If one writes a program using a connection directly from the driver and tries to run the same program inside a app server using pooled connection, he is in for a big trouble hmm?

See the code which I have taken from a java tutorials. I dont understand the need of rollback statement in the last try catch loop. As we had already set the auto commit to false then even if some error occurs one satement is not going to get commited. Then why to use rollback.

The simplest answer I can think of is that in a 'real' application, you should be using some sort of connection management e.g. a connection pool. In this case, if you leave the connection with uncommitted operations, the next user of that connection may unknowingly commit your partially completed work.

Even if you are explicitly calling close() on the Connection Object, you cannot be sure that will actually close the actual DB session. The Connection class can be implemented in many ways. close() may just return the underlying connection to the pool.

Finally, it's just plain sloppy work. Unnacceptable even if it works.

very nice code.

Since the question about rollback has been answered, I think it's clear this code, though from a tutorial, could use some cleanup.

As is, the code above is prone to bugs (JDBC resource leaks, improper transaction demarcation) and general (halloween type scarrryy) verbosity.

Here is implementation of the same unit of work using Spring's Transaction Management and JDBC abstraction libraries:

@Transactional
public void doUnitOfWork() {
    String sql = "update COFFEES set SALES = ?, TOTAL = TOTAL + ? where COF_NAME like ?";
    BatchSqlUpdate updater = new BatchSqlUpdate(getDataSource(), sql);
    int [] salesForWeek = { 175, 150, 60, 155, 90 };
    String [] coffees = { "Colombian", "French_Roast", "Espresso", "Colombian_Decaf", "French_Roast_Decaf"};
    for (int i = 0; i < coffess.length; i++) {
        updater.update(new Object[] { salesForWeek[i], salesForWeek[i], coffees[i] });
    }
    updater.flush();
    
    getJdbcTemplate().query("select COF_NAME, SALES, TOTAL from COFFEES", new RowCallbackHandler() {
        public void processRow(ResultSet rs) {
            String c = rs.getString("COF_NAME");
            int s = rs.getInt("SALES");
            int t = rs.getInt("TOTAL");
            System.out.println(c + " " + s + " " + t);
        }
    });
}

Woo! Much better. Connection managemnt is handled automatically (no more resource leaks), SQL exception translation/root cause analysis is automatic as well, and transaction demaracation is, too, in a fully @declarative fashion.

Anyone using raw JDBC these days should seriously consider why they are doing so, Sun tutorial writers included :-)

Keith

I know its off topic, it'd be great if TSS allowed posters to create a signature and append it to each post automatically. I only mentioning it because it useful to know where people come from when they post, like Keith's link to Spring. Not singling him out, in fact people would probably pay more attention to posters when they know they have an affiliation to a reputable group.

God knows it would save Cameron a lot of copying and pasting ;)

I know its off topic, it'd be great if TSS allowed posters to create a signature and append it to each post automatically. [..] God knows it would save Cameron a lot of copying and pasting ;)

I use a FireFox auto-signature plug-in that randomizes my sigs.

Peace,

Cameron Purdy
Tangosol Coherence: Now available in new Shiny Bauble Edition

I agree that code is pretty bad and needlessly complex, particularly for a tutorial. I don't know why authors feel that you have to write bad code to make it understandable.

However, I think you can certainly simplify the code as well as your Spring example using raw JDBC and your own homemade transactional tool. I don't think database transactions are complicated enough to warrant losing the flexibility you have with writing custom code.

The Observer Pattern is also known as a publisher and subscriber design pattern. The pattern is useful when you have one publisher and many subscribers (one-to-many) that are interested in the publisher's state or messages. This article by Scott Priolo describes an easy process of implementing the observer pattern within the Spring framework, as well as an easy way to start the Spring Framework in any project.

Chris Webster and Srividhya Narayanan, both Sun engineers working on the Java Studio Enterprise Tools Organization, had a tech talk on things like asynchronous web services, security, orchestration, tools, and binding to services.
(Sep 1, Tech Talk)
--------------------------------------------------------------------------------

Ajax in Action excerpt: The Page as an Application
 The excerpt from Manning's "Ajax in Action" applies refactoring and patterns to the client-side codebase, showing how normal Javascript code can be refactored into a robust view component.
(Aug 26, Article)
--------------------------------------------------------------------------------

JavaServer Faces vs Tapestry - A Head-to-Head Comparison
 In this article, Phil Zoio puts these frameworks head-to-head, comparing each on its merits. He rates the two on critical aspects of their design, development and runtime environments.
(Aug 24, Article)
--------------------------------------------------------------------------------

Building Custom JSF UI Components
 In this article on building custom JSF UI components, Chris Schalk walks readers through creating a new JSF component - a "Hello, World" component at first, becoming a stock price component in the end.
(Aug 16, Article)
--------------------------------------------------------------------------------

Under the Hood of J2EE Clustering
 In this article, Wang Yu will take you on a tour of the technology inside J2EE clustering and let you know the features and limitations of some popular J2EE clustering products.
(Aug 12, Article)

I have a few comments on this subject:

1) Firstly, if your REALLY must use raw JDBC (and there
has to be a need for this these days, as pointed out in the
previous post regarding Spring's JDBC template design
pattern) then you should ALWAYS perform proper rollback,
commits and closures in your code. I don't just say this
and not clarify; consider the following reasons:
- courtesy to other developers in clearly defining
transactional boundaries when they look at your code
- effective freeing of database resources early by letting the
RDBMS know your intention to complete, either successfully
or not, each transaction

2) What does:


...
con.close();
...


realy mean? Well, assume nothing! You, or more likely your caller, has created a connection/transaction for you. If the
connection was pooled, what effect does closing or
not rolling back have? The answer usually means
your transaction is rolled back for you. When you close the connection it really just gets given back to the pool. Any sensible pooling mechanism has to reset a returned connection to its original state
(as would be returned by DriverManager.getConnection(...) ).
This would include the autoCommit connection
property too - which, by default, is true.

The pooling mechanism has to reset the connection so that
the next client to ask for pooled connection gets one
in its default state and not with a previous caller's half
peformed transaction hanging off it!


Hope this helps,

Gary
______________________________________________________

I dont understand the need of rollback statement in the last try catch loop. As we had already set the auto commit to false then even if some error occurs one satement is not going to get commited. Then why to use rollback.

For the same reason you should use Connection.close(), even though when the Connection object leaves scope and gets garbage collected, it will (should?) automatically close the connection: A transaction requires resources, both on the database and in your application. Since the transaction is held by the connection, and there is no guarantee of when the connection will get garbage collected (and therefore closed), there is by extension no guarantee of when your uncommitted transaction will get rolled back. As noted, the biggest danger in this is locked data in the database, which can quickly lead to deadlocks, especially under high-demand situations.

Incidentally, I usually like to make extra certain my connection is closed as well with a finally:

try {
&nbsp;&nbsp;&nbsp;connection = //get connection
&nbsp;&nbsp;&nbsp;//do some DB work here
&nbsp;&nbsp;&nbsp;connection.commit();
} catch (SQLException se) {
&nbsp;&nbsp;&nbsp;//error handling stuff
&nbsp;&nbsp;&nbsp;connection.rollback();
} finally {
&nbsp;&nbsp;&nbsp;try {
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;connection.close();
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;connection = null;
&nbsp;&nbsp;&nbsp;} catch (Throwable t) {
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;//log error if desired, else do nothing
&nbsp;&nbsp;&nbsp;}
}

Heh... that didn't work:

try {
   connection = //get connection
   //do some DB work here
   connection.commit();
} catch (SQLException se) {
   //error handling stuff
   connection.rollback();
} finally {
   try {
      connection.close();
      connection = null;
   } catch (Throwable t) {
      //log error if desired, else do nothing
   }
}

finally {
&nbsp;&nbsp;&nbsp;try {
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;connection.close();
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;connection = null;
&nbsp;&nbsp;&nbsp;} catch (Throwable t) {
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;//log error if desired, else do nothing
&nbsp;&nbsp;&nbsp;}
}

Maybe the (perceived) need to null the reference to connection implies your scope is too broad. If you get the connection as a method parameter, there is no need to null it.

.. but what does setAutocommit do? It just prevents from issuing commit automatically after each statement. Nothing more! So this setting leaves (at least) the last statement uncommited. Does it affect closing connection? Obviously not. In many systems closing connection by default commits all pending changes, to prevent data loss and ... because it's far easier than rolling back :-)

In many systems closing connection by default commits all pending changes, to prevent data loss and ... because it's far easier than rolling back :-)

Any examples? That doesn't seem like the sane thing to do.

In many systems closing connection by default commits all pending changes, to prevent data loss and ... because it's far easier than rolling back :-)

Any examples? That doesn't seem like the sane thing to do.

Just delete some data using sql*plus (autocommit off by default) and type disconnect :-)

In many systems closing connection by default commits all pending changes, to prevent data loss and ... because it's far easier than rolling back :-)

Any examples? That doesn't seem like the sane thing to do.

Just delete some data using sql*plus (autocommit off by default) and type disconnect :-)

Like, that's irrelevant. SQL*Plus is a program, it does whatever it pleases, just like a database library/framework/whatever that issues rollback or commit or nothing when you connection::close.
Oracle DB's behaviour _is not_ SQL*Plus' behaviour.
Oracle documentation explicitly states that open transactions on closed connections will be rolled back. I'm quite sure that PostgreSQL does the same.
So, again, any examples of databases that actually choose to commit "abandoned" transactions?

Sql*plus, if you don't know, is a kind of etalon pogram to interact with Oracle. But if you think your connection::close works cleaner then just [re]read Oracle manuals that explicitly states:


If auto-commit mode is disabled and you close the connection without explicitly committing or rolling back your last changes, then an implicit COMMIT operation is executed.

http://download-west.oracle.com/docs/cd/B10501_01/java.920/a96654/basic.htm#1019059

Could you post any evidence in favour of your own varuiant of Oracle explicit statement?

Sql*plus, if you don't know, is a kind of etalon pogram to interact with Oracle. But if you think your connection::close works cleaner then just [re]read Oracle manuals that explicitly states:If auto-commit mode is disabled and you close the connection without explicitly committing or rolling back your last changes, then an implicit COMMIT operation is executed.http://download-west.oracle.com/docs/cd/B10501_01/java.920/a96654/basic.htm#1019059Could you post any evidence in favour of your own varuiant of Oracle explicit statement?

Dmytro, whatever makes you think I don't know what SQL*Plus is? And what exactly is "my" connection:close? It was a concept, I can tell you as much, not a speciffic connection, for instance a jdbc one, or a hibernate one, or an odbc one. Nevermind, people tend to become aggressive when contradicted.

At any rate. Oracle does roll back any uncommited changes if the connection is aborted. It's the only sane thing to do, if you think about it; anything else violates atomicity.
A transaction means 'I want this, this and that to be done, all or nothing' - correct? Now if you issue say 2 out of 3 things to be done, and then the connection is closed, and you never got to send in the 3rd, then if Oracle commits you have just been f*cked royally since what was commited was _not_ your atomic change, but part of it. Just as if there had been no transaction in the first place.

Now, what you're quoting there applies to a particular connection method to the database, i.e. JDBC.
If you check the Pro*C/C++ manual, you'd get this quote(from http://download-west.oracle.com/docs/cd/B10501_01/appdev920/a97269/pc_03dbc.htm):

Oracle is transaction oriented. That is, Oracle uses transactions to ensure data integrity. A transaction is a series of one or more logically related SQL statements you define to accomplish some task. Oracle treats the series of SQL statements as a unit so that all the changes brought about by the statements are either committed (made permanent) or rolled back (undone) at the same time. If your application program fails in the middle of a transaction, the database is automatically restored to its former (pre-transaction) state.

Pretty much what I said above, in their own words.
And then some more:

You end a transaction in one of the following ways:
Code a COMMIT or ROLLBACK statement, with or without the RELEASE option. This explicitly makes permanent or undoes changes to the database.
Code a data definition statement (ALTER, CREATE, or GRANT, for example), which issues an automatic COMMIT before and after executing. This implicitly makes permanent changes to the database.

A transaction also ends when there is a system failure or your user session stops unexpectedly because of software problems, hardware problems, or a forced interrupt. Oracle rolls back the transaction.

If your program fails in the middle of a transaction, Oracle detects the error and rolls back the transaction. If your operating system fails, Oracle restores the database to its former (pre-transaction) state.

So, the fact that the jdbc driver issues a commit if you close the connection simply means that the your open transaction is being commited by someone, in this case not explicitly by your code, but by the jdbc driver code - in the end there's really no difference. Why they chose to **** things up like this I cannot say for the life of me, it's an idiotic thing to do that basically invalidates all of the sound principles above. The database system however behaves properly in that it respects the atomicity principle.
If your java program aborts with runtime exception and you don't have hooks for connection pool cleanup, or you write a php script that just inserts data in the same table in a transaction and commits at the end and you interrupt it beforehand, if you kill your SQL*Plus, Oracle rolls back.

And for desert, here's a link from the SQL*Plus manual stating that SQL*Plus (not Oracle) commits as it exits, because that's how they like it to be:
(http://download-west.oracle.com/docs/cd/B14117_01/server.101/b12170/ch5.htm#sthref871):

Regardless of the AUTOCOMMIT setting, changes are committed when you exit SQL*Plus successfully.

Note the "successfully" bit. Kill it, and it's gonna get all rolled back.
As Tom Kyte said, "rolling back is always safe (as if never happened)". There are however batch processing scenarios where certain types of errors, such as disk space shortage, will not rollback but give you a chance to clean up and continue the transaction, but this is a different conversation altogether.

Too many words but again irrelevant. According to that quote Oracle aborts uncommited transactions in case of failure (like loosing control from client application), not in case of clean connection::close called by that external program.

Again:
<quote>

If auto-commit mode is disabled (this is the case) and you close the connection without explicitly committing or rolling back your last changes (this is also the case), then an implicit COMMIT operation is executed.

</quote>

This is not about sql*plus or any other program, this is about jdbc driver:
Oracle9i JDBC Developer's Guide and Reference/Basic Features/First Steps in JDBC/Commit Changes. Wasn't the original post about the need in rolling back transaction before closing connection using jdbc driver? So the answer is YES, it's necessary, at least when you work with Oracle.

It's off-topic, but if you wrote at least 1 program with Pro*C you should know that there's no such concept as connection::close in Pro*C and you should explicitly specify whether you need to COMMIT WORK RELEASE or ROLLBACK RELEASE. Thus I may definitely state that your ideas are quite intelligent but unfortunately have nothing to do with the reality.

P.S.
Yes, some people tend to accuse their opponents of aggresiveness when they realized that their concept was completely wrong. Then, they provide irrelevant references to honourable persons. And finally they start blaming f..cking jdbc driver developers, f..cking sql*plus developers, f..cking odbc driver developers etc (God only knows how Hibernate relates to this company).

Dmytro, you don't seem to understand what I'm talking about here.
I was interested what databases (and by database I mean database systems, not client programs or libraries), if any, choose to commit _themselves_ (again, not the client program or library, when it detects that a connection is closed) open transactions.
(As a side note, a connection API that does not commit the connection when you close it cleanly is the OCI8 in PHP).
Anyway this is really not worth pursuing further, I particulary have no taste for your tone, your obsession with the connection::close thing, and terribly bad interpretation of one's statements and intentions (I am not blaiming anyone - merely claiming, pissed off, that choosing to issue a commit when closing a connection in the jdbc driver is idiotic; also whatever I said in the previous post not only is correct but has everything to do with reality and the question I asked).
If you do care to pursue this conversation further, may I suggest that you try to answer the question I asked, in the following and clearer formulation: is there any database that chooses to commit an open transaction when the session's connection is closed, either by an IO error or by clean disconnect? I know Oracle does the right thing and does not commit, but rolls back. What's the situation with other databases ? (and yes, this is entirely off-topic, so feel free to ignore it)

Also, I don't think of you as an opponent, but a conversation partner.
And where did you come up with the blaming part again? In order to blame someone you firstly must have done something wrong or been in a position where something went wrong, and I'm not in such a position to blame anyone for anything, but instead state that from my point of view, choosing to commit an open connection on close is an idiotic thing to do for a database connection library, and I'm not even toning that down. SQL*Plus I believe does the right thing, because the scenario is probably along the lines of an operator performing some work on the database and when done closes the program, only to find one's self pulling hair forcefully because one forgot to issue a commit before quit. So I would guess that this is a correct decision from a human perspective.

Now what if you typed a wrong command and quit SQL*Plus ?

As we can see from this discussion, it's not clear that a close without a commit will rollback. If it's not in the javadoc, you can't make the assumption since it's the interface that you're really coding against. So I assume you do it explicitly so as to avoid any confusion. It's just a few extra lines of code and if you put it in a Builder utility, you only need to do it once. It may be unnecessary, but it doesn't hurt - sorta like formatting your code. It makes your code clearer and avoids any concerns about your database transactions.

Yeah this is pretty shocking up here.

Check out these sites:

idontgettransactions.com
idontunderstandprogramming.com

And MOST importantly

iwanttobeanindustryragbutcantactuallykeepthiscrapoffmysite.com