Конфликты блокировок в базе данных: быстро исправьте «горячие» таблицы и ожидания блокировок

Как выглядит конфликт блокировок простыми словами

Блокировка в базе данных — это как табличка «занято» на строке или таблице. Пока один запрос изменяет данные, база может блокировать другие запросы, пытающиеся изменить те же данные одновременно. Некоторые операции при этом могут читать, а другие должны ждать — это зависит от СУБД и типа блокировки.

Конфликт блокировок возникает, когда очередь ожидания становится длинной. База данных при этом не «упала». Она просто заставляет запросы дожидаться своей очереди.

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

Пользователь ощущает это как раздражающую и непостоянную работу: страницы, которые обычно открываются за 200 мс, внезапно тратят 10–30 секунд, затем всё возвращается к норме. Фоновые задания выглядят застопорившимися. Появляются таймауты, повторы и волна ошибок, которая исчезает прежде, чем кто-то успеет её воспроизвести.

Это может выглядеть как случайная медлительность, потому что сервера внешне в порядке. CPU не загружен, память стабильна, среднее время запроса может выглядеть нормально. Но в пиковой нагрузке несколько заблокированных записей создают пробку, и всё, что нуждается в тех же строках или таблице, начинает ждать.

Почему «горячие» таблицы становятся узкими местами

«Горячая» таблица — это таблица, к которой одновременно обращается много запросов, часто к одним и тем же строкам. Когда множество сессий пытаются обновить одни и те же данные, СУБД ставит их в очередь. Эта очередь и есть то, что воспринимают пользователи как contention: страницы загружаются, затем зависают, затем внезапно завершаются.

Самая распространённая причина — большой объём записей, сосредоточенных в одном месте. Огромная таблица может быть в порядке, если записи распределены. Малая таблица может стать хуже, если каждый запрос попадает в неё, например, одна строка с глобальным счётчиком, «последний номер счёта» или общий флаг состояния.

Горячие точки обычно возникают по одним и тем же паттернам:

• Одна строка, которую обновляют все (счётчики, глобальные настройки)
• Циклы «прочитать‑изменить‑записать» по одним и тем же записям
• Дизайн «upsert для всего», где конфликты ожидаемы
• Фоновые задания, трогающие те же строки, что и пользователи
• Лишняя работа внутри транзакции (вызовы API, загрузки файлов, долгие расчёты)

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

Время имеет значение. Ночная задача по пересчёту итогов может быть безопасной в 3:00, но болезненной, если она пересекается с пиковым трафиком. Развёртывания тоже могут усугубить ситуацию: изменение схемы, добавление индекса или триггера увеличивает время записи и растягивает период удержания блокировки.

Пример: поток оформления заказа обновляет строку со складским запасом, а затем вызывает платёжный провайдер до коммита. Если этот вызов занимает 10 секунд, все остальные попытки оформить заказ, которым нужна та же строка, будут ждать, и система будет казаться заблокированной.

Быстрые признаки того, что проблема в блокировках, а не просто медленная БД

Проблемы с блокировками ощущаются как «всё медленно», но сигналы отличаются от ситуации, когда база просто недоразмерена.

Разделите симптомы:

Если CPU загружен и запросы много считаются, вероятнее всего виноваты тяжёлые запросы, недостающие индексы или слишком большая работа на запрос. Если CPU в норме, но число активных соединений продолжает расти, запросы накапливаются. Когда вы видите много запросов в состоянии ожидания (waiting), а не выполняющихся, то вероятность того, что это contention, высокая.

Классический паттерн — резкие пики p95 и p99, тогда как p50 остаётся в основном нормальным. Один запрос попадает на заблокированную строку или таблицу и ждёт, затем за ним выстраивается очередь. Вы также можете увидеть падение общей пропускной способности, хотя база не загружена по CPU.

Логи обычно дают ранние подсказки: кластерные ошибки таймаутов, сообщения о дедлоках и растущее число повторов (из приложения, воркеров или ORM). Если вы недавно увеличили число воркеров и стало хуже, это сильный признак того, что конкурентные писатели конфликтуют.

Во время инцидента соберите небольшой набор фактов, чтобы потом соотнести всплеск с конкретным ожиданием блокировки:

• Точные временные метки (начало, пик, восстановление)
• Образцы медленных запросов и их параметры (или анонимизированные эквиваленты)
• Топ эндпойнтов или фоновых задач в это время
• Снимки базы: число активных соединений и ожидающих запросов
• Число воркеров и очередей (веб, задания, cron)

Пример: задержка оформления заказа прыгает с 300 мс до 20 с, CPU остаётся ровным, а соединений становится вдвое больше. Такая комбинация обычно значит «ожидание», а не «выполнение».

