Как устранить взаимоблокировку (Deadlock) в MS SQL Server при выгрузке данных из 1С?

При интеграции 1С со сторонними базами данных на платформе MS SQL Server разработчики нередко сталкиваются с ошибкой: Transaction (Process ID ...) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Разберем подробно, почему возникает эта ситуация при параллельной работе нескольких информационных баз или регламентных заданий и какими способами можно устранить проблему.

Выясняем причины возникновения взаимоблокировки

Проанализируем техническую природу ошибки. Взаимоблокировка (Deadlock) возникает тогда, когда два или более параллельных процесса захватывают ресурсы в разном порядке и бесконечно ожидают освобождения ресурсов друг другом. В сообщении об ошибке также часто фигурирует communication buffer resources, что указывает на специфические условия:

1. Параллелизм выполнения запросов (Intra-Query Parallelism): Оптимизатор MS SQL Server распределяет выполнение ресурсоемкой хранимой процедуры на несколько параллельных потоков. В процессе обмена данными через буферы коммуникации эти потоки могут заблокировать сами себя.
2. Эскалация блокировок (Lock Escalation): При выполнении объемных операций вставки (INSERT) или обновления (UPDATE) СУБД автоматически повышает блокировки отдельных строк до блокировок страниц или всей таблицы целиком.
3. Конкуренция параллельных потоков: Когда несколько сеансов 1С одновременно выгружают данные и запускают одну и ту же процедуру постобработки, возникает гонка за монопольный доступ к индексам и страницам данных.
4. Отсутствие необходимых индексов: Если внутри процедуры выполняется сканирование таблицы (Table Scan), СУБД блокирует значительно больший диапазон данных, чем требуется для конкретной операции.

Шаг 1. Сбор и анализ графа взаимоблокировки

Прежде чем менять код, необходимо точно определить участников конфликта и заблокированные ресурсы. Для этого проанализируем граф взаимоблокировки в MS SQL Server:

1. Использование расширенных событий (Extended Events): По умолчанию в системной сессии system_health регистрируются все случаи взаимоблокировок. Откроем отчет xml_deadlock_report в среде SQL Server Management Studio (SSMS), чтобы увидеть точные тексты конфликтующих запросов и типы захваченных блокировок.
2. Включение флагов трассировки: Для логирования информации в журнал ошибок SQL Server выполним команду: DBCC TRACEON (1222, -1); Это позволит получать подробный текстовый отчет о структуре взаимных блокировок в журнале SQL Server.

Шаг 2. Оптимизация на стороне 1С: паттерн повторных попыток и порционная выгрузка

Поскольку СУБД сама рекомендует повторить транзакцию (Rerun the transaction), реализуем в коде 1С обработку исключения с механизмом повторных попыток (Retry Logic). Также важно разбить большие объемы данных на небольшие порции, чтобы избежать эскалации блокировок.

Рассмотрим пример организации повторного выполнения вызова процедуры через ADO с паузой:

МаксимальноеКоличествоПопыток = 5;
ПопыткаВыполнения = 1;
Успешно = Ложь;

Пока ПопыткаВыполнения <= МаксимальноеКоличествоПопыток И Не Успешно Цикл
    Попытка
        КомандаSQL.Execute();
        Успешно = Истина;
    Исключение
        ТекстОшибки = ОписаниеОшибки();
        Если СтрНайти(ТекстОшибки, "deadlock") > 0 Тогда
            Если ПопыткаВыполнения = МаксимальноеКоличествоПопыток Тогда
                ВызватьИсключение "Превышено количество попыток устранения дедлока: " + ТекстОшибки;
            КонецЕсли;
            // Делаем небольшую случайную задержку перед повтором
            Приостановить(1 + (ПопыткаВыполнения * 2));
            ПопыткаВыполнения = ПопыткаВыполнения + 1;
        Иначе
            ВызватьИсключение ТекстОшибки;
        КонецЕсли;
    КонецПопытки;
КонецЦикла;

Шаг 3. Синхронизация параллельных процессов через прикладные блокировки

Если несколько баз 1С одновременно вызывают одну и ту же процедуру постобработки, разберем способ упорядочивания очередей на уровне MS SQL Server с помощью механизма sp_getapplock. Это предотвращает параллельный запуск тяжелого участка кода без необходимости блокировать таблицы данных.

Посмотрим на пример оборачивания логики внутри хранимой процедуры:

CREATE OR ALTER PROCEDURE dbo.AgreeNumLike_upd
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @LockResult int;
    
    -- Запрашиваем эксклюзивную прикладную блокировку
    EXEC @LockResult = sp_getapplock 
        @Resource = 'AgreeNumLike_upd_Lock', 
        @LockMode = 'Exclusive', 
        @LockOwner = 'Session', 
        @LockTimeout = 60000; -- Ожидание до 60 секунд
        
    IF @LockResult >= 0
    BEGIN
        BEGIN TRY
            -- Основной код обновления данных
            UPDATE dbo.Contracts
            SET SearchField = dbo.FormatNumber(ContractNumber)
            WHERE NeedUpdate = 1;
        END TRY
        BEGIN CATCH
            -- Освобождаем блокировку при возникновении ошибки
            EXEC sp_releaseapplock 
                @Resource = 'AgreeNumLike_upd_Lock', 
                @LockOwner = 'Session';
            THROW;
        END CATCH;
        
        -- Освобождаем блокировку после успешного выполнения
        EXEC sp_releaseapplock 
            @Resource = 'AgreeNumLike_upd_Lock', 
            @LockOwner = 'Session';
    END
    ELSE
    BEGIN
        RAISERROR('Не удалось захватить блокировку для выполнения процедуры.', 16, 1);
    END
END;

Шаг 4. Настройки СУБД и оптимизация запросов

Рассмотрим дополнительные методы на уровне конфигурации базы данных MS SQL Server:

1. Ограничение степени параллелизма (MAXDOP): Если взаимоблокировка вызвана параллелизмом внутри одного запроса (communication buffer resources), добавим в конец проблемного SQL-запроса подсказку OPTION (MAXDOP 1). Это принудительно заставит запрос выполняться в один поток.
2. Включение версионирования строк (RCSI): Переведем базу данных в режим READ_COMMITTED_SNAPSHOT, чтобы операции чтения не блокировали операции записи:
   Скопировать код

   
   ALTER DATABASE [ИмяБазыСайта] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
   

3. Использование промежуточных таблиц: Разделим процесс загрузки и постобработки. Базы 1С быстро загружают порции данных в изолированные буферные таблицы (staging), а финальную обработку и актуализацию основных данных выполняет регламентное задание на стороне SQL Server Agent по расписанию.