JDBC Transactions and Batch Operations

Auto commit

Connection connection = dataSource.getConnection();
System.out.println(connection.getAutoCommit());   // true by default

By default every statement commits on its own. That is convenient for a single read and wrong for anything that must happen as a unit.

A transaction

try (Connection connection = dataSource.getConnection()) {
    connection.setAutoCommit(false);

    try (PreparedStatement debit = connection.prepareStatement(
                 "UPDATE accounts SET balance = balance - ? WHERE id = ?");
         PreparedStatement credit = connection.prepareStatement(
                 "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {

        debit.setBigDecimal(1, amount);
        debit.setLong(2, fromId);
        debit.executeUpdate();

        credit.setBigDecimal(1, amount);
        credit.setLong(2, toId);
        credit.executeUpdate();

        connection.commit();          // both, or neither

    } catch (SQLException e) {
        connection.rollback();
        throw e;
    } finally {
        connection.setAutoCommit(true);   // important when the connection is pooled
    }
}

Restore auto commit before the connection returns to the pool. A pooled connection left with auto commit off will silently swallow the next caller writes until something commits.

ACID, briefly

Isolation levels

connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

• Dirty read - reading a change another transaction has not committed.
• Non repeatable read - reading the same row twice and getting different values.
• Phantom read - the same query returning a different set of rows.

Higher isolation costs concurrency. Leave the database default unless a specific anomaly has been observed.

Savepoints

connection.setAutoCommit(false);

insertNote(connection, note);
Savepoint afterNote = connection.setSavepoint("afterNote");

try {
    insertTags(connection, note.tags());
} catch (SQLException e) {
    connection.rollback(afterNote);    // keep the note, drop the tags
    logger.warning("Tags skipped: " + e.getMessage());
}

connection.commit();

A savepoint allows a partial rollback within one transaction. Useful for an optional step that should not lose the work already done.

Batch operations

// Slow: one network round trip per row
for (Note note : notes) {
    statement.setString(1, note.title());
    statement.executeUpdate();
}

// Fast: one round trip per batch
try (PreparedStatement statement = connection.prepareStatement(
        "INSERT INTO notes (title, slug, status) VALUES (?, ?, ?)")) {

    int count = 0;
    for (Note note : notes) {
        statement.setString(1, note.title());
        statement.setString(2, note.slug());
        statement.setString(3, note.status());
        statement.addBatch();

        if (++count % 1000 == 0) {
            statement.executeBatch();     // flush periodically
            statement.clearBatch();
        }
    }
    statement.executeBatch();             // the remainder
}

Batching is often the single largest improvement available for a bulk load: ten thousand inserts become ten round trips instead of ten thousand.

Batching inside a transaction

public void importNotes(List<Note> notes) throws SQLException {
    String sql = "INSERT INTO notes (title, slug, status) VALUES (?, ?, ?)";

    try (Connection connection = dataSource.getConnection()) {
        connection.setAutoCommit(false);

        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            int count = 0;
            for (Note note : notes) {
                statement.setString(1, note.title());
                statement.setString(2, note.slug());
                statement.setString(3, note.status());
                statement.addBatch();

                if (++count % 1000 == 0) {
                    statement.executeBatch();
                }
            }
            statement.executeBatch();
            connection.commit();

        } catch (SQLException e) {
            connection.rollback();
            throw e;
        } finally {
            connection.setAutoCommit(true);
        }
    }
}

Handling a partial batch failure

try {
    int[] results = statement.executeBatch();
} catch (BatchUpdateException e) {
    int[] counts = e.getUpdateCounts();      // one entry per statement attempted
    for (int i = 0; i < counts.length; i++) {
        if (counts[i] == Statement.EXECUTE_FAILED) {
            logger.warning("Row " + i + " failed");
        }
    }
    connection.rollback();
}

Whether the driver stops at the first failure or continues is vendor specific, so always inspect the update counts rather than assuming.

Optimistic locking

String sql = "UPDATE notes SET title = ?, version = version + 1 " +
             "WHERE id = ? AND version = ?";

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, title);
    statement.setLong(2, id);
    statement.setInt(3, expectedVersion);

    if (statement.executeUpdate() == 0) {
        throw new ConcurrentModificationException(
                "Note " + id + " was changed by someone else");
    }
}

The version column turns a lost update into a detectable conflict, without holding a database lock across a user thinking time.

Keep transactions short

// Poor: a network call inside an open transaction holds locks for its duration
connection.setAutoCommit(false);
updateNote(connection, note);
sendEmail(note);                  // slow, and unrelated
connection.commit();

// Better
connection.setAutoCommit(false);
updateNote(connection, note);
connection.commit();

sendEmail(note);                  // after the transaction ends

Common mistakes

• Forgetting setAutoCommit(false) and finding no rollback is possible.
• Not restoring auto commit on a pooled connection.
• Rolling back but not rethrowing, so the caller believes it succeeded.
• Building an enormous batch and exhausting memory.
• Holding a transaction open across slow external calls.
• Raising the isolation level to fix a bug that was really a missing transaction.

Best practices

• Start a transaction only when more than one statement must succeed together.
• Commit or roll back on every path, and restore auto commit in finally.
• Keep transactions short and free of external calls.
• Batch bulk work and flush every few hundred or thousand rows.
• Use optimistic locking for user editable records.
• Leave the isolation level at the default unless an anomaly is proven.

Practice

1. Write a transfer that debits one account and credits another, and verify a rollback on failure.
2. Why must auto commit be restored before returning a pooled connection?
3. Insert ten thousand rows with and without batching and compare the time.
4. Explain the difference between a non repeatable read and a phantom read.
5. Implement optimistic locking with a version column and demonstrate the conflict.

Conclusion

Turn off auto commit when statements must succeed together, commit or roll back on every path, and restore the connection state afterwards. Batch bulk work, keep transactions short, and detect conflicts with a version column rather than long held locks.