Долгие блокировки MySQL: почему сайт зависает при сохранении

Иногда сайт не падает с ошибкой, а просто зависает: долго сохраняется заказ, не обновляется товар, импорт стоит на месте, админка ждёт ответа. Одна из возможных причин — блокировки в MySQL. Один процесс держит транзакцию, другой ждёт, а пользователи видят медленную работу.

Блокировки не всегда являются ошибкой. Они нужны базе для целостности данных. Проблема начинается, когда транзакции слишком длинные, запросы обновляют лишние строки или фоновые задачи пересекаются с действиями пользователей.

Сначала увидеть, что происходит

При зависании нужно посмотреть текущие процессы MySQL. Команда покажет активные запросы, время выполнения и состояние.

SHOW PROCESSLIST;

В консоли:

mysql -u user -p -e "SHOW PROCESSLIST;"

Если видны запросы со статусом Waiting for lock или долгие UPDATE, нужно смотреть дальше.

InnoDB status

Для InnoDB полезна команда:

SHOW ENGINE INNODB STATUS\G

В выводе можно найти информацию о последних deadlock и активных транзакциях. Вывод большой, но в нём часто есть конкретные таблицы и запросы, которые конфликтовали.

Долгие транзакции

Транзакция должна быть короткой: открыть, изменить нужные данные, зафиксировать. Если внутри транзакции выполняется внешний API-запрос, генерация файла или долгий цикл, блокировка может держаться слишком долго.

$transaction = Yii::$app->db->beginTransaction();

try {
    $order->status = Order::STATUS_PAID;
    $order->save(false);

    $transaction->commit();
} catch (\Throwable $e) {
    $transaction->rollBack();
    throw $e;
}

Плохой вариант — держать транзакцию открытой во время медленной внешней операции:

// так лучше не делать
$transaction = Yii::$app->db->beginTransaction();
$api->sendOrder($order);
$order->save();
$transaction->commit();

Внешний запрос может зависнуть, а транзакция всё это время будет держать блокировки.

UPDATE без точного условия

Иногда блокировки появляются из-за слишком широкого UPDATE. Например, забыли условие по id или обновляют много строк там, где нужна одна.

UPDATE products SET status = 'A';

Перед массовыми UPDATE на production лучше сначала выполнить SELECT с тем же WHERE и понять объём.

SELECT COUNT(*)
FROM products
WHERE supplier_id = 10 AND status = 'N';

Индексы влияют на блокировки

Если UPDATE ищет строки без индекса, база может просматривать и блокировать больше данных, чем ожидалось. Поэтому для частых условий обновления нужны подходящие индексы.

EXPLAIN UPDATE product_amounts
SET amount = 0
WHERE supplier_id = 10 AND warehouse_id = 3;

Индекс под такой запрос может выглядеть так:

CREATE INDEX idx_amounts_supplier_warehouse
ON product_amounts (supplier_id, warehouse_id);

Параллельные cron-задачи

Фоновые импорты часто становятся источником блокировок. Один импорт ещё не закончился, второй уже стартовал, оба обновляют одни и те же таблицы.

Для таких задач нужен lock:

$mutex = Yii::$app->mutex;

if (!$mutex->acquire('supplier-import', 0)) {
    Yii::warning('Импорт уже выполняется', 'import');
    return;
}

try {
    // импорт
} finally {
    $mutex->release('supplier-import');
}

Также помогает обработка пакетами, чтобы не держать большие изменения одной длинной транзакцией.

Deadlock

Deadlock возникает, когда две транзакции ждут друг друга. MySQL обычно сам прерывает одну из них. В коде такую ситуацию нужно обрабатывать как повторяемую ошибку, особенно для фоновых задач.

for ($attempt = 1; $attempt <= 3; $attempt++) {
    try {
        // сохранение данных
        break;
    } catch (\yii\db\Exception $e) {
        if (strpos($e->getMessage(), 'Deadlock') === false) {
            throw $e;
        }

        usleep(200000 * $attempt);
    }
}

Но retry не заменяет исправление причины. Если deadlock повторяется постоянно, нужно смотреть порядок обновления таблиц и индексы.

Чек-лист диагностики

1. Посмотреть SHOW PROCESSLIST во время зависания.
2. Проверить SHOW ENGINE INNODB STATUS.
3. Найти долгие UPDATE и транзакции.
4. Проверить, нет ли внешних API внутри транзакций.
5. Проверить условия массовых UPDATE.
6. Проверить индексы для WHERE в UPDATE.
7. Проверить параллельные cron и импорты.
8. Добавить mutex для задач, которые нельзя запускать параллельно.

Блокировки MySQL лучше разбирать по фактическим запросам. Общие советы вроде “увеличить сервер” не помогут, если один cron держит транзакцию пять минут или массовый UPDATE работает без индекса.