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

Its hard to make out what you are asking. If a commit succeeds then its done, complete, you can't roll it back at that point. You have to mark a transaction for rollback ***before*** calling the `commit` method. — Perception, Feb 22 '13 at 19:40

score 14 · Accepted Answer · answered Feb 22 '13 at 19:46

14

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.

answered Feb 22 '13 at 19:46

jtahlborn

52,909
5
76
118

Vishal K · Answer 2 · 2013-02-22T20:05:39.780

6

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.

edited Feb 22 '13 at 20:05

answered Feb 22 '13 at 19:47

Vishal K

12,976
2
27
38

1

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. – Mark Rotteveel Feb 23 '13 at 07:23
@MarkRotteveel: So how is it different from what I mentioned in my post? – Vishal K Feb 23 '13 at 12:01
1

The difference is that you can have one or more Savepoints inside an active transaction. – Mark Rotteveel Feb 23 '13 at 12:52
@MarkRotteveel: I have nowhere mentioned that there can't be more than one Savepoints inside active transaction.. – Vishal K Feb 23 '13 at 12:54
1

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? – jmrodrigg Apr 26 '13 at 12:52
4

@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 – Vishal K Apr 27 '13 at 22:37
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! – jmrodrigg Apr 29 '13 at 09:03

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

2 Answers2