Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 119 ยท Databases with JDBC

Transactions with JDBC

Learn JDBC transactions in Java - autocommit, commit and rollback, a money transfer that fails safely, savepoints, isolation levels and the anomalies they prevent.

Some changes only make sense together. Moving money from one account to another is two updates: take 100 from Alice, add 100 to Bob. If the program crashes between them, the bank has lost 100. A transaction groups statements so the database applies all of them or none of them, no matter what goes wrong in between.

In this lesson you will see the damage a half-finished change does, then fix it with commit() and rollback(). You will also use savepoints to undo part of a transaction, and learn what isolation levels protect you from when several connections work at the same time. Every example runs against a real H2 database. If Connection and PreparedStatement are new to you, read CRUD with JDBC first.

ACID in one minute

Databases promise four properties for a transaction, known as ACID:

Property Meaning
Atomicity All statements take effect, or none do. There is no "half done".
Consistency The transaction moves the database from one valid state to another. Constraints such as CHECK and foreign keys still hold at the end.
Isolation Transactions running at the same time don't see each other's unfinished work (how strictly depends on the isolation level).
Durability Once commit returns, the change survives a crash or power loss.

JDBC gives you control over the first one directly, and over the third one through isolation levels. The database takes care of consistency checks and durability.

Autocommit: one statement, one transaction

A fresh JDBC connection is in autocommit mode. Every executeUpdate is its own tiny transaction that is committed the moment it finishes. That is convenient for single statements and dangerous for anything that needs several.

Here is a transfer written the naive way. The second update targets an account that doesn't exist, so it changes zero rows and the code throws. Look at the balances afterwards.

Java A transfer in autocommit mode loses money

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Alice now has 400 and Bob still has 200: 100 has vanished. The withdrawal was committed before the deposit even ran, and nothing can take it back.

commit and rollback

To group statements, switch autocommit off. From then on, every statement on that connection joins the current transaction, and nothing is permanent until you call commit(). If anything fails, rollback() throws all of the transaction's changes away.

The standard shape looks like this:

Java
connection.setAutoCommit(false);
try {
    // ... several statements ...
    connection.commit();
} catch (SQLException | RuntimeException e) {
    connection.rollback();
    throw e;
} finally {
    connection.setAutoCommit(true);
}

Three details matter:

  • Roll back on every failure, not just SQLException. A NullPointerException in your mapping code halfway through is just as fatal to the data.
  • Rethrow (or report) the exception. A transaction that silently rolls back looks like success to the caller.
  • Restore autocommit in finally. This becomes important with connection pools, where the next borrower gets the same physical connection (see Connection Pooling and DataSource).

Writing that boilerplate everywhere invites mistakes, so it is common to wrap it in a small helper that takes the work as a lambda. The next example does exactly that. It also lets the database enforce the rules: a CHECK (balance >= 0) constraint makes an overdraft fail, and the transaction turns that failure into "nothing happened".

Java Safe transfers with commit and rollback

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Only the first transfer took effect: Alice ends with 350 and Bob with 350. The second failed with our own SQLException (its SQLState is null because we created it), and the third failed with SQLState 23513, H2's code for a violated CHECK constraint. In both cases the withdrawal that had already run was undone. The total money in the bank is still 700.

Let constraints do the guarding. A CHECK, UNIQUE or foreign key in the schema protects the data even when a bug, a second application or a manual SQL session forgets the rules. Your Java code then only has to roll back when the database says no.

What if you forget to commit?

If autocommit is off and the connection is closed without commit() or rollback(), the JDBC specification leaves the result to the driver. Most drivers (including H2, PostgreSQL and MySQL) roll back, but Oracle's driver has traditionally committed instead, so never rely on either behavior. Always end a transaction explicitly.

Savepoints: undoing part of a transaction

Sometimes one step of a transaction is optional. Imagine placing an order where a gift-wrap row is nice to have but must not cancel the whole order if it fails. A savepoint marks a position inside the transaction. rollback(savepoint) undoes everything after that mark and keeps everything before it; the transaction stays open.

Java Rolling back to a savepoint

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The keyboard and mouse survive. The gift wrap was inserted successfully, but it came after the savepoint, so it was undone together with the failed sticker. Savepoints can be released early with releaseSavepoint(sp), and every savepoint disappears when the transaction commits or fully rolls back. They are handy but rare; if you find yourself nesting many of them, consider splitting the work into separate transactions instead.

Isolation levels and concurrency anomalies

So far, one connection did all the work. Real servers run hundreds of transactions at once, and the question becomes: what may transaction B see of transaction A's changes? The SQL standard describes three classic anomalies:

  • Dirty read: B reads a row that A changed but has not committed. If A rolls back, B acted on data that never officially existed.
  • Non-repeatable read: B reads a row twice and gets different values, because A committed a change in between.
  • Phantom read: B runs the same query twice and gets a different set of rows, because A inserted or deleted matching rows in between.

You choose how much protection you want with connection.setTransactionIsolation(level), ideally before the transaction starts:

