Transaction management and exceptions

A transaction is a group of SQL operations that must all succeed or all fail (Atomicity). JDBC uses auto-commit mode by default, where each statement is committed instantly.

Transaction Management Steps:

1. Disable auto-commit: conn.setAutoCommit(false);

2. Perform SQL operations

3. Commit: conn.commit(); if all succeed

4. Rollback: conn.rollback(); if any fails

Code Snippet:

try (Connection conn = DriverManager.getConnection(url, user, pass)) {
    conn.setAutoCommit(false);

    try (Statement stmt = conn.createStatement()) {
        stmt.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
        stmt.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
        conn.commit();
        System.out.println("Transaction successful.");
    } catch (SQLException e) {
        conn.rollback();
        System.err.println("Transaction failed and rolled back.");
    }

} catch (SQLException e) {
    e.printStackTrace();
}Code language: JavaScript (javascript)

Exception Handling Tips for Transactions

• Always rollback if any exception occurs within the transaction.

• Use try-with-resources for automatic closing of statements and result sets.

• Nested try-catch helps handle rollback exceptions separately.

• Log all exceptions for future diagnostics.

• Use Savepoints for partial rollback (conn.setSavepoint()).

When to Use Transactions

• Transferring funds between accounts

• Booking and payment systems

• Inserting records into multiple tables

• Deleting dependent entities (e.g., orders and order items)