JAVA-250 · Lesson 4 of 12 · 23 min

Transactions in JDBC

▶ Video coming soon A video version of this lesson is on the way.

Velotek's delivery came on the morning of Friday, October 8: twenty 700x25c inner tubes and six brass bells. Receiving it in StockStore meant two writes for each part, raising the quantity in the part table and adding a RECEIVED row to stock_movement, so that the log Kofi used to answer "where did the tubes go?" would always add up to the count on the shelf. At the afternoon session Ingrid asked the question this lesson answers: "What happens if the program dies between the first write and the second?" With each statement saved on its own, the count would go up and the log would never hear about it. The cure is a transaction.

What a transaction promises

A transaction is a group of statements that the database treats as one unit. Database people describe its guarantees with the letters ACID. Atomicity means all of the statements take effect or none do; there is no state in which the quantity has changed and the movement has not. Consistency means the transaction moves the database from one valid state to another, with every constraint satisfied at the end. Isolation means transactions running at the same time do not see each other's unfinished work, to a degree you choose. Durability means that once the database reports a commit, the result survives a crash, the property StockRoom lacked in August.

A timeline for receiving tubes. Inside a bracket labelled one transaction are two boxes: UPDATE part quantity plus 20, and INSERT stock_movement RECEIVED 20. The bracket ends in a fork: commit makes both permanent; rollback or a crash leaves neither.
A transaction makes the two writes of a delivery succeed or fail together.

Auto-commit, commit and rollback

A new JDBC connection is in auto-commit mode: each statement is its own transaction and is committed as soon as it finishes. That is right for a single statement and wrong for a group. To make several statements one transaction, call setAutoCommit(false), run the statements, and call commit(). If anything fails, call rollback(), which undoes everything since the transaction began. The pattern below is the shape of almost every transaction in plain JDBC; lesson 6 hides it inside one helper so that it is written once.

Java Example · Part of Receiving.java (needs a driver)
public void receive(Connection conn, String code, int quantity) throws SQLException {
    boolean oldAutoCommit = conn.getAutoCommit();
    conn.setAutoCommit(false);
    try (PreparedStatement up = conn.prepareStatement(
                 "UPDATE part SET quantity = quantity + ? WHERE code = ?");
         PreparedStatement log = conn.prepareStatement(
                 "INSERT INTO stock_movement (code, change, reason, at) VALUES (?, ?, 'RECEIVED', ?)")) {
        up.setInt(1, quantity);
        up.setString(2, code);
        if (up.executeUpdate() != 1) throw new SQLException("unknown part " + code, "02000");
        log.setString(1, code);
        log.setInt(2, quantity);
        log.setString(3, now());
        log.executeUpdate();
        conn.commit();                       // both writes become permanent together
    } catch (SQLException | RuntimeException e) {
        conn.rollback();                     // neither write happened
        throw e;
    } finally {
        conn.setAutoCommit(oldAutoCommit);
    }
}

What this code does

Turning auto-commit off starts a transaction that lasts until commit or rollback. If the part code is unknown, or the insert fails, the catch block rolls back both statements and rethrows the exception so the caller knows. The finally block restores auto-commit, which matters when the connection goes back to a pool and is reused.

The same thing can be watched in SQL. In SQLite, BEGIN starts a transaction and ROLLBACK abandons it; the script below adds twenty tubes inside a transaction, changes its mind, and then checks the count, which is back where it started.

BEGIN;
UPDATE part SET quantity = quantity + 20 WHERE code = 'TUBE-700x25';
INSERT INTO stock_movement (code, change, reason, at)
VALUES ('TUBE-700x25', 20, 'RECEIVED', '2027-10-08 10:15');
ROLLBACK;

What this code does

Both statements run inside the transaction, so the database holds the new count and the new movement until the end. ROLLBACK throws both away. The check afterwards shows the original 14 tubes and the original five movements.

Result after ROLLBACK: the count and the log are unchanged.
codequantitymovements
TUBE-700x25145

Rollback is not only for crashes. A transaction should also be rolled back when the program discovers that the work cannot be completed, for example when a sale would take a quantity below zero. The part table's CHECK constraint makes the database refuse that on its own, and in a transaction the refusal undoes the rest of the work too.

UPDATE part SET quantity = quantity - 5 WHERE code = 'BELL';

What this code does

There are only three bells, so taking five would leave minus two. The CHECK (quantity >= 0) constraint in the table makes SQLite refuse the update, and through JDBC the refusal arrives as an SQLException; PostgreSQL reports it with SQLState 23514, a class 23 integrity error.

What SQLite answers
Error: CHECK constraint failed: quantity >= 0

A transaction you can run here

