Transaction Management

A database transaction is a group of SQL operations that must all succeed or all fail together. If you transfer money between two bank accounts, for example, the debit and the credit must both complete — or neither should. JDBC gives you full programmatic control over transactions so you can enforce exactly that kind of all-or-nothing guarantee.

What Makes a Transaction? (ACID)

Every reliable transaction follows the ACID properties:

Property	Meaning
Atomicity	All operations succeed, or none are applied.
Consistency	The database moves from one valid state to another.
Isolation	Concurrent transactions don’t interfere with each other.
Durability	Once committed, changes survive crashes and restarts.

Understanding ACID helps you choose the right isolation level and design your transaction boundaries correctly.

Auto-Commit: The Default Behaviour

By default, every JDBC Connection runs in auto-commit mode: each SQL statement is immediately committed as its own transaction. That is convenient for simple reads, but disastrous when you need several statements to succeed as a unit.

Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn.getAutoCommit()); // true — auto-commit is ON by default

Turn auto-commit off before your transaction begins:

conn.setAutoCommit(false); // start manual transaction control

Warning: Forgetting to call setAutoCommit(false) is the most common JDBC transaction mistake. If you leave auto-commit on, each statement commits instantly and you cannot roll back earlier statements.

commit() and rollback()

With auto-commit disabled, you drive the transaction yourself using two methods:

• conn.commit() — permanently saves all changes since the last commit/rollback.
• conn.rollback() — discards all changes since the last commit/rollback.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class TransferDemo {

    static final String URL  = "jdbc:mysql://localhost:3306/bank";
    static final String USER = "root";
    static final String PASS = "secret";

    public static void transfer(int fromId, int toId, double amount) throws Exception {
        String debit  = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
        String credit = "UPDATE accounts SET balance = balance + ? WHERE id = ?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
            conn.setAutoCommit(false); // begin transaction

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

                ps1.setDouble(1, amount);
                ps1.setInt(2, fromId);
                ps1.executeUpdate();

                ps2.setDouble(1, amount);
                ps2.setInt(2, toId);
                ps2.executeUpdate();

                conn.commit(); // both succeeded — persist
                System.out.println("Transfer complete.");

            } catch (Exception e) {
                conn.rollback(); // something failed — undo everything
                System.out.println("Transfer failed, rolled back: " + e.getMessage());
                throw e;
            }
        }
    }

    public static void main(String[] args) throws Exception {
        transfer(1, 2, 500.00);
    }
}

Tip: Always call rollback() inside the catch block and re-throw (or wrap) the exception so callers know the operation failed.

Savepoints

A savepoint marks an intermediate point within a transaction. You can roll back to a savepoint without discarding the entire transaction — useful when you want to undo only part of the work.

import java.sql.*;

public class SavepointDemo {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost:3306/shop";

        try (Connection conn = DriverManager.getConnection(url, "root", "secret")) {
            conn.setAutoCommit(false);

            Statement stmt = conn.createStatement();

            // Step 1: insert order header
            stmt.executeUpdate("INSERT INTO orders(id, customer) VALUES (101, 'Alice')");

            Savepoint sp = conn.setSavepoint("afterOrderHeader"); // mark here

            // Step 2: insert order items (might fail)
            try {
                stmt.executeUpdate("INSERT INTO order_items(order_id, sku) VALUES (101, 'INVALID-SKU')");
                conn.commit();
            } catch (SQLException e) {
                // Roll back only the failed items insert, keep the order header
                conn.rollback(sp);
                System.out.println("Items failed; rolled back to savepoint.");
                conn.commit(); // commit just the order header
            }
        }
    }
}

You can release a savepoint when you no longer need it:

conn.releaseSavepoint(sp);

Note: Not all databases support savepoints equally. MySQL InnoDB and PostgreSQL handle them well. Always check your driver’s documentation.

Transaction Isolation Levels

Two transactions running at the same time can step on each other’s data in three classic ways:

