JDBC Transactions

🔄 Database Transactions

A transaction is a sequence of database operations that are treated as a single unit. Transactions ensure data consistency by following ACID properties: Atomicity, Consistency, Isolation, and Durability.


// Basic transaction example
conn.setAutoCommit(false); // Start transaction
// ... database operations ...
conn.commit(); // Commit transaction

Transaction Properties (ACID)

⚛️

Atomicity

All operations succeed or all fail

// Either all operations complete or none

✅

Consistency

Database remains in valid state

// Data integrity rules maintained

🔒

Isolation

Concurrent transactions don't interfere

conn.setTransactionIsolation(level);

💾

Durability

Committed changes are permanent

conn.commit(); // Changes persisted

🔹 Basic Transaction Management

Managing transactions with commit and rollback:

import java.sql.*;

public class BasicTransaction {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "password";
        
        Connection conn = null;
        
        try {
            conn = DriverManager.getConnection(url, username, password);
            
            // Disable auto-commit to start transaction
            conn.setAutoCommit(false);
            System.out.println("Transaction started...");
            
            // Create accounts table
            String createTable = "CREATE TABLE IF NOT EXISTS accounts (" +
                               "id INT PRIMARY KEY, " +
                               "name VARCHAR(50), " +
                               "balance DECIMAL(10,2))";
            
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(createTable);
            
            // Insert initial data
            stmt.executeUpdate("INSERT INTO accounts VALUES (1, 'Alice', 1000.00) ON DUPLICATE KEY UPDATE balance=1000.00");
            stmt.executeUpdate("INSERT INTO accounts VALUES (2, 'Bob', 500.00) ON DUPLICATE KEY UPDATE balance=500.00");
            
            System.out.println("Initial accounts created");
            
            // Transfer money from Alice to Bob
            PreparedStatement debitStmt = conn.prepareStatement(
                "UPDATE accounts SET balance = balance - ? WHERE id = ?");
            PreparedStatement creditStmt = conn.prepareStatement(
                "UPDATE accounts SET balance = balance + ? WHERE id = ?");
            
            double transferAmount = 200.00;
            
            // Debit from Alice (id=1)
            debitStmt.setDouble(1, transferAmount);
            debitStmt.setInt(2, 1);
            int debitResult = debitStmt.executeUpdate();
            
            // Credit to Bob (id=2)
            creditStmt.setDouble(1, transferAmount);
            creditStmt.setInt(2, 2);
            int creditResult = creditStmt.executeUpdate();
            
            // Check if both operations succeeded
            if (debitResult == 1 && creditResult == 1) {
                conn.commit(); // Commit transaction
                System.out.println("Transaction committed successfully!");
                System.out.println("Transferred $" + transferAmount + " from Alice to Bob");
            } else {
                conn.rollback(); // Rollback transaction
                System.out.println("Transaction rolled back - operation failed");
            }
            
            // Verify final balances
            ResultSet rs = stmt.executeQuery("SELECT name, balance FROM accounts ORDER BY id");
            System.out.println("\nFinal balances:");
            while (rs.next()) {
                System.out.println(rs.getString("name") + ": $" + rs.getDouble("balance"));
            }
            
        } catch (SQLException e) {
            System.out.println("Error occurred: " + e.getMessage());
            try {
                if (conn != null) {
                    conn.rollback(); // Rollback on error
                    System.out.println("Transaction rolled back due to error");
                }
            } catch (SQLException rollbackEx) {
                System.out.println("Rollback failed: " + rollbackEx.getMessage());
            }
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true); // Restore auto-commit
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Output:

Transaction started...

Initial accounts created

Transaction committed successfully!

Transferred $200.0 from Alice to Bob

Final balances:

Alice: $800.0

Bob: $700.0

🔹 Transaction with Error Handling

Proper error handling and rollback in transactions:

import java.sql.*;

public class TransactionErrorHandling {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            
            // Start transaction
            conn.setAutoCommit(false);
            
