JDBC ·
‹ Previous Next ›
⏱ 5 min read Modified: 2026-11-21

JDBC Transactions: setAutoCommit, commit and rollback

A money transfer is two queries: take the amount from one account and add it to another. Run them through JDBC as is, and if the second query fails (say, the recipient's account does not exist), the first one is already saved: Alice had 1000, now she has 900, and the 100 went nowhere. The protection against this is a transaction.

A transaction is a group of SQL statements executed as a single unit: either all changes are saved (commit) or all are undone (rollback). In JDBC, transactions are controlled through the Connection object: by default every statement commits on its own, and calling setAutoCommit(false) lets you group several statements into one transaction. What ACID means and how transactions work on the database side is covered in SQL Transactions.

1. Auto-Commit Mode

A new JDBC connection works in auto-commit mode: getAutoCommit() returns true, and every executeUpdate is committed to the database immediately as a separate transaction. To make several statements run as one unit, turn auto-commit off: connection.setAutoCommit(false).

Here is a money transfer without a transaction. The accounts table:

CREATE TABLE accounts (
    id      INT PRIMARY KEY,
    owner   VARCHAR(50)    NOT NULL,
    balance DECIMAL(10, 2) NOT NULL
);

INSERT INTO accounts (id, owner, balance) VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 500.00);

Example 1. A transfer without a transaction

static void transferWithoutTransaction(Connection connection, int fromId, int toId,
                                       BigDecimal amount) throws SQLException {
    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.setInt(2, fromId);
        debit.executeUpdate();             // already committed

        credit.setBigDecimal(1, amount);
        credit.setInt(2, toId);
        if (credit.executeUpdate() != 1) {
            throw new SQLException("Account " + toId + " not found");
        }
    }
}

Calling transferWithoutTransaction(connection, 1, 99, new BigDecimal("100.00")) throws "Account 99 not found", and the table is left with:

Alice = 900.00, Bob = 500.00

The debit ran and was committed at once, and there is nothing left to undo it with. In auto-commit mode, a Java exception does not roll back what the database has already saved.

The Connection methods that control a transaction:

Method What it does
setAutoCommit(boolean) false: statements accumulate in one transaction until commit() or rollback(). true: each statement commits on its own; if a transaction is open, it is committed at the moment of the call
getAutoCommit() The current mode. A new connection returns true
commit() Saves all changes of the current transaction and releases locks
rollback() Undoes all changes of the current transaction
setSavepoint(String) / rollback(Savepoint) Sets a savepoint and rolls the transaction back to it, keeping everything done before it
setTransactionIsolation(int) Sets the isolation level for the next transactions on this connection

2. Money Transfer: commit and rollback

A JDBC transaction is built like this: setAutoCommit(false), then all the statements, then commit(); if any statement throws, the catch block calls rollback(), and the database returns to the state before the transaction. Here is the same transfer, now atomic.

