JDBC & Databases

Transactions

15 min Lesson 7 of 13

Transactions

A transaction is a unit of work that must either succeed completely or leave the database entirely unchanged. JDBC gives you fine-grained control over transactions through three related concepts: the autoCommit flag, explicit commit() / rollback() calls, and isolation levels that govern what concurrent transactions can see of each other's work.

Why Transactions Matter

Consider a funds transfer: you debit account A and credit account B. If the application crashes between the two statements, you need the database to undo the debit automatically. Transactions provide the four ACID guarantees that make this safe:

Atomicity — all statements in a transaction commit together or none do.
Consistency — the database moves from one valid state to another.
Isolation — concurrent transactions do not interfere with each other (to a configurable degree).
Durability — once committed, data survives crashes.

autoCommit: the Default Behaviour

By default JDBC sets autoCommit = true on every new connection. Every single SQL statement is immediately committed to the database as soon as it executes — there is no opportunity to roll anything back.

To control transactions yourself you must disable this setting before issuing any DML:

Connection conn = DriverManager.getConnection(url, user, pass);
conn.setAutoCommit(false);   // disable auto-commit — now WE control when data is saved

Forget to turn off autoCommit? The moment you call conn.executeUpdate(...) with autoCommit on, the change is permanent. There is nothing to roll back. Always set autoCommit(false) at the start of any multi-statement operation you want to be atomic.

commit() and rollback()

Once autoCommit is off, you signal the database to make all pending changes permanent with conn.commit(), or to discard them with conn.rollback().

The canonical pattern wraps the operation in a try/catch/finally block:

String debit  = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
String credit = "UPDATE accounts SET balance = balance + ? WHERE id = ?";

Connection conn = null;
try {
    conn = DriverManager.getConnection(url, user, pass);
    conn.setAutoCommit(false);

    try (PreparedStatement ps1 = conn.prepareStatement(debit);
         PreparedStatement ps2 = conn.prepareStatement(credit)) {

        ps1.setBigDecimal(1, amount);
        ps1.setLong(2, fromAccountId);
        ps1.executeUpdate();

        ps2.setBigDecimal(1, amount);
        ps2.setLong(2, toAccountId);
        ps2.executeUpdate();
    }

    conn.commit();   // both rows saved atomically
    System.out.println("Transfer complete.");

} catch (SQLException e) {
    if (conn != null) {
        try {
            conn.rollback();   // neither row is saved
            System.err.println("Transfer rolled back: " + e.getMessage());
        } catch (SQLException rbEx) {
            System.err.println("Rollback failed: " + rbEx.getMessage());
        }
    }
} finally {
    if (conn != null) {
        try {
            conn.setAutoCommit(true);   // restore default for connection pool reuse
            conn.close();
        } catch (SQLException ignored) {}
    }
}

Restore autoCommit before returning a connection to a pool. If you borrow a connection from a pool (HikariCP, c3p0, etc.) and leave it in manual-commit mode, the next borrower will unknowingly be in a transaction they never started. Always reset to true — or let the pool's connectionInitSql setting do it for you.

Savepoints

A savepoint lets you roll back to a specific point within a transaction without discarding everything. This is useful when a large operation has several phases and only the last phase fails.

conn.setAutoCommit(false);

// phase 1 — insert header row
insertHeader(conn, orderId);
Savepoint afterHeader = conn.setSavepoint("after_header");   // mark this point

try {
    // phase 2 — insert line items (may fail due to stock check)
    insertLineItems(conn, orderId, items);
    conn.commit();

} catch (SQLException e) {
    conn.rollback(afterHeader);   // undo phase 2 only; header row is still pending
    // handle partial failure — maybe insert an "on backorder" row instead
    conn.commit();
}

Not all databases support savepoints (MySQL InnoDB does; some lightweight engines do not). Check DatabaseMetaData.supportsSavepoints() at startup if portability matters.

Isolation Levels

When multiple transactions run at the same time they can interfere. The SQL standard defines four concurrency anomalies and four isolation levels that progressively prevent them:

Dirty read — reading data written by an uncommitted transaction that may later roll back.
Non-repeatable read — a row you read once has different values if you read it again (another transaction committed an update in between).
Phantom read — a range query returns different rows when re-run (another transaction inserted or deleted rows).
Lost update — two concurrent transactions each read and then overwrite the same row; one write is silently lost.

JDBC exposes four isolation levels as constants on Connection:

TRANSACTION_READ_UNCOMMITTED — allows dirty reads. Rarely used; maximum throughput.
TRANSACTION_READ_COMMITTED — prevents dirty reads. The default for most databases (PostgreSQL, SQL Server, Oracle). Non-repeatable reads and phantoms still possible.
TRANSACTION_REPEATABLE_READ — prevents dirty reads and non-repeatable reads. MySQL InnoDB default. Phantoms still possible in some engines.
TRANSACTION_SERIALIZABLE — prevents all anomalies. Reads behave as if transactions ran one after another. Highest correctness, lowest throughput.

Setting an isolation level before you start your work:

conn.setAutoCommit(false);
conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

// now reads within this transaction see a consistent snapshot
ResultSet rs = stmt.executeQuery("SELECT balance FROM accounts WHERE id = 42");
rs.next();
BigDecimal balance = rs.getBigDecimal("balance");

// ... other work ...

// reading the same row again will return the SAME balance
// even if another transaction committed an update in between
rs = stmt.executeQuery("SELECT balance FROM accounts WHERE id = 42");
rs.next();
BigDecimal sameBalance = rs.getBigDecimal("balance");   // guaranteed equal under REPEATABLE_READ

conn.commit();

Higher isolation = more locking overhead. SERIALIZABLE can cause significant lock contention and deadlocks under high concurrency. Default to READ_COMMITTED and only raise the level for operations where the anomaly would actually cause a bug (financial recalculations, inventory checks, etc.).

Choosing the Right Level in Practice

A realistic rule of thumb:

READ_COMMITTED — most CRUD operations, reporting queries, web API handlers.
REPEATABLE_READ — operations that read a value and later use it in a write (read-modify-write cycles, e.g., incrementing a counter).
SERIALIZABLE — financial transfers, booking systems where double-allocation must be impossible; use only on the specific transaction that needs it, not globally.

Summary

Transactions in JDBC revolve around three controls: setAutoCommit(false) to take manual control, commit() / rollback() to finalise or undo work, and setTransactionIsolation() to declare how much visibility concurrent transactions have into each other. Savepoints add intra-transaction checkpoints for complex workflows. The key engineering skill is matching the isolation level to the anomaly you actually need to prevent, rather than defaulting to the most restrictive level everywhere.