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

← На главную

Рассмотрим частую и довольно неприятную ситуацию, с которой сталкиваются многие разработчики при загрузке больших объемов данных в 1С. Предположим, стоит задача: прочитать из Excel-файла около 20 000 строк, содержащих артикулы и цены, найти по каждому артикулу номенклатуру в базе и создать документ с ценами. Если попытаться реализовать поиск данных запросом в цикле по каждой из 20 000 строк, система с высокой долей вероятности выдаст ошибку: «MS SQL Server Транзакция вызвала взаимоблокировку ресурсов и стала жертвой взаимоблокировки».

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

Почему возникают взаимоблокировки при запросах в цикле?

Ошибка SQL Server (так называемый дедлок) при запросах в цикле возникает из-за того, что база данных пытается управлять конкурентным доступом. Когда в цикле 20 000 раз идет обращение к справочнику Номенклатура, платформа 1С накладывает множество кратковременных блокировок. Если параллельно с вашей загрузкой кто-то из пользователей записывает или редактирует номенклатуру, возникает ситуация, когда две разные транзакции захватывают ресурсы в пересекающемся порядке. В результате они блокируют друг друга, и SQL Server принудительно обрывает одну из них, делая её «жертвой».

Главное правило оптимизации: никогда не используйте запросы в цикле! Замена десятков тысяч запросов на один общий запрос с соединением таблиц (JOIN) решает эту проблему архитектурно. СУБД сама строит оптимальный план выполнения и захватывает ресурсы однократно.

Шаг 1. Чтение данных из файла без использования COM-объектов

Проанализируем старые подходы. Часто разработчики читают Excel через создание объекта COMОбъект("Excel.Application"). На сегодняшний день это считается плохой практикой. Работа через COM-соединение работает крайне медленно на больших объемах данных, а главное — требует обязательной установки Microsoft Office на сервере или клиенте. В современных версиях платформы 1С лучше использовать платформенный объект ТабличныйДокумент.

Посмотрим на пример безопасного чтения файла:


ТабличныйДокумент = Новый ТабличныйДокумент;
Попытка
    ТабличныйДокумент.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
    Сообщение = Новый СообщениеПользователю;
    Сообщение.Текст = "Ошибка чтения файла: " + ОписаниеОшибки();
    Сообщение.Сообщить();
    Возврат;
КонецПопытки;

Шаг 2. Подготовка типизированной Таблицы Значений

Чтобы передать прочитанные данные в запрос, нам необходимо поместить их в ТаблицаЗначений. Здесь кроется важнейший нюанс: все колонки этой ТЗ должны быть строго типизированы с помощью объекта ОписаниеТипов. Если хотя бы одна колонка останется нетипизированной, платформа выдаст ошибку при попытке передать таблицу параметром в запрос.

Сформируем таблицу значений и перенесем в нее данные:


ТЗВрем = Новый ТаблицаЗначений;
// Обязательно указываем типы колонок!
ТЗВрем.Колонки.Добавить("Артикул", Новый ОписаниеТипов("Строка", , Новый КвалификаторыСтроки(25)));
ТЗВрем.Колонки.Добавить("Цена", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2)));

// В реальной задаче здесь будет цикл обхода строк Табличного документа
// Для примера покажем логику заполнения
Стр = ТЗВрем.Добавить();
Стр.Артикул = "ART-12345";
Стр.Цена = 1500.50;

Шаг 3. Использование временных таблиц и пакетных запросов

Теперь загрузим нашу таблицу значений в запрос, поместим её во временную таблицу базы данных и соединим со справочником номенклатуры. Мы будем использовать один пакетный запрос (тексты запросов разделяются символом ;). В этом случае нам даже не обязательно явно создавать МенеджерВременныхТаблиц, платформа сама сохранит промежуточную таблицу после первой части пакета.

Для ускорения работы (ведь мы ищем 20 000 строк) мы применим инструкцию ИНДЕКСИРОВАТЬ ПО. Также, для страховки от расхождения типов, мы используем функцию ВЫРАЗИТЬ.

Разберем текст запроса:


Запрос = Новый Запрос;
Запрос.Текст = 
"ВЫБРАТЬ
|    ВЫРАЗИТЬ(ТЗ_Данные.Артикул КАК СТРОКА(25)) КАК Артикул,
|    ВЫРАЗИТЬ(ТЗ_Данные.Цена КАК ЧИСЛО(15,2)) КАК Цена
|ПОМЕСТИТЬ втАртикулСЦеной
|ИЗ
|    &ТаблицаДанных КАК ТЗ_Данные
|
|ИНДЕКСИРОВАТЬ ПО
|    Артикул
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
|    Номенклатура.Ссылка КАК НоменклатураСсылка,
|    Номенклатура.Артикул КАК Артикул,
|    втАртикулСЦеной.Цена КАК Цена
|ИЗ
|    втАртикулСЦеной КАК втАртикулСЦеной
|        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК Номенклатура
|        ПО втАртикулСЦеной.Артикул = Номенклатура.Артикул
|ГДЕ
|    НЕ Номенклатура.ЭтоГруппа
|    И Номенклатура.Ссылка В ИЕРАРХИИ(&РодительскаяГруппа)";

Запрос.УстановитьПараметр("ТаблицаДанных", ТЗВрем);
Запрос.УстановитьПараметр("РодительскаяГруппа", НужнаяГруппаСправочника);

РезультатЗапроса = Запрос.Выполнить().Выгрузить();

Шаг 4. Обработка результата

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

Проанализируем преимущества такого подхода:

Скорость работы: Один запрос выполняется в десятки раз быстрее, чем 20 000 обращений к базе.
Отсутствие блокировок (Deadlocks): Сервер SQL единовременно планирует выполнение соединения таблиц, не конфликтуя с параллельными сеансами.
Независимость от окружения: Отказ от COM-объектов позволяет коду стабильно работать на серверах Linux и не зависеть от установки MS Office.

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

← На главную