Recent Posts

Friday, 16 November 2018

Transaction Management in JDBC

     Process of combining all related operations into a single unit and executing on the rule "either all or none", is called transaction management. Hence transaction is a single unit of work and it will work on the rule "either all or none".

Case 1 : Funds Transfer 
1. debit funds from sender's account
2. credit funds into receiver's account
     All operations should be performed as a single unit only. If debit from sender's account completed and credit into receiver's account fails then there may be a chance of data inconsistency problems.

Case 2 : Movie Ticket Reservation 
1. Verify the status
2. Reserve the tickets
3. Payment
4. issue tickets.
     All operations should be performed as a single unit only. If some operations success and some operations fails then there may be data inconsistency problems.

Transaction Properties
     Every Transaction should follow the following four ACID properties.
1. A ➔ Atomiticity
     Either all operations should be done or None.
2. C ➔ Consistency (Reliabile Data) 
     It ensures bringing database from one consistent state to another consistent state.
3. I ➔ isolation (Sepatation)
     Ensures that transaction is isolated from other transactions
4. D ➔ Durability
     It means once transaction committed, then the results are permanent even in the case of system restarts, errors etc.

Example
create table accounts(name varchar2(10),balance number);

insert into accounts values('Ashok',36500);
insert into accounts values('Vinod',120000);
import java.sql.*; 
import java.util.*; 
/**
 * 
 * @author ashok.mariyala
 *
 */
public class TransactionTest {
   public static void main(String[] args) throws Exception { 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      Statement st = con.createStatement(); 
      System.out.println("Data before Transaction"); 
      System.out.println("-------------------------"); 
      ResultSet rs =st.executeQuery("select * from accounts"); 
      while(rs.next()) {
         System.out.println(rs.getString(1)+"..."+rs.getInt(2)); 
      } 
      System.out.println("Transaction begins..."); 
      con.setAutoCommit(false); 
      st.executeUpdate("update accounts set balance=balance-10000 where name='Ashok'"); 
      st.executeUpdate("update accounts set balance=balance+10000 where name='Vinod'"); 
      System.out.println("Can you please confirm this transaction of 10000....[Yes|No]");
      Scanner sc = new Scanner(System.in); 
      String option = sc.next(); 
      if(option.equalsIgnoreCase("yes")) { 
         con.commit(); 
         System.out.println("Transaction Commited"); 
      } else { 
         con.rollback(); 
         System.out.println("Transaction Rolled Back"); 
      }
      System.out.println("Data After Transaction"); 
      System.out.println("-------------------------"); 
      ResultSet rs1 =st.executeQuery("select * from accounts");
      while(rs1.next()) { 
         System.out.println(rs1.getString(1)+"..."+rs1.getInt(2)); 
      } 
      con.close(); 
   } 
}
Savepoint (I)
     Savepoint is an interface present in java.sql package. Introduced in JDBc 3.0 Version. Driver Software Vendor is responsible to provide implementation. Savepoint concept is applicable only in Transactions.
     Within a transaction if we want to rollback a particular group of operations based on some condition then we should go for Savepoint. We can set Savepoint by using setSavepoint() method of Connection interface.
Savepoint sp = con.setSavepoint();

     To perform rollback operation for a particular group of operations wrt Savepoint, we can use rollback() method as follows.
con.rollback(sp);
     We can release or delete Savepoint by using release Savepoint() method of Connection interface. con.releaseSavepoint(sp);
Example
con.setAutoCommit(false); 
Operation-1; 
Operation-2; 
Operation-3; 
Savepoint sp = con.setSavepoint(); 
Operation-4; 
Operation-5; 
if(balance<10000) { 
   con.rollback(sp); 
} else { 
  con.releaseSavepoint(sp);
}
operation-6; 
con.commit();
     If balance <10000 then operations 4 and 5 will be Rollback, otherwise all operations will be performed normally.

Note
1. Some drivers won't provide support for Savepoint. Type-1 Driver won't provide support, but Type-4 Driver can provide support.

2. Type-4 Driver of Oracle provide support only for setSavepoint() and rollback() methods but not for releaseSavepoint() method.

Transaction Concurrency Problems
     Whenever multiple transactions are executing concurrently then there may be a chance of transaction concurrency problems. The following are the most commonly occurred concurrency problems.

