☕ Java

Transactions

A transaction is a sequence of one or more SQL operations that must succeed or fail together as a single atomic unit, and JDBC exposes transaction control directly on the Connection interface rather than through a separate transaction object. Getting transaction boundaries right — where they start, where they end, what happens on failure, and what isolation guarantees apply while they're open — is one of the areas where JDBC code most commonly goes subtly wrong. This entry covers auto-commit versus manual transaction mode, the commit/rollback/savepoint lifecycle, transaction isolation levels and the anomalies they prevent, and how transaction boundaries interact with connection pooling.

Auto-Commit Mode and the Manual Transaction Lifecycle

Every JDBC Connection starts in auto-commit mode by default, meaning each individual SQL statement is treated as its own complete transaction and is committed automatically the instant it finishes executing — there is no way to undo one auto-committed statement once it has run, and there is no way to group several auto-committed statements so that they either all happen or none do. This default is convenient for simple, single-statement operations but is actively wrong for any sequence of operations that must be atomic, such as moving money between two accounts, where leaving the system in a state with the debit applied but not the corresponding credit (or vice versa) would corrupt the data. Calling conn.setAutoCommit(false) switches a connection into manual transaction mode: a transaction implicitly begins with the next statement executed, and stays open — accumulating every subsequent statement's effects — until the application explicitly calls conn.commit() (making all changes since the transaction began permanent and visible to other transactions, subject to isolation rules) or conn.rollback() (discarding every change made since the transaction began, restoring the database to its prior state as if none of it had happened). The standard structural pattern wraps the statements, the commit, and a rollback-on-exception together, typically inside a try/catch block, and restores auto-commit to its prior state in a finally block — particularly important when the connection came from a pool and will be reused by an unrelated caller afterward.
Java
import java.sql.*;
import java.math.BigDecimal;

public class TransactionLifecycle {
    public static void transferFunds(Connection conn, long fromAccount, long toAccount, BigDecimal amount)
            throws SQLException {
        boolean originalAutoCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);  // begin manual transaction mode

        try {
            try (PreparedStatement debit = conn.prepareStatement(
                    "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?")) {
                debit.setBigDecimal(1, amount);
                debit.setLong(2, fromAccount);
                debit.setBigDecimal(3, amount);  // guard against overdraft
                int rows = debit.executeUpdate();
                if (rows == 0) {
                    throw new SQLException("Insufficient funds or account not found");
                }
            }

            try (PreparedStatement credit = conn.prepareStatement(
                    "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
                credit.setBigDecimal(1, amount);
                credit.setLong(2, toAccount);
                credit.executeUpdate();
            }

            conn.commit();   // both updates become permanent together, atomically

        } catch (SQLException e) {
            conn.rollback();  // undo everything done since the transaction began
            throw e;
        } finally {
            conn.setAutoCommit(originalAutoCommit);  // restore prior state, esp. before pool return
        }
    }
}

Savepoints for Partial Rollback

A plain rollback() undoes the entire transaction back to its starting point, but sometimes a transaction involves several logically separable steps where, if a later step fails, only that step (and anything after it) should be undone while earlier successful steps remain intact, rather than discarding the whole transaction. Savepoint, created mid-transaction via conn.setSavepoint() (unnamed) or conn.setSavepoint("label") (named, useful when multiple savepoints exist in the same transaction), marks such an intermediate point that conn.rollback(savepoint) can later return to without undoing everything before it. Savepoints only have meaning within an open, manually-managed transaction — calling setSavepoint() while in auto-commit mode is generally either disallowed or meaningless depending on the driver, since there's no multi-statement transaction for the savepoint to exist inside. After a successful commit() or a full rollback() (without specifying a savepoint), all savepoints created during that transaction become invalid, since the transaction they belonged to has ended; attempting to roll back to a savepoint from an already-completed transaction throws a SQLException. Not all databases support savepoints (it is an optional JDBC feature from the driver's perspective), so checking conn.getMetaData().supportsSavepoints() before relying on them is worthwhile in code that needs to run portably across different database backends.
Java
import java.sql.*;