Example 2. The MoneyTransfer class

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class MoneyTransfer {
    private static final String DEBIT =
            "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?";
    private static final String CREDIT =
            "UPDATE accounts SET balance = balance + ? WHERE id = ?";

    public static void transfer(Connection connection, int fromId, int toId, BigDecimal amount)
            throws SQLException {
        boolean oldAutoCommit = connection.getAutoCommit();
        connection.setAutoCommit(false);
        try (PreparedStatement debit = connection.prepareStatement(DEBIT);
             PreparedStatement credit = connection.prepareStatement(CREDIT)) {
            debit.setBigDecimal(1, amount);
            debit.setInt(2, fromId);
            debit.setBigDecimal(3, amount);
            if (debit.executeUpdate() != 1) {
                throw new SQLException("Insufficient funds on account " + fromId);
            }

            credit.setBigDecimal(1, amount);
            credit.setInt(2, toId);
            if (credit.executeUpdate() != 1) {
                throw new SQLException("Account " + toId + " not found");
            }

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

The AND balance >= ? condition makes the balance check part of the UPDATE itself: if there is not enough money, the query changes no rows, executeUpdate() returns 0, and the method throws. A separate SELECT balance before the debit does not work this way: between the check and the debit, another thread can spend the same money.

Example 3. Running the transfers

MoneyTransfer.transfer(connection, 1, 2, new BigDecimal("300.00"));
printBalances(connection);

try {
    MoneyTransfer.transfer(connection, 2, 1, new BigDecimal("5000.00"));
} catch (SQLException e) {
    System.out.println("Rolled back: " + e.getMessage());
}
try {
    MoneyTransfer.transfer(connection, 1, 99, new BigDecimal("100.00"));
} catch (SQLException e) {
    System.out.println("Rolled back: " + e.getMessage());
}
printBalances(connection);

The printBalances method prints owner and balance for every account. Output with starting balances of 1000.00 and 500.00:

Alice = 700.00, Bob = 800.00
Rolled back: Insufficient funds on account 2
Rolled back: Account 99 not found
Alice = 700.00, Bob = 800.00

The first transfer is committed. In the second one, the debit fails the balance condition. In the third, Alice's debit runs, but rollback() undoes it, so Alice has 700.00 again rather than the 600.00 she would have had without a transaction.

3. How to Write a Transaction Correctly

A correct JDBC transaction has four required parts: turn auto-commit off, call commit() at the end of the success path, call rollback() on any exception, and restore the connection's previous mode in finally. Skipping any of them compiles fine but corrupts data.

  • rollback() in catch. Forget it, and uncommitted changes stay on the connection. The next commit() on that connection, even in a completely different method, saves them together with its own.
  • Rethrow the exception. After the rollback, MoneyTransfer does throw e: the caller needs to know the transfer did not happen. A swallowed exception looks like a successful transfer to the user.
  • Restore autoCommit in finally. The method remembers the previous value and puts it back. This matters for connection pools: the connection goes back to the pool and is handed to code that expects auto-commit. HikariCP resets a changed autoCommit and rolls back an uncommitted transaction when a connection is returned, but you should not rely on the behaviour of a particular pool.
  • Keep transactions short. While a transaction is open, the database holds locks on the changed rows. Do not wait for user input or an external HTTP call inside a transaction.

Several changes sent as a batch also belong inside a transaction: then an error in the middle of the batch rolls back the statements that already ran, not just the one that failed.

Interview question

"What happens if you call commit() without turning auto-commit off?" Per the JDBC specification, a SQLException. The MySQL driver reports Can't call commit when autocommit=true. Calling setAutoCommit(true) in the middle of an open transaction, on the other hand, raises no error: the transaction is silently committed.

4. Savepoint: Rolling Back Part of a Transaction

A Savepoint is a marker inside a transaction. connection.setSavepoint("name") sets it, connection.rollback(savepoint) undoes the changes made after it, and everything done before the marker stays in the transaction and is saved by a regular commit().

Example 4. Rolling back to a savepoint

connection.setAutoCommit(false);
try (PreparedStatement update = connection.prepareStatement(
        "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
    update.setBigDecimal(1, new BigDecimal("50.00"));   // bonus for Alice
    update.setInt(2, 1);
    update.executeUpdate();

    Savepoint afterBonus = connection.setSavepoint("after_bonus");

    update.setBigDecimal(1, new BigDecimal("-900.00")); // wrong debit from Bob
    update.setInt(2, 2);
    update.executeUpdate();
    connection.rollback(afterBonus);                    // undo only the debit

    connection.commit();
} finally {
    connection.setAutoCommit(true);
}

With balances of 700.00 and 800.00, after this code the table holds:

Alice = 750.00, Bob = 800.00

Alice's bonus is committed, Bob's debit is undone. Note that rollback(savepoint) does not end the transaction: you still need a commit() or a full rollback() after it. Savepoints are handy when processing a long list of operations where a failure in one of them can be skipped without losing the rest. connection.getMetaData().supportsSavepoints() tells whether the driver supports them; MySQL (InnoDB), PostgreSQL and Oracle do.

5. Isolation Levels in JDBC

The isolation level is set with connection.setTransactionIsolation(int) using one of the Connection constants. It determines whether a transaction sees uncommitted changes and changes committed concurrently by other transactions. Which anomalies (dirty, non-repeatable and phantom reads) each level allows is covered in SQL Transactions.

Connection constant Value SQL level Default in
TRANSACTION_NONE 0 Transactions not supported —
TRANSACTION_READ_UNCOMMITTED 1 READ UNCOMMITTED —
TRANSACTION_READ_COMMITTED 2 READ COMMITTED PostgreSQL, Oracle, SQL Server
TRANSACTION_REPEATABLE_READ 4 REPEATABLE READ MySQL (InnoDB)
TRANSACTION_SERIALIZABLE 8 SERIALIZABLE —
System.out.println(connection.getTransactionIsolation()); // 4 on MySQL: REPEATABLE READ

connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
connection.setAutoCommit(false);
// ... transaction statements
connection.commit();

Change the level before the transaction starts: the JDBC specification leaves calling setTransactionIsolation in the middle of an open transaction implementation-defined, and drivers behave differently. The new level applies to this connection until changed again, so in a pool it should be restored, just like autoCommit. getMetaData().supportsTransactionIsolationLevel(level) tells whether the database supports a given level.

6. One Transaction Across Several DAOs

A JDBC transaction belongs to a single Connection object. If every DAO method takes its own connection from the pool, like UserDao in DAO Pattern and HikariCP Connection Pool in JDBC, two DAO calls end up in different transactions, and they cannot be rolled back together.

Take placing an order: orderDao.create(order) and accountDao.debit(userId, sum). If the debit fails, the order is already saved through its own connection. The framework-free fix is to open the connection and the transaction at the service level and pass that connection to every DAO method:

public void placeOrder(Order order) throws SQLException {
    try (Connection connection = dataSource.getConnection()) {
        connection.setAutoCommit(false);
        try {
            orderDao.create(connection, order);
            accountDao.debit(connection, order.getUserId(), order.getTotal());
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            throw e;
        }
    }
}

In this version, DAO methods receive the Connection as a parameter and do not close it: the service owns the connection and the transaction. setAutoCommit(true) is not needed here if the pool restores the mode on return, as HikariCP does; otherwise add a finally as in MoneyTransfer. In Spring, the @Transactional annotation on a service method does the same job: the framework binds one connection to the current thread, and every DAO inside the method works in one transaction.

7. Deadlocks and Retrying a Transaction

When two transactions wait for each other (a deadlock), the database rolls one of them back, and the driver throws SQLTransactionRollbackException, a subclass of SQLException with an SQLState starting with 40. Such a transaction is safe to retry: all its changes have already been undone.

On a deadlock, MySQL returns error 1213 with SQLState 40001, and Connector/J throws exactly SQLTransactionRollbackException. Since MoneyTransfer.transfer rethrows the original exception after the rollback, the retry goes in the calling code:

int attempts = 0;
while (true) {
    try {
        MoneyTransfer.transfer(connection, 1, 2, new BigDecimal("100.00"));
        break;
    } catch (SQLTransactionRollbackException e) {
        if (++attempts == 3) {
            throw e;            // give up after three attempts
        }
    }
}

Only transactions rolled back by the database as a whole are retried. Errors like "insufficient funds" make no sense to retry, which is why the code catches SQLTransactionRollbackException rather than any SQLException. Why deadlocks happen and how InnoDB locks rows is covered in SQL Transactions.

8. Common Mistakes

Most JDBC transaction mistakes do not throw: the code runs, but data is saved at the wrong moment or only partly. Below are the typical slips seen in code reviews and interview questions.

Mistake What happens How to fix it
No setAutoCommit(false) Every UPDATE commits at once, rollback() has nothing to undo, and commit() throws SQLException Turn auto-commit off before the first statement of the transaction
No rollback() in catch Uncommitted changes stay on the connection and are saved by the next commit() rollback() in every catch, then rethrow
setAutoCommit(false) without commit() When the connection closes, MySQL rolls the transaction back, and the changes silently disappear commit() at the end of the success path
Balance checked with a separate SELECT before the UPDATE Two concurrent transfers both see enough money, and the account goes negative A condition in the UPDATE itself (AND balance >= ?) or SELECT ... FOR UPDATE
Every DAO takes its own connection Operations from different DAOs cannot be rolled back together One connection per transaction passed to every DAO, or @Transactional
CREATE, ALTER, DROP inside a transaction on MySQL A DDL statement implicitly commits the transaction, and a later rollback() undoes nothing Do not mix DDL with data changes in one transaction

Frequently Asked Questions

Does a transaction start when setAutoCommit(false) is called?

No. setAutoCommit(false) only changes the connection mode. The transaction starts with the first SQL statement after it and lasts until commit() or rollback(); the next statement after that starts a new transaction. You do not need to call setAutoCommit(false) again before each transaction as long as the mode has not been switched back to true.

Do I need to call commit() after a SELECT?

If auto-commit is off, yes: even a read opens a transaction, and at REPEATABLE READ it keeps seeing the old snapshot of the data until it ends. For reads, it is convenient to keep autoCommit = true or call connection.setReadOnly(true) so the database can optimise a read-only transaction.

Can one transaction span two different databases?

Not with a plain JDBC transaction: it belongs to one Connection, which means one database. Two databases need a distributed (XA) transaction through a JTA transaction manager, for example in an application server or with Atomikos. In practice it is often replaced with patterns such as Saga, where each database commits its own part and compensating operations run on failure.

How does this relate to @Transactional in Spring?

@Transactional makes the same JDBC calls for you: before the method it takes a connection and turns auto-commit off, after a normal return it calls commit(), and on an unchecked exception it calls rollback(). Checked exceptions do not roll the transaction back by default; that is configured with the rollbackFor attribute. Knowing the manual code from this lesson helps when you need to figure out why a Spring transaction did not roll back.

Comments

Please log in or register to have a possibility to add comment.