Для чего нужен SELECT FOR UPDATE?

# Для чего нужен SELECT FOR UPDATE?

SELECT FOR UPDATE — это SQL конструкция, которая используется для пессимистической блокировки строк базы данных. Это критически важный инструмент для предотвращения race conditions при многопоточном доступе к данным.

Определение

SELECT FOR UPDATE — это SQL оператор, который:

• Читает строки из таблицы (как обычный SELECT)
• Блокирует эти строки для других пользователей
• Гарантирует эксклюзивный доступ до конца транзакции
• Предотвращает одновременные изменения данных

Проблема без SELECT FOR UPDATE

Race Condition в банковской операции

Сценарий: Два перевода со счета одновременно

Начальный баланс: 1000 рублей

Транзакция 1 (Перевод 300)          Транзакция 2 (Перевод 400)
├─ 13:00:00: SELECT баланс → 1000   │
├─ 13:00:01: Проверка: 1000 >= 300  ├─ 13:00:01: SELECT баланс → 1000
├─ 13:00:02: Проверка: 1000 >= 400  │
├─ 13:00:03: UPDATE баланс = 700    ├─ 13:00:03: UPDATE баланс = 600
│                                     │
Результат: баланс = 600 (ОШИБКА!)

Потеря 300 рублей!
Оба перевода обрабатаны, но баланс неправильный.

Что произошло:

1. Обе транзакции читают одно значение баланса
2. Обе проверяют условия (достаточно ли денег)
3. Обе записывают результат независимо
4. Последнее обновление перезаписывает предыдущее
5. Потеря данных!

Решение: SELECT FOR UPDATE

Правильная реализация с блокировкой

BEGIN TRANSACTION;

SELECT balance FROM accounts 
WHERE account_id = 123
FOR UPDATE;  -- Блокируем эту строку

-- Теперь никто другой не может читать или писать эту строку

IF balance >= amount THEN
    UPDATE accounts 
    SET balance = balance - amount 
    WHERE account_id = 123;
END IF;

COMMIT;  -- Блокировка снимается

Последовательность с блокировкой:

Начальный баланс: 1000 рублей

Транзакция 1 (Перевод 300)          Транзакция 2 (Перевод 400)
├─ 13:00:00: SELECT FOR UPDATE       │
│   баланс → 1000                    │
│   (БЛОКИРУЕТ строку)               │
│                                     ├─ 13:00:01: SELECT FOR UPDATE
│                                     │   (ЖДЕТ блокировку T1)
├─ 13:00:02: UPDATE баланс = 700     │
├─ 13:00:03: COMMIT                  │
│   (Отпускает блокировку)           │
│                                     ├─ 13:00:04: SELECT FOR UPDATE
│                                     │   баланс → 700
│                                     │   (ПОЛУЧАЕТ блокировку)
│                                     ├─ 13:00:05: UPDATE баланс = 300
│                                     ├─ 13:00:06: COMMIT

Результат: баланс = 300 (ПРАВИЛЬНО!)

Типы блокировок

1. FOR UPDATE (эксклюзивная блокировка)

SELECT * FROM users 
WHERE user_id = 1
FOR UPDATE;  -- Эксклюзивная блокировка

-- Другие транзакции НЕ могут:
-- - Читать с FOR UPDATE
-- - Писать в эту строку
-- - Удалять эту строку

2. FOR SHARE (блокировка для чтения)

SELECT * FROM users 
WHERE user_id = 1
FOR SHARE;  -- Блокировка для чтения

-- Другие транзакции:
-- ✅ Могут читать с FOR SHARE
-- ❌ НЕ могут писать в эту строку

3. FOR UPDATE NOWAIT (без ожидания)

SELECT * FROM users 
WHERE user_id = 1
FOR UPDATE NOWAIT;  -- Если занята, ошибка

-- Если строка уже заблокирована:
-- - Сразу вернет ошибку
-- - Не будет ждать
-- - Приложение может повторить попытку

4. FOR UPDATE SKIP LOCKED (пропустить заблокированные)

SELECT * FROM tasks 
WHERE status = pending
FOR UPDATE SKIP LOCKED
LIMIT 10;

-- Вернет только незаблокированные строки
-- Полезно для очередей обработки задач

Практические примеры

Пример 1: Переводы денег

@Transactional
public void transferMoney(Long fromAccountId, Long toAccountId, BigDecimal amount) {
    // Блокируем счет ДО изменения (пессимистическая блокировка)
    Account fromAccount = accountRepository.findByIdForUpdate(fromAccountId);
    
    if (fromAccount.getBalance().compareTo(amount) < 0) {
        throw new InsufficientFundsException();
    }
    
    // Теперь никто не может изменить баланс
    fromAccount.setBalance(fromAccount.getBalance().subtract(amount));
    accountRepository.save(fromAccount);
    
    Account toAccount = accountRepository.findByIdForUpdate(toAccountId);
    toAccount.setBalance(toAccount.getBalance().add(amount));
    accountRepository.save(toAccount);
    
    // Когда транзакция завершится, блокировка снимется
}

// В интерфейсе репозитория
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("SELECT a FROM Account a WHERE a.id = ?1")
Account findByIdForUpdate(Long id);

Пример 2: Обработка очереди задач

@Transactional
public Task getNextTaskForProcessing() {
    // Получи первую незавершенную задачу, блокируя ее
    List<Task> tasks = taskRepository.findNextTasksForUpdate();
    
    if (tasks.isEmpty()) {
        return null;  // Нет задач
    }
    
    Task task = tasks.get(0);
    task.setStatus(TaskStatus.IN_PROGRESS);
    task.setProcessedBy(getCurrentUser());
    task.setProcessedAt(Instant.now());
    
    return taskRepository.save(task);
}

