CODEWITHSUNDEEP
javajdbc
mmo2112
mmo2112

Reputation: 15

When shall use setAutoCommit(true) in Java

I've searched about the difference between getConnection().commit() (1) vs getConnection().setAutoCommit(true) (2). But most of case just describe that (1) is default method and rarely to use it instead of (2) or both method are "different purposes" and same result. I use the example on Oracle docs (https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html#commit_transactions):

 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);
        }
    }
}

At this example I don't know the purpose of con.setAutoCommit(true) (1) at finally block because every task have already been committed from try block though it may occur an exception or not. May anyone give me a clearly explanation when shall use (1)? Thanks for help so much.

If the default method is true, so I just simply to do a commit() instead of (1) for ending a transaction block and the next block method (no need to manage the transaction for this block) will be become to default mode (1) and I don't need to set it to true again?

I have a hypothesis that con.commit() is just commit the statement and it still locks some row/table before (1) will be called again. I think I'm misunderstanding the commit() will automatically set the currently default become to true instead of trying to call (1) again because I've test some code, after calling commit() all locks from row/table will be released all, so my hypothesis is wrong too.

Upvotes: 0

Views: 1122

Answers (1)

Stephen C
Stephen C

Reputation: 718986

In the example you showed us, the code turns off autocommit and then turns it on at the end. While autocommit is off, it repeatedly executes two updates at a time with a commit after each pair of updates.

In the example, the purpose for turning autocommit off at the end should be clear. The programmer (presumably!) wants to execute pairs of updates with a commit between them because the pair of updates need to be done atomically. If you don't turn off autocommit, you can't do this.

The purpose of turning autocommit back on in the finally block is to return it to its default state. This presumably is done to allow the connection to be used again. However, it is debatable that this is necessary:

  • It costs almost nothing to turn autocommit off or on.
  • There is no harm in calling setAutoCommit(false) if the connection already has autocommit turned off.

To my mind, the consequences of the above are that there are two sensible ways to deal with this:

  • If the entire application needs explicit transactions, turn off autocommit when you establish the connection .... and leave it off.

  • If different parts of the application have different autocommit requirement, then the simplest approach is for the code managing each transaction to set autocommit to what it needs, then not bother resetting it.

The 5 or so lines of extra code to restore the default autocommit state in a finally block don't strike me as necessary. Indeed, unless you can ensure that the autocommit state is always restored, you are better of assuming that the state is uncertain; e.g. at the start of the updateCoffeeSales method ... or at the start of some other method that wants autocommit enabled.

Upvotes: 3

Related Questions