The behaviour of commit and rollback is easy to model in Java, which is a good way to understand it. The class below keeps committed stock in one map and the current transaction's changes in another; reads see the transaction's own changes, commit copies them into the committed map, and rollback throws them away. A real database does far more, including surviving crashes, but the observable rules are the same.

Java Example · Main.java
import java.util.*;

public class Main {
    static class Store {
        private final Map<String, Integer> committed = new TreeMap<>();
        private Map<String, Integer> pending = null;           // null when no transaction is open
        private final List<String> log = new ArrayList<>();

        void begin() { pending = new TreeMap<>(committed); }
        int get(String code) { return (pending != null ? pending : committed).getOrDefault(code, 0); }
        void add(String code, int by) {
            int next = get(code) + by;
            if (next < 0) throw new IllegalStateException("CHECK failed: " + code + " would be " + next);
            pending.put(code, next);
        }
        void commit() { committed.clear(); committed.putAll(pending); pending = null; log.add("commit"); }
        void rollback() { pending = null; log.add("rollback"); }
    }

    static void sell(Store s, Map<String, Integer> order) {
        s.begin();
        try {
            order.forEach((code, qty) -> s.add(code, -qty));
            s.commit();
            System.out.println("Sold " + order);
        } catch (IllegalStateException e) {
            s.rollback();
            System.out.println("Sale refused (" + e.getMessage() + "), nothing changed");
        }
    }

    public static void main(String[] args) {
        Store s = new Store();
        s.begin(); s.add("BELL", 3); s.add("TUBE-700x25", 14); s.commit();

        sell(s, new LinkedHashMap<>(Map.of("TUBE-700x25", 2)));
        LinkedHashMap<String, Integer> big = new LinkedHashMap<>();
        big.put("TUBE-700x25", 1);
        big.put("BELL", 5);
        sell(s, big);

        System.out.println("Tubes: " + s.get("TUBE-700x25") + ", bells: " + s.get("BELL") + ", log: " + s.log);
    }
}
Output
Sold {TUBE-700x25=2}
Sale refused (CHECK failed: BELL would be -2), nothing changed
Tubes: 12, bells: 3, log: [commit, commit, rollback]

What this code does

The second sale takes a tube successfully inside its transaction and then fails on the bells, because five is more than three. Rollback discards the pending map, so the tube that was taken a moment earlier is back as well. Only the first sale and the initial stock were committed, which leaves 12 tubes and 3 bells.

The lost update

Transactions also protect against a quieter problem that appears as soon as two computers share stock. On Saturdays the counter and the parts room both sell bells. Suppose each program reads the count, subtracts in Java and writes the result back. If both read before either writes, both write the same new value and one sale disappears from the count. This is called a lost update, and it was the same bug as Kofi's minus-one tube in the concurrency course, now between two computers instead of two threads.

Java Example · Main.java
import java.util.*;

public class Main {
    static Map<String, Integer> db = new HashMap<>(Map.of("BELL", 3));

    public static void main(String[] args) {
        // read-modify-write in Java, interleaved the unlucky way
        int counterRead = db.get("BELL");          // counter reads 3
        int partsRoomRead = db.get("BELL");        // parts room reads 3
        db.put("BELL", counterRead - 1);           // counter writes 2
        db.put("BELL", partsRoomRead - 1);         // parts room writes 2
        System.out.println("Read-modify-write: two bells sold, count says " + db.get("BELL"));

        // the database does the arithmetic: UPDATE part SET quantity = quantity - 1 WHERE code = ? AND quantity >= 1
        db.put("BELL", 3);
        System.out.println("Counter sale applied: " + sellOne("BELL"));
        System.out.println("Parts room sale applied: " + sellOne("BELL"));
        System.out.println("Atomic update: two bells sold, count says " + db.get("BELL"));
        db.put("BELL", 0);
        System.out.println("Sale with none left applied: " + sellOne("BELL"));
    }

    // models one atomic conditional UPDATE: the check and the change happen together
    static boolean sellOne(String code) {
        int q = db.get(code);
        if (q < 1) return false;                   // 0 rows updated
        db.put(code, q - 1);                       // 1 row updated
        return true;
    }
}
Output
Read-modify-write: two bells sold, count says 2
Counter sale applied: true
Parts room sale applied: true
Atomic update: two bells sold, count says 1
Sale with none left applied: false

What this code does

In the first half each program computes its new value from a stale read, so the second write replaces the first and the count says 2 when it should say 1. In the second half each sale is one conditional UPDATE in which the database checks and changes the value in a single step, and the program learns from the update count whether it worked. When no bells are left the UPDATE changes no rows, and the sale is refused instead of driving the count below zero.

