Enthuware

  Connection con = DriverManager.getConnection(dbURL);

  //code for enabling transactions

  String updateString =
        "update SALES set T_AMOUNT = 100 where T_NAME = 'BOB'";
  Statement stmt = con.createStatement();
  stmt.executeUpdate(updateString);
  stmt.executeUpdate("update ACCOUNTS set STATUS=1");

  con.commit();  //REPLACE THIS LINE OF CODE

//code for enabling transactions

Now, in this question, it is not clear from the code whether the auto-commit has been set to true or false.

Case 1: If auto-commit was set to true. In this case, every statement is committed after it completes. Therefore, the call to commit() is not actually required and so it can be replaced with setAutoCommit(true) or even setAutoCommit(false) without any impact.

  setAutoCommit(true) // <========== (1)

  String updateString =
        "update SALES set T_AMOUNT = 100 where T_NAME = 'BOB'";
  Statement stmt = con.createStatement();
  stmt.executeUpdate(updateString);
  stmt.executeUpdate("update ACCOUNTS set STATUS=1");

  setAutoCommit(false) // <========== (2)

Case 2: If auto-commit was set to false. In this case, it is necessary to commit the transaction explicitly. This can be done either by calling con.commit() as shown in the code or by changing the auto-commit mode from false to true. This utilizes the side effect of changing the auto-commit mode of the connection. If the setAutoCommit method is called during a transaction and the auto-commit mode is changed, the transaction is committed. If setAutoCommit is called and the auto-commit mode is not changed, the call is a no-op.

  setAutoCommit(false) // <========== (1)

  String updateString =
        "update SALES set T_AMOUNT = 100 where T_NAME = 'BOB'";
  Statement stmt = con.createStatement();
  stmt.executeUpdate(updateString);
  stmt.executeUpdate("update ACCOUNTS set STATUS=1");

  setAutoCommit(true) // <========== (2)

schchen2000 wrote:

Code: Select all

  Connection con = DriverManager.getConnection(dbURL);

  //code for enabling transactions

  String updateString =
        "update SALES set T_AMOUNT = 100 where T_NAME = 'BOB'";
  Statement stmt = con.createStatement();
  stmt.executeUpdate(updateString);
  stmt.executeUpdate("update ACCOUNTS set STATUS=1");

  con.commit();  //REPLACE THIS LINE OF CODE

In that code above,

//code for enabling transactions

is meant to be replaced by either con.setAutoCommit(false); or con.setAutoCommit(true);

We are including either con.setAutoCommit(false); or con.setAutoCommit(true); here for the sake of this question. Even without either one of them being included here specifically, con.setAutoCommit(true); is set by default in Java for us.

Is that correct?

Question 1
---------------

Now, in this question, it is not clear from the code whether the auto-commit has been set to true or false.

Case 1: If auto-commit was set to true. In this case, every statement is committed after it completes. Therefore, the call to commit() is not actually required and so it can be replaced with setAutoCommit(true) or even setAutoCommit(false) without any impact.

In the quote immediately above this question, you said "even setAutoCommit(false) without any impact." Why without any impact? Is that because of the following order of reasoning?

The line indicated by (1) below is followed by

a lot of execute statements and all of them will be committed due to the auto commit being true in the line indicated by (1).

By the time we get to the line indicated by (2) below, all the commits have been made and thus the auto commit being false in (2) won't make any impact because all the work has be done at this point.

Is that correct?

Code: Select all

  setAutoCommit(true) // <========== (1)

  String updateString =
        "update SALES set T_AMOUNT = 100 where T_NAME = 'BOB'";
  Statement stmt = con.createStatement();
  stmt.executeUpdate(updateString);
  stmt.executeUpdate("update ACCOUNTS set STATUS=1");

  setAutoCommit(false) // <========== (2)

schchen2000 wrote:Question 2
---------------

Case 2: If auto-commit was set to false. In this case, it is necessary to commit the transaction explicitly. This can be done either by calling con.commit() as shown in the code or by changing the auto-commit mode from false to true. This utilizes the side effect of changing the auto-commit mode of the connection. If the setAutoCommit method is called during a transaction and the auto-commit mode is changed, the transaction is committed. If setAutoCommit is called and the auto-commit mode is not changed, the call is a no-op.

Due to the auto commit being false in line marked by (1) below, all the execute statements are "queued up" for commit. While those in the queue that are waiting to be committed, we can still do roll back at this stage.

Is that correct?

Once they are committed by auto commit being true as indicated by line (2) below, what would then happen if we do the roll back after a commit, i.e. nothing left in the queue after the commit?

Code: Select all

  setAutoCommit(false) // <========== (1)

  String updateString =
        "update SALES set T_AMOUNT = 100 where T_NAME = 'BOB'";
  Statement stmt = con.createStatement();
  stmt.executeUpdate(updateString);
  stmt.executeUpdate("update ACCOUNTS set STATUS=1");

  setAutoCommit(true) // <========== (2)

Many thanks.

Schmichael

admin wrote:If you call commit if the connection is in auto-commit mode, then you will get an exception: https://docs.oracle.com/javase/7/docs/a ... tml#commit()

admin wrote:Same with rollback. If the connection is in auto-commit mode, you will get an exception. If not, nothing will happen. Just check the JavaDoc description of rollback method https://docs.oracle.com/javase/7/docs/a ... l#rollback().

admin wrote:No, there is no exception if you call rollback after a commit.
BTW, given that you've already called commit successfully, that means the connection is not in auto-commit mode.
I would again suggest you to go through the JavaDoc link that I gave above.
-Paul.

If the setAutoCommit method is called during a transaction and the auto-commit mode is changed, the transaction is committed. If setAutoCommit is called and the auto-commit mode is not changed, the call is a no-op.