Using transactions in JDBC is essential when you need to ensure that a group of SQL statements either all succeed or all fail together (maintaining Atomicity).

By default, a JDBC Connection is in auto-commit mode, meaning every single SQL statement is treated as its own transaction and committed immediately.

To manage transactions manually, follow these three main steps:

1. Disable Auto-Commit

The first step is to tell the connection not to commit automatically after every execution.

connection.setAutoCommit(false);

2. Perform Your Database Operations

Execute your SQL statements (inserts, updates, deletes). If any of these throw an exception, you should catch it to handle the failure.

3. Commit or Rollback

• commit(): If everything went well, save the changes permanently.
• rollback(): If an error occurred, undo all changes made since the last commit.

---

Basic Example

Here is a clean pattern using a try-with-resources block for the connection and a nested try-catch for the transaction logic:

try (Connection conn = DriverManager.getConnection(url, user, pass)) {
    // Step 1: Disable auto-commit
    conn.setAutoCommit(false);

    try (PreparedStatement pstmt1 = conn.prepareStatement("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
         PreparedStatement pstmt2 = conn.prepareStatement("UPDATE accounts SET balance = balance + 100 WHERE id = 2")) {

        // Execute operations
        pstmt1.executeUpdate();
        pstmt2.executeUpdate();

        // Step 3a: Commit changes
        conn.commit();
        System.out.println("Transaction committed successfully!");

    } catch (SQLException e) {
        // Step 3b: Rollback changes if something goes wrong
        conn.rollback();
        System.err.println("Transaction rolled back due to error.");
        e.printStackTrace();
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Important Tips:

• Always use rollback() in the catch block: If you don’t roll back on failure, the connection might hold onto locks or leave the session in an inconsistent state.
• Savepoints: If you have a very long transaction and want to roll back only a part of it, you can use conn.setSavepoint().
• Transaction Isolation: You can control how “isolated” your transaction is from others using conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE), though the default is usually enough for standard applications.