Как регулировать два параллельных запроса, идущих к одной колонке таблицы?

Проблема параллельных запросов к одной колонке и её решения

При разработке PHP Backend систем часто возникает ситуация, когда два или более параллельных запроса (например, от разных пользователей или процессов) пытаются одновременно работать с одной колонкой таблицы в базе данных. Это может приводить к конфликтам, потере данных и некорректному состоянию системы. Регулирование таких запросов — критически важная задача для обеспечения консистентности данных.

Основные проблемы и риски

• Конкуренция за ресурсы: Запросы могут "перекрываться", вызывая блокировки.
• Race Condition: Ситуация, когда результат зависит от порядка выполнения, который непредсказуем.
• Инкрементные операции: Например, одновременное увеличение значения счетчика может привести к потере одного из увеличений.
• Обновление одного поля: Два запроса UPDATE могут "перебивать" значения друг друга.

Методы регулирования параллельных запросов

1. Использование транзакций и уровней изоляции

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

// Пример с PDO и уровнем изоляции READ COMMITTED
$pdo->beginTransaction();
// Установка уровня изоляции может зависеть от драйвера
// Для PostgreSQL, например:
// $pdo->exec("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");

try {
    // 1. Выборка текущего значения с блокировкой
    $stmt = $pdo->prepare("SELECT value FROM counters WHERE id = ? FOR UPDATE");
    $stmt->execute([$id]);
    $current = $stmt->fetchColumn();

    // 2. Обновление значения
    $newValue = $current + 1;
    $updateStmt = $pdo->prepare("UPDATE counters SET value = ? WHERE id = ?");
    $updateStmt->execute([$newValue, $id]);

    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    throw $e;
}

Ключевые уровни изоляции для регулирования:

• READ COMMITTED: Гарантирует, что читаются только зафиксированные данные, но не предотвращает все race conditions.
• REPEATABLE READ: Гарантирует, что данные, прочитанные в транзакции, не изменятся другими транзакциями.
• SERIALIZABLE: Самый строгий уровень, фактически выполняет транзакции последовательно, полностью предотвращая конфликты, но снижает производительность.

2. Применение явных блокировок строк

Наиболее эффективный метод для операций UPDATE — использование явных блокировок.

• SELECT ... FOR UPDATE: Блокирует выбранные строки для текущей транзакции. Другие транзакции не могут изменять эти строки или также выбирать их FOR UPDATE до завершения вашей транзакции.
• LOCK IN SHARE MODE (в MySQL): Блокировка для совместного чтения, но запрещает другим обновлять строку.

-- Пример SQL для MySQL/InnoDB
START TRANSACTION;
SELECT value FROM my_table WHERE id = 123 FOR UPDATE;
-- ... логика вычисления нового значения ...
UPDATE my_table SET value = new_value WHERE id = 123;
COMMIT;

В PHP это реализуется внутри транзакции, как показано выше.

3. Оптимистичные и пессимистичные блокировки

• Пессимистичная блокировка (описанная выше): Предполагает, что конфликты вероятны, и блокирует данные заранее (FOR UPDATE).
• Оптимистичная блокировка: Предполагает, что конфликты редки. Использует механизм версий или временных меток.

// Пример оптимистичной блокировки через версию
$pdo->beginTransaction();

try {
    // Выбираем данные вместе с версией
    $stmt = $pdo->prepare("SELECT value, version FROM data WHERE id = ?");
    $stmt->execute([$id]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    $currentValue = $row['value'];
    $currentVersion = $row['version'];
    $newValue = $currentValue + 10;

    // Обновляем, проверяя, что версия не изменилась
    $updateStmt = $pdo->prepare("UPDATE data SET value = ?, version = version + 1 WHERE id = ? AND version = ?");
    $updateStmt->execute([$newValue, $id, $currentVersion]);

    // Если количество обновленных строк = 0, значит версия изменилась (конфликт)
    if ($updateStmt->rowCount() === 0) {
        $pdo->rollBack();
        throw new OptimisticLockException("Data was modified concurrently");
    }

    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    throw $e;
}

4. Использование атомарных операций базы данных

Для простых инкрементных операций лучшим решением часто являются атомарные операции SQL.

UPDATE counters SET value = value + 1 WHERE id = 123;

Эта операция выполняется на уровне базы данных как единое целое и не требует дополнительных блокировок в коде. В PHP это выглядит просто:

$stmt = $pdo->prepare("UPDATE counters SET value = value + ? WHERE id = ?");
$stmt->execute([$increment, $id]);

5. Очереди и мьютексы на уровне приложения

Если БД не справляется или нужно регулировать на уровне бизнес-логики, можно использовать:

• Системы очередей (RabbitMQ, Redis): Запросы ставятся в очередь и обрабатываются последовательно.
• Мьютексы/семафоры в PHP: В рамках одного процесса можно использовать sem_acquire()/sem_release(), но это не работает между разными процессами или серверами.
• Redis-мьютексы: Использование Redis для создания распределенных блокировок (например, через SETNX).

// Пример распределенной блокировки с Redis
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);

$lockKey = "lock:column:{$table}:{$columnId}";
$lockTimeout = 5; // секунд

// Попытка получить блокировку
if ($redis->set($lockKey, 1, ['nx', 'ex' => $lockTimeout])) {
    try {
        // Выполнение опасной операции с колонкой
        performCriticalOperation();
    } finally {
        // Освобождение блокировки (можно удалить ключ)
        $redis->del($lockKey);
    }
} else {
    throw new Exception("Could not acquire lock. Operation in progress.");
}

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

1. Оцените частоту конфликтов: Если конфликты редки — используйте оптимистичные блокировки или атомарные операции. Если часты — пессимистичные.
2. Минимизируйте время блокировки: В пессимистичном подходе выполняйте всю логику до SELECT ... FOR UPDATE или делайте его как можно позже, чтобы сократить время блокировки строк.
3. Тестируйте под нагрузкой: Используйте стресс-тесты для проверки корректности поведения при высокой конкуренции.
4. Мониторинг блокировок: Настройте мониторинг длительных блокировок в БД.
5. Рассмотрите архитектурные изменения: Если проблема масштабируется, возможно, стоит изменить структуру данных (например, использовать шардинг) или бизнес-процесс.

Выбор метода зависит от конкретной задачи, масштаба системы и требований к производительности и консистентности. В большинстве случаев для прямых манипуляций с колонками таблицы атомарные операции SQL и пессимистичные блокировки через SELECT ... FOR UPDATE являются наиболее надежными и практичными решениями в контексте PHP Backend разработки.