Transactions

Group statements so they commit or roll back together, keep transactions short, and pick the right tool when concurrent requests race for the same rows.

Syntaxconn.setAutoCommit(false); try { /* statements on conn */ conn.commit(); } catch (SQLException | RuntimeException e) { conn.rollback(); throw e; } finally { conn.setAutoCommit(true); }

A transaction groups statements so that they all take effect or none do. In JDBC every new connection starts in auto-commit mode: each statement is its own transaction and is committed the moment it finishes. That is fine for a single INSERT. It is wrong as soon as two statements must agree, such as taking stock off a product and inserting the order that used it.

The pattern

Call setAutoCommit(false), run the statements on that same connection, then commit(). If anything throws, rollback() and rethrow. Every statement must use the one connection. A repository that fetches its own connection from the DataSource is outside the transaction even when it is called from inside the same method, which is why transactional code passes the Connection down, or uses a framework that binds it to the current thread.

Keep them short

Row locks taken by an UPDATE are held until commit or rollback. A transaction that calls a payment provider or sends an email while it is open keeps those rows locked, and a pooled connection borrowed, for as long as the remote call takes. Do slow work before the transaction starts or after it commits, and keep only database statements inside.

Concurrent requests

setTransactionIsolation controls how much concurrent transactions see of each other. PostgreSQL defaults to READ COMMITTED and MySQL's InnoDB to REPEATABLE READ. Under read committed, two requests can both read a stock of 1 and both sell it. The usual fixes:

  • A conditional update: UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?, then check that the row count is 1.
  • SELECT ... FOR UPDATE to lock the row before reading and changing it.
  • SERIALIZABLE isolation, with a retry of the whole transaction when the database reports a serialisation failure (SQLState 40001).

Savepoints

conn.setSavepoint() marks a point you can return to with rollback(savepoint), undoing only the work after it while keeping the rest of the transaction. It is useful when one optional step may fail.

With a pool

A pooled connection is handed to the next borrower when you close it, so always end with a commit or a rollback. HikariCP rolls back uncommitted work and resets auto-commit when a connection is returned, but code that relies on that is hiding a bug. In Spring, @Transactional on a service method does all of this for you. By default it rolls back only on unchecked exceptions; a checked exception still commits unless you set rollbackFor.

Example

Example · java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

class OutOfStockException extends RuntimeException {
    OutOfStockException(long productId) {
        super("product " + productId + " is out of stock");
    }
}

final class Checkout {
    private final DataSource dataSource;

    Checkout(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    long placeOrder(long customerId, long productId, int quantity) throws SQLException {
        try (Connection conn = dataSource.getConnection()) {
            conn.setAutoCommit(false);
            try {
                // Conditional update: safe even when two requests race for the last item.
                try (PreparedStatement ps = conn.prepareStatement(
                        "UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?")) {
                    ps.setInt(1, quantity);
                    ps.setLong(2, productId);
                    ps.setInt(3, quantity);
                    if (ps.executeUpdate() == 0) {
                        throw new OutOfStockException(productId);
                    }
                }
                long orderId;
                try (PreparedStatement ps = conn.prepareStatement(
                        "INSERT INTO orders (customer_id, product_id, quantity) VALUES (?, ?, ?)",
                        new String[] {"id"})) {
                    ps.setLong(1, customerId);
                    ps.setLong(2, productId);
                    ps.setInt(3, quantity);
                    ps.executeUpdate();
                    try (ResultSet keys = ps.getGeneratedKeys()) {
                        keys.next();
                        orderId = keys.getLong(1);
                    }
                }
                conn.commit();                        // both changes become visible together
                return orderId;
            } catch (SQLException | RuntimeException e) {
                conn.rollback();                      // neither change happened
                throw e;
            } finally {
                conn.setAutoCommit(true);
            }
        }
    }
}

When to use it

  • Reserve stock and create the order in one transaction so a failed insert never leaves stock missing.
  • Stop two concurrent checkouts from selling the last item by using a conditional UPDATE and its row count.
  • Import a spreadsheet of contacts where duplicate rows are skipped and reported instead of aborting the upload.

More examples

A reusable transaction helper

Writing commit and rollback once removes the copy-pasted try blocks where bugs hide. If rollback itself fails (the network dropped, say), addSuppressed keeps the original exception as the one that is reported.

Example · java
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;

@FunctionalInterface
interface SqlWork<T> {
    T run(Connection conn) throws SQLException;
}

final class Tx {
    static <T> T inTransaction(DataSource dataSource, SqlWork<T> work) throws SQLException {
        try (Connection conn = dataSource.getConnection()) {
            conn.setAutoCommit(false);
            try {
                T result = work.run(conn);
                conn.commit();
                return result;
            } catch (SQLException | RuntimeException e) {
                try {
                    conn.rollback();
                } catch (SQLException rollbackFailure) {
                    e.addSuppressed(rollbackFailure); // keep the original error on top
                }
                throw e;
            } finally {
                conn.setAutoCommit(true);
            }
        }
    }
}

// Usage: both repository calls receive the same connection.
// long id = Tx.inTransaction(dataSource, conn -> {
//     stock.reserve(conn, productId, quantity);
//     return orders.insert(conn, customerId, productId, quantity);
// });

Skipping bad rows with savepoints

In PostgreSQL, any error marks the whole transaction as aborted and every later statement fails until you roll back. Rolling back to the savepoint clears that state, so a duplicate email skips one row instead of losing the whole import.

Example · java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.util.ArrayList;
import java.util.List;

record Contact(String email, String name) {}

final class ContactImport {
    /** Inserts what it can in one transaction and returns the rows that were rejected. */
    static List<Contact> importAll(Connection conn, List<Contact> contacts) throws SQLException {
        List<Contact> rejected = new ArrayList<>();
        conn.setAutoCommit(false);
        try (PreparedStatement ps = conn.prepareStatement(
                "INSERT INTO contacts (email, name) VALUES (?, ?)")) {
            for (Contact c : contacts) {
                Savepoint before = conn.setSavepoint();
                try {
                    ps.setString(1, c.email());
                    ps.setString(2, c.name());
                    ps.executeUpdate();
                    conn.releaseSavepoint(before);
                } catch (SQLException e) {
                    conn.rollback(before);           // undo this row only
                    rejected.add(c);
                }
            }
            conn.commit();
        } catch (SQLException | RuntimeException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
        return rejected;
    }
}

The same boundary with Spring's @Transactional

Spring binds the connection to the current thread so repositories built on JdbcClient or JPA join the transaction automatically. The annotation works through a proxy, so calling placeOrder from another method of the same class bypasses it.

Example · java
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

interface ProductRepository {
    boolean reserve(long productId, int quantity);
}

interface OrderRepository {
    long insert(long customerId, long productId, int quantity);
}

class OutOfStockException extends RuntimeException {
    OutOfStockException(long productId) {
        super("Out of stock: product " + productId);
    }
}

@Service
public class OrderService {
    private final ProductRepository products;
    private final OrderRepository orders;

    public OrderService(ProductRepository products, OrderRepository orders) {
        this.products = products;
        this.orders = orders;
    }

    // One transaction around both repository calls; both use the same connection.
    @Transactional
    public long placeOrder(long customerId, long productId, int quantity) {
        if (!products.reserve(productId, quantity)) {
            throw new OutOfStockException(productId);  // unchecked, so Spring rolls back
        }
        return orders.insert(customerId, productId, quantity);
    }
}

Related

java preparedstatement and parametersjava connection pooling with hikaricpjava the repository patternjava multi catch and rethrowingjava what a framework adds

Discussion

  • Be the first to comment on this lesson.