Какие знаешь SQL-феномены?

SQL-феномены в системах с параллельными транзакциями

SQL-феномены — это нежелательные явления, возникающие при одновременном выполнении нескольких транзакций в системе управления базами данных. Понимание этих феноменов критично для выбора правильного уровня изоляции транзакций и проектирования надёжных приложений.

Основные SQL-феномены

1. Dirty Read (Грязное чтение)

Транзакция B читает данные, которые ещё не были закоммичены транзакцией A. Если транзакция A откатится, то B имела дело с данными, которые никогда не существовали.

// Сценарий Dirty Read
// Транзакция A:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- баланс = 900
// (баланс не закоммичен)

// Параллельно, Транзакция B:
SELECT balance FROM accounts WHERE id = 1;  -- читает 900 (грязное значение)

// Транзакция A откатывается:
ROLLBACK;  -- баланс остался 1000

// Результат: B видела несуществующее значение 900

Пример в Java с JDBC:

Connection conn = dataSource.getConnection();
conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

// Уровень READ_UNCOMMITTED позволяет Dirty Read
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM accounts");

Защита: Уровни изоляции READ_COMMITTED и выше.

2. Non-Repeatable Read (Неповторяемое чтение)

Транзакция A читает одни и те же данные дважды, но между чтениями транзакция B изменила эти данные и закоммитила изменения.

// Транзакция A, Чтение 1:
SELECT balance FROM accounts WHERE id = 1;  -- читает 1000

// Параллельно, Транзакция B:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- баланс = 900, изменения видны

// Транзакция A, Чтение 2:
SELECT balance FROM accounts WHERE id = 1;  -- читает 900 (другое значение!)

// Результат: два чтения одного поля дали разные результаты

Пример в Java:

public class NonRepeatableReadExample {
    private DataSource dataSource;
    
    public void demonstrateProblem() throws SQLException {
        Connection conn = dataSource.getConnection();
        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        
        // Первое чтение
        int balance1 = readBalance(conn, 1);
        
        // Здесь другой поток может обновить данные
        // ...
        
        // Второе чтение
        int balance2 = readBalance(conn, 1);
        
        if (balance1 != balance2) {
            System.out.println("Non-repeatable read произошёл!");
        }
    }
    
    private int readBalance(Connection conn, int id) throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(
            "SELECT balance FROM accounts WHERE id = " + id
        );
        rs.next();
        return rs.getInt("balance");
    }
}

Защита: Уровни изоляции REPEATABLE_READ и выше.

3. Phantom Read (Фантомное чтение)

Транзакция A выполняет запрос, возвращающий набор строк, затем транзакция B добавляет новые строки, соответствующие условию A. Когда A повторяет запрос, получает дополнительные "фантомные" строки.

// Транзакция A, Запрос 1:
SELECT * FROM orders WHERE customer_id = 1;
// Результат: 3 заказа

// Параллельно, Транзакция B:
INSERT INTO orders (customer_id, amount) VALUES (1, 5000);
COMMIT;

// Транзакция A, Запрос 2 (с тем же WHERE):
SELECT * FROM orders WHERE customer_id = 1;
// Результат: 4 заказа (появился фантом!)

Пример в Java:

public class PhantomReadExample {
    private DataSource dataSource;
    
    public void demonstrateProblem() throws SQLException {
        Connection conn = dataSource.getConnection();
        conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
        
        // Первый запрос
        int count1 = countOrders(conn, 1);
        System.out.println("Первый запрос: " + count1 + " заказов");
        
        // Другой поток может добавить новый заказ
        // ...
        
        // Второй запрос
        int count2 = countOrders(conn, 1);
        System.out.println("Второй запрос: " + count2 + " заказов");
        
        if (count1 != count2) {
            System.out.println("Phantom read произошёл!");
        }
    }
    
    private int countOrders(Connection conn, int customerId) throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(
            "SELECT COUNT(*) FROM orders WHERE customer_id = " + customerId
        );
        rs.next();
        return rs.getInt(1);
    }
}

Защита: Уровень изоляции SERIALIZABLE.

4. Lost Update (Потеря обновления)

Две транзакции читают одно и то же значение, обе модифицируют его на основе прочитанного значения, и вторая запись перезаписывает первую.

