Back to JDBC Hub

Nearby lessons

16 of 37
JDBC - Data TypesJDBC - MetadataJDBC - TransactionsJDBC - ACID PropertiesJDBC - Batch Processing

JDBC - Transaction Management

📌 What You Will Learn
  • What a transaction is and the 'all or none' rule
  • The ACID properties of a transaction
  • Local vs Global transactions
  • The three JDBC steps: setAutoCommit(false), commit, rollback
  • Savepoints for partial rollback

Transaction Management is a fundamental part of database programming with JDBC. This lesson explains What is a Transaction?, Local vs Global Transactions and The Three Steps of Transaction Management in JDBC with complete, runnable code examples, clear step-by-step explanations, and common mistakes to avoid with exam-style MCQs at the end.

What is a Transaction?

A transaction is a group of related operations combined into one single unit, executed on the rule "either all or none". If every operation succeeds, the whole unit is saved (committed). If even one fails, everything is undone (rolled back).

Classic example — funds transfer:

This is why a transaction must be one unit: partial success is not allowed.

In simple words: "All or none" means either every operation of the group gets saved, or none of them does. If any one step fails, the whole transaction is cancelled, so the database is never left half-updated.
Example01
JCode Cell
1Case: Transfer Rs. 5000 from A's account to B's account.
2 
31. Debit 5000 from A's account (A balance - 5000)
42. Credit 5000 into B's account (B balance + 5000)
5 
6If step 1 succeeds but step 2 fails -> money vanishes -> DATA INCONSISTENCY!
7 
8Transaction rule: both must happen together, or none at all.

Local vs Global Transactions

TypeWhere operations runExample
Local transactionOn the same databaseTransfer money between two accounts of the same bank
Global transactionOn different databasesTransfer money between accounts of two different banks
Trainer's Note: JDBC provides support for local transactions only. For global transactions (across multiple databases) we use EJB or the Spring framework (which use the Java Transaction API). This is a favourite interview point.

The Three Steps of Transaction Management in JDBC

By default, JDBC runs in auto-commit mode — every SQL statement is saved immediately. To build a transaction, we disable that and control the commit ourselves:

In simple words: The three steps are: switch off auto-commit, run all the operations, then commit or rollback. With auto-commit off, nothing is saved until you call commit(), and rollback() undoes everything if any step fails.

The complete program:

Example03
JCode Cell
1// Step 1 - disable auto-commit (now WE decide when to save)
2con.setAutoCommit(false);
3 
4try {
5 // Step 2 - do all the operations
6 st.executeUpdate("UPDATE account SET balance = balance - 5000 WHERE acc=1");
7 st.executeUpdate("UPDATE account SET balance = balance + 5000 WHERE acc=2");
8 
9 // Step 3a - if everything succeeded, save it all
10 con.commit();
11} catch (Exception e) {
12 // Step 3b - if anything failed, undo it all
13 con.rollback();
14}

The Three Steps of Transaction Management in JDBC

Example04
JCode Cell
1import java.sql.*;
2 
3public class TransactionDemo {
4 public static void main(String[] args) throws Exception {
5 Connection con = DriverManager.getConnection(
6 "jdbc:mysql://localhost:3306/bank", "root", "password");
7 
8 con.setAutoCommit(false); // 1. manual control
9 try {
10 Statement st = con.createStatement();
11 st.executeUpdate("UPDATE account SET balance = balance - 5000 WHERE acc=101");
12 st.executeUpdate("UPDATE account SET balance = balance + 5000 WHERE acc=102");
13 con.commit(); // 2. all or nothing - save
14 System.out.println("Transfer successful");
15 } catch (SQLException e) {
16 con.rollback(); // 3. undo on any failure
17 System.out.println("Transfer failed, rolled back: " + e.getMessage());
18 } finally {
19 con.close();
20 }
21 }
22}

Savepoints — Partial Rollback

Sometimes you don't want to undo everything. A Savepoint is a marker inside a transaction — you can roll back to that marker instead of to the start.

In simple words: A Savepoint is a bookmark placed inside a transaction. Roll back to it with con.rollback(sp1) to undo only the work done after the bookmark — everything before it stays.

Program: savepoint in action

Example06
JCode Cell
1import java.sql.*;
2 
3public class SavepointDemo {
4 public static void main(String[] args) throws Exception {
5 Connection con = DriverManager.getConnection(
6 "jdbc:mysql://localhost:3306/bank", "root", "password");
7 
8 con.setAutoCommit(false);
9 Statement st = con.createStatement();
10 
11 st.executeUpdate("UPDATE account SET balance = balance - 5000 WHERE acc=1");
12 
13 Savepoint sp1 = con.setSavepoint("after-step-1"); // marker
14 
15 st.executeUpdate("UPDATE account SET balance = balance - 90000000 WHERE acc=2");
16 
17 // the second update seems wrong - undo only from the marker
18 con.rollback(sp1); // keeps step 1, undoes step 2
19 con.releaseSavepoint(sp1); // remove the marker
20 
21 con.commit(); // save what survived
22 System.out.println("Transaction committed with partial rollback");
23 con.close();
24 }
25}

Transaction Methods — Quick Table

MethodWhat it does
con.setAutoCommit(false)Starts manual control of transactions
con.setAutoCommit(true)Back to automatic saving after every query
con.commit()Permanently saves all changes in the transaction
con.rollback()Undoes all uncommitted changes
con.setSavepoint(name)Puts a marker inside the transaction
con.rollback(savepoint)Undoes changes made after the marker
con.releaseSavepoint(sp)Removes the marker
📝 Key Takeaways
  • Transaction = related operations as one unit, rule: all or none.
  • ACID: Atomicity, Consistency, Isolation, Durability.
  • JDBC supports local transactions only; global ones need EJB/Spring.
  • Three steps: setAutoCommit(false) -> operations -> commit() or rollback().
  • Savepoints allow partial rollback to a marker.
  • Without transactions, a failed step leaves the database inconsistent.

🧠 Test Your Knowledge

4 Questions
Progress: 0 / 4