Как по шагам идентифицировать ожидания блокировок

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

Шаг 1: найдите, кто ждёт, а кто блокирует

Начните с системных представлений сессий и блокировок в вашей СУБД (active sessions, lock tables, wait events). Вам нужна цепочка: много сессий в ожидании и одна сессия в начале, держащая блокировку.

Практический рабочий процесс:

• Перечислите активные сессии и отфильтруйте ожидающие блокировок
• Для каждого ждущего найдите id блокирующей сессии
• Проверьте возраст транзакции блокировщика (как долго она открыта)
• Вытяните SQL‑текст и для заблокированного, и для блокирующего запросов
• Зафиксируйте тип ожидания и объект (таблица, индекс, ключ строки, если доступно)

Шаг 2: сопоставьте SQL с реальным действием пользователя

Только текст SQL недостаточен. Свяжите его с тем, что делало приложение: именем эндпойнта, фоновой задачей, воркером или админской операцией. Помогают теги запросов, имя приложения в соединении и логи запросов/заданий.

Проверочный паттерн: если одна и та же таблица появляется регулярно и та же область ключей или индекс вовлечён, скорее всего у вас настоящая горячая точка (например, все обновляют одну и ту же строку статуса).

Шаг 3: решение об убитии сессии — только после оценки риска

Убийство блокирующей сессии может быстро восстановить работу, но может и откатить изменения и спровоцировать повторы. Перед этим убедитесь:

• Это действительно зависшая транзакция или явно превышает нормальное время выполнения
• Откат не приведёт к внешним побочным эффектам (отправленные письма, списанные платежи)
• Вы понимаете, что повторит задачу и не вызовет ли это нового накопления

Точно укажите таблицу, запрос и путь в коде

Самый быстрый выигрыш — назвать точную таблицу, точный SQL‑запрос и место в коде, откуда он исходит. Иначе вы рискуете гадать и менять не то место.

Начните с перечисления всех операций записи, которые могут трогать подозрительную таблицу: INSERT, UPDATE, DELETE и UPSERT. Не полагайтесь на то, что фича «должна» делать. Возьмите данные из логов, ORM‑выхода или статистики запросов. Горячие таблицы часто пишутся одновременно веб‑запросами, фоновыми задачами и повторами.

Потом проверьте предположение «изменяется одна строка». Частая неожиданность — UPDATE с широкой WHERE (или без подходящего индекса), который сканирует много строк и блокирует намного больше, чем ожидалось. Если ожидания блокировок взлетают при маленьком обновлении, сначала проверьте индексы. Отсутствие или неправильный индекс превращают быстрый поиск в большое заблокированное сканирование.

Также ищите блокировки, установленные при чтении. Если вы видите SELECT ... FOR UPDATE или похожие паттерны, проверьте, действительно ли такая блокировка необходима. Многие приложения добавляют её «про запас» и в итоге блокируют несвязанные операции.

Простой способ связать данные БД с кодом приложения:

• Захватите текст блокирующего запроса и возраст транзакции
• Сопоставьте его с эндпойнтом, именем задачи или воркером в логах
• Уточните, сколько строк он затрагивает (оценка vs реальное число)
• Проверьте, не делает ли приложение повторы по таймаутам и не накапливают ли они писателей
• Найдите точную функцию или метод сервиса, который строит запрос

Пример: ночная задача по очистке делает неиндексированный UPDATE, работает минуты, и начались таймауты оформления заказов. БД даёт запрос, логи — какой воркер его запустил. Исправление индекса или сужение WHERE обычно быстро останавливают пробку.

Уменьшайте длительность транзакций, которые блокируют всех

Длинные транзакции — один из самых быстрых способов создать contention. Пока транзакция открыта, она может держать блокировки на строках (а иногда и больше), которые нужны другим запросам. Если в транзакции есть медленная работа, все остальные ждут.

Правило: внутри транзакции держите только операции чтения и записи в базе. Всё остальное выполняйте до начала или после коммита.

Частые исправления, которые приносят немедленный эффект: вынести медленную работу из транзакции (вызовы API, загрузки файлов, отправка писем, генерация PDF, тяжёлые вычисления), коммитить раньше, когда это безопасно, и разбивать большие обновления на небольшие батчи.

Повторы могут умножать проблему. Если таймауты вызывают лавину повторов, вы добавляете больше писателей в тот же узел. Идемпотентные ключи (например, request id) помогают безопасно перезапускать без дублирования строк.

Конкретный пример: поток оформления заказа начинает транзакцию, вставляет заказ, потом вызывает платёжный API и отправляет подтверждение до коммита. Если платёжный API зависает на 30 секунд, строки заказа будут заблокированы 30 секунд. Лучше быстро записать заказ, выполнить коммит, а потом обрабатывать платёж и письмо с понятными правилами повторов.