Level (Connection constant) Dirty read Non-repeatable read Phantom read
TRANSACTION_READ_UNCOMMITTED possible possible possible
TRANSACTION_READ_COMMITTED prevented possible possible
TRANSACTION_REPEATABLE_READ prevented prevented possible (by the standard)
TRANSACTION_SERIALIZABLE prevented prevented prevented

The standard only defines the minimum protection. Real databases often do more: PostgreSQL treats read uncommitted as read committed, and H2, PostgreSQL and MySQL's InnoDB implement repeatable read with a snapshot, so phantoms don't appear there either. The defaults also differ: H2, PostgreSQL, Oracle and SQL Server use read committed, while MySQL InnoDB uses repeatable read.

The next example opens two connections to the same in-memory database (the DB_CLOSE_DELAY=-1 setting keeps it alive between connections). Connection writer changes a balance and inserts a row; connection reader looks at the data before and after the commit, once for each level.

Java What each isolation level lets you see

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Read the output line by line:

  • Read uncommitted saw 50 while the writer had not committed: a dirty read.
  • Read committed saw only committed data, but the value changed from 100 to 50 inside one transaction (a non-repeatable read), and the row count jumped from 1 to 2 (a phantom).
  • Repeatable read and serializable kept showing the snapshot from the start of the reader's transaction: 100 and one row, even after the writer committed.

Stronger isolation is not free. Databases implement it with snapshots, locks or both, so higher levels can mean more waiting, more memory and more transactions that fail with a "could not serialize" error that you must retry. Most applications use read committed and add targeted protection where it matters.

The lost update and optimistic locking

One anomaly survives read committed and catches many developers: the lost update. Two users load the same product, both edit it, and both save. The second save silently overwrites the first, because each UPDATE just writes the values it had in memory.

Two common fixes:

  • Pessimistic locking: SELECT ... FOR UPDATE locks the rows you read, so other writers wait until you commit. Simple, but locks held for a long time hurt throughput.
  • Optimistic locking: add a version column, and make every update say "only if the version is still what I read". If zero rows change, someone else got there first. JPA does this automatically with @Version (covered in Entity Lifecycle and Transactions).

Here is optimistic locking by hand. Two "users" read version 1 of the same row; the first save wins and bumps the version, and the second save notices.

Java Optimistic locking with a version column

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Ben's save returns false instead of quietly undoing Ana's price change. A real application would reload the product, show Ben what changed and let him try again.

Transaction pitfalls in real code

  • Transactions belong to a connection. Every statement in the transaction must use the same Connection object. If your code opens a new connection per DAO method, those methods can never share a transaction. The usual fix is to let the service layer own the connection and pass it down; see The DAO and Repository Patterns.
  • Keep transactions short. Never wait for user input, a slow HTTP call or a file upload while a transaction is open. Locks and snapshots are held until you commit.
  • Don't swallow exceptions inside the transaction. Catching an SQLException, logging it and carrying on means you may commit a half-done change. Some databases (PostgreSQL) even refuse every further statement until you roll back.
  • DDL may commit implicitly. In MySQL and Oracle, CREATE TABLE or ALTER TABLE commits the current transaction. Don't mix schema changes into business transactions.
  • Frameworks do the same thing for you. Spring's @Transactional runs exactly the commit/rollback pattern from this lesson around your method, using one connection per transaction. Knowing the plain JDBC version makes its behavior much less mysterious.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A new JDBC connection runs two executeUpdate calls and the second one throws. What happened to the first update?

    In autocommit mode every statement is committed as soon as it finishes, so the first update is permanent before the second one runs.
  2. 2. With autocommit off, you insert rows A and B, set a savepoint, insert C, call rollback(savepoint), insert D and commit. Which rows are saved?

    Rolling back to the savepoint undoes only C. The transaction stays open, so D joins it, and the commit saves A, B and D.
  3. 3. Inside one read committed transaction you read a row twice and get two different values. Which anomaly is this?

    Another transaction committed a change between your two reads. Read committed prevents dirty reads but allows non-repeatable reads.
  4. 4. An optimistic-locking update ... WHERE id = ? AND version = ? returns 0. What does that usually mean?

    The version no longer matches the one you loaded, so another writer saved first. Reload the row and let the user decide.
  5. 5. Why should the transaction helper catch RuntimeException as well as SQLException?

    The data is just as inconsistent after a NullPointerException as after an SQLException. Roll back on every failure, then rethrow.

Summary

  • A transaction makes several statements atomic: all of them take effect, or none do.
  • JDBC connections start in autocommit mode, where every statement commits on its own. Call setAutoCommit(false) to group statements.
  • End every transaction with commit() on success or rollback() on any failure, rethrow the error, and restore autocommit in finally. A small inTransaction helper keeps this in one place.
  • Savepoints let you roll back part of an open transaction.
  • Isolation levels trade safety for concurrency: read committed (the common default) prevents dirty reads; repeatable read and serializable also prevent non-repeatable reads and phantoms.
  • Read committed does not prevent lost updates; use SELECT ... FOR UPDATE or a version column (optimistic locking).

Next: Statements, Stored Procedures and ResultSet Types.