BackendBench
Coffee

Unravel › Java concepts › JDBC and SQL mappers

Persistence

JDBC transactions and batching

By default each JDBC statement commits on its own.

🔎 Unravel this examplePrerequisites (1)

Learn these first

JDBC connections, DataSource and pools

At a glance

Mental model

A bank transfer written on a slip: take money out and put it in must both happen or neither.

What happens, in order
  1. 1conn.setAutoCommit(false); try { ps1.executeUpdate(); ps2.executeUpdate(); conn.commit(); } catch (SQLException e) { conn.rollback(); throw e; }Restore auto-commit or close the connection afterwards, because pools reuse it.
  2. 2Batch: for (row : rows) { ps.setLong(1, row.id); ps.addBatch(); } int[] counts = ps.executeBatch(); often 10 to 100 times faster than single insertsCommit every few thousand rows for huge loads.
  3. 3conn.setTransactionIsolation(...) selects what a transaction can see of others: READ_COMMITTED (typical), REPEATABLE_READ, SERIALIZABLEA Savepoint allows a partial rollback.
  4. 4prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) and ps.getGeneratedKeys() return database-generated ids
Remember
Watch out
No rollback on failureReturning a connection to the pool with auto-commit left offSingle-row inserts in a loopRemote calls inside a database transaction
Example (highlighted lines explain it)
import java.sql.*;import java.util.List;public class Loader {  static void insertAll(Connection conn, List<String> names) throws SQLException {    boolean old = conn.getAutoCommit();    conn.setAutoCommit(false);    try (PreparedStatement ps = conn.prepareStatement("INSERT INTO people(name) VALUES (?)")) {      for (String n : names) { ps.setString(1, n); ps.addBatch(); }      ps.executeBatch();      conn.commit();    } catch (SQLException e) {      conn.rollback();      throw e;    } finally {      conn.setAutoCommit(old);    }  }}
  1. line 6 uses setAutoCommit, the core of JDBC transactions and batching
  2. line 8 uses addBatch, the core of JDBC transactions and batching
  3. line 9 uses executeBatch, the core of JDBC transactions and batching
  4. line 10 uses commit, the core of JDBC transactions and batching
  5. line 12 uses rollback, the core of JDBC transactions and batching
  6. line 15 uses setAutoCommit, the core of JDBC transactions and batching
Read the full explanation (about 1 min)

By default each JDBC statement commits on its own. A transaction groups several statements so they succeed or fail together: setAutoCommit(false), run the statements, then commit() or rollback(). Batching sends many rows in one round trip.

A mental model

A bank transfer written on a slip: take money out and put it in must both happen or neither. Batching is posting a whole bag of mail at once instead of walking to the post box for each letter.

How it works

conn.setAutoCommit(false); try { ps1.executeUpdate(); ps2.executeUpdate(); conn.commit(); } catch (SQLException e) { conn.rollback(); throw e; }. Restore auto-commit or close the connection afterwards, because pools reuse it.

Batch: for (row : rows) { ps.setLong(1, row.id); ps.addBatch(); } int[] counts = ps.executeBatch(); often 10 to 100 times faster than single inserts. Commit every few thousand rows for huge loads.

conn.setTransactionIsolation(...) selects what a transaction can see of others: READ_COMMITTED (typical), REPEATABLE_READ, SERIALIZABLE. A Savepoint allows a partial rollback.

prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) and ps.getGeneratedKeys() return database-generated ids.

Going deeper

Hand-written commit/rollback code is easy to get wrong (missing rollback on an exception path, leaked connection state). That is why @Transactional in Spring and JPA exist: they apply this try/catch for you.

Long transactions hold locks and block others, and increase deadlock chances. Keep them short and never include remote calls inside.

Batch settings matter: some drivers need rewriteBatchedStatements=true (MySQL) or reWriteBatchedInserts=true (PostgreSQL) to really send one statement.

Under the hood

Auto-commit mode wraps each statement in its own transaction on the server. With it off the driver sends BEGIN implicitly, and commit()/rollback() sends the matching command; pools reset the flag when the connection returns.

Common mistakes

  • No rollback on failure.
  • Returning a connection to the pool with auto-commit left off.
  • Single-row inserts in a loop.
  • Remote calls inside a database transaction.

How Unravel recognises it

When you paste code, Unravel looks for these signs of jdbc transactions and batching. Spotting them yourself is a good habit when you read unfamiliar code:

Part of a learning path

Related concepts

@TransactionalSpringJDBC connections, DataSource and poolsPersistenceResultSet and mapping rows to objectsPersistence

Frequently asked questions

What is JDBC transactions and batching?

By default each JDBC statement commits on its own. A transaction groups several statements so they succeed or fail together: setAutoCommit(false), run the statements, then commit() or rollback(). Batching sends many rows in one round trip.

What are the most common mistakes with JDBC transactions and batching?

No rollback on failure. Returning a connection to the pool with auto-commit left off. Single-row inserts in a loop. Remote calls inside a database transaction.

How can I see JDBC transactions and batching in real code?

Paste your own Java into Unravel and it will point out where jdbc transactions and batching appears, explain how it works at the detail level you choose, and draw the flow of the code around it.