Дедлоки в SQL Server 2019. Как возникают дедлоки, как снизить риски и что делать, если дедлок уже произошёл

Дедлок (взаимоблокировка) — это, пожалуй, одна из самых неприятных ситуаций, с которой сталкивается администратор SQL Server. Внешне всё выглядит так: часть запросов начинает «подвисать», а затем один из них принудительно завершается с ошибкой. В отличие от обычной блокировки, которая рано или поздно разрешается сама, дедлок — это тупик, требующий вмешательства. В SQL Server 2019 и более новых версиях механизмы обнаружения и разрешения дедлоков стали гораздо совершеннее, а у разработчиков и администраторов появились инструменты, позволяющие не только бороться с последствиями, но и предотвращать саму возможность их возникновения.

Что такое дедлок и почему это не просто «зависший запрос»

Принципиальное различие между блокировкой (blocking) и дедлоком (deadlock) заключается в природе ожидания. Блокировка возникает, когда одна транзакция удерживает ресурс, а вторая ожидает его освобождения. Как только первая транзакция завершится, вторая продолжит выполнение — это штатная ситуация в конкурентной среде.

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

1. Транзакция A захватывает монопольную блокировку на строку 1 таблицы Products.
2. Транзакция B захватывает монопольную блокировку на строку 2 таблицы Inventory.
3. Транзакция A пытается получить доступ к строке 2 таблицы Inventory и вынуждена ждать, пока транзакция B освободит ресурс.
4. Транзакция B, в свою очередь, пытается получить доступ к строке 1 таблицы Products и также переходит в режим ожидания.
5. Образуется замкнутый круг: A ждёт B, а B ждёт A. Ни одна из транзакций не может завершиться без внешнего вмешательства[reference:1].

SQL Server непрерывно отслеживает подобные ситуации с помощью фонового потока lock monitor. Если циклическая зависимость обнаружена, сервер выбирает одну из транзакций в качестве «жертвы» (deadlock victim) и принудительно завершает её с ошибкой 1205, позволяя второй транзакции продолжить работу.

Почему возникают дедлоки: четыре кита проблемы

Томас ЛяРок (Thomas LaRock), признанный эксперт в области SQL Server, сформулировал четыре ключевые причины, которые — по отдельности или в комбинации — приводят к взаимоблокировкам:

• Прикладной код. Порядок доступа к таблицам в разных транзакциях различается. Классический пример: одна процедура обновляет сначала Orders, затем OrderDetails, а другая — в обратном порядке.
• Схема базы данных. Отсутствие необходимых индексов вынуждает оптимизатор выбирать сканирование таблицы, захватывая избыточное количество блокировок, что резко расширяет «поле конфликта».
• Шаблоны доступа. Непредсказуемая последовательность обращений из разных частей приложения к одним и тем же таблицам.
• Уровень изоляции транзакций. Используемый по умолчанию уровень READ COMMITTED часто усугубляет блокировки, тогда как более современные уровни на основе версионирования строк способны кардинально снизить число конфликтов.

Как уменьшить риск дедлоков: проактивная стратегия

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

1. Унифицируйте порядок доступа к объектам

Самое действенное правило: все транзакции должны захватывать ресурсы в одной и той же последовательности. Если и процедура оформления заказа, и процедура расчёта скидки начинают с таблицы Customers, затем переходят к Orders и только потом к OrderDetails — циклического ожидания не возникнет.

2. Делайте транзакции максимально короткими

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

3. Создайте правильные индексы

Запрос, использующий индексный поиск (seek), захватывает считанные блокировки. Тот же запрос, вынужденный выполнять сканирование таблицы из-за отсутствия индекса, блокирует сотни и тысячи строк, многократно увеличивая риск конфликта.

4. Включите изоляцию на основе версионирования строк (RCSI)

Технология READ COMMITTED SNAPSHOT ISOLATION (RCSI) — одно из самых мощных средств борьбы с конфликтами чтения-записи. При включённом RCSI читающие запросы не накладывают разделяемых блокировок. Вместо этого они получают последнюю зафиксированную версию строки из хранилища версий в tempdb, в то время как писатели продолжают работать с актуальными данными[reference:7][reference:8].

Включить RCSI можно командой:

ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;

Важно: RCSI создаёт дополнительную нагрузку на tempdb, поэтому подсистема хранения должна быть готова к возросшему потоку операций ввода-вывода.

5. Используйте оптимизированную блокировку (SQL Server 2023+)

