JDBC Transaction Management

Why we need Transaction Management?

Consider Bob needs to transfer 100$ to Alice account through bank X. Bank debit the money from Bob’s account and updates his remaining balance. Then the bank deposits that money into Alice account. If there are no intermediate errors while depositing money then its happy path. Think, While depositing the money there is a connection/intermediate error occurs. Then the amount does not add into Alice account. But Bob thinks that he has transferred the money to Alice but Alice does not get the money. Then it will be big trouble for the bank. To overcome these kinds of issues we need proper transaction management to manage the processes.

Let's consider the same process with transaction management. This time Bank starts the transaction. As a first step bank debit the money from bob account but does not update his account balance. Then the bank system deposit that money into Alice’s account. If there are no intermediate errors then the bank will update both accounts with the new balances at the end of this transaction. If some intermediate errors occur then the bank safely reverts the process and it does not affect the transaction.

Now we will go through how this process works in JDBC and some important topics about Transaction Management.

auto-commit mode

When we start the connection the connection will start with auto-commit mode. Auto-commit is the one starts the transaction in the SQL. By Default the auto-commit is true. We can change the value of auto-commit according to our needs.

set auto-commit as true: When the auto-commit state is true then each SQL statements considers as one single transaction. The SQL statements automatically commit after each SQL statement has executed.

set auto-commit as false: When the auto-commit state is false then multiple SQL statements process within one single transaction. The multiple SQL statements get committed after executing all the SQL statements.

We can divide the entire transaction process into two main parts,

1. Start the Transaction
2. End the Transaction

1. Start the Transaction

As we mention before transaction start when setting the AutoCommit. Mostly we use default AutoCommit to start the single SQL statements.

If we are dealing with a group of multiple SQL statements then we enable a manual- transaction to increase the performance and maintain the integrity of business processes.

connection.setAutoCommit(false);

2. End the transaction

When we use default AutoCommit to start the single SQL statements. It will automatically commit the transaction. So we don't want to take this as a serious issue.

When we enable a manual- transaction,

We should call the commit() method to commit the changes into the database. This method commits all the SQL statements together as a unit.

connection.commit();

If some intermediate error/ connection error occurs or if we want to revert the queries executed by the specific connection, we can revert the queries using rollback() method. There are two ways to revert the updates done by the SQL connection,

1. Rollback connection: If something goes wrong before committing or if we want to revert all the queries executed by the specific connection we can rollback the updates using rollback() method.

connection.rollback();

2. Rollback to savepoint: If you want to revert the updates until certain points we can revert using savepoint.

For that, we need to initialize savepoints until where we want to undo. We can achieve this by calling setSavepoint() method.

Savepoint savepoint = connection.setSavepoint();

Then if something went wrong after savepoint we can rollback the transaction until the place we put savepoint by calling rollback method with the savepoint name.

connection.rollback(savepoint);

Sample code

try{
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   String SQL = "INSERT INTO Employees  " +
                "VALUES (162, 131, 'Alice', 'Bob')";
   x=1;
   stmt.executeUpdate(SQL);  
   Savepoint savepoint = conn.setSavepoint();
   String SQL = "INSERTED IN Employees  " +
                "VALUES (17, 223, 'Ram', 'Raja')";
   x=2;
   stmt.executeUpdate(SQL);
   conn.commit();
}catch(SQLException se){
   if(x==2) {
      conn.rollback();
   } else {
      conn.rollback(savepoint);
   }
}

Common mistakes developers do while working with Transaction Management

• When we work with a manual- transaction we should commit or rollback the transaction at the end. If we missed committing then it is not updated into the database.

try{
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   
   String SQL = "INSERT INTO Employees  " +
                "VALUES (106, 20, 'Rita', 'Tez')";
   stmt.executeUpdate(SQL);  
   String SQL = "INSERTED IN Employees  " +
                "VALUES (107, 22, 'Sita', 'Singh')";
   stmt.executeUpdate(SQL);
}

• We can not add two or more commits within one transaction.

try{
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   
   String SQL = "INSERT INTO Employees  " +
                "VALUES (106, 20, 'Rita', 'Tez')";
   stmt.executeUpdate(SQL);  
   conn.commit();
   String SQL = "INSERTED IN Employees  " +
                "VALUES (107, 22, 'Sita', 'Singh')";
   stmt.executeUpdate(SQL);
   conn.commit();
}catch(SQLException se){
   conn.rollback();
}

• We can not add commit and rollback at once because it is useless.

try{
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   
   String SQL = "INSERT INTO Employees  " +
                "VALUES (106, 20, 'Rita', 'Tez')";
   stmt.executeUpdate(SQL);  
   String SQL = "INSERTED IN Employees  " +
                "VALUES (107, 22, 'Sita', 'Singh')";
   stmt.executeUpdate(SQL);
   conn.commit();
   conn.rollback();
}

****************************** END *******************************