Loading published curriculum…

08 · SQL and JDBC / LESSON 35 OF 102

Manual transactions, rollback, and batches

A transaction makes several related changes succeed or fail as one unit.

Before this lesson: ResultSet mapping and generated keys

Understand the topic

JDBC connections normally begin with auto-commit enabled. Disable it for a multi-statement unit, commit after all required changes, and roll back on failure. Restore connection state before returning a pooled connection; handle rollback failures without losing the original cause.

Batching reduces round trips but does not by itself provide all-or-nothing semantics. Use a transaction when the batch must be atomic. Avoid mixing slow network calls into the same database transaction because locks and connections remain occupied.

EXAMPLE · JAVA

JDBC transaction fragment

// Dedicated Connection c, no transaction already active.
boolean previous = c.getAutoCommit();
c.setAutoCommit(false);
try {
 try (var ps = c.prepareStatement("UPDATE work_items SET done = true WHERE owner_id = ?")) {
  ps.setLong(1, 7L);
  ps.executeUpdate();
 }
 c.commit();
} catch (java.sql.SQLException ex) {
 try { c.rollback(); } catch (java.sql.SQLException rollback) { ex.addSuppressed(rollback); }
 throw ex;
} finally { c.setAutoCommit(previous); }

What to expect

Updates commit together; SQL failure triggers rollback and the original auto-commit mode is restored.

YOUR TURN

Put it into practice

  1. 01Perform two related updates in one transaction.
  2. 02Deliberately fail the second statement and verify state from a separate connection.
  3. 03Verify: Neither update persists after failure and successful execution persists both.

Pass a quiz or complete a lab to check today’s streak. Each lesson counts once per day, including when you review it later.

UNDER THE HOOD

Build the mental model

A transaction makes several related changes succeed or fail as one unit.

  1. 01JDBC connections normally begin with auto-commit enabled. Disable it for a multi-statement unit, commit after all required changes, and roll back on failure. Restore connection state before returning a pooled connection; handle rollback failures without losing the original cause.
  2. 02Batching reduces round trips but does not by itself provide all-or-nothing semantics. Use a transaction when the batch must be atomic. Avoid mixing slow network calls into the same database transaction because locks and connections remain occupied.
  3. 03Trace the example: Updates commit together; SQL failure triggers rollback and the original auto-commit mode is restored.

A mistake worth avoiding

The first update remains after the second fails.

DEBUGGING ROOM

Something broke. Where do you look?

The first update remains after the second fails.

Before revealing the diagnosis, write down your first investigation step.

CHECK YOUR UNDERSTANDING

Does addBatch alone make a batch atomic?

Choose one answer

BUILD IT YOURSELF

Perform two related updates in one transaction.

  1. 1.Perform two related updates in one transaction.
  2. 2.Deliberately fail the second statement and verify state from a separate connection.
  3. 3.Reproduce this failure, then repair it: The first update remains after the second fails.

Acceptance checklist

Lab completion is your self-report. Knowledge checks do not certify project quality.

RETRIEVE IT FROM MEMORY

Does addBatch alone make a batch atomic?

Revisit this lesson in three days after a successful check or lab.

Ready for the next step?

Neither update persists after failure and successful execution persists both.

Reading is separate from passing a knowledge check or completing a lab.

Go deeper · official references