Java Notes

Transaction Management in JDBC

Master JDBC transaction management — ACID properties, isolation levels, savepoints, distributed transactions, and real-world patterns for data consistency.

What is a Transaction?

A transaction is a sequence of database operations that must execute as a single atomic unit — either ALL succeed or ALL are rolled back. This ensures data consistency.

Classic Example	Bank Transfer ($500 from Account A to Account B)
1. Debit A: $1000	$500 ✅
3. Credit B	never happens ❌
Result	$500 vanished!
1. Debit A: $1000	$500
Result	A still has $1000, B unchanged ✅

Basic Transaction Control

java example

import java.sql.*;

public class TransactionBasics {
    private static final String URL = "jdbc:mysql://localhost:3306/java_course?useSSL=false&serverTimezone=UTC";
    
    public static void transferFunds(int fromId, int toId, double amount) throws SQLException {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(URL, "root", "password");
            
            // Step 1: Disable auto-commit (starts transaction)
            conn.setAutoCommit(false);
            
            // Step 2: Execute operations
            try (PreparedStatement debit = conn.prepareStatement(
                     "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?");
                 PreparedStatement credit = conn.prepareStatement(
                     "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
                
                // Debit source account
                debit.setDouble(1, amount);
                debit.setInt(2, fromId);
                debit.setDouble(3, amount); // Ensure sufficient balance
                int debitRows = debit.executeUpdate();
                
                if (debitRows == 0) {
                    throw new SQLException("Insufficient funds or account not found: " + fromId);
                }
                
                // Credit destination account
                credit.setDouble(1, amount);
                credit.setInt(2, toId);
                int creditRows = credit.executeUpdate();
                
                if (creditRows == 0) {
                    throw new SQLException("Destination account not found: " + toId);
                }
                
                // Step 3: Commit if all succeeded
                conn.commit();
                System.out.printf("Transfer successful: $%.2f from %d to %d%n", amount, fromId, toId);
            }
            
        } catch (SQLException e) {
            // Step 4: Rollback on any error
            if (conn != null) {
                try {
                    conn.rollback();
                    System.err.println("Transaction rolled back: " + e.getMessage());
                } catch (SQLException ex) {
                    System.err.println("Rollback failed: " + ex.getMessage());
                }
            }
            throw e;
        } finally {
            // Step 5: Restore auto-commit and close
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Transaction Isolation Levels

Isolation levels control how transactions interact with each other:

Problems Without Proper Isolation

Problem	Description
Dirty Read	Reading uncommitted changes from another transaction
Non-Repeatable Read	Same query returns different results within one transaction
Phantom Read	New rows appear between queries in same transaction

Isolation Level Matrix

Level	Dirty Read	Non-Repeatable	Phantom	Performance
READ_UNCOMMITTED	⚠️ Possible	⚠️ Possible	⚠️ Possible	Fastest
READ_COMMITTED	✅ Prevented	⚠️ Possible	⚠️ Possible	Fast
REPEATABLE_READ	✅ Prevented	✅ Prevented	⚠️ Possible	Medium
SERIALIZABLE	✅ Prevented	✅ Prevented	✅ Prevented	Slowest

Example

public static void isolationLevelDemo() throws SQLException { Connection conn = DriverManager.getConnection(URL, "root", "password"); // Check current isolation level int level = conn.getTransactionIsolation(); System.out.println("Current level: " + getIsolationName(level)); // Set isolation level (BEFORE starting transaction) conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); conn.setAutoCommit(false); // Available constants: // Connection.TRANSACTION_READ_UNCOMMITTED (1) // Connection.TRANSACTION_READ_COMMITTED (2) ← Oracle default // Connection.TRANSACTION_REPEATABLE_READ (4) ← MySQL default // Connection.TRANSACTION_SERIALIZABLE (8) // ... perform operations ... conn.commit(); } private static String getIsolationName(int level) { switch (level) { case Connection.TRANSACTION_READ_UNCOMMITTED: return "READ_UNCOMMITTED"; case Connection.TRANSACTION_READ_COMMITTED: return "READ_COMMITTED"; case Connection.TRANSACTION_REPEATABLE_READ: return "REPEATABLE_READ"; case Connection.TRANSACTION_SERIALIZABLE: return "SERIALIZABLE"; default: return "UNKNOWN (" + level + ")"; } }

Savepoints

Savepoints allow partial rollback within a transaction:

java example

public static void savepointDemo() throws SQLException {
    Connection conn = DriverManager.getConnection(URL, "root", "password");
    conn.setAutoCommit(false);
    Savepoint savepoint1 = null;
    Savepoint savepoint2 = null;
    
    try (Statement stmt = conn.createStatement()) {
        // Operation 1: Insert department
        stmt.executeUpdate("INSERT INTO departments (name, budget) VALUES ('Legal', 250000)");
        savepoint1 = conn.setSavepoint("after_department");
        
        // Operation 2: Insert employees
        stmt.executeUpdate("INSERT INTO employees (first_name, last_name, email, department, salary) " +
                          "VALUES ('Eva', 'Green', 'eva@company.com', 'Legal', 80000)");
        savepoint2 = conn.setSavepoint("after_first_employee");
        
        // Operation 3: Another insert that might fail
        try {
            stmt.executeUpdate("INSERT INTO employees (first_name, last_name, email, department, salary) " +
                              "VALUES ('Duplicate', 'Person', 'eva@company.com', 'Legal', 70000)");
            // This fails (duplicate email)!
        } catch (SQLException e) {
            System.out.println("Operation 3 failed, rolling back to savepoint2");
            conn.rollback(savepoint2); // Undo only operation 3
        }
        
        // Operations 1 and 2 are still intact!
        conn.commit();
        System.out.println("Department and first employee saved successfully");
        
    } catch (SQLException e) {
        conn.rollback(); // Rollback everything
        System.err.println("Complete rollback: " + e.getMessage());
    }
}

Output

Operation 3 failed, rolling back to savepoint2
Department and first employee saved successfully

Real-World Transaction Pattern: Order Processing

java example

public class OrderProcessor {
    
    public static long processOrder(int customerId, List<OrderItem> items) throws SQLException {
        Connection conn = null;
        Savepoint inventorySavepoint = null;
        
        try {
            conn = DriverManager.getConnection(URL, "root", "password");
            conn.setAutoCommit(false);
            conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
            
            // 1. Create order record
            long orderId;
            try (PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO orders (customer_id, order_date, status) VALUES (?, NOW(), 'PENDING')",
                    Statement.RETURN_GENERATED_KEYS)) {
                ps.setInt(1, customerId);
                ps.executeUpdate();
                ResultSet keys = ps.getGeneratedKeys();
                keys.next();
                orderId = keys.getLong(1);
            }
            
            // 2. Insert order items and calculate total
            double totalAmount = 0;
            try (PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)")) {
                for (OrderItem item : items) {
                    ps.setLong(1, orderId);
                    ps.setInt(2, item.getProductId());
                    ps.setInt(3, item.getQuantity());
                    ps.setDouble(4, item.getPrice());
                    ps.addBatch();
                    totalAmount += item.getPrice() * item.getQuantity();
                }
                ps.executeBatch();
            }
            
            // Savepoint before inventory (can retry)
            inventorySavepoint = conn.setSavepoint("before_inventory");
            
            // 3. Reduce inventory
            try (PreparedStatement ps = conn.prepareStatement(
                    "UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?")) {
                for (OrderItem item : items) {
                    ps.setInt(1, item.getQuantity());
                    ps.setInt(2, item.getProductId());
                    ps.setInt(3, item.getQuantity());
                    int updated = ps.executeUpdate();
                    if (updated == 0) {
                        throw new InsufficientStockException(
                            "Product " + item.getProductId() + " has insufficient stock");
                    }
                }
            }
            
            // 4. Update order total and status
            try (PreparedStatement ps = conn.prepareStatement(
                    "UPDATE orders SET total_amount = ?, status = 'CONFIRMED' WHERE id = ?")) {
                ps.setDouble(1, totalAmount);
                ps.setLong(2, orderId);
                ps.executeUpdate();
            }
            
            conn.commit();
            System.out.println("Order " + orderId + " processed successfully. Total: $" + totalAmount);
            return orderId;
            
        } catch (InsufficientStockException e) {
            if (conn != null && inventorySavepoint != null) {
                conn.rollback(inventorySavepoint);
                // Could mark order as "PENDING_STOCK" instead of full rollback
            }
            throw e;
        } catch (SQLException e) {
            if (conn != null) conn.rollback();
            throw e;
        } finally {
            if (conn != null) {
                conn.setAutoCommit(true);
                conn.close();
            }
        }
    }
}

Transaction Template Pattern

java example

@FunctionalInterface
interface TransactionCallback<T> {
    T execute(Connection conn) throws SQLException;
}

public class TransactionTemplate {
    private final DataSource dataSource;
    
    public TransactionTemplate(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    
    public <T> T executeInTransaction(TransactionCallback<T> callback) throws SQLException {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            
            T result = callback.execute(conn);
            
            conn.commit();
            return result;
        } catch (SQLException e) {
            if (conn != null) conn.rollback();
            throw e;
        } finally {
            if (conn != null) {
                conn.setAutoCommit(true);
                conn.close();
            }
        }
    }
}

// Usage
TransactionTemplate txTemplate = new TransactionTemplate(dataSource);
long orderId = txTemplate.executeInTransaction(conn -> {
    // All operations here are in one transaction
    // No manual commit/rollback needed
    return processOrderLogic(conn, customerId, items);
});

Interview Questions

Q1: What are ACID properties? Explain each.

Answer: Atomicity (all-or-nothing), Consistency (valid state to valid state), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes). They guarantee reliable database operations.

Q2: What is the default auto-commit behavior in JDBC?

Answer: Auto-commit is TRUE by default. Each SQL statement is automatically committed as its own transaction. You must call setAutoCommit(false) to start multi-statement transactions.

Q3: Explain the four isolation levels.

Answer: READ_UNCOMMITTED (sees uncommitted data), READ_COMMITTED (only committed data, Oracle default), REPEATABLE_READ (consistent reads within transaction, MySQL default), SERIALIZABLE (full isolation, transactions appear sequential).

Q4: What is a savepoint and when would you use it?

Answer: A savepoint marks a point within a transaction that you can rollback to without rolling back the entire transaction. Use when you want partial rollback — e.g., batch processing where one item fails but you want to keep the others.

Q5: What happens if the connection is closed without commit or rollback?

Answer: The behavior is driver/database dependent but typically triggers an automatic rollback. Never rely on this — always explicitly commit or rollback. Resource pools may also reset auto-commit on connection return.

Q6: How do you prevent deadlocks in JDBC transactions?

Answer: (1) Access tables/rows in consistent order, (2) Keep transactions short, (3) Use appropriate isolation levels, (4) Set lock timeouts, (5) Use SELECT FOR UPDATE judiciously, (6) Retry logic for deadlock exceptions (MySQL error 1213).

Summary

In this chapter, we learned about Transaction Management in JDBC in detail. Here are the key points:

Basic introduction to the concept and why it is needed
Syntax and structure with complete examples
Internal working and best practices
Real-world applications and use cases
Common mistakes and how to avoid them
How this topic is asked in interviews

To practice these concepts, write and run the code in your IDE and verify the output. Modify each example and experiment so that you deeply understand the concept.

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for Transaction Management in JDBC.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this Java Master Course topic.

Search Terms

java-master-course, java master course, java, master, course, jdbc, transaction, management

Continue learning this concept

JDBCBatch Processing in JDBCMaster JDBC batch processing — Statement and PreparedStatement batching, performance optimization, error handling, and bulk data import patterns.JDBCCallableStatement in JDBCComplete guide to CallableStatement — calling stored procedures and functions, IN/OUT/INOUT parameters, and handling multiple result sets from MySQL procedures.JDBCJDBC ArchitectureDeep dive into JDBC architecture — two-tier and three-tier models, driver manager, connection pooling, DataSource, and internal execution flow.JDBCJDBC IntroductionComplete introduction to JDBC — Java Database Connectivity concepts, API overview, driver types, and how Java applications communicate with databases.JDBCJDBC ProjectsHands-on JDBC projects — Student Management System, Employee Payroll, Library Management with full working code including DAO pattern, connection pooling, and menu-driven interface.JDBCMySQL Connection with JDBCStep-by-step guide to connecting Java with MySQL — driver setup, connection URLs, properties, SSL configuration, and troubleshooting common errors.

PreviousStatement Interface in JDBC NextCookies in Servlets

Java Notes

Transaction Management in JDBC

Master JDBC transaction management — ACID properties, isolation levels, savepoints, distributed transactions, and real-world patterns for data consistency.

What is a Transaction?

A transaction is a sequence of database operations that must execute as a single atomic unit — either ALL succeed or ALL are rolled back. This ensures data consistency.

Classic Example	Bank Transfer ($500 from Account A to Account B)
1. Debit A: $1000	$500 ✅
3. Credit B	never happens ❌
Result	$500 vanished!
1. Debit A: $1000	$500
Result	A still has $1000, B unchanged ✅

Basic Transaction Control

java example

import java.sql.*;

public class TransactionBasics {
    private static final String URL = "jdbc:mysql://localhost:3306/java_course?useSSL=false&serverTimezone=UTC";
    