1. Dirty Read Problem
2. Non Repeatable Read Problem
3. Phantom Read Problem

1. Dirty Read Problem
     Also known as uncommitted dependency problem. Before committing the transaction, if its intermediate results used by any other transaction then there may be a chance of Data inconsistency problems. This is called Dirty Read Problem.
T1:update accounts set balance=balance+50000 where name='Ashok'
T2:select balance from accounts where name='Ashok'
T1: con.rollback();
     At the end, T1 point of view, Ashok has 50000 balance and T2 point of view Ashok has 1Lakh. There may be a chance of data inconsistency problem. This is called Dirty Read Problem.

2. Non-Repeatable Read Problem
     For the same Read Operation, in the same transaction if we get different results at different times, then such type of problem is called Non-Repeatable Read Problem.
E.g:
T1: select * from employees;
T2: update employees set esal=10000 where ename='Ashok';
T1: select * from employees;
     In the above example Transaction-1 got different results at different times for the same query.

3. Phantom Read Problem
     A phantom read occurs when one transaction reads all the rows that satisfy a where condition and second transaction insert a new row that satisfy same where condition. If the first transaction reads for the same condition in the result an additional row will come. This row is called phantom row and this problem is called phantom read problem.
E.g
T1: select * from employees where esal >5000;
T2: insert into employees values(300,'ravi',8000,'hyd');
T1: select * from employees where esal >5000;
     In the above code whenever transaction-1 performing read operation second time, a new row will come in the result. To overcome these problems we should go for Transaction isolation levels. Connection interface defines the following 4 transaction isolation levels.
1. TRANSACTION_READ_UNCOMMITTED ➔ 1
2. TRANSACTION_READ_COMMITTED ➔ 2
3. TRANSACTION_REPEATABLE_READ ➔ 4
4. TRANSACTION_SERIALIZABLE ➔ 8

1. TRANSACTION_READ_UNCOMMITTED
☀ It is the lowest level of isolation.
☀ Before committing the transaction its intermediate results can be used by other transactions.
☀ Internally it won't use any locks.
☀ It does not prevent Dirty Read Problem, Non-Repeatable Read Problem and Phantom Read Problem.
☀ We can use this isolation level just to indicate database supports transactions.
☀ This isolation level is not recommended to use.

2. TRANSACTION_READ_COMMITTED
☀ This isolation level ensures that only committed data can be read by other transactions.
☀ It prevents Dirty Read Problem. But there may be a chance of Non Repeatable Read Problem and Phantom Read Problem.

3. TRANSACTION_REPEATABLE_READ
☀ This is the default value for most of the databases. Internally the result of SQL Query will be locked for only one transaction. If we perform multiple read operations, then there is a guarantee that for same result.
☀ It prevents Dirty Read Problem and Non Repeatable Read Problems. But still there may be a chance of Phantom Read Problem.

4. TRANSACTION_SERIALIZABLE
☀ It is the highest level of isolation.
☀ The total table will be locked for one transaction at a time.
☀ It prevents Dirty Read, Non-Repeatable Read and Phantom Read Problems.
☀ It is not Recommended to use because it may creates performance problems.
☀ Connection interface defines the following method to know isolation level.
getTransactionIsolation()
☀ Connection interface defines the following method to set our own isolation level.
setTransactionIsolation(int level)
E.g:
System.out.println(con.getTransactionIsolation());
con.setTransactionIsolation(8);
System.out.println(con.getTransactionIsolation());

Note  
☀ For Oracle database, the default isolation level is: 2(TRANSACTION_READ_COMMITED).
☀ Oracle database provides support only for isolation levels 2 and 8.
☀ For MySql database, the default isolation level is: 4(TRANSACTION_REPEATABLE_READ).
☀ MySql database can provide support for all isolation levels (1, 2, 4 and 8).

In JDBC how many transaction isolation levels are defined? 
     The following 5 isolation levels are defined.
1. TRANSACTION_NONE ➔ 0
     It indicates that database won't provide support for transactions.
2. TRANSACTION_READ_UNCOMMITTED ➔ 1
3. TRANSACTION_READ_COMMITTED ➔ 2
4. TRANSACTION_REPEATABLE_READ ➔ 4
5. TRANSACTION_SERIALIZABLE ➔ 8



Next Tutorial : MetaData

Previous Tutorial : About Properties Class

1 comment: