Transactions in JDBC: Ensuring Data Integrity

Learn how to group database operations into atomic units for reliability

What Are Transactions?

A transaction is a sequence of database operations (e.g., inserts, updates) treated as a single logical unit. Transactions ensure ACID properties:

• Atomicity: All operations succeed or fail together.
• Consistency: Data remains valid before and after the transaction.
• Isolation: Concurrent transactions don’t interfere.
• Durability: Changes persist after a transaction commits.

Real-World Example:
Imagine transferring $100 between two bank accounts:

1. Deduct $100 from Account A.
2. Add $100 to Account B.
   If either step fails, both should roll back to avoid inconsistency.

Default Behavior: Auto-Commit Mode

By default, JDBC uses auto-commit mode, where every SQL statement is treated as a separate transaction. This is risky for multi-step operations:

// Without transactions (risky!)  
statement.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1");  
statement.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2");  
// If the second query fails, $100 is lost!  

Managing Transactions in JDBC

Step 1: Disable Auto-Commit

Connection conn = DriverManager.getConnection(url, user, password);  
conn.setAutoCommit(false); // Transactions now manual  

Step 2: Group Operations

Execute all queries as part of a single transaction:

try {  
    // Transfer $100 from Account 1 to 2  
    PreparedStatement deduct = conn.prepareStatement(  
        "UPDATE accounts SET balance = balance - 100 WHERE id = ?"  
    );  
    deduct.setInt(1, 1);  
    deduct.executeUpdate();  

    PreparedStatement add = conn.prepareStatement(  
        "UPDATE accounts SET balance = balance + 100 WHERE id = ?"  
    );  
    add.setInt(1, 2);  
    add.executeUpdate();  

    // Commit if both succeed  
    conn.commit();   
} catch (SQLException e) {  
    conn.rollback(); // Undo all changes on failure  
}  

Step 3: Handle Commit/Rollback

• Commit: Save changes permanently with conn.commit().
• Rollback: Undo changes with conn.rollback().

Transaction Isolation Levels

Isolation levels control how transactions interact with concurrent operations. Common levels include:

Setting Isolation Level

conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);  

Best Practices

1. Keep Transactions Short: Avoid long-running transactions to reduce locking.
2. Always Handle Exceptions: Rollback on errors to prevent partial updates.
3. Use Savepoints (Advanced): Create checkpoints within transactions for partial rollbacks.

Savepoint savepoint = conn.setSavepoint("SAVEPOINT_1");  
// ...  
conn.rollback(savepoint); // Rollback to savepoint  

Full Example: Bank Transfer

public class BankTransfer {  
    public static void main(String[] args) {  
        String url = "jdbc:mysql://localhost:3306/bank";  
        String user = "root";  
        String password = "root123";  

        try (Connection conn = DriverManager.getConnection(url, user, password)) {  
            conn.setAutoCommit(false); // Start transaction  

            try {  
                // Deduct from Account 1  
                PreparedStatement deduct = conn.prepareStatement(  
                    "UPDATE accounts SET balance = balance - ? WHERE id = ?"  
                );  
                deduct.setDouble(1, 100.0);  
                deduct.setInt(2, 1);  
                deduct.executeUpdate();  

                // Add to Account 2  
                PreparedStatement add = conn.prepareStatement(  
                    "UPDATE accounts SET balance = balance + ? WHERE id = ?"  
                );  
                add.setDouble(1, 100.0);  
                add.setInt(2, 2);  
                add.executeUpdate();  

                conn.commit(); // Commit transaction  
                System.out.println("Transfer successful!");  

            } catch (SQLException e) {  
                conn.rollback(); // Rollback on error  
                System.err.println("Transfer failed: " + e.getMessage());  
            }  

        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  
}  

Common Errors

Summary

• Transactions ensure atomicity and consistency for multi-step operations.
• Use setAutoCommit(false), commit(), and rollback() to manage transactions.
• Choose isolation levels based on concurrency needs.

Next Up: JDBC Best Practices – Learn how to write efficient, secure database code!