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.

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

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:

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

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.

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