In SQL the fix is to let the database do the arithmetic, with a condition that makes the update refuse when stock is short. executeUpdate then returns 1 for a successful sale and 0 for a refused one. Run here against the sample data, the first statement sells one road brake pad and the second, which asks for ten, changes nothing.

UPDATE part SET quantity = quantity - 1 WHERE code = 'PAD-ROAD' AND quantity >= 1;
UPDATE part SET quantity = quantity - 10 WHERE code = 'PAD-ROAD' AND quantity >= 10;

What this code does

The first update finds three pads, which is at least one, so it takes one. The second finds two, which is fewer than ten, so its WHERE clause matches no row and nothing changes. Through JDBC the two executeUpdate calls would return 1 and 0.

Result: one pad sold, the oversized sale refused, two pads left.
codequantity
PAD-ROAD2

When an operation needs to read, decide in Java and then write, for example to compare a price before reserving stock, the other tools are a pessimistic lock, SELECT ... FOR UPDATE in PostgreSQL, which makes other transactions wait for the row, and optimistic locking, which adds a version column and updates only WHERE version = ? the value that was read. JPA supports optimistic locking with a @Version field, which lesson 8 mentions.

Isolation levels

Complete isolation is expensive, so SQL defines four isolation levels that trade safety for concurrency. Each prevents more of the read phenomena: a dirty read sees another transaction's uncommitted change; a non-repeatable read reads the same row twice and gets different values because another transaction committed in between; a phantom read runs the same query twice and gets new rows. You set the level for a connection with setTransactionIsolation and the constants on Connection.

The four standard isolation levels.
LevelDirty readNon-repeatable readPhantomNotes
READ UNCOMMITTEDpossiblepossiblepossibleRarely used; PostgreSQL treats it as READ COMMITTED
READ COMMITTEDpreventedpossiblepossiblePostgreSQL's default
REPEATABLE READpreventedpreventedpossible in the standardPostgreSQL also prevents phantoms here
SERIALIZABLEpreventedpreventedpreventedBehaves as if transactions ran one at a time; SQLite effectively works this way
Java Example · Main.java
import java.sql.Connection;
import java.util.*;

public class Main {
    public static void main(String[] args) {
        Map<String, Integer> levels = new LinkedHashMap<>();
        levels.put("TRANSACTION_NONE", Connection.TRANSACTION_NONE);
        levels.put("TRANSACTION_READ_UNCOMMITTED", Connection.TRANSACTION_READ_UNCOMMITTED);
        levels.put("TRANSACTION_READ_COMMITTED", Connection.TRANSACTION_READ_COMMITTED);
        levels.put("TRANSACTION_REPEATABLE_READ", Connection.TRANSACTION_REPEATABLE_READ);
        levels.put("TRANSACTION_SERIALIZABLE", Connection.TRANSACTION_SERIALIZABLE);
        levels.forEach((name, value) -> System.out.printf("%-30s %d%n", name, value));

        int chosen = Connection.TRANSACTION_SERIALIZABLE;
        boolean stricter = chosen > Connection.TRANSACTION_READ_COMMITTED;
        System.out.println("SERIALIZABLE is stricter than READ COMMITTED: " + stricter);
    }
}
Output
TRANSACTION_NONE               0
TRANSACTION_READ_UNCOMMITTED   1
TRANSACTION_READ_COMMITTED     2
TRANSACTION_REPEATABLE_READ    4
TRANSACTION_SERIALIZABLE       8
SERIALIZABLE is stricter than READ COMMITTED: true

What this code does

The constants are plain ints defined in the Connection interface, so they print without any database. Each stricter level has a larger value, which is why the comparison works, although code should name the constant rather than use the number. You would pass one of them to conn.setTransactionIsolation before starting a transaction.

For StockStore, Ingrid's advice was to use the database default, write updates in the atomic style shown above, and reach for SERIALIZABLE only for the few transactions that must read, think and write, such as checking that a rental booking does not overlap another. At SERIALIZABLE the database may abort one of two conflicting transactions with SQLState 40001, and the right response is to run the whole transaction again.

Keeping transactions healthy

Transactions hold locks and other resources until they end, so keep them short. Do all the slow work, such as reading a file, calling a supplier's price service or waiting for a person to click Confirm, before the transaction begins, and run only the database statements inside it. Never leave a transaction open while waiting for user input. Make sure every path ends in commit or rollback, which the try, catch and finally pattern guarantees. Savepoints, created with conn.setSavepoint(), let a long transaction roll back part of its work and continue, but most applications never need them.

Try it yourself

Roll back a failed transfer

The method moveStock moves a quantity from one shelf bin to another inside a transaction.

It already begins the transaction and commits it. Add the missing rollback so that a failed move leaves both bins unchanged, then the program should print Moved 2: {A-02=4, B-07=8} and Refused, bins: {A-02=4, B-07=8}.

