Transactions and Batch Processing

The Art of Data Integrity

When working with databases, you often need to execute multiple operations that must either all succeed or all fail together. Imagine transferring money between bank accounts - you can't debit one account without crediting another. Transactions ensure this "all or nothing" behavior, while batch processing lets you execute multiple statements efficiently.

Understanding Transactions

A transaction is a sequence of database operations treated as a single logical unit. It follows the ACID principles:

┌─────────────────────────────────────────────────────────────────┐
│                    ACID Properties                               │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   A - Atomicity:     All operations succeed, or none do        │
│                      (All or nothing)                           │
│                                                                 │
│   C - Consistency:   Database moves from one valid state        │
│                      to another valid state                     │
│                                                                 │
│   I - Isolation:     Concurrent transactions don't              │
│                      interfere with each other                  │
│                                                                 │
│   D - Durability:    Once committed, changes persist            │
│                      even after system failures                 │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

Transaction Lifecycle

A typical transaction follows this flow:

┌─────────────────────────────────────────────────────────────────┐
│                     Transaction Flow                             │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   1. Disable auto-commit                                        │
│      └── conn.setAutoCommit(false)                             │
│                                                                 │
│   2. Execute operations                                         │
│      ├── INSERT...                                             │
│      ├── UPDATE...                                             │
│      └── DELETE...                                             │
│                                                                 │
│   3a. Success: Commit all changes                               │
│       └── conn.commit()                                        │
│                                                                 │
│   3b. Failure: Rollback all changes                            │
│       └── conn.rollback()                                      │
│                                                                 │
│   4. Restore auto-commit                                        │
│      └── conn.setAutoCommit(true)                              │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

Auto-Commit Mode

By default, JDBC connections operate in auto-commit mode - each statement is immediately committed to the database. For transactions, you need to disable this:

// Default behavior - each statement commits immediately
Connection conn = DriverManager.getConnection(url, user, password);
// conn.getAutoCommit() returns true

// For transactions, disable auto-commit
conn.setAutoCommit(false);  // Now changes are not committed automatically

Basic Transaction Pattern

Here's the fundamental pattern for transaction handling:

Connection conn = null;

