Java JDBC Transactions

A transaction groups several SQL statements into one all-or-nothing unit: either every change commits, or none does. The classic example is a bank transfer — debit one account, credit another — where applying one without the other would lose or invent money. JDBC controls transactions through the Connection object.

This chapter covers how to open a transaction by disabling auto-commit, how commit() and rollback() work, how savepoints let you undo part of a transaction, and how isolation levels trade consistency for concurrency.

Auto-commit is on by default

A fresh connection runs in auto-commit mode: each statement commits the instant it finishes. That is fine for single statements but wrong for multi-statement units. To open a transaction, turn auto-commit off:

conn.setAutoCommit(false);   // begin a transaction
try {
  // ... several statements ...
  conn.commit();             // make all changes permanent
} catch (SQLException e) {
  conn.rollback();           // undo everything since the last commit
  throw e;
} finally {
  conn.setAutoCommit(true);  // restore default for the pool
}

commit and rollback

commit() makes every change since the transaction began permanent and visible to others. rollback() discards them all. The golden rule: commit on success, rollback on any exception. Forgetting the rollback leaves the connection holding locks and a half-finished transaction.

A worked transfer

The transfer uses two PreparedStatement objects, runs both UPDATEs, and only commits once both have executed. If either throws, the catch rolls everything back so no money is created or lost:

conn.setAutoCommit(false);
try (PreparedStatement debit = conn.prepareStatement(
        "UPDATE acct SET bal = bal - ? WHERE id = ?");
     PreparedStatement credit = conn.prepareStatement(
        "UPDATE acct SET bal = bal + ? WHERE id = ?")) {
  debit.setBigDecimal(1, amount); debit.setInt(2, fromId); debit.executeUpdate();
  credit.setBigDecimal(1, amount); credit.setInt(2, toId); credit.executeUpdate();
  conn.commit();
} catch (SQLException e) {
  conn.rollback();
  throw e;
}

Savepoints: partial rollback

A Savepoint is a marker inside a transaction you can roll back to, undoing later work while keeping earlier work. This is useful when one optional step inside a larger unit might fail but you do not want to abandon the whole transaction:

Savepoint sp = conn.setSavepoint("afterDebit");
// ... risky step ...
conn.rollback(sp);   // undo back to the savepoint, not the whole transaction

Two rules to remember: rolling back to a savepoint does not end the transaction — you still need commit() or a full rollback() afterwards — and you should release savepoints you no longer need with conn.releaseSavepoint(sp) to free database resources. Savepoints only exist while auto-commit is off.

Isolation levels

setTransactionIsolation(...) trades consistency for concurrency. From weakest to strongest: READ_UNCOMMITTED (sees others' uncommitted "dirty" rows), READ_COMMITTED (the common default), REPEATABLE_READ (re-reads see the same rows), and SERIALIZABLE (transactions behave as if run one at a time). Stronger levels prevent more anomalies but allow less parallelism.

The anomalies each level guards against, in order:

• Dirty read — reading a row another transaction has changed but not committed. Prevented from READ_COMMITTED upward.
• Non-repeatable read — re-reading the same row and getting a different value because another transaction committed a change in between. Prevented from REPEATABLE_READ upward.
• Phantom read — re-running the same query and getting extra rows because another transaction inserted matching rows. Prevented only by SERIALIZABLE.

Set the level before the work begins, and check what your database actually supports with DatabaseMetaData.supportsTransactionIsolationLevel(...) — not every driver honors every level.

A worked example: isolation levels and the unit of work

This program prints the four isolation-level constants in strength order, then models a transfer as an atomic unit — staging two updates and either committing both or rolling both back — mirroring the setAutoCommit/commit/rollback cycle without a live database.

What to take from the run:

• The isolation constants ascend with strength: READ_UNCOMMITTED (1), READ_COMMITTED (2), REPEATABLE_READ (4), SERIALIZABLE (8). You pass one to setTransactionIsolation to choose how much concurrency anomaly you will tolerate.
• The transfer stages both updates before committing. That is the essence of a transaction: the two UPDATEs are one unit, and the commit() only happens once both are ready.
• The commit() step here moves both statements from pending to committed together — modelling how the database makes all changes durable and visible at the same instant, never half.
• The catch clears pending — the stand-in for rollback(). The takeaway is the discipline: any exception inside the transaction must lead to a rollback, or you leave the database in a half-applied state.
• transfer applied: true reflects a successful commit. Flip the logic so the guard fails and you would see a rollback with nothing applied — exactly the all-or-nothing guarantee a transaction exists to give you.

Practice

Related topics

• JDBC Connection — where auto-commit, commit, and rollback live.
• PreparedStatement — the safe way to run the parameterized updates inside a transaction.
• Batch Processing — group many statements; batches and transactions often work together.
• DatabaseMetaData — query which isolation levels your driver supports.