JDBC: Does call to rollback() method have effect only if call to commit() method does not succeed?

Question

I am new to Java JDBC, and developed a small database application. I am learning from O'Reilly - Database Programming with JDBC and Java 2nd Edition.

Does con.rollback() have effect only ifcon.commit does not succeed?

I expected that calling con.rollback() has its effect even if con.commit() succeeded. In other words, utilizing it as an "Undo" action.

I tried calling con.rollback() after con.commit() succeeded, but it is not working as expected. So is it alright/expected?

This example is from the book I mentioned above:

The call to con.rollback() is commented out. It is near the end before con.close(). I tried uncommenting it and running it. However, con.rollback() doesn't roll things back after con.commit() succeeded.

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class UpdateLogic
{

    public static void main(String args[])
    {
        Connection con = null;

        try
        {
            String driver = "com.mysql.jdbc.Driver";
            Class.forName(driver).newInstance();
            String url = "jdbc:mysql://localhost:3306/Company";
            Statement s;
            con = DriverManager.getConnection(url, "root", "");
            con.setAutoCommit(false); // make sure auto commit is off!
            s = con.createStatement();// create the first statement
            s.executeUpdate("INSERT INTO employee VALUES ('1', 'employee 1', '22','00-1234' )");

            s.close(); // close the first statement
            s = con.createStatement(); // create the second statement
            s.executeUpdate("INSERT INTO employee VALUES ('2', 'employee 2', '21','00_4321' )");

            con.commit(); // commit the two statements
            System.out.println("Insert succeeded.");
            s.close(); // close the second statement
        } catch (ClassNotFoundException | InstantiationException | IllegalAccessException ex)
        {
            Logger.getLogger(UpdateLogic.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException e)
        {
            if (con != null)
            {
                try
                {
                    con.rollback();
                } // rollback on error
                catch (SQLException i)
                {
                }
            }
            e.printStackTrace();
        } finally
        {
            if (con != null)
            {
                try
                {
                  //con.rollback();
                    con.close();
                } catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }
        }
    }
}

Solution 1

When you call commit(), you complete/close the current transaction. Thus, since rollback() undoes any changes in the current transaction (as per the javadoc), it will effectively do nothing.

Solution 2

Is con.rollback() has effect only if con.commit not succeeded?

YES And It also has effect if you call it before con.commit . And prerequisite is that autocommit mode of connection should be false using con.setAutoCommit(false)
Any transaction that you make in database using DML SQL queries using JDBC with con.setAutoCommit(false) is not commited to database until con.commit() is called. The latest commited transaction that you make in database acts as the savepoint for that connection. When you call con.rollback() all transaction that you have done after that savepoint is undone. Also if some exception occurs while calling con.commit(), it means that transactions are not saved in database. It is a good practice to call con.rollback() in catch statement if con.commit() fails.

Comments

[Mark Rotteveel]

Savepoints are a different but related concept: Savepoints are inside a transaction and allow you to mark points in a transaction that you can rollback to without redoing the whole transaction.

[Vishal K]

@MarkRotteveel: So how is it different from what I mentioned in my post?

[Mark Rotteveel]

The difference is that you can have one or more Savepoints inside an active transaction.

[Vishal K]

@MarkRotteveel: I have nowhere mentioned that there can't be more than one Savepoints inside active transaction..

[jmrodrigg]

Hi, you said "It is a good practice to call con.rollback() in catch statement if con.commit() fails". Is it strictly necessary? What happens if you don't do it?

[Vishal K]

@jmrodrigg: As I have mentioned in my post that con.rollback() and con.commit() shows its effect only for false mode of autocommit . Now If you are performing a set of transactions atomically and you get some error while n(>1)th transaction is performing. This will cause the control to execute catch statement. In case you don't call con.rollback then If at other part of your code a successful transaction is commited for the same Connection object then this will cause those half successful transactions of the unsuccessful atomic operation to commit also which will cause loss of Data integrity

[jmrodrigg]

Ok, thanks for the explanation. Although it's not my scenario (I create the connection Object for each transaction, when I need it), it's good to know how it really works. thanks!