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
Atomicity: This property identifies that transaction is atomic and An atomic transaction is either not begun at all or fully completed.
Consistency: A Transaction enforces consistency in the system state by only ensuring that at the end of the transaction the system is in a ( valid ) state.
Isolation: Isolation 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 Transaction: When 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 Transaction: When 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.
- Dirty Read Problem
- Repeatable Read Problem
- 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.
- TRANSACTION_READ_UNCOMMITTED 1
- TRANSACTION_READ_COMMITTED 2
- TRANSACTION_REPEATABLE_READ 4
- TRANSACTION_SERIALIZABLE 8
- Use the following method to specify the required Transactional Isolation Level
- connection.setTransactionIsolation(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();
}
}
}
}
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
About Author
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.