            try {
                System.out.println("Starting risky transaction...");
                
                // Operation 1: Insert new user
                PreparedStatement insertUser = conn.prepareStatement(
                    "INSERT INTO users (name, email) VALUES (?, ?)");
                insertUser.setString(1, "Transaction User");
                insertUser.setString(2, "[email protected]");
                insertUser.executeUpdate();
                System.out.println("✓ User inserted");
                
                // Operation 2: Update account balance
                PreparedStatement updateBalance = conn.prepareStatement(
                    "UPDATE accounts SET balance = balance + ? WHERE id = ?");
                updateBalance.setDouble(1, 100.00);
                updateBalance.setInt(2, 1);
                int updated = updateBalance.executeUpdate();
                
                if (updated == 0) {
                    throw new SQLException("Account not found for update");
                }
                System.out.println("✓ Balance updated");
                
                // Operation 3: Intentional error simulation
                // Uncomment next line to simulate error:
                // throw new SQLException("Simulated error for testing");
                
                // If we reach here, commit the transaction
                conn.commit();
                System.out.println("✅ Transaction committed successfully!");
                
            } catch (SQLException e) {
                // Rollback transaction on any error
                System.out.println("❌ Error in transaction: " + e.getMessage());
                conn.rollback();
                System.out.println("🔄 Transaction rolled back");
                
                // Verify rollback worked
                verifyRollback(conn);
            }
            
        } catch (SQLException e) {
            System.out.println("Connection error: " + e.getMessage());
        }
    }
    
    private static void verifyRollback(Connection conn) throws SQLException {
        // Check if the user was actually not inserted due to rollback
        PreparedStatement checkUser = conn.prepareStatement(
            "SELECT COUNT(*) FROM users WHERE email = ?");
        checkUser.setString(1, "[email protected]");
        
        ResultSet rs = checkUser.executeQuery();
        if (rs.next()) {
            int count = rs.getInt(1);
            if (count == 0) {
                System.out.println("✓ Rollback verified - user was not inserted");
            } else {
                System.out.println("⚠️ Rollback may have failed - user exists");
            }
        }
    }
}

Output:

Starting risky transaction...

✓ User inserted

✓ Balance updated

✅ Transaction committed successfully!

🔹 Savepoints in Transactions

Using savepoints for partial rollbacks:

import java.sql.*;

public class SavepointExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            
            conn.setAutoCommit(false);
            System.out.println("Transaction with savepoints started");
            
            Statement stmt = conn.createStatement();
            
            // Initial operation
            stmt.executeUpdate("INSERT INTO users (name, email) VALUES ('User1', '[email protected]')");
            System.out.println("✓ User1 inserted");
            
            // Create first savepoint
            Savepoint savepoint1 = conn.setSavepoint("SavePoint1");
            System.out.println("📍 Savepoint1 created");
            
            // Second operation
            stmt.executeUpdate("INSERT INTO users (name, email) VALUES ('User2', '[email protected]')");
            System.out.println("✓ User2 inserted");
            
            // Create second savepoint
            Savepoint savepoint2 = conn.setSavepoint("SavePoint2");
            System.out.println("📍 Savepoint2 created");
            
            try {
                // Third operation (this might fail)
                stmt.executeUpdate("INSERT INTO users (name, email) VALUES ('User3', '[email protected]')");
                System.out.println("✓ User3 inserted");
                
                // Simulate an error condition
                boolean simulateError = true;
                if (simulateError) {
                    throw new SQLException("Simulated error after User3 insertion");
                }
                
            } catch (SQLException e) {
                System.out.println("❌ Error occurred: " + e.getMessage());
                
                // Rollback to savepoint2 (keeps User1 and User2, removes User3)
                conn.rollback(savepoint2);
                System.out.println("🔄 Rolled back to Savepoint2");
            }
            
            // Continue with more operations
            stmt.executeUpdate("INSERT INTO users (name, email) VALUES ('User4', '[email protected]')");
            System.out.println("✓ User4 inserted");
            
            // Commit the transaction
            conn.commit();
            System.out.println("✅ Transaction committed");
            
            // Verify final state
            ResultSet rs = stmt.executeQuery("SELECT name FROM users WHERE email LIKE '%test.com' ORDER BY name");
            System.out.println("\nFinal users in database:");
            while (rs.next()) {
                System.out.println("- " + rs.getString("name"));
            }
            
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}

