Что делает оператор SELECT FOR UPDATE в SQL и когда его применяют?

SELECT ... FOR UPDATE — это SQL-оператор, реализующий пессимистическую блокировку на уровне строк (или таблиц). Он блокирует выбранные строки для других транзакций, пытающихся выполнить SELECT FOR UPDATE, UPDATE или DELETE на этих же строках, до завершения текущей транзакции (COMMIT или ROLLBACK).

Назначение: Предотвращение аномалий параллелизма, таких как "потерянное обновление" (lost update), когда изменения одной транзакции перезаписываются другой.

Пример использования в Java (JDBC):

Connection conn = dataSource.getConnection();
try {
    conn.setAutoCommit(false); // Начало транзакции

    // 1. Пессимистическая блокировка строки
    String selectSql = "SELECT id, balance FROM accounts WHERE id = ? FOR UPDATE";
    PreparedStatement selectStmt = conn.prepareStatement(selectSql);
    selectStmt.setLong(1, accountId);
    ResultSet rs = selectStmt.executeQuery();

    if (rs.next()) {
        double currentBalance = rs.getDouble("balance");
        double newBalance = currentBalance - 100.0;

        // 2. Безопасное обновление заблокированной строки
        String updateSql = "UPDATE accounts SET balance = ? WHERE id = ?";
        PreparedStatement updateStmt = conn.prepareStatement(updateSql);
        updateStmt.setDouble(1, newBalance);
        updateStmt.setLong(2, accountId);
        updateStmt.executeUpdate();
    }
    conn.commit(); // Освобождение блокировок
} catch (SQLException e) {
    conn.rollback(); // Освобождение блокировок при ошибке
    throw e;
} finally {
    conn.setAutoCommit(true);
    conn.close();
}

Важные аспекты и подводные камни:

1. Область действия блокировки: Действует до конца транзакции. Долгие транзакции = долгие блокировки = снижение параллелизма.
2. Риск взаимных блокировок (deadlock): Возникает, если две транзакции блокируют ресурсы в разном порядке. Решение: всегда захватывать блокировки в согласованном порядке.
3. Производительность: Пессимистические блокировки снижают параллелизм. Альтернатива — оптимистическая блокировка (через версию или timestamp).
4. В Spring (@Transactional): Уровень изоляции SERIALIZABLE или использование JdbcTemplate/EntityManager с нативным запросом.

   @Transactional
   public void transferMoney(Long fromId, Long toId, double amount) {
   // Использование JPA с нативным запросом
   entityManager.createNativeQuery("SELECT * FROM accounts WHERE id = :id FOR UPDATE")
                .setParameter("id", fromId)
                .getResultList();
   // ... логика обновления
   }