Java JDBC transactions: atomic updates and rollback

Last updated: 5 Oct 20264 min read

tutorial

A JDBC transaction groups database changes into a commit or rollback boundary on a connection with auto-commit disabled.

Java 8+. The JDBC programs need the H2 driver on the runtime classpath.

Treat related writes as one operation

A stock transfer decreases one location and increases another. If only the first write commits, the application loses inventory. The sample uses one connection and commits only after both writes report exactly one updated row.

The source update checks available stock in the WHERE condition. Checking stock in a separate earlier query would create a race window. A failed destination update causes rollback, which undoes the first change inside the same transaction.

Add com.h2database:h2:2.3.232 for this disposable test database. Real databases have their own locking and isolation details; the demonstrated invariant must be retested against the production engine.

Give the transaction one owner

The helper requires auto-commit to be off and owns the operation’s commit or rollback. Call it with a connection dedicated to this transaction. It is unsuitable inside a larger transaction whose caller expects to make the final commit decision.

If rollback itself fails, preserve that error as suppressed on the original failure. Losing the first failure makes diagnosis harder. A connection with uncertain transaction state should not be casually returned to a pool.

A database transaction does not atomically publish an email or charge an external service. Those side effects need another delivery design, such as an outbox and retry policy, beyond this two-table update.

Working program

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class StockTransfer {
    static void transfer(Connection connection, String source, String target, int units)
            throws SQLException {
        if (units <= 0) throw new IllegalArgumentException("Units must be positive");
        if (connection.getAutoCommit()) throw new IllegalStateException("Transaction required");
        try (PreparedStatement debit = connection.prepareStatement(
                    "UPDATE stock SET units = units - ? WHERE site = ? AND units >= ?");
             PreparedStatement credit = connection.prepareStatement(
                    "UPDATE stock SET units = units + ? WHERE site = ?")) {
            debit.setInt(1, units); debit.setString(2, source); debit.setInt(3, units);
            if (debit.executeUpdate() != 1) throw new SQLException("Insufficient stock");
            credit.setInt(1, units); credit.setString(2, target);
            if (credit.executeUpdate() != 1) throw new SQLException("Unknown target");
            connection.commit();
        } catch (SQLException failure) {
            try { connection.rollback(); }
            catch (SQLException rollbackFailure) { failure.addSuppressed(rollbackFailure); }
            throw failure;
        }
    }
    public static void main(String[] args) throws SQLException {
        try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:stock")) {
            try (PreparedStatement schema = connection.prepareStatement(
                    "CREATE TABLE stock(site VARCHAR PRIMARY KEY, units INT CHECK(units >= 0))")) {
                schema.executeUpdate();
            }
            try (PreparedStatement seed = connection.prepareStatement(
                    "INSERT INTO stock VALUES ('north', 10), ('south', 2)")) {
                seed.executeUpdate();
            }
            connection.setAutoCommit(false);
            transfer(connection, "north", "south", 3);
            try { transfer(connection, "north", "missing", 2); }
            catch (SQLException expected) { System.out.println("failed transfer rolled back"); }
            try (PreparedStatement query = connection.prepareStatement("SELECT site, units FROM stock ORDER BY site");
                 ResultSet rows = query.executeQuery()) {
                while (rows.next()) System.out.println(rows.getString(1) + "=" + rows.getInt(2));
            }
            connection.rollback();
        }
    }
}

Output

failed transfer rolled back
north=7
south=5

Cost and design choices

The transaction changes two rows, but elapsed cost includes lock waits and log persistence. Deadlocks and serialization failures may require a bounded retry of the whole operation. Retrying an individual debit after a partially understood failure can duplicate work.

The sample uses int quantities and a constrained database column. Check large quantities and destination overflow in the actual schema. Commit success also does not eliminate later application delivery failures.