Output:

Transaction with savepoints started

✓ User1 inserted

📍 Savepoint1 created

✓ User2 inserted

📍 Savepoint2 created

✓ User3 inserted

❌ Error occurred: Simulated error after User3 insertion

🔄 Rolled back to Savepoint2

✓ User4 inserted

✅ Transaction committed

Final users in database:

- User1

- User2

- User4

🔹 Transaction Isolation Levels

Different isolation levels and their effects:

import java.sql.*;

public class IsolationLevels {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            
            // Display current isolation level
            int currentLevel = conn.getTransactionIsolation();
            System.out.println("Current isolation level: " + getIsolationLevelName(currentLevel));
            
            // Set different isolation levels
            demonstrateIsolationLevel(conn, Connection.TRANSACTION_READ_UNCOMMITTED);
            demonstrateIsolationLevel(conn, Connection.TRANSACTION_READ_COMMITTED);
            demonstrateIsolationLevel(conn, Connection.TRANSACTION_REPEATABLE_READ);
            demonstrateIsolationLevel(conn, Connection.TRANSACTION_SERIALIZABLE);
            
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
    
    private static void demonstrateIsolationLevel(Connection conn, int level) throws SQLException {
        conn.setTransactionIsolation(level);
        System.out.println("\n🔒 Set isolation level to: " + getIsolationLevelName(level));
        
        // Start a transaction with this isolation level
        conn.setAutoCommit(false);
        
        try {
            // Perform some database operations
            PreparedStatement stmt = conn.prepareStatement("SELECT COUNT(*) FROM users");
            ResultSet rs = stmt.executeQuery();
            
            if (rs.next()) {
                System.out.println("   User count: " + rs.getInt(1));
            }
            
            // Simulate some work
            Thread.sleep(100);
            
            conn.commit();
            System.out.println("   ✅ Transaction completed successfully");
            
        } catch (Exception e) {
            conn.rollback();
            System.out.println("   ❌ Transaction failed: " + e.getMessage());
        } finally {
            conn.setAutoCommit(true);
        }
    }
    
    private static String getIsolationLevelName(int level) {
        switch (level) {
            case Connection.TRANSACTION_NONE:
                return "TRANSACTION_NONE";
            case Connection.TRANSACTION_READ_UNCOMMITTED:
                return "READ_UNCOMMITTED (Level 1)";
            case Connection.TRANSACTION_READ_COMMITTED:
                return "READ_COMMITTED (Level 2)";
            case Connection.TRANSACTION_REPEATABLE_READ:
                return "REPEATABLE_READ (Level 3)";
            case Connection.TRANSACTION_SERIALIZABLE:
                return "SERIALIZABLE (Level 4)";
            default:
                return "UNKNOWN (" + level + ")";
        }
    }
}

Output:

Current isolation level: REPEATABLE_READ (Level 3)

🔒 Set isolation level to: READ_UNCOMMITTED (Level 1)

User count: 6

✅ Transaction completed successfully

🔒 Set isolation level to: READ_COMMITTED (Level 2)

User count: 6

✅ Transaction completed successfully

🔒 Set isolation level to: REPEATABLE_READ (Level 3)

User count: 6

✅ Transaction completed successfully

🔒 Set isolation level to: SERIALIZABLE (Level 4)

User count: 6

✅ Transaction completed successfully

🔄 Database Transactions

Transaction Properties (ACID)

Atomicity

Consistency

Isolation

Durability

🔹 Basic Transaction Management

Output:

🔹 Transaction with Error Handling

Output:

🔹 Savepoints in Transactions

Output:

🔹 Transaction Isolation Levels

Output:

🧠 Test Your Knowledge

Which method is used to permanently save transaction changes?