Какие знаешь способы выполнения транзакции с помощью SQL?

Способы выполнения транзакций в SQL

Транзакция — это последовательность SQL команд, которые выполняются как одна единица. Либо все команды успешно выполняются (commit), либо все откатываются (rollback). Это гарантирует консистентность данных.

1. ACID свойства транзакций

Прежде чем говорить о способах, нужно понимать гарантии:

• A (Atomicity) — все или ничего
• C (Consistency) — данные переходят из одного корректного состояния в другое
• I (Isolation) — транзакции не мешают друг другу
• D (Durability) — данные сохранены навсегда

2. Базовый синтаксис транзакций

-- Начало транзакции (в PostgreSQL автоматически)
BEGIN;

-- SQL команды
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Фиксирование изменений
COMMIT;

-- Или откат
ROLLBACK;

Это основной способ. Либо обе команды выполняются, либо обе откатываются.

3. Работа с транзакциями в Java (JDBC)

Connection conn = dataSource.getConnection();
try {
    // Отключаем autocommit
    conn.setAutoCommit(false);
    
    // Выполняем SQL
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    stmt.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
    
    // Фиксируем транзакцию
    conn.commit();
    
} catch (Exception e) {
    // Откатываем все изменения
    conn.rollback();
    throw e;
} finally {
    conn.close();
}

4. Spring Transaction Management (аннотация @Transactional)

Это самый удобный способ для Java приложений:

@Service
public class TransferService {
    
    @Autowired
    private AccountRepository accountRepository;
    
    @Transactional
    public void transfer(Long fromId, Long toId, BigDecimal amount) {
        Account from = accountRepository.findById(fromId).orElseThrow();
        Account to = accountRepository.findById(toId).orElseThrow();
        
        from.setBalance(from.getBalance().subtract(amount));
        to.setBalance(to.getBalance().add(amount));
        
        accountRepository.save(from);
        accountRepository.save(to);
        
        // Если здесь выбросится исключение, всё откатится
        if (amount.compareTo(BigDecimal.ZERO) < 0) {
            throw new IllegalArgumentException("Сумма не может быть отрицательной");
        }
    }
}

При исключении Spring автоматически вызывает rollback.

5. Уровни изоляции транзакций

Разные уровни защиты от конфликтов между параллельными транзакциями:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
-- Может читать неподтвержденные данные других транзакций (dirty read)
COMMIT;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- Только подтвержденные данные (default в PostgreSQL)
COMMIT;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- Повторяемое чтение (может быть phantom read)
COMMIT;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- Максимальная изоляция, как будто транзакции выполняются по одной
COMMIT;

6. Savepoints (контрольные точки)

Возможность частичного отката:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Устанавливаем контрольную точку
SAVEPOINT sp1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Если здесь ошибка, откатываем только до sp1
ROLLBACK TO sp1;

-- Остаток транзакции продолжается
UPDATE accounts SET balance = balance + 50 WHERE id = 3;

COMMIT;

В Java:

Connection conn = dataSource.getConnection();
try {
    conn.setAutoCommit(false);
    
    // Первая операция
    executeUpdate(conn, "UPDATE accounts ...");
    
    // Сохраняем состояние
    Savepoint sp = conn.setSavepoint("sp1");
    
    try {
        // Вторая операция
        executeUpdate(conn, "UPDATE accounts ...");
    } catch (Exception e) {
        // Откатываем только до savepoint
        conn.rollback(sp);
    }
    
    conn.commit();
} finally {
    conn.close();
}

7. Explicit Lock (Явная блокировка)

Для критичных операций нужна синхронизация:

-- Блокируем строку для чтения
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- Другие транзакции могут читать, но не писать
COMMIT;

-- Исключительная блокировка
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Другие транзакции не могут ни читать, ни писать
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

В Spring JPA:

@Transactional
public void transfer(Long fromId, Long toId, BigDecimal amount) {
    // Блокируем счета для обновления
    Account from = accountRepository.findByIdForUpdate(fromId)
        .orElseThrow();
    Account to = accountRepository.findByIdForUpdate(toId)
        .orElseThrow();
    
    from.setBalance(from.getBalance().subtract(amount));
    to.setBalance(to.getBalance().add(amount));
}

// В repository
@Query("SELECT a FROM Account a WHERE a.id = :id")
@Lock(LockModeType.PESSIMISTIC_WRITE)
Optional<Account> findByIdForUpdate(@Param("id") Long id);

8. Two-Phase Commit (2PC)

Для транзакций в нескольких БД:

public void transferAcrossDatabases(Long fromId, Long toId, BigDecimal amount) {
    // Фаза 1: Prepare (подготовка)
    Connection conn1 = db1.getConnection();
    Connection conn2 = db2.getConnection();
    
    try {
        conn1.setAutoCommit(false);
        conn2.setAutoCommit(false);
        
        // Обновляем в обеих БД
        conn1.createStatement().executeUpdate("UPDATE accounts ...");
        conn2.createStatement().executeUpdate("UPDATE accounts ...");
        
        // Фаза 2: Commit (фиксирование)
        conn1.commit();
        conn2.commit();
        
    } catch (Exception e) {
        conn1.rollback();
        conn2.rollback();
        throw e;
    }
}

Промышленный пример с JTA:

@Transactional
public void transferAcrossDatabases(@Qualifier("tm1") EntityManager em1,
                                    @Qualifier("tm2") EntityManager em2) {
    // Обе операции в одной JTA транзакции
    Account acc1 = em1.find(Account.class, 1L);
    Account acc2 = em2.find(Account.class, 2L);
    
    acc1.setBalance(acc1.getBalance().subtract(amount));
    acc2.setBalance(acc2.getBalance().add(amount));
    
    // Коммит с координацией обеих БД
}

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

Вместо блокировки используем версионирование:

@Entity
public class Account {
    @Id
    private Long id;
    
    private BigDecimal balance;
    
    @Version  // Автоматически отслеживает версию
    private Long version;
}

@Transactional
public void transfer(Long fromId, Long toId, BigDecimal amount) {
    Account from = accountRepository.findById(fromId).orElseThrow();
    Account to = accountRepository.findById(toId).orElseThrow();
    
    from.setBalance(from.getBalance().subtract(amount));
    to.setBalance(to.getBalance().add(amount));
    
    try {
        accountRepository.save(from);
        accountRepository.save(to);
    } catch (OptimisticLockingFailureException e) {
        // Версия изменилась, нужно повторить
        transfer(fromId, toId, amount);
    }
}

10. Deadlock обработка

При deadlock нужно повторить:

@Transactional(rollbackFor = Exception.class)
public void transferWithRetry(Long fromId, Long toId, BigDecimal amount) 
        throws InterruptedException {
    int retries = 3;
    while (retries > 0) {
        try {
            transfer(fromId, toId, amount);
            return;
        } catch (DeadlockLoserDataAccessException e) {
            retries--;
            if (retries == 0) throw e;
            // Ждём случайное время перед повтором
            Thread.sleep(new Random().nextInt(100));
        }
    }
}

11. Best Practices

• Держи транзакции как можно короче
• Избегай больших блокировок
• Используй правильный уровень изоляции
• Обрабатывай исключения (rollback гарантирован)
• Логируй ошибки, но не в транзакции
• Мониторь deadlock'и и slow транзакции