public class SavepointExample {
    public static void processOrderWithOptionalSteps(Connection conn, long orderId) throws SQLException {
        conn.setAutoCommit(false);

        try {
            try (PreparedStatement insertOrder = conn.prepareStatement(
                    "UPDATE orders SET status = 'processing' WHERE id = ?")) {
                insertOrder.setLong(1, orderId);
                insertOrder.executeUpdate();
            }

            Savepoint beforeOptionalStep = conn.setSavepoint("beforeOptionalStep");

            try {
                try (PreparedStatement applyPromo = conn.prepareStatement(
                        "UPDATE orders SET discount = calculate_promo(?) WHERE id = ?")) {
                    applyPromo.setLong(1, orderId);
                    applyPromo.setLong(2, orderId);
                    applyPromo.executeUpdate();
                }
            } catch (SQLException promoFailure) {
                // Promo calculation failing shouldn't abort the whole order —
                // just undo the promo attempt and continue
                conn.rollback(beforeOptionalStep);
            }

            try (PreparedStatement finalize = conn.prepareStatement(
                    "UPDATE orders SET status = 'confirmed' WHERE id = ?")) {
                finalize.setLong(1, orderId);
                finalize.executeUpdate();
            }

            conn.commit();  // order confirmed, with or without the promo applied

        } catch (SQLException e) {
            conn.rollback();  // something unrelated to the optional step failed — undo everything
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
}

Isolation Levels and Their Concurrency Tradeoffs

Transaction isolation determines how visible one transaction's in-progress changes are to other transactions running concurrently, and JDBC exposes the four standard SQL isolation levels as int constants on Connection, settable via setTransactionIsolation(): TRANSACTION_READ_UNCOMMITTED (the weakest — permits dirty reads, where one transaction can see another's uncommitted changes, which might later be rolled back), TRANSACTION_READ_COMMITTED (prevents dirty reads, but still permits non-repeatable reads, where re-running the same query twice within one transaction can return different results if another transaction committed changes in between), TRANSACTION_REPEATABLE_READ (prevents non-repeatable reads, but may still permit phantom reads, where a range query run twice can return additional or missing rows due to concurrent inserts/deletes), and TRANSACTION_SERIALIZABLE (the strongest — transactions behave as if executed one at a time in some serial order, preventing all of the above anomalies at the cost of the least concurrency and the most potential for transactions to block or fail with serialization conflicts). Not every database implements all four levels identically, or even at all — some databases' "read committed" behaves closer to repeatable read under the hood due to MVCC implementation details, and PostgreSQL, for instance, treats READ_UNCOMMITTED as equivalent to READ_COMMITTED since it has no true dirty-read mechanism — so conn.getMetaData().supportsTransactionIsolationLevel(level) should be checked when portability across databases matters, and the database's own documentation should be consulted for what each level actually guarantees on that specific system rather than assuming the standard SQL definitions apply literally. Stricter isolation levels generally trade away concurrency and throughput for stronger consistency guarantees, so the right level for a given transaction is a deliberate choice based on what anomalies that specific operation can tolerate — defaulting to the database's default (commonly READ_COMMITTED) is reasonable for most application code, with stricter levels reserved for operations specifically sensitive to the anomalies a weaker level would permit.
Java
import java.sql.*;

public class IsolationLevelExample {
    public static void runWithAppropriateIsolation(Connection conn) throws SQLException {
        DatabaseMetaData meta = conn.getMetaData();

        // Check support before relying on the strictest level
        if (meta.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)) {
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        } else if (meta.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ)) {
            conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
        } else {
            conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        }

        conn.setAutoCommit(false);
        try {
            // Example: a report that must see a single, internally consistent
            // snapshot across two queries — sensitive to non-repeatable reads,
            // so READ_COMMITTED alone would not be strong enough here
            BigDecimalTotal firstRead;
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery("SELECT SUM(balance) AS total FROM accounts")) {
                rs.next();
                firstRead = new BigDecimalTotal(rs.getBigDecimal("total"));
            }

            // ... other work happens here, potentially concurrent with other transactions ...

            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery("SELECT SUM(balance) AS total FROM accounts")) {
                rs.next();
                // At REPEATABLE_READ or SERIALIZABLE, this is guaranteed to match firstRead
                // within the same transaction; at READ_COMMITTED it might not.
            }

            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        }
    }

    record BigDecimalTotal(java.math.BigDecimal value) {}
}

Related Topics in Java Database Connectivity (JDBC)

JDBC Architecture
JDBC (Java Database Connectivity) is the standard Java API for connecting to and executing operations against relational databases, defined as a set of interfaces in java.sql and javax.sql that database vendors implement with concrete driver classes. Its architecture is deliberately layered to decouple application code from any specific database product: applications code against interfaces like Connection, Statement, and ResultSet, while a pluggable driver underneath translates those calls into the vendor-specific network protocol the database actually understands. This entry covers the two-layer (API/driver) architecture, the four historical driver types, the role of the JDBC driver manager and connection pooling layers, and how the pieces fit together at runtime.
JDBC Drivers
A JDBC driver is the vendor-specific library that implements the java.sql/javax.sql interfaces for a particular database product, translating standard JDBC calls into that database's native network protocol. Choosing, loading, registering, and configuring the correct driver is a prerequisite for any JDBC code to run, and the mechanics of how a driver gets discovered and instantiated have evolved significantly since JDBC's introduction. This entry covers driver registration via the Service Provider Interface (SPI) versus the older explicit Class.forName() approach, JDBC URL syntax and structure, driver-specific configuration properties, and how to choose and manage driver dependencies in a real project.
DriverManager
java.sql.DriverManager is the original, classic entry point into JDBC: a static factory class that maintains a registry of available Driver implementations and hands back a live Connection when given a JDBC URL and credentials. It predates the javax.sql.DataSource abstraction and connection pooling, and while it remains fully functional and is still commonly used for simple applications, scripts, and tests, production server-side code generally prefers DataSource-based connection pooling for the reasons covered below. This entry covers how DriverManager locates and selects a driver, its core methods, login timeout and logging configuration, and where it fits (and doesn't fit) in modern application architecture.
Connection
java.sql.Connection represents a single session with a specific database — the live link through which all statements, transactions, and metadata queries flow, and the factory from which Statement, PreparedStatement, and CallableStatement objects are created. A Connection is a relatively expensive, stateful resource that wraps an underlying network socket and server-side session, which is why its lifecycle, transaction settings, and proper closing matter so much in practice. This entry covers obtaining and closing connections, transaction control (auto-commit, commit, rollback, savepoints, isolation levels), creating statements from a connection, and connection metadata and health checks.
Prev
ResultSet
Next
Batch Processing