    public static void transferFunds(int fromId, int toId, double amount) throws SQLException {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(URL, "root", "password");
            
            // Step 1: Disable auto-commit (starts transaction)
            conn.setAutoCommit(false);
            
            // Step 2: Execute operations
            try (PreparedStatement debit = conn.prepareStatement(
                     "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?");
                 PreparedStatement credit = conn.prepareStatement(
                     "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
                
                // Debit source account
                debit.setDouble(1, amount);
                debit.setInt(2, fromId);
                debit.setDouble(3, amount); // Ensure sufficient balance
                int debitRows = debit.executeUpdate();
                
                if (debitRows == 0) {
                    throw new SQLException("Insufficient funds or account not found: " + fromId);
                }
                
                // Credit destination account
                credit.setDouble(1, amount);
                credit.setInt(2, toId);
                int creditRows = credit.executeUpdate();
                
                if (creditRows == 0) {
                    throw new SQLException("Destination account not found: " + toId);
                }
                
                // Step 3: Commit if all succeeded
                conn.commit();
                System.out.printf("Transfer successful: $%.2f from %d to %d%n", amount, fromId, toId);
            }
            
        } catch (SQLException e) {
            // Step 4: Rollback on any error
            if (conn != null) {
                try {
                    conn.rollback();
                    System.err.println("Transaction rolled back: " + e.getMessage());
                } catch (SQLException ex) {
                    System.err.println("Rollback failed: " + ex.getMessage());
                }
            }
            throw e;
        } finally {
            // Step 5: Restore auto-commit and close
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Transaction Isolation Levels

Isolation levels control how transactions interact with each other:

Problems Without Proper Isolation

Problem	Description
Dirty Read	Reading uncommitted changes from another transaction
Non-Repeatable Read	Same query returns different results within one transaction
Phantom Read	New rows appear between queries in same transaction

Isolation Level Matrix

Level	Dirty Read	Non-Repeatable	Phantom	Performance
READ_UNCOMMITTED	⚠️ Possible	⚠️ Possible	⚠️ Possible	Fastest
READ_COMMITTED	✅ Prevented	⚠️ Possible	⚠️ Possible	Fast
REPEATABLE_READ	✅ Prevented	✅ Prevented	⚠️ Possible	Medium
SERIALIZABLE	✅ Prevented	✅ Prevented	✅ Prevented	Slowest

Example

Savepoints

Savepoints allow partial rollback within a transaction:

java example

public static void savepointDemo() throws SQLException {
    Connection conn = DriverManager.getConnection(URL, "root", "password");
    conn.setAutoCommit(false);
    Savepoint savepoint1 = null;
    Savepoint savepoint2 = null;
    
    try (Statement stmt = conn.createStatement()) {
        // Operation 1: Insert department
        stmt.executeUpdate("INSERT INTO departments (name, budget) VALUES ('Legal', 250000)");
        savepoint1 = conn.setSavepoint("after_department");
        
        // Operation 2: Insert employees
        stmt.executeUpdate("INSERT INTO employees (first_name, last_name, email, department, salary) " +
                          "VALUES ('Eva', 'Green', 'eva@company.com', 'Legal', 80000)");
        savepoint2 = conn.setSavepoint("after_first_employee");
        
        // Operation 3: Another insert that might fail
        try {
            stmt.executeUpdate("INSERT INTO employees (first_name, last_name, email, department, salary) " +
                              "VALUES ('Duplicate', 'Person', 'eva@company.com', 'Legal', 70000)");
            // This fails (duplicate email)!
        } catch (SQLException e) {
            System.out.println("Operation 3 failed, rolling back to savepoint2");
            conn.rollback(savepoint2); // Undo only operation 3
        }
        
        // Operations 1 and 2 are still intact!
        conn.commit();
        System.out.println("Department and first employee saved successfully");
        
    } catch (SQLException e) {
        conn.rollback(); // Rollback everything
        System.err.println("Complete rollback: " + e.getMessage());
    }
}

Output

Operation 3 failed, rolling back to savepoint2
Department and first employee saved successfully

Real-World Transaction Pattern: Order Processing

java example

public class OrderProcessor {
    
    public static long processOrder(int customerId, List<OrderItem> items) throws SQLException {
        Connection conn = null;
        Savepoint inventorySavepoint = null;
        
        try {
            conn = DriverManager.getConnection(URL, "root", "password");
            conn.setAutoCommit(false);
            conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
            
            // 1. Create order record
            long orderId;
            try (PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO orders (customer_id, order_date, status) VALUES (?, NOW(), 'PENDING')",
                    Statement.RETURN_GENERATED_KEYS)) {
                ps.setInt(1, customerId);
                ps.executeUpdate();
                ResultSet keys = ps.getGeneratedKeys();
                keys.next();
                orderId = keys.getLong(1);
            }
            
            // 2. Insert order items and calculate total
            double totalAmount = 0;
            try (PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)")) {
                for (OrderItem item : items) {
                    ps.setLong(1, orderId);
                    ps.setInt(2, item.getProductId());
                    ps.setInt(3, item.getQuantity());
                    ps.setDouble(4, item.getPrice());
                    ps.addBatch();
                    totalAmount += item.getPrice() * item.getQuantity();
                }
                ps.executeBatch();
            }
            
            // Savepoint before inventory (can retry)
            inventorySavepoint = conn.setSavepoint("before_inventory");
            
            // 3. Reduce inventory
            try (PreparedStatement ps = conn.prepareStatement(
                    "UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?")) {
                for (OrderItem item : items) {
                    ps.setInt(1, item.getQuantity());
                    ps.setInt(2, item.getProductId());
                    ps.setInt(3, item.getQuantity());
                    int updated = ps.executeUpdate();
                    if (updated == 0) {
                        throw new InsufficientStockException(
                            "Product " + item.getProductId() + " has insufficient stock");
                    }
                }
            }
            
            // 4. Update order total and status
            try (PreparedStatement ps = conn.prepareStatement(
                    "UPDATE orders SET total_amount = ?, status = 'CONFIRMED' WHERE id = ?")) {
                ps.setDouble(1, totalAmount);
                ps.setLong(2, orderId);
                ps.executeUpdate();
            }
            
            conn.commit();
            System.out.println("Order " + orderId + " processed successfully. Total: $" + totalAmount);
            return orderId;
            
        } catch (InsufficientStockException e) {
            if (conn != null && inventorySavepoint != null) {
                conn.rollback(inventorySavepoint);
                // Could mark order as "PENDING_STOCK" instead of full rollback
            }
            throw e;
        } catch (SQLException e) {
            if (conn != null) conn.rollback();
            throw e;
        } finally {
            if (conn != null) {
                conn.setAutoCommit(true);
                conn.close();
            }
        }
    }
}

Transaction Template Pattern

java example

@FunctionalInterface
interface TransactionCallback<T> {
    T execute(Connection conn) throws SQLException;
}

public class TransactionTemplate {
    private final DataSource dataSource;
    
