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)