// Транзакция A:
SELECT salary FROM employees WHERE id = 1;  -- 1000
UPDATE employees SET salary = salary + 100 WHERE id = 1;  -- 1100
COMMIT;

// Параллельно, Транзакция B (читала до коммита A):
SELECT salary FROM employees WHERE id = 1;  -- тоже 1000 (грязное чтение?)
UPDATE employees SET salary = salary + 200 WHERE id = 1;  -- 1200, перезаписало A
COMMIT;

// Результат: увеличение на 100 потеряно, осталось только +200

Защита: Использование блокировок (SELECT FOR UPDATE) или оптимистичных блокировок (версионирование).

Матрица уровней изоляции

Уровень изоляции          | Dirty Read | Non-Rep. Read | Phantom Read
-----------------------|----------|-------------|----------
READ_UNCOMMITTED       | ✓ (да)   | ✓           | ✓
READ_COMMITTED         | ✗        | ✓           | ✓
REPEATABLE_READ        | ✗        | ✗           | ✓
SERIALIZABLE           | ✗        | ✗           | ✗

Уровни изоляции в Java

public class IsolationLevelExample {
    private DataSource dataSource;
    
    // 1. READ_UNCOMMITTED - самый низкий
    public void readUncommittedExample() throws SQLException {
        Connection conn = dataSource.getConnection();
        conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
        // Быстро, но небезопасно - возможны все феномены
    }
    
    // 2. READ_COMMITTED - по умолчанию во многих БД
    public void readCommittedExample() throws SQLException {
        Connection conn = dataSource.getConnection();
        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        // Защита от Dirty Read
    }
    
    // 3. REPEATABLE_READ - средний уровень
    public void repeatableReadExample() throws SQLException {
        Connection conn = dataSource.getConnection();
        conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
        // Защита от Dirty Read и Non-Repeatable Read
    }
    
    // 4. SERIALIZABLE - максимальный уровень
    public void serializableExample() throws SQLException {
        Connection conn = dataSource.getConnection();
        conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        // Полная защита, но низкая производительность
    }
}

Практические рекомендации

Использование SELECT FOR UPDATE

public class PessimisticLockingExample {
    private DataSource dataSource;
    
    public void transferMoney(int fromId, int toId, BigDecimal amount) 
            throws SQLException {
        Connection conn = dataSource.getConnection();
        try {
            conn.setAutoCommit(false);
            
            // Пессимистичная блокировка
            Statement stmt = conn.createStatement();
            
            // Читаем с блокировкой
            ResultSet rs = stmt.executeQuery(
                "SELECT * FROM accounts WHERE id IN (" + fromId + ", " + toId + ") "
                + "FOR UPDATE"
            );
            
            // Обновляем
            stmt.executeUpdate(
                "UPDATE accounts SET balance = balance - " + amount 
                + " WHERE id = " + fromId
            );
            
            stmt.executeUpdate(
                "UPDATE accounts SET balance = balance + " + amount 
                + " WHERE id = " + toId
            );
            
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        }
    }
}

Оптимистичная блокировка (Optimistic Locking)

public class OptimisticLockingExample {
    private DataSource dataSource;
    
    public static class Account {
        public int id;
        public BigDecimal balance;
        public int version;  // Версия для оптимистичной блокировки
    }
    
    public void updateBalance(Account account, BigDecimal newBalance) 
            throws SQLException {
        Connection conn = dataSource.getConnection();
        
        Statement stmt = conn.createStatement();
        
        // UPDATE с проверкой версии
        String sql = "UPDATE accounts SET balance = " + newBalance 
            + ", version = version + 1 "
            + "WHERE id = " + account.id + " AND version = " + account.version;
        
        int updated = stmt.executeUpdate(sql);
        
        if (updated == 0) {
            throw new OptimisticLockException(
                "Версия данных изменилась, обновление отменено"
            );
        }
    }
}

public class OptimisticLockException extends RuntimeException {
    public OptimisticLockException(String message) {
        super(message);
    }
}

Вывод

Понимание SQL-феноменов позволяет разработчику:

1. Выбирать правильный уровень изоляции для своего приложения
2. Прогнозировать возможные проблемы в многопоточных приложениях
3. Реализовать надёжные механизмы синхронизации данных
4. Оптимизировать производительность без потери целостности данных

Ключевое правило: выше уровень изоляции = безопаснее, но медленнее. Нужно найти баланс для конкретного приложения.