CODEWITHSUNDEEP
javajdbc
user291701
user291701

Reputation: 39681

When do we need to call java.sql.Connection.rollback()?

I have to modify a few tables in one function. They must all succeed, or all fail. If one operation fails, I want them all to fail. I have the following:

public void foo() throws Exception {
    Connection conn = null;
    try {
        conn = ...;
        conn.setAutoCommit(false);
        grok(conn);
        conn.commit();
    }
    catch (Exception ex) {
        // do I need to call conn.rollback() here?
    }
    finally {
        if (conn != null) {
            conn.close();
            conn = null;
        }
    }
}

private void grok(Connection conn) throws Exception {
    PreparedStatement stmt = null;
    try {
        // modify table "apple" 
        stmt = conn.prepareStatement(...);
        stmt.executeUpdate();
        stmt.close();

        // modify table "orange"
        stmt = conn.prepareStatement(...);
        stmt.executeUpdate();
        stmt.close();

        ...
    }
    finally {
        if (stmt != null) {
            stmt.close();
        }
    }
}

I'm wondering if I need to call rollback() in the case that something goes wrong during this process.

Other info: I'm using connection pooling. In the sample above, I'm also making sure to close each PreparedStatement using finally statements as well, just left out for brevity.

Thank you

Upvotes: 1

Views: 7759

Answers (3)

Usman Mutawakil
Usman Mutawakil

Reputation: 5259

Yes you need to call rollback if any of your statements fails or you have detected an exception prior to calling commit. This is an old post but the accepted answer is wrong. You can try it for yourself by throwing an exception before commit and observing that your inserts still make it into the database if you do not manually rollback.

JDBC Documentation https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html#call_rollback

Example Correct Usage from the doc

public void updateCoffeeSales(HashMap<String, Integer> salesForWeek)
    throws SQLException {

    PreparedStatement updateSales = null;
    PreparedStatement updateTotal = null;

    String updateString =
        "update " + dbName + ".COFFEES " +
        "set SALES = ? where COF_NAME = ?";

    String updateStatement =
        "update " + dbName + ".COFFEES " +
        "set TOTAL = TOTAL + ? " +
        "where COF_NAME = ?";

    try {
        con.setAutoCommit(false);
        updateSales = con.prepareStatement(updateString);
        updateTotal = con.prepareStatement(updateStatement);

        for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
            updateSales.setInt(1, e.getValue().intValue());
            updateSales.setString(2, e.getKey());
            updateSales.executeUpdate();
            updateTotal.setInt(1, e.getValue().intValue());
            updateTotal.setString(2, e.getKey());
            updateTotal.executeUpdate();
            con.commit();
        }
    } catch (SQLException e ) {
        JDBCTutorialUtilities.printSQLException(e);
        if (con != null) {
            try {
                System.err.print("Transaction is being rolled back");
                con.rollback();
            } catch(SQLException excep) {
                JDBCTutorialUtilities.printSQLException(excep);
            }
        }
    } finally {
        if (updateSales != null) {
            updateSales.close();
        }
        if (updateTotal != null) {
            updateTotal.close();
        }
        con.setAutoCommit(true);
    }
}

Upvotes: 2

DavidB
DavidB

Reputation: 2234

If you call "commit" then the transaction will be committed. If you have multiple insert/update statements and one of them fails, committing will cause the inserts/updates that didn't fail to commit to the database. So yes, if you don't want the other statements to commit to the db, you need to call rollback. What you are essentially doing by setting autocommit to false is allowing multiple statements to be committed or rolledback together. Otherwise each individual statement will automatically commit.

Upvotes: 0

Miserable Variable
Miserable Variable

Reputation: 28752

You don't need to call rollback(). If the connection closes without completing commit() it will be rolled back.

You don't need to set conn to null either; and since the try block starts after conn is initialized (assuming ... cannot evaluate to null) you don't need the != null in finally either.

Upvotes: 0

Related Questions