Transactions

A money transfer is two actions: take money from Anna's account and put it into Oleg's. If the program crashes between them, the money disappears. A transaction joins several commands into one whole: either all of them run, or none. In every example of this lesson the database is created by the openBank() method: Anna has $100 in her account, Oleg has $50. The printBalances method prints the balances.

Auto-commit

By default a JDBC connection works in auto-commit mode: each command is saved in the database immediately and for good. Let's see what happens if the second command of a transfer fails — here because of a typo in the table name:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = openBank(); Statement st = conn.createStatement()) {
        IO.println("Auto-commit: " + conn.getAutoCommit());
        try {
            st.executeUpdate("UPDATE accounts SET balance = balance - 30 WHERE owner = 'Anna'");
            st.executeUpdate("UPDATE acounts SET balance = balance + 30 WHERE owner = 'Oleg'");
        } catch (SQLException e) {
            IO.println("Error: " + e.getMessage());
        }
        printBalances(conn);
    }
}
 
// Test database: Anna has $100, Oleg has $50
Connection openBank() throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
    try (Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE accounts (owner TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))");
        st.executeUpdate("INSERT INTO accounts VALUES ('Anna', 100), ('Oleg', 50)");
    }
    return conn;
}
 
void printBalances(Connection conn) throws SQLException {
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT owner, balance FROM accounts ORDER BY owner")) {
        while (rs.next()) {
            IO.println(rs.getString("owner") + ": $" + rs.getInt("balance"));
        }
    }
}

Output:

Auto-commit: true
Error: [SQLITE_ERROR] SQL error or missing database (no such table: acounts)
Anna: $70
Oleg: $50

The withdrawal was saved, but the deposit wasn't: $30 vanished.

commit

To control a transaction by hand, turn auto-commit off: conn.setAutoCommit(false). Now changes pile up and become permanent only after conn.commit() — committing the transaction:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = openBank(); Statement st = conn.createStatement()) {
        conn.setAutoCommit(false);
        st.executeUpdate("UPDATE accounts SET balance = balance - 30 WHERE owner = 'Anna'");
        st.executeUpdate("UPDATE accounts SET balance = balance + 30 WHERE owner = 'Oleg'");
        conn.commit();
        printBalances(conn);
    }
}
 
// Test database: Anna has $100, Oleg has $50
Connection openBank() throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
    try (Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE accounts (owner TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))");
        st.executeUpdate("INSERT INTO accounts VALUES ('Anna', 100), ('Oleg', 50)");
    }
    return conn;
}
 
void printBalances(Connection conn) throws SQLException {
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT owner, balance FROM accounts ORDER BY owner")) {
        while (rs.next()) {
            IO.println(rs.getString("owner") + ": $" + rs.getInt("balance"));
        }
    }
}

Output:

Anna: $70
Oleg: $80

rollback

If something goes wrong, conn.rollback() — a rollback — undoes all changes since the start of the transaction. Let's repeat the failed transfer, this time inside a transaction:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = openBank(); Statement st = conn.createStatement()) {
        conn.setAutoCommit(false);
        try {
            st.executeUpdate("UPDATE accounts SET balance = balance - 30 WHERE owner = 'Anna'");
            st.executeUpdate("UPDATE acounts SET balance = balance + 30 WHERE owner = 'Oleg'");
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            IO.println("Transfer cancelled");
        }
        printBalances(conn);
    }
}
 
// Test database: Anna has $100, Oleg has $50
Connection openBank() throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
    try (Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE accounts (owner TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))");
        st.executeUpdate("INSERT INTO accounts VALUES ('Anna', 100), ('Oleg', 50)");
    }
    return conn;
}
 
void printBalances(Connection conn) throws SQLException {
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT owner, balance FROM accounts ORDER BY owner")) {
        while (rs.next()) {
            IO.println(rs.getString("owner") + ": $" + rs.getInt("balance"));
        }
    }
}

Output:

Transfer cancelled
Anna: $100
Oleg: $50

The withdrawal was undone too — the money is in place.

A reliable pattern

In real code a transfer goes into a method: statements in try-with-resources, commit at the end, rollback on any error, and the exception is passed on so the caller learns about the failure. Here the database itself won't let a balance go negative — thanks to the CHECK (balance >= 0) condition in the table:

import java.sql.*;
 
void transfer(Connection conn, String from, String to, int amount) throws SQLException {
    conn.setAutoCommit(false);
    try (PreparedStatement deposit = conn.prepareStatement("UPDATE accounts SET balance = balance + ? WHERE owner = ?");
         PreparedStatement withdraw = conn.prepareStatement("UPDATE accounts SET balance = balance - ? WHERE owner = ?")) {
        deposit.setInt(1, amount);
        deposit.setString(2, to);
        deposit.executeUpdate();
 
        withdraw.setInt(1, amount);
        withdraw.setString(2, from);
        withdraw.executeUpdate();
 
        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    } finally {
        conn.setAutoCommit(true);
    }
}
 
void main() throws SQLException {
    try (Connection conn = openBank()) {
        transfer(conn, "Anna", "Oleg", 40);
        try {
            transfer(conn, "Oleg", "Anna", 500);
        } catch (SQLException e) {
            IO.println("Failed: " + e.getMessage());
        }
        printBalances(conn);
    }
}
 
// Test database: Anna has $100, Oleg has $50
Connection openBank() throws SQLException {
    Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
    try (Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE accounts (owner TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))");
        st.executeUpdate("INSERT INTO accounts VALUES ('Anna', 100), ('Oleg', 50)");
    }
    return conn;
}
 
void printBalances(Connection conn) throws SQLException {
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT owner, balance FROM accounts ORDER BY owner")) {
        while (rs.next()) {
            IO.println(rs.getString("owner") + ": $" + rs.getInt("balance"));
        }
    }
}

Output:

Failed: [SQLITE_CONSTRAINT_CHECK] A CHECK constraint failed (CHECK constraint failed: balance >= 0)
Anna: $60
Oleg: $90

The first transfer went through. In the second, the deposit to Anna had already run, but withdrawing $500 from Oleg broke the condition — and rollback undid the deposit. The finally block turns auto-commit back on so the connection stays in its usual state. In Spring applications the @Transactional annotation does the same job.

Common mistake

With auto-commit off it's easy to forget commit(). The program runs without errors, but when the connection is closed, uncommitted changes are lost in SQLite, PostgreSQL and most other databases (the exact behaviour depends on the driver, and you mustn't rely on it). Let's check with a database in a file: write, close the connection and open it again:

import java.sql.*;
 
void main() throws Exception {
    Path file = Files.createTempFile("shop", ".db");
    String url = "jdbc:sqlite:" + file;
 
    try (Connection conn = DriverManager.getConnection(url); Statement st = conn.createStatement()) {
        st.executeUpdate("CREATE TABLE orders (customer TEXT)");
        conn.setAutoCommit(false);
        st.executeUpdate("INSERT INTO orders VALUES ('Anna'), ('Oleg')");
        IO.println("Orders added");
    }
 
    try (Connection conn = DriverManager.getConnection(url); Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT count(*) FROM orders")) {
        rs.next();
        IO.println("Orders in the database: " + rs.getInt(1));
    }
    Files.delete(file);
}

Output:

Orders added
Orders in the database: 0

The table was created while still in auto-commit mode and was kept, but both orders were lost. End every transaction explicitly — commit() on success and rollback() on error, as in the pattern above.

Thanks for reading!

Auto-commit

commit

rollback

A reliable pattern

Common mistake

Thanks for reading!