Перенос информационных баз 1С:Предприятие на новые версии СУБД (например, переход с устаревшего MS SQL Server 2008 на современный MS SQL Server 2017) часто преподносит неприятные сюрпризы. Одна из таких классических проблем — критическое замедление отката транзакций (ROLLBACK) при возникновении ошибок проведения документов (например, при попытке записи дублирующихся записей в уникальный индекс регистра сведений).
В штатном режиме документ объемом в 2000 строк может записываться и проводиться всего за 2–3 секунды. Однако, если система сталкивается с ошибкой дублирования ключа, откат транзакции затягивается на 30 и более минут. В течение всего этого времени таблица регистра сведений оказывается полностью заблокированной, парализуя работу пользователей. Давайте подробно разберем, почему возникает эта проблема, как на нее влияют особенности архитектуры MS SQL Server и платформы 1С, и как исправить ситуацию программными и административными методами.
Для того чтобы понять природу долгого отката, нам необходимо проанализировать поведение системы на двух уровнях: на уровне кода 1С:Предприятие и на уровне механизмов хранения данных в MS SQL Server.
При проведении документа платформа 1С открывает неявную транзакцию СУБД. Все операции записи в регистры, выполняемые в процедуре ОбработкаПроведения, происходят внутри этой транзакции. Если в процессе записи возникает ошибка (например, нарушение уникальности индекса), платформа посылает СУБД команду ROLLBACK TRANSACTION для возврата базы данных в исходное состояние.
Медленный откат транзакции в MS SQL Server 2017 по сравнению со старой СУБД SQL Server 2008 обусловлен несколькими критическими факторами:
РегистрСведенийМенеджерЗаписи внутри цикла для модификации данных порождает лавинообразный рост записей в журнале транзакций СУБД (LDF), которые затем приходится последовательно отменять в обратном направлении.Давайте подробно рассмотрим, как оптимизировать код 1С и настройки СУБД для решения этой проблемы.
Проанализируем исходный программный код процедуры ЗаписатьСоответсвиеСтроковыхПредставленийСМО, представленный в обсуждении темы. В нем для каждой строки табличной части создается отдельный менеджер записи регистра сведений:
Процедура ЗаписатьСоответсвиеСтроковыхПредставленийСМО() Экспорт
Для Каждого СтрокаТЧ Из СоответствиеМестРаботы Цикл
Если Не ЗначениеЗаполнено(СтрокаТЧ.МестоРаботыСсылка) Тогда
Продолжить;
КонецЕсли;
Хеш = Новый ХешированиеДанных(ХешФункция.MD5);
Хеш.Добавить(СтрокаТЧ.МестоРаботы);
Запись = РегистрыСведений.СоответствиеСтроковыхПредставленийСМО.СоздатьМенеджерЗаписи();
Запись.СтраховаяОрганизация = СтраховаяОрганизация;
Запись.ХэшСтроки = Хеш.ХешСумма;
Запись.МестоРаботы = СтрокаТЧ.МестоРаботыСсылка;
Запись.Записать();
КонецЦикла;
// Аналогичный цикл для СоответствиеПрограммСтрахования...
КонецПроцедуры
Почему этот код неоптимален? Объект РегистрСведенийМенеджерЗаписи предназначен для интерактивной или точечной работы с одной конкретной записью регистра. При вызове метода Записать() менеджер записи выполняет в СУБД сразу несколько тяжелых операций: сначала он пытается прочитать запись по указанному ключу (запрос SELECT с блокировкой), затем удаляет старую запись (DELETE) и только после этого вставляет новую (INSERT).
Если в табличной части документа находится 2000 строк, СУБД в рамках одной транзакции вынуждена выполнить до 6000 отдельных SQL-запросов! Это генерирует колоссальный объем служебной информации в журнале транзакций (файле .ldf). В случае сбоя СУБД тратит колоссальное время на то, чтобы прочитать этот лог в обратном направлении и отменить каждую из 6000 микроопераций.
Как сделать правильно? Мы должны отказаться от использования менеджера записи в цикле и переписать код с использованием объекта РегистрСведенийНаборЗаписей. Набор записей позволяет записать весь массив данных одним пакетом (одним или несколькими массовыми INSERT-запросами), что в десятки раз сокращает объем транзакционного лога и ускоряет время возможного отката.
Давайте перепишем процедуру оптимизированным способом:
Процедура ЗаписатьСоответсвиеСтроковыхПредставленийСМО_Оптимизированная() Экспорт
// Шаг 1. Подготавливаем таблицу данных для записи
ТаблицаДляЗаписи = Новый ТаблицаЗначений;
ТаблицаДляЗаписи.Колонки.Добавить("СтраховаяОрганизация");
ТаблицаДляЗаписи.Колонки.Добавить("ХэшСтроки");
ТаблицаДляЗаписи.Колонки.Добавить("МестоРаботы");
ТаблицаДляЗаписи.Колонки.Добавить("ПрограммаСтрахования");
// Шаг 2. Заполняем таблицу данными из первой коллекции
Для Каждого СтрокаТЧ Из СоответствиеМестРаботы Цикл
Если Не ЗначениеЗаполнено(СтрокаТЧ.МестоРаботыСсылка) Тогда
Продолжить;
КонецЕсли;
Хеш = Новый ХешированиеДанных(ХешФункция.MD5);
Хеш.Добавить(СтрокаТЧ.МестоРаботы);
НоваяСтрока = ТаблицаДляЗаписи.Добавить();
НоваяСтрока.СтраховаяОрганизация = СтраховаяОрганизация;
НоваяСтрока.ХэшСтроки = Хеш.ХешСумма;
НоваяСтрока.МестоРаботы = СтрокаТЧ.МестоРаботыСсылка;
КонецЦикла;
// Шаг 3. Заполняем таблицу данными из второй коллекции
Для Каждого СтрокаТЧ Из СоответствиеПрограммСтрахования Цикл
Если Не ЗначениеЗаполнено(СтрокаТЧ.ПрограммаСтрахованияСсылка) Тогда
Продолжить;
КонецЕсли;
Хеш = Новый ХешированиеДанных(ХешФункция.MD5);
Хеш.Добавить(СтрокаТЧ.ПрограммаСтрахования);
НоваяСтрока = ТаблицаДляЗаписи.Добавить();
НоваяСтрока.СтраховаяОрганизация = СтраховаяОрганизация;
НоваяСтрока.ХэшСтроки = Хеш.ХешСумма;
НоваяСтрока.ПрограммаСтрахования = СтрокаТЧ.ПрограммаСтрахованияСсылка;
КонецЦикла;
// Шаг 4. Избавляемся от дубликатов в памяти 1С перед записью в БД!
// Это исключит саму ошибку дублирования ключа на уровне СУБД.
ТаблицаДляЗаписи.Свернуть("СтраховаяОрганизация, ХэшСтроки", "МестоРаботы, ПрограммаСтрахования");
// Шаг 5. Записываем данные через Набор Записей одним пакетом
НаборЗаписей = РегистрыСведений.СоответствиеСтроковыхПредставленийСМО.СоздатьНаборЗаписей();
// Устанавливаем отбор по ключевому измерению, если это необходимо
НаборЗаписей.Отбор.СтраховаяОрганизация.Установить(СтраховаяОрганизация);
// Загружаем подготовленную таблицу и записываем ее в базу данных
НаборЗаписей.Загрузить(ТаблицаДляЗаписи);
// Запись происходит за одно обращение к СУБД
НаборЗаписей.Записать(Истина);
КонецПроцедуры
Благодаря этому изменению мы достигаем сразу трех целей:
Свернуть(), что предотвращает появление ошибок СУБД.Если оптимизация кода 1С решает локальную проблему конкретного документа, то системные настройки СУБД помогут защитить базу данных от медленных откатов в масштабе всей системы.
Проанализируем ключевые административные настройки, которые необходимо выполнить на СУБД MS SQL Server 2017 после миграции со старых версий:
При переносе базы на новый сервер файл журнала транзакций (.ldf) часто создается заново с дефолтными настройками автоприращения (например, шаг в 1 Мб или 10%). В результате частых расширений файл физически разбивается внутри СУБД на тысячи мелких сегментов — Virtual Log Files (VLF).
При откате транзакции SQL Server вынужден читать файл .ldf в обратном направлении, последовательно переходя от одного VLF к другому. Если количество VLF превышает 1000, производительность операции ROLLBACK падает в геометрической прогрессии. Рекомендуется выполнить сжатие (Shrink) файла лога, а затем вручную установить его целевой размер (например, сразу 10 Гб) с шагом авторасширения не менее 1024 Мб.
В обычных условиях СУБД выполняет в файл лога последовательную запись (Sequential Write). Однако во время отката транзакции СУБД осуществляет активное случайное чтение лога. Если файл .ldf расположен на медленном HDD-накопителе или на общем RAID-массиве с высоким показателем задержки чтения (Read Latency), то откат транзакции мгновенно упирается в дисковую очередь. Перенесите файлы журналов транзакций на высокоскоростные твердотельные накопители SSD или NVMe.
Поскольку откат транзакции выполняется в один поток на одном ядре процессора, критически важной становится частота этого ядра. По умолчанию в Windows Server часто установлена схема электропитания "Сбалансированная" (Balanced). При отсутствии общей высокой нагрузки на сервер операционная система искусственно занижает частоту процессора. Одиночный поток ROLLBACK не может заставить процессор поднять частоту, и операция выполняется крайне медленно. На сервере СУБД и сервере приложений 1С должна быть строго установлена схема питания «Высокая производительность» (High Performance).
В MS SQL Server 2017 используется новый механизм оценки кардинальности (Cardinality Estimator). Иногда он строит неоптимальные планы для сложных запросов 1С, использующих временные таблицы. Попробуйте установить уровень совместимости базы данных в значение SQL Server 2012 (110). Это заставит СУБД использовать старый, проверенный временем оптимизатор запросов, который работал на вашей старой версии SQL Server 2008.
Для предотвращения критических зависаний базы данных при ошибках проведения документов всегда придерживайтесь следующих правил:
РегистрСведенийМенеджерЗаписи внутри циклов, особенно в транзакционных процедурах проведения документов (ОбработкаПроведения).РегистрСведенийНаборЗаписей.