Transactions in JDBC

A transaction is a group of one or more SQL statements that must succeed or fail together as a single unit — for example, debiting one bank account and crediting another. JDBC gives you direct control over transaction boundaries through the Connection interface, letting you decide exactly when changes become permanent.

Auto-Commit Mode

By default, a JDBC Connection is in auto-commit mode: every individual SQL statement is committed to the database immediately after it executes, as its own implicit transaction. This is convenient for single, independent statements, but it is dangerous for a sequence of related statements — if the second statement fails after the first has already been auto-committed, the database is left in an inconsistent, partially-updated state.

Manual Transaction Control

Calling conn.setAutoCommit(false) switches the connection into manual mode: statements you execute afterward are held as part of one open transaction until you explicitly call conn.commit() (making all of them permanent together) or conn.rollback() (undoing all of them as if none had happened). This is essential whenever multiple statements must succeed or fail as a single atomic unit.

• conn.setAutoCommit(false) — start a manual transaction.
• conn.commit() — make all statements since the last commit/rollback permanent.
• conn.rollback() — discard all statements since the last commit/rollback.
• conn.setAutoCommit(true) — return to auto-commit mode once the manual transaction is done, if reusing the connection.

The Standard try/catch/rollback Pattern

The conventional shape is: disable auto-commit, run the statements inside a try block, call commit() at the end of the try block if everything succeeded, and call rollback() in a catch block if any statement threw an exception. This guarantees the database never ends up with only half of a logically related change applied.

Not Every Failure Is an Exception

An UPDATE whose WHERE clause matches nothing is not an error in SQL. Transferring money to account 99, which does not exist, runs both statements without any exception: the debit changes one row, the credit changes zero, and a naive transfer commits, so the money simply disappears. Check the count returned by executeUpdate() and throw when it is not what the operation needs, so the rollback path runs. Database constraints help as well: a CHECK (balance >= 0) turns an overdraft into an exception instead of a negative balance.

Savepoints: Undoing Part of a Transaction

Sometimes only the last step of a transaction is optional. conn.setSavepoint("name") marks a point inside the open transaction, and conn.rollback(savepoint) undoes everything done after that mark while keeping what came before it. The transaction is still open afterwards, so you still have to call commit(). A typical use is a batch import where one bad record should be skipped without throwing away the records that were already processed.

Isolation Levels in Brief

While your transaction is open, other connections are working too. The isolation level decides how much of their uncommitted or newly committed work you can see. JDBC defines four levels, from TRANSACTION_READ_UNCOMMITTED to TRANSACTION_SERIALIZABLE, and you set one with conn.setTransactionIsolation(...). Most databases, including PostgreSQL, Oracle, SQL Server and H2, default to READ_COMMITTED: you never see another transaction's uncommitted changes, but re-reading a row later in the same transaction can return a newer value. MySQL's InnoDB defaults to REPEATABLE_READ. Raise the level only where a calculation really depends on data not changing underneath it, because higher levels mean more locking or more retries.

Examples

// accounts: Asha = 500.00, Ravi = 100.00, with CHECK (balance >= 0)
// Auto-commit is on (the default). Credit first, then debit 800 from Asha:
update(conn, "UPDATE accounts SET balance = balance + ? WHERE id = ?", "800.00", 2);
update(conn, "UPDATE accounts SET balance = balance - ? WHERE id = ?", "800.00", 1); // violates the CHECK

start: Asha=500.00  Ravi=100.00
failed: Check constraint violation: "CONSTRAINT_AF: "; SQL statement: ...
after auto-commit failure: Asha=500.00  Ravi=900.00

(The credit was committed on its own before the debit failed:
 800.00 was created out of nothing.)

static void transfer(Connection conn, int from, int to, BigDecimal amount) throws SQLException {
    boolean oldAutoCommit = conn.getAutoCommit();
    conn.setAutoCommit(false);
    try (PreparedStatement debit = conn.prepareStatement(
                 "UPDATE accounts SET balance = balance - ? WHERE id = ?");
         PreparedStatement credit = conn.prepareStatement(
                 "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
        debit.setBigDecimal(1, amount);
        debit.setInt(2, from);
        if (debit.executeUpdate() != 1) throw new SQLException("No account " + from);

        credit.setBigDecimal(1, amount);
        credit.setInt(2, to);
        if (credit.executeUpdate() != 1) throw new SQLException("No account " + to);

        conn.commit();
        System.out.println("Transfer of " + amount + " committed.");
    } catch (SQLException e) {
        conn.rollback();
        System.out.println("Transfer of " + amount + " rolled back: " + e.getMessage());
        throw e;
    } finally {
        conn.setAutoCommit(oldAutoCommit);    // leave the connection as we found it
    }
}

// Starting from Asha=500.00, Ravi=100.00:
transfer(conn, 1, 2, new BigDecimal("200.00"));
transfer(conn, 1, 2, new BigDecimal("800.00"));   // more than Asha has
transfer(conn, 1, 99, new BigDecimal("50.00"));   // account 99 does not exist

Transfer of 200.00 committed.
after transfer 200: Asha=300.00  Ravi=300.00
Transfer of 800.00 rolled back: Check constraint violation: "CONSTRAINT_AF: "; SQL statement: ...
after failed transfer 800: Asha=300.00  Ravi=300.00
Transfer of 50.00 rolled back: No account 99
after transfer to missing account: Asha=300.00  Ravi=300.00

conn.setAutoCommit(false);
try (Statement st = conn.createStatement()) {
    st.executeUpdate("UPDATE accounts SET balance = balance - 10 WHERE id = 1");   // monthly fee
    Savepoint afterFee = conn.setSavepoint("after_fee");
    try {
        st.executeUpdate("INSERT INTO audit_log VALUES ('fee charged')");
        st.executeUpdate("UPDATE accounts SET balance = balance - 1000 WHERE id = 1"); // too big
    } catch (SQLException e) {
        System.out.println("Purchase failed: " + e.getMessage());
        conn.rollback(afterFee);      // undo only what came after the savepoint
    }
    conn.commit();                    // the fee is still part of the transaction
}
conn.setAutoCommit(true);

Purchase failed: Check constraint violation ...
Balance: 290.00
Audit rows: 0

(Balance started at 300.00. The fee survived the partial rollback; the audit
 row was written after the savepoint, so it was undone together with the purchase.)

Exercise

Using the lesson's Why auto-commit is dangerous for a two-step change (H2 2.4.240) example, create a small implementation or design sketch that demonstrates By default a JDBC Connection auto-commits every statement as its own transaction.. Add a test case or scenario that would catch Leaving auto-commit on for a multi-statement operation, so a failure midway leaves the database partially updated., and describe the expected result.

Quick quiz

Interview questions