Переработайте паттерны записи для «горячих» таблиц

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

Типичный пример — единый общий счётчик (next_invoice_number или daily_signups). Каждая запись встает за той же строкой. Безопасная альтернатива — вести счётчики по tenant, по пользователю или по шарду и агрегировать при необходимости глобального значения. Большинству продуктов не нужен идеально актуальный глобальный счётчик в реальном времени.

Ещё частая причина — постоянные обновления одной строки статуса (last_seen, прогресс, счётчик повторов). Если строка трогается при каждом запросе, со временем она задушит систему. Лучше использовать append-only события (audit_log, events, status_history) и вычислять текущее состояние по последнему событию или склеивать в фоне.

Паттерны, которые стабильно снижают ожидания блокировок:

• Замените единые счётчики на по‑тенантные (или по‑аккаунтные) и периодическую агрегацию
• Используйте append-only события вместо постоянного обновления одной строки
• Переносите некритичные записи в очередь и обновляйте асинхронно
• Разбейте «горячие» данные по стабильному ключу (tenant_id, account_id), чтобы распределить записи

Конкретный сценарий: при оформлении заказа пишется строка запасов и одновременно обновляются user_last_purchase, daily_sales_total и marketing_attribution в той же транзакции. При нагрузке строка с общими итогами становится бутылочным горлышком и блокирует записи запасов. Если оставить изменение запасов в транзакции, а totals и маркетинг вынести в фоновую задачу — checkout остаётся быстрым, а горячая строка перестаёт блокировать всех.

Уровни изоляции и область блокировок: малые выборы — большой эффект

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

Большинство команд думают в терминах блокировок строк: «только одна строка заблокирована». На практике некоторые движки также блокируют промежутки между строками или целые диапазоны, чтобы помешать вставкам в этот диапазон, пока вы читаете или обновляете. Обновление «всего от даты X до даты Y» может блокировать вставки в этот диапазон, даже если вы ещё не затронули новые строки.

Более безопасные паттерны, которые обычно уменьшают область блокировок, не меняя бизнес‑логики:

• Предпочитайте точечные обновления по первичному ключу вместо широких диапазонных обновлений
• Добавляйте жёсткие WHERE и батчьте, если нужно обновлять много строк
• Держите порядок доступа консистентным (например, всегда сначала блокируйте родителя, потом ребёнка), чтобы избежать дедлоков
• Следите за внешними ключами и каскадами: одно удаление или обновление может дольше блокировать несколько таблиц
• Избегайте схем «select затем update позже», которые держат транзакцию открытой, пока приложение делает лишнюю работу

Таймауты помогают быстро завершать зависшие запросы вместо создания пробки. Установите разумные lock timeouts и statement timeouts, чтобы застрявший запрос возвращал ошибку быстро, и приложение могло повторить или показать понятное сообщение.

Пример: фоновая задача работает на строгом уровне изоляции и обновляет все неоплаченные счета за прошлый месяц. Она держит range locks во время сканирования. В это же время checkout пытается вставить новый счёт в тот же диапазон и ждёт. Короткие батчи и менее строгий уровень изоляции часто решают проблему.

Пример сценария: одна задача блокирует checkout на 20 минут

У стартапа простая схема: таблица orders (горячая), эндпойнт checkout и фоновая задача, поддерживающая данные в порядке. В загружённый день checkout вдруг сильно замедляется. Некоторые пользователи получают таймауты, другие видят повторяющиеся «попробуйте ещё раз» сообщения.

Триггером стал долгий отчётный запрос, который кто‑то запустил внутри транзакции. Он начинается с BEGIN, затем читает большой фрагмент orders, чтобы посчитать метрики. Разработчик подумал: «Это чтение, значит безопасно». Но транзакция остаётся открытой долго и держит блокировки дольше, чем ожидалось при текущем уровне изоляции.

Одновременно фоновая задача обновляет тысячи строк в orders (например, бэфилл нового столбца) во время пикового трафика. Эти обновления тоже требуют блокировок. Очередь ожидания растёт: checkout пытается записать новый заказ и ждёт, повторы усиливают нагрузку, соединения скапливаются, всё кажется медленным.

Ещё одна проблема усугубляет ситуацию: endpoint checkout использует upsert типа «insert or update», но в таблице отсутствует нужный уникальный индекс. БД сканирует, чтобы найти совпадения, трогая больше строк, чем нужно, и каждая запись держит блокировки дольше.

Фикс быстро меняет поведение системы. Отчёт перестаёт использовать длинные транзакции (или переносится на реплику для чтения). Фоновая задача переключается на маленькие батчи (например, 500–1000 строк) с коммитом после каждого батча. И добавляется отсутствующий уникальный индекс, чтобы upsert быстро находил строки.