Problem	Description
Dirty Read	You read data that another transaction has changed but not yet committed.
Non-Repeatable Read	You read the same row twice and get different values because another transaction committed a change in between.
Phantom Read	You run the same query twice and get different rows because another transaction inserted or deleted rows in between.

JDBC lets you choose an isolation level that trades performance for protection:

Level	Dirty Read	Non-Repeatable Read	Phantom Read	JDBC Constant
READ UNCOMMITTED	Possible	Possible	Possible	TRANSACTION_READ_UNCOMMITTED
READ COMMITTED	Prevented	Possible	Possible	TRANSACTION_READ_COMMITTED
REPEATABLE READ	Prevented	Prevented	Possible	TRANSACTION_REPEATABLE_READ
SERIALIZABLE	Prevented	Prevented	Prevented	TRANSACTION_SERIALIZABLE

Set the level before you start your transaction:

conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
conn.setAutoCommit(false);
// ... your SQL statements ...
conn.commit();

Tip: Most applications work fine with the database’s default (READ COMMITTED for PostgreSQL and MySQL). Only step up to SERIALIZABLE when your business logic truly demands it — higher isolation means more locking and lower throughput.

Check what your database supports:

DatabaseMetaData meta = conn.getMetaData();
System.out.println(meta.supportsTransactionIsolationLevel(
    Connection.TRANSACTION_SERIALIZABLE)); // true/false

A Complete Pattern: Try-With-Resources + Rollback

Here is a clean, reusable template you can copy into any project:

import java.sql.*;

public class TransactionTemplate {

    public static void runTransaction(Connection conn) throws SQLException {
        boolean previousAutoCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);

        try {
            // --- your SQL work here ---
            try (PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO audit_log(event) VALUES (?)")) {
                ps.setString(1, "batch-job-started");
                ps.executeUpdate();
            }
            // ---------------------------

            conn.commit();

        } catch (SQLException e) {
            conn.rollback();
            throw e; // let the caller handle it
        } finally {
            conn.setAutoCommit(previousAutoCommit); // restore original state
        }
    }
}

Restoring the original auto-commit state in finally is especially important when you borrow connections from a connection pool — the connection will be reused by another part of the application.

Under the Hood

When you call conn.setAutoCommit(false), JDBC sends a BEGIN (or equivalent) statement to the database, opening a transaction context on the server. Every subsequent statement executes inside that context. The database engine typically acquires row-level or page-level locks depending on the isolation level.

conn.commit() translates to a COMMIT SQL command: the database writes all pending changes from its transaction log to the actual data files and releases locks. conn.rollback() sends ROLLBACK, which discards the log entries and releases locks without touching the data files.

Savepoints map directly to SAVEPOINT <name> and ROLLBACK TO SAVEPOINT <name> in SQL — JDBC is just a thin wrapper here.

From a JVM perspective, all of this is network I/O. The Connection object holds a socket to the database server. commit() and rollback() are synchronous calls — they block until the server acknowledges the operation. In high-throughput systems, you minimise transaction duration to hold locks for as short a time as possible, reducing contention.

Note: JDBC transactions are single-connection, single-database. If you need to coordinate writes across multiple databases or message brokers, you need a distributed transaction manager (JTA/XA), which is outside JDBC’s scope.

Common Mistakes to Avoid

• Forgetting setAutoCommit(false) — your “transaction” is actually many tiny auto-committed transactions.
• Not rolling back on exception — partial changes stay in the database, corrupting data.
• Committing inside a loop — batching many small commits is slower than one commit at the end. See Batch Processing.
• Opening long transactions — holding a transaction open while waiting for user input locks rows and starves other users.
• Swallowing exceptions after rollback — always re-throw or log so the failure is visible.

Related Topics

• PreparedStatement — the safe way to build SQL statements inside a transaction
• Batch Processing — execute many statements efficiently under a single transaction
• ResultSet — read and navigate query results returned within a transaction
• JDBC Steps — the full setup and connection flow before you manage transactions
• Connection Interface — all methods on the Connection object, including transaction controls
• Exception Handling — handling SQLException and wrapping rollback logic cleanly