try {
    conn = DriverManager.getConnection(url, user, password);
    conn.setAutoCommit(false);  // Start transaction
    
    // Execute multiple operations
    // Operation 1
    PreparedStatement pstmt1 = conn.prepareStatement(
        "UPDATE accounts SET balance = balance - ? WHERE id = ?");
    pstmt1.setDouble(1, 100.00);
    pstmt1.setInt(2, fromAccountId);
    pstmt1.executeUpdate();
    
    // Operation 2
    PreparedStatement pstmt2 = conn.prepareStatement(
        "UPDATE accounts SET balance = balance + ? WHERE id = ?");
    pstmt2.setDouble(1, 100.00);
    pstmt2.setInt(2, toAccountId);
    pstmt2.executeUpdate();
    
    conn.commit();  // All operations succeeded - commit
    System.out.println("Transfer successful!");
    
} catch (SQLException e) {
    if (conn != null) {
        try {
            conn.rollback();  // Something failed - rollback all changes
            System.out.println("Transaction rolled back!");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    e.printStackTrace();
} finally {
    if (conn != null) {
        try {
            conn.setAutoCommit(true);  // Restore default behavior
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Real-World Example: Bank Transfer

Let's see a complete, production-ready example:

public class BankService {
    
    public boolean transferMoney(int fromAccount, int toAccount, 
            double amount) throws SQLException {
        
        String debitSql = "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?";
        String creditSql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
        String logSql = "INSERT INTO transactions (from_account, to_account, amount, timestamp) VALUES (?, ?, ?, ?)";
        
        try (Connection conn = dataSource.getConnection()) {
            conn.setAutoCommit(false);
            
            try {
                // Step 1: Debit the source account
                try (PreparedStatement debitStmt = conn.prepareStatement(debitSql)) {
                    debitStmt.setDouble(1, amount);
                    debitStmt.setInt(2, fromAccount);
                    debitStmt.setDouble(3, amount);  // Check sufficient balance
                    
                    int rows = debitStmt.executeUpdate();
                    if (rows == 0) {
                        throw new SQLException("Insufficient funds or account not found");
                    }
                }
                
                // Step 2: Credit the destination account
                try (PreparedStatement creditStmt = conn.prepareStatement(creditSql)) {
                    creditStmt.setDouble(1, amount);
                    creditStmt.setInt(2, toAccount);
                    
                    int rows = creditStmt.executeUpdate();
                    if (rows == 0) {
                        throw new SQLException("Destination account not found");
                    }
                }
                
                // Step 3: Log the transaction
                try (PreparedStatement logStmt = conn.prepareStatement(logSql)) {
                    logStmt.setInt(1, fromAccount);
                    logStmt.setInt(2, toAccount);
                    logStmt.setDouble(3, amount);
                    logStmt.setTimestamp(4, Timestamp.valueOf(LocalDateTime.now()));
                    logStmt.executeUpdate();
                }
                
                // All steps succeeded - commit
                conn.commit();
                return true;
                
            } catch (SQLException e) {
                conn.rollback();  // Undo all changes
                throw e;  // Re-throw for caller to handle
            }
        }
    }
}

Savepoints: Partial Rollbacks

Savepoints allow you to rollback part of a transaction while keeping earlier changes:

Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);

Savepoint savepoint1 = null;
Savepoint savepoint2 = null;

try {
    // Step 1: Create order
    executeInsert(conn, "INSERT INTO orders ...");
    savepoint1 = conn.setSavepoint("orderCreated");
    
    // Step 2: Add order items
    executeInsert(conn, "INSERT INTO order_items ...");
    savepoint2 = conn.setSavepoint("itemsAdded");
    
    // Step 3: Update inventory (might fail)
    try {
        executeUpdate(conn, "UPDATE inventory SET stock = stock - 1 ...");
    } catch (SQLException e) {
        // Inventory update failed - rollback to savepoint2
        // Order and items are preserved
        conn.rollback(savepoint2);
        System.out.println("Inventory update failed, but order is saved");
    }
    
    conn.commit();
    
} catch (SQLException e) {
    conn.rollback();  // Complete rollback
    throw e;
} finally {
    conn.setAutoCommit(true);
}

Transaction Isolation Levels

Isolation levels control how transactions interact with each other:

Setting Isolation Level

// Check current isolation level
int level = conn.getTransactionIsolation();

// Set isolation level (before starting transaction)
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
conn.setAutoCommit(false);

// Execute transaction...

Understanding the Problems

Dirty Read: Reading uncommitted changes from another transaction
Non-Repeatable Read: Same query returns different results within a transaction
Phantom Read: New rows appear between two identical queries

Batch Processing

When you need to execute many similar statements, batch processing significantly improves performance by sending multiple statements to the database in one round-trip:

Without Batch:                    With Batch:
┌──────────────┐                  ┌──────────────┐
│   App        │                  │   App        │
├──────────────┤                  ├──────────────┤
│ INSERT 1 ──────► DB             │ INSERT 1     │
│ INSERT 2 ──────► DB             │ INSERT 2     │ ──► DB (single trip)
│ INSERT 3 ──────► DB             │ INSERT 3     │
│ INSERT 4 ──────► DB             │ INSERT 4     │
└──────────────┘                  └──────────────┘
   4 round-trips                     1 round-trip

Basic Batch Operations

Using Statement for Different Queries

try (Connection conn = dataSource.getConnection();
     Statement stmt = conn.createStatement()) {
    
    conn.setAutoCommit(false);
    
    // Add different SQL statements to batch
    stmt.addBatch("INSERT INTO users (name) VALUES ('Alice')");
    stmt.addBatch("INSERT INTO users (name) VALUES ('Bob')");
    stmt.addBatch("UPDATE products SET price = price * 1.1 WHERE category = 'Electronics'");
    stmt.addBatch("DELETE FROM logs WHERE created_at < '2024-01-01'");
    
    // Execute all at once
    int[] results = stmt.executeBatch();
    
    conn.commit();
    
    // Check results
    for (int i = 0; i < results.length; i++) {
        System.out.println("Statement " + (i + 1) + " affected " + results[i] + " rows");
    }
}

Using PreparedStatement for Same Query

More common - inserting many rows with the same structure:

String sql = "INSERT INTO employees (name, department, salary) VALUES (?, ?, ?)";

try (Connection conn = dataSource.getConnection();
     PreparedStatement pstmt = conn.prepareStatement(sql)) {
    
    conn.setAutoCommit(false);
    
    List<Employee> employees = getEmployeesToInsert();
    
    for (Employee emp : employees) {
        pstmt.setString(1, emp.getName());
        pstmt.setString(2, emp.getDepartment());
        pstmt.setDouble(3, emp.getSalary());
        pstmt.addBatch();  // Add to batch
    }
    
    int[] results = pstmt.executeBatch();
    conn.commit();
    
    int totalInserted = Arrays.stream(results).sum();
    System.out.println("Inserted " + totalInserted + " employees");
}

Batch Processing with Chunking

For very large datasets, process in chunks to manage memory:

public void insertLargeDataset(List<Product> products) throws SQLException {
    String sql = "INSERT INTO products (name, price, category) VALUES (?, ?, ?)";
    int batchSize = 1000;  // Process 1000 at a time
    
    try (Connection conn = dataSource.getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        
        conn.setAutoCommit(false);
        int count = 0;
        
        for (Product product : products) {
            pstmt.setString(1, product.getName());
            pstmt.setDouble(2, product.getPrice());
            pstmt.setString(3, product.getCategory());
            pstmt.addBatch();
            
            count++;
            
            // Execute batch every 1000 records
            if (count % batchSize == 0) {
                pstmt.executeBatch();
                pstmt.clearBatch();  // Clear for next chunk
                System.out.println("Processed " + count + " records...");
            }
        }
        
        // Execute remaining records
        if (count % batchSize != 0) {
            pstmt.executeBatch();
        }
        
        conn.commit();
        System.out.println("Total: " + count + " products inserted");
    }
}

Combining Transactions and Batches

Transactions and batch processing work great together:

public void importOrderWithItems(Order order, List<OrderItem> items) 
        throws SQLException {
    
    String orderSql = "INSERT INTO orders (customer_id, total, status) VALUES (?, ?, ?)";
    String itemSql = "INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)";
    
    try (Connection conn = dataSource.getConnection()) {
        conn.setAutoCommit(false);
        
        try {
            // Insert order and get generated ID
            long orderId;
            try (PreparedStatement orderStmt = conn.prepareStatement(
                    orderSql, Statement.RETURN_GENERATED_KEYS)) {
                orderStmt.setLong(1, order.getCustomerId());
                orderStmt.setBigDecimal(2, order.getTotal());
                orderStmt.setString(3, "PENDING");
                orderStmt.executeUpdate();
                
                try (ResultSet keys = orderStmt.getGeneratedKeys()) {
                    keys.next();
                    orderId = keys.getLong(1);
                }
            }
            
            // Batch insert all items
            try (PreparedStatement itemStmt = conn.prepareStatement(itemSql)) {
                for (OrderItem item : items) {
                    itemStmt.setLong(1, orderId);
                    itemStmt.setLong(2, item.getProductId());
                    itemStmt.setInt(3, item.getQuantity());
                    itemStmt.setBigDecimal(4, item.getPrice());
                    itemStmt.addBatch();
                }
                itemStmt.executeBatch();
            }
            
            conn.commit();
            
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        }
    }
}

Best Practices Summary

For Transactions:

• Always disable auto-commit explicitly
• Use try-catch-finally or try-with-resources
• Always rollback in case of exceptions
• Choose appropriate isolation level
• Keep transactions short to reduce locking

For Batch Processing:

• Use PreparedStatement for repeated operations
• Process large datasets in chunks
• Clear the batch after execution
• Monitor memory usage for very large batches
• Combine with transactions for data integrity

Transactions ensure all-or-nothing execution with ACID properties. Use setAutoCommit(false), commit(), rollback(). Savepoints enable partial rollback with setSavepoint(). Control concurrent access with isolation levels. Batch processing improves performance for bulk operations—use addBatch(), executeBatch(). Always handle both commit and rollback scenarios. Combine transactions and batches for reliable, efficient data operations.