Transaction is the process of performing multiple database operations as one Atomic unit with All-Nothing Criteria.
When all the database operations in the unit are successful then Transaction is successful and should be commited.
When any one database operation in the unit is failed then Transaction is failed and should be rolled back.
When you implement transactions properly in your application, It gaurantees ACID Properties.
A - Atomicity
C - Consistency
I - Isolation
D - Durability
Types of Transactions
Local Transactions
Distributed Transactions
Local Transactions
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 same bank or same database.
Distributed Transactions
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 banks or different databases.
Note: JDBC Supports only Local Transactions and doesn’t support Distributed Transactions.
Specifying the Transactional Boundaries.
Connection con=null;
try{
con=...;
con.setAutoCommit(false); // Transaction Begin
op1;
op2;
op3;
con.commit(); // Transaction End
}catch(Exception e){
if(con!=null){
con.rollback(); // Transaction End
}
}
When multiple transactions are running concurrently then you may get some transactional concurrency problems.
Dirty Read Problem
Repeatable Read Problem
Phantom Read Problem
You need to specify the Transactional Isolation Levels to solve these transactional concurrency problems.
There are 4 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
con.setTransactionIsolation(2);
con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
DB Work for Program
1. Create the following table
create table myaccounts(
accno int primary key,
atype char(2),
bal double
);
2. Insert some Sample Records
insert into myaccounts values(101,'SA',25000);
insert into myaccounts values(102,'SA',5000);
package com.jtcindia.jdbc;
import java.sql.*;
class InvalidAccountNumberException extends RuntimeException {
int accno;
InvalidAccountNumberException() {}
InvalidAccountNumberException(int accno) {
this.accno = accno;
}
public String toString() {
return "Accno : " + accno + " is Not Found";
}
}
class InsufficientFundsException extends RuntimeException {
InsufficientFundsException() {}
public String toString() {
return "Sufficient Funds are Not Available";
}
}
class AccountService {
void fundsTransfer(int saccno, int daccno, double amt) throws SQLException {
Connection con = null;
PreparedStatement ps1 = null;
PreparedStatement ps2 = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/tutorial?autoReconnect=true&useSSL=false";
con = DriverManager.getConnection(url, "root", "root");
con.setAutoCommit(false); // Tx Begin
String SQL1 = "select bal from myaccounts where accno=?";
String SQL2 = "update myaccounts set bal=? where accno=?";
ps1 = con.prepareStatement(SQL1);
ps2 = con.prepareStatement(SQL2);
// Deposit
ps1.setInt(1, daccno); // 102
rs = ps1.executeQuery(); //OP1-Select
if (rs.next()) {
double bal = rs.getInt(1);
bal = bal + amt;
ps2.setDouble(1, bal);
ps2.setInt(2, daccno);
ps2.executeUpdate(); // OP2-Update
} else {
throw new InvalidAccountNumberException();
}
// Withdraw
ps1.setInt(1, saccno); // 101
rs = ps1.executeQuery(); //OP3-Select
if (rs.next()) {
double bal = rs.getInt(1);
if (bal >= amt) {
bal = bal - amt;
ps2.setDouble(1, bal);
ps2.setInt(2, saccno);
ps2.executeUpdate(); // OP4-Update
} else {
throw new InsufficientFundsException();
}
} else {
throw new InvalidAccountNumberException();
}
con.commit(); // End of Tx
System.out.println("----Done-----");
} catch (Exception ex) {
ex.printStackTrace();
try {
con.rollback(); // End of Tx
} catch (SQLException e) {
e.printStackTrace();
}
} finally {
rs.close();
ps1.close();
con.close();
}
}
}
public class Transaction_Management {
public static void main(String[] args) throws SQLException {
AccountService as = new AccountService();
as.fundsTransfer(101, 102, 5000);
}
}
