Транзакции

Перевод денег — это два действия: списать со счёта Анны и зачислить на счёт Олега. Если программа упадёт между ними, деньги исчезнут. Транзакция объединяет несколько команд в одно целое: либо выполняются все, либо ни одной. Во всех примерах урока база создаётся методом openBank(): у Анны на счёте $100, у Олега $50. Метод printBalances выводит остатки.

Автофиксация

По умолчанию соединение JDBC работает в режиме автофиксации (auto-commit): каждая команда сразу и навсегда сохраняется в базе. Посмотрим, что будет, если вторая команда перевода сорвётся — здесь из-за опечатки в имени таблицы:

import java.sql.*;
 
void main() throws SQLException {
    try (Connection conn = openBank(); Statement st = conn.createStatement()) {
        IO.println("Автофиксация: " + conn.getAutoCommit());
        try {
            st.executeUpdate("UPDATE accounts SET balance = balance - 30 WHERE owner = 'Анна'");
            st.executeUpdate("UPDATE acounts SET balance = balance + 30 WHERE owner = 'Олег'");
        } catch (SQLException e) {
            IO.println("Ошибка: " + e.getMessage());
        }
        printBalances(conn);
    }
}
 
// Тестовая база: у Анны $100, у Олега $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 ('Анна', 100), ('Олег', 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"));
        }
    }
}

Результат:

Автофиксация: true
Ошибка: [SQLITE_ERROR] SQL error or missing database (no such table: acounts)
Анна: $70
Олег: $50

Списание сохранилось, а зачисление — нет: $30 пропали.

commit

Чтобы управлять транзакцией вручную, автофиксацию выключают: conn.setAutoCommit(false). Теперь изменения копятся и становятся постоянными только после conn.commit() — фиксации транзакции:

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 = 'Анна'");
        st.executeUpdate("UPDATE accounts SET balance = balance + 30 WHERE owner = 'Олег'");
        conn.commit();
        printBalances(conn);
    }
}
 
// Тестовая база: у Анны $100, у Олега $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 ('Анна', 100), ('Олег', 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"));
        }
    }
}

Результат:

Анна: $70
Олег: $80

rollback

Если что-то пошло не так, conn.rollback() — откат — отменяет все изменения с начала транзакции. Повторим неудачный перевод, но теперь в транзакции:

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 = 'Анна'");
            st.executeUpdate("UPDATE acounts SET balance = balance + 30 WHERE owner = 'Олег'");
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            IO.println("Перевод отменён");
        }
        printBalances(conn);
    }
}
 
// Тестовая база: у Анны $100, у Олега $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 ('Анна', 100), ('Олег', 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"));
        }
    }
}

Результат:

Перевод отменён
Анна: $100
Олег: $50

Списание тоже отменилось — деньги на месте.

Надёжный шаблон

В настоящем коде перевод выносят в метод: запросы — в try-with-resources, commit в конце, rollback при любой ошибке, а исключение передают дальше, чтобы вызывающий узнал о неудаче. Здесь база сама не даёт уйти в минус — условие CHECK (balance >= 0) в таблице:

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, "Анна", "Олег", 40);
        try {
            transfer(conn, "Олег", "Анна", 500);
        } catch (SQLException e) {
            IO.println("Не вышло: " + e.getMessage());
        }
        printBalances(conn);
    }
}
 
// Тестовая база: у Анны $100, у Олега $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 ('Анна', 100), ('Олег', 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"));
        }
    }
}

Результат:

Не вышло: [SQLITE_CONSTRAINT_CHECK] A CHECK constraint failed (CHECK constraint failed: balance >= 0)
Анна: $60
Олег: $90

Первый перевод прошёл. Во втором зачисление Анне уже выполнилось, но списание $500 у Олега нарушило условие — и rollback отменил зачисление. В блоке finally автофиксацию включают обратно, чтобы соединение осталось в привычном состоянии. В приложениях на Spring то же самое делает аннотация @Transactional.

Частая ошибка

С выключенной автофиксацией легко забыть commit(). Программа работает без ошибок, но при закрытии соединения незафиксированные изменения в SQLite, PostgreSQL и большинстве других баз пропадают (точное поведение зависит от драйвера, и полагаться на него нельзя). Проверим на базе в файле: запишем, закроем соединение и откроем заново:

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 ('Анна'), ('Олег')");
        IO.println("Заказы добавлены");
    }
 
    try (Connection conn = DriverManager.getConnection(url); Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT count(*) FROM orders")) {
        rs.next();
        IO.println("Заказов в базе: " + rs.getInt(1));
    }
    Files.delete(file);
}

Результат:

Заказы добавлены
Заказов в базе: 0

Таблица создана ещё в режиме автофиксации и сохранилась, а оба заказа потерялись. Каждую транзакцию заканчивайте явно — commit() при успехе и rollback() при ошибке, как в шаблоне выше.

Спасибо, что читаете!

Автофиксация

commit

rollback

Надёжный шаблон

Частая ошибка

Спасибо, что читаете!