import java.util.*;

public class Main {
    static Map<String, Integer> committed = new TreeMap<>(Map.of("A-02", 6, "B-07", 6));
    static Map<String, Integer> pending;

    static void take(String bin, int n) {
        int left = pending.get(bin) - n;
        if (left < 0) throw new IllegalStateException("not enough in " + bin);
        pending.put(bin, left);
    }

    static void moveStock(String from, String to, int n) {
        pending = new TreeMap<>(committed);
        try {
            pending.put(to, pending.get(to) + n);
            take(from, n);
            committed = pending;
            System.out.println("Moved " + n + ": " + committed);
        } catch (IllegalStateException e) {
            committed = pending;   // wrong: this keeps half of the failed move
            System.out.println("Refused, bins: " + committed);
        }
    }

    public static void main(String[] args) {
        moveStock("A-02", "B-07", 2);
        moveStock("A-02", "B-07", 10);
    }
}

Try it yourself

A conditional sale

Write sell so it behaves like UPDATE part SET quantity = quantity - n WHERE code = ? AND quantity >= n: if there is enough stock, subtract n and return 1 (one row changed); otherwise change nothing and return 0.

The program sells chains three times and prints each update count and the count left.

import java.util.*;

public class Main {
    static Map<String, Integer> stock = new HashMap<>(Map.of("CHAIN-11", 3));

    static int sell(String code, int n) {
        stock.put(code, stock.get(code) - n);
        return 1;
    }

    public static void main(String[] args) {
        System.out.println(sell("CHAIN-11", 2) + " row(s), left " + stock.get("CHAIN-11"));
        System.out.println(sell("CHAIN-11", 2) + " row(s), left " + stock.get("CHAIN-11"));
        System.out.println(sell("CHAIN-11", 1) + " row(s), left " + stock.get("CHAIN-11"));
    }
}

Common mistakes

  • MistakeRunning related writes in auto-commit mode.

    InsteadTurn auto-commit off, run the statements, then commit, or roll back on any failure.

  • MistakeCatching an exception inside a transaction and committing anyway.

    InsteadRoll back in the catch block and rethrow.

  • MistakeReading a count, subtracting in Java and writing it back.

    InsteadLet the database do the arithmetic in one conditional UPDATE and check the update count.

  • MistakeCalling slow services or waiting for a person inside a transaction.

    InsteadDo slow work first and keep the transaction to the database statements.

  • MistakeForgetting to restore auto-commit before a pooled connection is reused.

    InsteadRestore it in a finally block.

Summary and key terms

  • A transaction groups statements so they succeed or fail together, with the ACID guarantees of atomicity, consistency, isolation and durability.
  • JDBC connections start in auto-commit mode, where each statement commits on its own; setAutoCommit(false) starts a transaction that ends with commit() or rollback().
  • The standard pattern runs the statements in try, commits at the end, rolls back and rethrows in catch, and restores auto-commit in finally.
  • Constraints such as CHECK make the database refuse invalid changes, and in a transaction the refusal undoes the rest of the work.
  • A lost update happens when two programs read, change in Java and write back; atomic conditional UPDATE statements, row locks or optimistic version checks prevent it.
  • Isolation levels from READ UNCOMMITTED to SERIALIZABLE prevent progressively more read phenomena; serializable transactions may be aborted and must then be retried.
  • Keep transactions short and never wait for a person inside one.
Transaction
A group of statements that the database applies completely or not at all.
ACID
Atomicity, consistency, isolation and durability, the guarantees of a transaction.
Auto-commit
The default JDBC mode in which each statement is committed on its own.
Commit
Ending a transaction so that its changes become permanent.
Rollback
Ending a transaction by undoing all of its changes.
Lost update
A change that disappears because another transaction wrote over it using a stale read.
Isolation level
A setting that controls how much concurrent transactions can see of each other.
Dirty read
Reading another transaction's uncommitted change.
Optimistic locking
Detecting conflicting updates by checking a version number when writing.
Savepoint
A marker inside a transaction to which part of the work can be rolled back.

Practice problems

Work each problem on paper first, then reveal the answer to check.

  1. Why must the quantity update and the movement insert share a transaction?

    Show answer

    So that the log always matches the count, even if the program fails between them.

  2. What mode is a new JDBC connection in?

    Show answer

    Auto-commit.

  3. What should a catch block inside a transaction do?

    Show answer

    Roll back, then rethrow.

  4. How does UPDATE ... WHERE quantity >= 1 prevent a lost update?

    Show answer

    The database checks and changes the value in one step.

  5. What SQLState tells you a serializable transaction should be retried?

    Show answer

    40001.

Lesson quiz · 5 questions

Score 60% or more to complete this lesson.

Take the quiz