    public TransactionTemplate(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    
    public <T> T executeInTransaction(TransactionCallback<T> callback) throws SQLException {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            
            T result = callback.execute(conn);
            
            conn.commit();
            return result;
        } catch (SQLException e) {
            if (conn != null) conn.rollback();
            throw e;
        } finally {
            if (conn != null) {
                conn.setAutoCommit(true);
                conn.close();
            }
        }
    }
}

// Usage
TransactionTemplate txTemplate = new TransactionTemplate(dataSource);
long orderId = txTemplate.executeInTransaction(conn -> {
    // All operations here are in one transaction
    // No manual commit/rollback needed
    return processOrderLogic(conn, customerId, items);
});

Interview Questions

Q1: What are ACID properties? Explain each.

Q2: What is the default auto-commit behavior in JDBC?

Answer: Auto-commit is TRUE by default. Each SQL statement is automatically committed as its own transaction. You must call setAutoCommit(false) to start multi-statement transactions.

Q3: Explain the four isolation levels.

Q4: What is a savepoint and when would you use it?

Q5: What happens if the connection is closed without commit or rollback?

Q6: How do you prevent deadlocks in JDBC transactions?

Summary

In this chapter, we learned about Transaction Management in JDBC in detail. Here are the key points:

Basic introduction to the concept and why it is needed
Syntax and structure with complete examples
Internal working and best practices
Real-world applications and use cases
Common mistakes and how to avoid them
How this topic is asked in interviews

To practice these concepts, write and run the code in your IDE and verify the output. Modify each example and experiment so that you deeply understand the concept.

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for Transaction Management in JDBC.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this Java Master Course topic.

Search Terms

java-master-course, java master course, java, master, course, jdbc, transaction, management

Continue learning this concept

PreviousStatement Interface in JDBC NextCookies in Servlets