В современных версиях SQL Server появился механизм Optimized Locking. Он кардинально меняет подход к управлению блокировками: вместо удержания тысяч строчных блокировок до конца транзакции используется идентификатор транзакции (TID), что снижает потребление памяти и предотвращает эскалацию блокировок.

Что делать, если дедлок уже произошёл: алгоритм расследования

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

Шаг 0. Подготовка: включите сбор информации ДО того, как случится беда

На постоянной основе должен быть включён как минимум один из следующих механизмов:

• Расширенное событие system_health. Встроенная сессия, которая по умолчанию активна в SQL Server и автоматически сохраняет информацию о дедлоках в кольцевой буфер. Это самый быстрый способ заглянуть в недавнюю историю взаимоблокировок без настройки дополнительных инструментов.
• Специализированная сессия расширенных событий. Событие xml_deadlock_report даёт максимально детализированную информацию и сохраняет её в файл для последующего анализа.
• Флаг трассировки 1222. Включает запись информации о дедлоках непосредственно в лог ошибок SQL Server. Внимание: при большом количестве дедлоков этот флаг может создать избыточную нагрузку на сервер.

Шаг 1. Получите граф дедлока (Deadlock Graph)

Deadlock Graph — это XML-документ (и его визуальное представление), который содержит исчерпывающую информацию о конфликте: идентификаторы процессов, полные тексты запросов-участников, названия захваченных ресурсов и типы блокировок. Получить его можно несколькими способами:

• Из system_health: выполните запрос к sys.dm_xe_session_targets, чтобы извлечь XML из кольцевого буфера.
• Из файла расширенных событий: используйте функцию sys.fn_xe_file_target_read_file, если настроена файловая сессия.
• Из лога ошибок SQL Server: если включён флаг трассировки 1222, информация будет записана в ERRORLOG.

Шаг 2. Проанализируйте граф

Откройте XML-файл в SQL Server Management Studio — он автоматически отобразит графическую схему дедлока. На ней будут видны два (или более) процесса и ресурсы, за которые они борются. Ключевые моменты для анализа:

• Тексты запросов. Какие именно инструкции выполнялись в момент конфликта?
• Типы блокировок. Какие блокировки уже были захвачены, а какие запрашивались?
• Ресурсы. На каких строках, страницах или ключах произошёл конфликт?
• Объекты. Какие таблицы, индексы и разделы вовлечены в дедлок?

Шаг 3. Определите корневую причину и устраните её

Анализ графа обычно указывает на одну из типовых проблем:

• Разный порядок доступа. Измените код транзакций так, чтобы последовательность операций была одинаковой.
• Отсутствие индекса. Если в графе фигурирует сканирование таблицы, создайте индекс, который позволит выполнять поиск по предикату запроса.
• Слишком длинная транзакция. Разбейте её на несколько более мелких или вынесите нетранзакционные операции за границы транзакции.
• Высокий уровень изоляции. Рассмотрите переход на RCSI или, как минимум, избегайте использования SERIALIZABLE без крайней необходимости.

Шаг 4. Реализуйте устойчивость приложения к дедлокам

Даже при идеальной профилактике единичные дедлоки возможны в системах с очень высокой конкурентностью. Приложение должно быть готово к ошибке 1205 и содержать логику повторного выполнения транзакции. Простейший шаблон на T-SQL:

DECLARE @retries INT = 3;
WHILE @retries > 0
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        -- Ваши операции с данными
        COMMIT TRANSACTION;
        BREAK;
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 1205 AND @retries > 0
        BEGIN
            ROLLBACK TRANSACTION;
            SET @retries = @retries - 1;
            WAITFOR DELAY '00:00:00.500'; -- Небольшая пауза перед повтором
        END
        ELSE
        BEGIN
            THROW;
        END
    END CATCH
END

Как SQL Server выбирает жертву и можно ли на это повлиять

Когда монитор блокировок фиксирует дедлок, он обязан выбрать, какую транзакцию завершить. По умолчанию сервер руководствуется «стоимостью отката» (rollback cost) — количеством работы, уже проделанной транзакцией. Жертвой становится та, которая записала в лог меньше всего данных.

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

-- Установка высокого приоритета для критически важного процесса
SET DEADLOCK_PRIORITY HIGH;

Диапазон значений: от LOW (-5) до HIGH (5), а также числовые значения от -10 до 10. Эта настройка особенно полезна, когда в одной базе одновременно работают критически важные ETL-процессы и лёгкие пользовательские запросы.