// SQL с SKIP LOCKED
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("SELECT t FROM Task t WHERE t.status = PENDING ORDER BY t.createdAt LIMIT 1")
List<Task> findNextTasksForUpdate();

Пример 3: Инвентарь товаров

@Transactional
public void decreaseInventory(Long productId, int quantity) {
    // Блокируем товар перед изменением количества
    Product product = productRepository.findByIdForUpdate(productId);
    
    if (product.getStock() < quantity) {
        throw new OutOfStockException("Not enough stock");
    }
    
    product.setStock(product.getStock() - quantity);
    productRepository.save(product);
    
    // Создаем запись о списании
    inventoryLog.log(productId, quantity);
}

// JPA
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("SELECT p FROM Product p WHERE p.id = ?1")
Product findByIdForUpdate(Long id);

Различия: SELECT FOR UPDATE vs SELECT

SELECT (без блокировки)

BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1;
-- balance = 1000
-- Другие могут менять этот баланс!
COMMIT;

SELECT FOR UPDATE (с блокировкой)

BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;
-- balance = 1000
-- Другие ЖДУТ или получают ошибку!
UPDATE accounts SET balance = 700 WHERE account_id = 1;
COMMIT;  -- Блокировка снимается

SELECT FOR UPDATE в разных БД

PostgreSQL

SELECT * FROM orders 
WHERE order_id = 123
FOR UPDATE;  -- Поддерживает

-- SKIP LOCKED (PostgreSQL 9.5+)
SELECT * FROM tasks 
WHERE status = pending
FOR UPDATE SKIP LOCKED;

MySQL

SELECT * FROM orders 
WHERE order_id = 123
FOR UPDATE;  -- Поддерживает

-- NOWAIT
SELECT * FROM orders 
WHERE order_id = 123
FOR UPDATE NOWAIT;

Oracle

SELECT * FROM orders 
WHERE order_id = 123
FOR UPDATE;  -- Поддерживает

SQLite

NE поддерживает SELECT FOR UPDATE (используй другие механизмы)

JPA/Hibernate реализация

Использование @Lock

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    
    @Lock(LockModeType.PESSIMISTIC_WRITE)  // SELECT FOR UPDATE
    @Query("SELECT o FROM Order o WHERE o.id = ?1")
    Order findByIdForUpdate(Long id);
    
    @Lock(LockModeType.PESSIMISTIC_READ)   // SELECT FOR SHARE
    @Query("SELECT o FROM Order o WHERE o.id = ?1")
    Order findByIdForShare(Long id);
}

Типы блокировок в JPA

LockModeType.PESSIMISTIC_WRITE    // FOR UPDATE (эксклюзивная)
LockModeType.PESSIMISTIC_READ     // FOR SHARE (для чтения)
LockModeType.PESSIMISTIC_FORCE_INCREMENT  // Обновляет версию

Проблемы и их решения

1. Deadlock (взаимная блокировка)

Проблема:

Транзакция 1:              Транзакция 2:
1. Lock Account A          1. Lock Account B
2. Wait for Account B      2. Wait for Account A
→ DEADLOCK!

Решение:

// Всегда блокируй в одном порядке
public void transfer(Long accountA, Long accountB, BigDecimal amount) {
    // Всегда берем меньший ID первым
    Long first = Math.min(accountA, accountB);
    Long second = Math.max(accountA, accountB);
    
    Account acc1 = accountRepository.findByIdForUpdate(first);
    Account acc2 = accountRepository.findByIdForUpdate(second);
    
    // Теперь deadlock невозможен
}

2. Timeout ожидания

@Lock(LockModeType.PESSIMISTIC_WRITE)
@QueryHints({
    @QueryHint(name = "javax.persistence.lock.timeout", value = "5000")
})
@Query("SELECT o FROM Order o WHERE o.id = ?1")
Order findByIdForUpdate(Long id);

3. Performance проблемы

Проблема: Держание блокировок слишком долго снижает concurrency

Решение:

@Transactional  // Минимальная область
public void processOrder(Long orderId) {
    // Блокируем только необходимую часть
    Order order = orderRepository.findByIdForUpdate(orderId);
    order.setStatus(OrderStatus.PROCESSING);
    orderRepository.save(order);  // Блокировка снимается здесь
    
    // Остальная логика БЕЗ блокировки
    sendEmailNotification(order);  // Не блокирует БД
}

Когда использовать SELECT FOR UPDATE

✅ Используй когда:

• Критические данные (деньги, инвентарь)
• Высокая конкурентность (many concurrent transactions)
• Нужна консистентность перед обновлением
• Race conditions возможны (check-then-act pattern)

❌ Избегай когда:

• Блокировка держится долго (performance hit)
• Можно использовать optimistic locking (версионирование)
• Низкая конкурентность (не нужна)
• Чтение-только операции (используй FOR SHARE)

Альтернативы

1. Optimistic Locking (версионирование)

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

// При конфликте вернет OptimisticLockException

2. Atomic операции БД

UPDATE accounts 
SET balance = balance - 100 
WHERE account_id = 1 
AND balance >= 100;
-- Если обновлены 0 строк, то не хватает денег

Заключение

SELECT FOR UPDATE нужен для:

1. Предотвращения race conditions при многопоточном доступе
2. Гарантии консистентности при check-then-act операциях
3. Критических операциях с деньгами, инвентарем
4. Обработки очередей (SKIP LOCKED)
5. Пессимистической блокировки вместо optimistic locking

Рекомендация: Используй SELECT FOR UPDATE для критических операций, но избегай держания блокировок долго, чтобы не снизить производительность системы.