Skip to content
javahacks.dev
Hibernate

JDBC Transactions Managed Manually With Commit and Rollback

Manage JDBC transactions by hand: turn off auto commit, commit and roll back, use savepoints, set isolation levels and keep the connection handling safe.

6 min read
Summarize in AI
Flat illustration of a commit graph with a branch that merges back, with the Hibernate, OpenJDK, GitHub and GitLab logos.

By default a JDBC connection is in auto-commit mode: every statement is its own transaction and commits as soon as it executes. That is fine for one statement and wrong for two that must succeed together. Moving money between accounts as two separate commits means a crash between them leaves the money gone. Controlling the transaction by hand takes a few lines, and the failures come from the details around them: a connection returned to the pool still in the wrong mode, an exception that skips the rollback, isolation levels that mean different things in different databases. This post shows the pattern on JDK 25, then isolation levels, savepoints, DDL and batches.

The pattern

src/main/java/com/example/bank/TransferDao.javajava
void transfer(DataSource ds, long from, long to, BigDecimal amount) throws SQLException {
    try (Connection con = ds.getConnection()) {
        boolean previous = con.getAutoCommit();
        con.setAutoCommit(false);
        try {
            debit(con, from, amount);
            credit(con, to, amount);
            con.commit();
        } catch (SQLException | RuntimeException e) {
            con.rollback();
            throw e;
        } finally {
            con.setAutoCommit(previous);
        }
    }
}

setAutoCommit(false) starts a transaction that lasts until commit() or rollback(). Both statements share the connection, so they belong to the same transaction; passing the Connection to debit and credit is how that is expressed. Opening a second connection inside credit would put its work in a different transaction, outside the rollback. The catch rolls back on any failure and rethrows, so the caller still sees the error. Catching RuntimeException as well as SQLException matters: a NullPointerException thrown between the statements must not leave the transaction open.

The try-with-resources closes the connection, which for a pooled connection returns it to the pool. An exception during rollback() itself would hide the original; for production code, add the rollback exception as suppressed: catch (Exception e) { try { con.rollback(); } catch (SQLException r) { e.addSuppressed(r); } throw e; }.

The first tutorial on the connection basics is connecting to a database, and statements built from user input need the care in preventing SQL injection.

Do not leave the connection dirty

A pooled connection is reused. If you change its state and do not restore it, the next borrower inherits the change. The finally above restores auto-commit, because a connection left with auto-commit off will not commit the next borrower's single statements, and their updates vanish when the connection is returned and rolled back by the pool. This is a notorious bug: writes that "do not stick", seen only under load when the dirty connection is reused. Restore the same way for the isolation level, read-only flag and catalog if you changed them.

Also never keep a transaction open across a user interaction or a slow remote call. An open transaction holds locks and a connection; a connection leak looks like the pool running dry, which is diagnosed in JDBC connection leaks. Keep the span from setAutoCommit(false) to commit() short and free of network calls.

Frameworks do this for you: Spring's @Transactional is this pattern, applied by a proxy, and its pitfalls are in @Transactional not working. Understanding the manual form tells you what the annotation does underneath.

Isolation levels

java
con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

The isolation level decides what a transaction sees of concurrent changes. JDBC defines four, from weakest to strongest: READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ and SERIALIZABLE. The anomalies they prevent are dirty reads (seeing uncommitted data), non-repeatable reads (a row changes between two reads in one transaction) and phantoms (new rows appear in a repeated query).

The names promise more uniformity than databases deliver. PostgreSQL's default is READ_COMMITTED, and its REPEATABLE_READ is a snapshot isolation that also prevents phantoms, while SERIALIZABLE detects conflicts and aborts a transaction with a serialization failure (SQLState 40001) that the application must retry. MySQL's InnoDB defaults to REPEATABLE_READ with next-key locking. Oracle has READ_COMMITTED and SERIALIZABLE only. SQL Server's default is READ_COMMITTED with locking unless snapshot isolation is turned on. READ_UNCOMMITTED is treated as READ_COMMITTED by PostgreSQL. So test on the database you deploy, and read its documentation for the level you pick.

A transaction at a stricter level needs retry logic for serialization failures and deadlocks (SQLState 40001 and 40P01 in PostgreSQL): catch them, roll back, and run the whole unit again, a bounded number of times. Retrying a failed commit without re-reading the data would write a decision based on stale reads. The default READ_COMMITTED plus explicit locking (select ... for update) on the rows you will change is often simpler than raising the isolation for the whole transaction.

Savepoints

A savepoint marks a point inside a transaction to which you can roll back without abandoning everything:

java
Savepoint sp = con.setSavepoint("before-optional");
try {
    insertLoyaltyBonus(con, customerId);
} catch (SQLException e) {
    con.rollback(sp);                  // undo the bonus only
}
insertOrder(con, order);
con.commit();                          // order is committed

Rollback to the savepoint undoes the work after it and keeps the earlier work in the transaction. Release it with releaseSavepoint when it is no longer needed. In PostgreSQL a failed statement aborts the whole transaction, so after any error the only way to continue is to roll back to a savepoint taken before it; without one, every later statement fails with "current transaction is aborted". That makes savepoints the tool for "try this, and carry on if it fails" inside one transaction, such as skipping a bad row in an import. They cost a little per use, so do not take one per row of a million-row load unless you must.

Autocommit and DDL

Statements that change the schema, such as create table and alter table, behave differently across databases. MySQL and Oracle commit the current transaction implicitly before and after DDL, so a failed migration cannot be rolled back as a whole. PostgreSQL and SQL Server support transactional DDL, so a migration script inside a transaction either applies completely or not at all. Do not mix data changes and DDL in one hand-managed transaction on a database that commits implicitly, because the commit happens in the middle without warning. Migration tools such as Flyway and Liquibase know these differences and wrap each migration accordingly.

Batch with a transaction

Inserting many rows is faster as a batch inside one transaction, instead of one commit per row:

java
con.setAutoCommit(false);
try (PreparedStatement ps = con.prepareStatement("insert into reading(sensor, ts, value) values (?,?,?)")) {
    int n = 0;
    for (Reading r : readings) {
        ps.setLong(1, r.sensor()); ps.setTimestamp(2, Timestamp.from(r.ts())); ps.setBigDecimal(3, r.value());
        ps.addBatch();
        if (++n % 1000 == 0) ps.executeBatch();
    }
    ps.executeBatch();
    con.commit();
}

Executing every thousand rows keeps the batch from growing without bound; the commit at the end makes the whole load atomic. For a load too big for one transaction, commit per chunk and record progress so a restart can resume. Batch options and driver flags (like reWriteBatchedInserts in the PostgreSQL driver) are in JDBC batch updates. Work that spans several services cannot use one database transaction at all, and needs a pattern like sagas.

Where this does not apply

With a pool and a framework, you rarely write this by hand; use it for scripts, small tools and the cases where the annotation does not fit. The API is documented in the Connection Javadoc and the JDBC transactions tutorial.

Share

Keep reading

Get new posts by email

We send one email when a new post is published. No spam.

Unsubscribe in one click. No spam, ever.