SELECT
    xevents.event_data.value('(@timestamp)[1]', 'datetime2')                AS Deadlock_DateTime,
    xevents.event_data.value('(data/value/deadlock/victim-list/victimProcess/@id)[1]', 'varchar(20)') AS Victim_Process,
    DB_NAME(resources.resource.value('@dbid', 'int'))                       AS Database_Name,
    OBJECT_SCHEMA_NAME(resources.resource.value('@objectid', 'int'),
                       resources.resource.value('@dbid', 'int'))            AS Schema_Name,
    OBJECT_NAME(resources.resource.value('@objectid', 'int'),
                resources.resource.value('@dbid', 'int'))                   AS Table_or_View_Name,
    resources.resource.value('@indexname', 'nvarchar(128)')                 AS Index_Name,
    resources.resource.value('@mode', 'nvarchar(60)')                       AS Lock_Mode,
    resources.resource.value('@associatedObjectId', 'bigint')               AS Associated_Object_Id,
    xevents.event_data.query('(data/value/deadlock/process-list/process/inputbuf)[1]') AS Involved_Statements
FROM
    (
        SELECT CAST(target_data AS xml) AS target_data
        FROM sys.dm_xe_session_targets st
        INNER JOIN sys.dm_xe_sessions s
            ON s.address = st.event_session_address
        WHERE s.name = 'system_health'
          AND st.target_name = 'ring_buffer'
    ) AS t
CROSS APPLY target_data.nodes('/RingBufferTarget/event[@name="xml_deadlock_report"]') AS xevents(event_data)
CROSS APPLY xevents.event_data.nodes('(data/value/deadlock/resource-list//*)') AS resources(resource)
ORDER BY Deadlock_DateTime DESC;

Профилактика на уровне инфраструктуры

• Мониторинг и алертинг. Настройте алерты на событие xml_deadlock_report или периодически проверяйте system_health на предмет появления новых дедлоков.
• Регулярный анализ планов выполнения. Иногда дедлоки возникают из-за того, что оптимизатор выбирает неоптимальный план (например, решает выполнить просмотр таблицы вместо поиска по индексу). Анализ планов и обновление статистики помогают избежать подобных сюрпризов.
• Управление нагрузкой. Ресурсоёмкие отчёты и задачи обслуживания планируйте на часы минимальной пользовательской активности, чтобы снизить конкуренцию за блокировки.

Что нового в борьбе с дедлоками в SQL Server 2019 и новее

Каждая новая версия SQL Server привносит улучшения, прямо или косвенно снижающие вероятность дедлоков:

• Ускоренное восстановление базы данных (Accelerated Database Recovery, ADR). Эта технология, появившаяся в SQL Server 2019, кардинально ускоряет откат транзакций. Это означает, что даже если транзакция стала жертвой дедлока, её откат происходит практически мгновенно, не создавая каскадных задержек.
• Интеллектуальная обработка запросов (Intelligent Query Processing, IQP). Улучшения в оптимизаторе, включая адаптивные соединения и обратную связь по памяти, помогают выбирать более эффективные планы, снижая время удержания блокировок.
• Оптимизированная блокировка (Optimized Locking). Как упоминалось выше, этот механизм радикально уменьшает количество блокировок, предотвращая эскалацию и целые классы дедлоков.

Чек-лист: что делать прямо сейчас

1. Настройте сбор информации о дедлоках через system_health или расширенные события — это ваш «чёрный ящик».
2. Проведите аудит самых длительных транзакций и оптимизируйте их.
3. Проверьте, все ли запросы обеспечены адекватными индексами, чтобы избежать лишних блокировок.
4. Оцените возможность включения READ COMMITTED SNAPSHOT ISOLATION для наиболее конфликтных баз данных.
5. Обучите разработчиков принципам унифицированного порядка доступа и корректной обработки ошибки 1205.

Заключение

Дедлок — это не приговор, а инженерная задача, имеющая чёткое решение. Современный SQL Server предоставляет богатый арсенал средств для предотвращения, обнаружения и анализа взаимоблокировок. Ключ к успеху — проактивный подход: правильное проектирование схемы, грамотно написанный код приложения и использование возможностей версионирования строк. Если же дедлок всё-таки произошёл, вооружённый графом дедлока и описанной методологией администратор способен быстро локализовать и устранить корневую причину.

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