How to do Transaction Management in JDBC

Posted By : Winkle Jindal | 30-Apr-2019

 

Transaction Management:

  • A transaction is a process of performing multiple database operations as one Atomic unit with All-Nothing Criteria. 
  • The transaction is successful and should be committed when all the database operations in the unit are successful.
  • When anyone database operation in the unit is failed then the Transaction is failed and should be rolled back. 

When we implement Transactions properly in our application. It guarantees ACID Properties-

  • Atomicity
  • Consistency
  • Isolation
  • Durability

AtomicityThis property identifies that transaction is atomic and An atomic transaction is either not begun at all or fully completed.

ConsistencyA Transaction enforces consistency in the system state by only ensuring that at the end of the transaction the system is in a ( valid ) state.

IsolationIsolation is that one transaction can not read data from another transaction until its completion.

Durability: A Transaction is said to be durable when it has been successful only once and it is completed for all the changes made to the system are permanent. 

Types Of Transactions 

  • Local Transaction
  • Distributed Transaction 

Local TransactionWhen a single database is participating in the Transactional Operations then it is called as Local Transactions.

Ex: Transfer the funds from one account to another account where two accounts are in the same bank or same database.

Distributed TransactionWhen two or more Databases are participating in the transactional operations then it is called as Distributed Transactions.

Ex: Transfer the funds from one account to another account where two accounts are in different bank s or different databases.

Note JDBC Supports only Local Transactions and does not support Distributed Transactions.

JDBC Transaction Management

  • Specifying the Transactional Boundaries:

Connection connection = null;
try{
   connection = ...;
   connection.setAutoCommit(false);  // Transaction Begin
   operation1;
   operation2;
   operation3;
   connection.commit();              // Transaction End
}catch(Exception e){
   if(connection != null){
       connection.rollBack();        // Transaction End
   }
}
  • When multiple transactions are running concurrently then we may get some transactional concurrency problems.
  1. Dirty Read Problem
  2. Repeatable Read Problem 
  3. Phantom Read Problem
  • We need to specify the Transactional Isolation Levels to solve these transactional concurrency problems.
  • There are four Transactional Isolation Levels which are defined as constants in Connection Interface as follows.
  1. TRANSACTION_READ_UNCOMMITTED     1
  2. TRANSACTION_READ_COMMITTED           2
  3. TRANSACTION_REPEATABLE_READ          4
  4. TRANSACTION_SERIALIZABLE                    8
  • Use the following method to specify the required Transactional Isolation Level 
  1. connection.setTransactionIsolation(2);
  2. connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

Here is a simple java program to understand jdbc transaction-

public class AccountTransaction
{
	public static void main(String[] args) 
	{
		Account account=new Account();
		account.transfer(101, 201, 2000);

	}
}

class Account
{
	int balance;
	int destinationAccountBalance, sourceAccountBalance, destinationAccountNewBalance, sourceAccountNewBalance;
	public void transfer(int sourceAccount, int destinationAccount, int amount)
	{
		Connection connection=null;
		PreparedStatement preparedStatement1=null;
		PreparedStatement preparedStatement2=null;
		try
		{
            Class.forName("oracle.jdbc.OracleDriver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jlc","root","tiger");
			//Starting Transaction
			connection.setAutoCommit(false);
			//OPERATION 1 => check destination Account
			preparedStatement1=connection.prepareStatement("select balance from account where accno=?");
			preparedStatement1.setInt(1, destinationAccount);
			ResultSet resultSet1 = preparedStatement1.executeQuery();
			if(resultSet1.next())
				destinationAccountBalance = resultSet1.getInt(1);
			else
				throw new InvalidAccountNumberException(destinationAccount);
			
			destinationAccountNewBalance = destinationAccountBalance + amount;
			
			//OERATION 2 => Updating Destination Account
			preparedStatement2=connection.prepareStatement("update account set balance=? where accountNo=?");
			preparedStatement2.setInt(1, destinationAccountNewBalance);
			preparedStatement2.setInt(2, destinationAccount);
			preparedStatement2.executeUpdate();
			System.out.println("Destination Account Updated : "+da);
			
			//OPERATION 3 => Checking Source Account
			preparedStatement1.setInt(1, sourceAccount);
			resultSet1 = preparedStatement1.executeQuery();
			if(resultSet1.next())
			{
				sourceAccountBalance = resultSet1.getInt(1);
			}
			else
			{
				throw new InvalidAccountNumberException(sourceAccount);
			}
			
			if(sourceAccountBalance >= amount)
			{
				sourceAccountNewBalance = sourceAccountBalance - amount;
			}
			else
			{
				throw new InsufficientFundException();
			}
			
			// OPERATION 4 => Updating Source Account
			preparedStatement2.setInt(1, sourceAccountNewBalance);
			preparedStatement2.setInt(2, sourceAccount);
			preparedStatement2.executeUpdate();
			
			connection.commit();
			System.out.println("Source Account Updated : "+sa);
			System.out.println("Fund Transfered");
		}
		catch(Exception e)
		{
			System.out.println(e);
			try
			{
				connection.rollback();
			}
			catch(Exception e1)
			{
				System.out.println(e1);
			}
				
		}
		finally
		{
            try
			{
				if(preparedStatement1 != null)
					preparedStatement1.close();
				if(connection != null)
					connection.close();
			}catch(Exception e){
				e.printStackTrace();
			}
            try
			{
				if(preparedStatement2 != null)
					preparedStatement2.close();
				if(connection != null)
					connection.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		}
	}
}

 

About Author

Author Image
Winkle Jindal

Winkle is masters in computer application.She is currently working as Java Developer. she is quick learner and always passionate to learn new technologies.

Request for Proposal

Name is required

Comment is required

Sending message..