Быстрый чек‑лист перед изменением кода

Прежде чем переписывать что‑то, потратьте 15 минут, чтобы подтвердить, что вы имеете дело с contention, а не случайной медлительностью. Эти проверки часто укажут на причину и помогут не «чинить» не то.

Начните с возраста транзакций в пиковое время. Если транзакции остаются открытыми дольше нескольких секунд — это тревожный знак. Длинная транзакция удерживает блокировки дольше, и даже одна такая транзакция может заставить быстрые запросы выстраиваться в очередь.

Затем найдите главный блокирующий запрос. Большинство СУБД показывают, какая сессия блокирует другие. Чаще всего главный блокер — скучная вещь: админский скрипт, фоновая задача или воркер с повторами.

Чек‑лист в порядке выполнения:

• Найдите самую старую открытую транзакцию в пике и зафиксируйте, что она делает
• Определите топ блокирующий запрос и захватите его SQL и действие приложения, которое его запустило
• Убедитесь, что UPDATE и DELETE используют ожидаемый индекс (правильный ключ, а не полный скан таблицы)
• Проверьте штормы повторов и отсутствие таймаутов
• Проверьте недавние изменения схемы (миграции часто являются триггером)

Пример: воркер обновляет строки, не используя нужный индекс, сканирует много строк и держит транзакцию открытой, пока вызывает внешний API. Запросы checkout ждут блокировок, повторяются и усугубляют пробку.

Частые ловушки, из‑за которых конфликт блокировок возвращается

Contention часто возвращается, потому что первое исправление лечит симптом (таймауты, дедлоки), а не причину (когда и как вы держите блокировки).

Главная ошибка — держать транзакцию открытой, пока выполняется что‑то, что может приостановиться. Вызов платёжного API, вебхук, отправка письма, загрузка файла или медленный кэш‑запрос могут заморозить транзакцию и удерживать блокировки всё это время. Делайте работу с базой, коммитьте, затем выполняйте внешнюю работу.

Другая частая ловушка — большие запросы записи, которые затрагивают слишком много строк сразу. Батч‑UPDATE без LIMIT, без селективного WHERE или без правильного индекса может заблокировать большой диапазон строк и помешать несвязанным запросам.

Дедлоки часто «решают» добавлением большего числа повторов. Повторы могут скрыть проблему, но они увеличивают нагрузку и повышают давление блокировок. Если два пути кода постоянно борются за одни и те же строки, повторы просто повторяют конфликт.

Также следите за единой глобальной строкой, использующейся как счётчик или флаг состояния (одна строка в таблице settings). При нагрузке эта одна строка становится единой точкой узкого места.

Наконец, быстро написанный или AI‑сгенерированный код часто неправильно задаёт границы транзакций. Он может оборачивать слишком много работы в одну транзакцию или смешивать чтения и записи в неожиданном порядке. Если у вас прототип унаследованный от кого‑то, пересмотр границ транзакций часто даёт самый быстрый результат.

Вопросы, которые стоит задать, если проблема повторяется:

• Включает ли какая‑нибудь транзакция сетевые вызовы или долгие циклы?
• Бьют ли пакетные записи порциями и дружелюбны ли они к индексам?
• Обновляются ли конфликтующие строки в согласованном порядке?
• Есть ли одна строка, которую обновляют все?
• Являются ли транзакции явными и минимальными, а не случайными?

Следующие шаги: стабилизируйте сейчас, затем предотвратите повторения

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

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

Практический порядок действий:

• Найдите 1–3 запроса, которые держат блокировки дольше всего, и уберите лишнюю работу из транзакции
• Добавьте или ужесточите таймауты, чтобы одна зависшая транзакция не блокировала систему
• Разбейте большие обновления на мелкие батчи или перенесите некритичную работу в асинхронные задачи
• Переработайте горячие записи (append-only логи, шардинговые счётчики, таблицы‑очереди) после стабилизации
• Перетестируйте худшие сценарии под нагрузкой (checkout, логин, бэфиллы, cron‑задачи)

Мониторинг не должен быть сложным. Несколько сигналов быстро покажут, когда блокировки снова накапливаются: время ожидания блокировок, возраст транзакций, дедлоки и частота таймаутов запросов. Волновать следует тренды, а не единичные всплески.

Если вы имеете дело с унаследованным AI‑сгенерированным кодом и баги с блокировками трудно отследить, FixMyMess (fixmymess.ai) специализируется на диагностике и исправлении таких проблем: длинные транзакции, небезопасные паттерны записи и уязвимости в AI‑сборках приложений. Быстрый аудит часто достаточно, чтобы найти блокирующий запрос и точный участок кода, который требует изменения.