При интенсивной работе с информационной базой 1С, особенно в конфигурациях прошлых поколений, таких как 1С:Управление торговлей 10.3, пользователи часто сталкиваются с проблемами производительности, проявляющимися в виде длительного проведения документов, ошибок "Конфликт блокировок при выполнении транзакции" или "Превышено время ожидания запроса на блокировку", а также полных взаимоблокировок (deadlocks) в MS SQL Server. Эти проблемы могут привести к нестабильной работе системы, потере данных и существенному снижению эффективности бизнес-процессов.
Давайте подробно разберем, как мы можем диагностировать и решить эти проблемы, основываясь на опыте, представленном в данном обсуждении.
Изначально для решения проблемы были предприняты следующие шаги:
Разберем, почему эти подходы либо не дали полного решения, либо имели существенные недостатки:
Частое обновление статистики и очистка кэша:
Обновление статистики: Хотя актуальная статистика жизненно важна для оптимизатора запросов SQL Server, ее обновление каждые 30 минут, скорее всего, избыточно. Если данные в таблицах не меняются кардинально с такой частотой, то прирост производительности от частых обновлений будет минимальным, а нагрузка на сервер, напротив, увеличится. Кроме того, в данном случае проблема была не только в неоптимальных планах, но и в самих механизмах блокировок.
Очистка кэша планов запросов (DBCC FREEPROCCACHE): Эта операция является "ядерной бомбой" для кэша SQL Server. Она принуждает сервер перекомпилировать все планы выполнения запросов с нуля. Если в кэше есть много неоптимальных планов из-за Parameter sniffing, это может дать временное облегчение. Однако в долгосрочной перспективе это очень плохая идея с точки зрения производительности. Каждый раз, когда SQL Server тратит время на компиляцию плана, а не использует уже существующий, он расходует ценные ресурсы. Это может привести к общей деградации производительности, замедлению выполнения запросов и, как следствие, увеличению времени удержания блокировок, что лишь усугубит проблему конфликтов.
Временное отключение "Списывать партии при проведении документов":
Это является временным решением, которое позволило оперативно снять проблему с зависаниями, но имеет серьезные последствия для управленческого учета. При отключении этой опции себестоимость товаров не рассчитывается в момент проведения документа, а значит, отчеты, такие как "Валовая прибыль", будут показывать неактуальные данные. Для получения корректной информации требуется периодическое (например, раз в месяц) восстановление последовательности документов, что само по себе может быть длительной и ресурсоемкой операцией, способной вызвать блокировки, если выполняется в часы пик.
Эти действия, хотя и направлены на улучшение производительности, не затрагивают первопричину конфликтов блокировок, которая часто кроется в настройках СУБД или особенностях алгоритмов 1С.
Для выявления истинных причин конфликтов блокировок и взаимоблокировок нам потребуется использовать специализированные инструменты:
Технологический журнал 1С (ТЖ) - это мощный инструмент для отслеживания событий, происходящих на сервере 1С:Предприятия. Он позволяет увидеть, какие именно операции 1С, на каких объектах метаданных и в каком контексте вызывают проблемы.
Мы настраиваем технологический журнал для сбора следующей информации:
События базы данных (DBMSSQL): Для отслеживания всех взаимодействий 1С с SQL Server, включая выполнение запросов, транзакции, информацию о блокировках на стороне СУБД.
Исключения (EXCP): Для регистрации всех ошибок и исключений, возникающих в системе, в том числе связанных с блокировками.
События вызовов методов (CALL): Для понимания, какие методы 1С и в какой последовательности вызывают проблемные SQL-запросы.
События контекста (Context): Для получения подробной информации о программном коде 1С, который выполнялся в момент возникновения события.
Пример настройки файла конфигурации технологического журнала (logcfg.xml) для получения необходимой информации:
После включения ТЖ, в его логах будут появляться записи, подобные этой, указывающие на взаимоблокировку и конкретный SQL-запрос:
00:34.752000-0,EXCP,6,process=rphost,p:processName=Trade103,OSThread=208832,t:clientID=308636,t:applicationName=1CV8,t:computerName=[Comp],t:connectID=169440,SessionID=560,Usr=[Usr],DBMS=DBMSSQL,DataBase=[Comp]\Trade103,dbpid=59,Exception=DataBaseException,Descr='Конфликт блокировок при выполнении транзакции: Microsoft SQL Server Native Client 11.0: Транзакция (идентификатор процесса 59) вызвала взаимоблокировку ресурсов блокировка с другим процессом и стала жертвой взаимоблокировки. Запустите транзакцию повторно. HRESULT=80004005, SQLSrvr: SQLSTATE=40001, state=30, Severity=D, native=1205, line=1 ',Context=' ОбщийМодуль.УправлениеЗапасамиПартионныйУчет.Модуль : 4606 : ВыполнитьСписание(...);'
00:34.752001-15827960,DBMSSQL,5,process=rphost,p:processName=Trade103,OSThread=208832,t:clientID=308636,t:applicationName=1CV8,t:computerName=[Comp],t:connectID=169440,SessionID=560,Usr=[Usr],DBMS=DBMSSQL,DataBase=[Comp]\Trade103,Trans=1,dbpid=59,Sql='INSERT INTO dbo._AccumRg8581 WITH(SERIALIZABLE) (_Period,_RecorderTRef,_RecorderRRef,...) VALUES(?,?,?,...)',Prm=' ... '
Из этих записей мы видим, что проблема возникла при вызове метода Записать() в модуле ОбщийМодуль.УправлениеЗапасамиПартионныйУчет, который выполнял SQL-запрос INSERT INTO dbo._AccumRg8581 WITH(SERIALIZABLE). Это указывает на то, что регистр накопления (_AccumRg8581, который, вероятно, соответствует регистру "Партии товаров на складах" или "СписанныеТовары") является узким местом.
Когда мы сталкиваемся с взаимоблокировками, крайне важно получить так называемый "график взаимоблокировки" (Deadlock Graph) в формате XML. Этот отчет детально описывает все процессы, участвующие во взаимоблокировке, какие ресурсы они удерживали и какие ресурсы запрашивали, что привело к циклическому ожиданию.
Для сбора Deadlock Graph используются расширенные события (Extended Events) SQL Server. Настраиваем сессию Extended Events на событие deadlock_report. После возникновения взаимоблокировки мы можем извлечь XML-файл с отчетом.
Анализ Deadlock Graph позволит нам увидеть, какие конкретные таблицы, индексы и типы блокировок (например, RangeS-S, RangeI-N, X) были задействованы. Как правило, графические инструменты (например, в SQL Server Management Studio) помогают визуализировать этот процесс и быстро определить жертву и виновника взаимоблокировки.
На основе анализа технологического журнала и Deadlock Graph, а также информации о конфигурации УТ 10.3 и SQL Server 2019, мы выделим ключевые причины проблемы и предложим решения.
Одним из наиболее вероятных источников взаимоблокировок в базах 1С, работающих на MS SQL Server, является использование уровня изоляции транзакций REPEATABLE READ, который часто устанавливается по умолчанию или был настроен так ранее. Давайте разберем его влияние и предложим оптимальное решение.
При уровне изоляции REPEATABLE READ, транзакция, которая читает данные, устанавливает на них разделяемые блокировки (S-блокировки). Эти S-блокировки удерживаются до конца транзакции, чтобы гарантировать, что повторное чтение тех же данных внутри транзакции даст тот же результат. Однако, когда другая транзакция пытается изменить данные, которые удерживаются S-блокировкой, или вставить новые данные в диапазон, который заблокирован S-блокировкой (например, RangeS-S на индексе), может возникнуть конфликт. Если оба процесса ожидают ресурсов, удерживаемых друг другом, возникает взаимоблокировка.
Как мы видели в анализе Deadlock Graph, одна транзакция устанавливала RangeS-S блокировку на индексы _InfoRg7812_ByResource7872_RTRN и _AccumRg8581_ByPeriod_TRN (что является следствием REPEATABLE READ), а другая транзакция пыталась вставить новую строку в _AccumRg8581 с хинтом WITH(SERIALIZABLE), запрашивая RangeI-N (блокировка диапазона для вставки) и затем эксклюзивную (X) блокировку, которая была несовместима с уже установленной RangeS-S.
Современной и рекомендуемой практикой для баз 1С на MS SQL Server является установка уровня изоляции READ COMMITTED SNAPSHOT (RCSI). При включении RCSI, транзакции, которые читают данные, используют моментальные снимки (версии) данных, которые существовали на момент начала выполнения запроса или транзакции. Это означает, что операции чтения не накладывают блокировок, препятствующих операциям записи, и операции записи не блокируют чтение. Это значительно снижает вероятность конфликтов блокировок между операциями чтения и записи.
Для включения RCSI необходимо выполнить следующие шаги в SQL Server Management Studio:
Включить поддержку моментальных снимков:
ALTER DATABASE [ИмяВашейБазы] SET ALLOW_SNAPSHOT_ISOLATION ON;
Установить уровень изоляции Read Committed Snapshot:
ALTER DATABASE [ИмяВашейБазы] SET READ_COMMITTED_SNAPSHOT ON;
Важно: Выполнение этих команд требует монопольного доступа к базе данных или может быть выполнено во время минимальной активности пользователей. После изменения уровня изоляции необходимо перезапустить все клиентские приложения 1С.
После применения этих настроек большинство проблем с блокировками, вызванных чтением данных, должны исчезнуть.
В 1С:Предприятии 8 существуют два основных режима управления блокировками: автоматический и управляемый.
В УТ 10.3 (обычные формы) используется преимущественно автоматический режим блокировок. В этом режиме платформа 1С самостоятельно определяет, какие блокировки и на какие данные накладывать. Однако, иногда этого контроля недостаточно, особенно при чтении данных, которые впоследствии будут изменяться в той же транзакции.
Если в запросе 1С мы сначала читаем данные, а затем в этой же транзакции планируем их изменить, и при этом не указываем явно, что эти данные нужны "для изменения", то платформа может наложить лишь разделяемую (S) блокировку. Другая транзакция может также прочитать эти данные, получив S-блокировку. Когда одна из транзакций попытается изменить данные, ей понадобится эксклюзивная (X) блокировка, которая несовместима с S-блокировкой другой транзакции, что может привести к ожиданию или взаимоблокировке.
Решением в этом случае является явное указание в запросе 1С конструкции ДЛЯ ИЗМЕНЕНИЯ:
ВЫБРАТЬ
...
ИЗ
РегистрНакопления.ПартииТоваровНаСкладах КАК ПартииТоваровНаСкладах
ГДЕ
...
ДЛЯ ИЗМЕНЕНИЯ
РегистрНакопления.ПартииТоваровНаСкладах
Эта конструкция указывает платформе, что на выбранные данные должна быть наложена более строгая блокировка, предотвращающая их изменение другими транзакциями, тем самым снижая вероятность конфликтов.
Полный переход всей конфигурации УТ 10.3 на управляемые блокировки является очень амбициозной и трудоемкой задачей, требующей значительной переработки типового кода. Однако, мы можем рассмотреть частичное применение управляемых блокировок для наиболее проблемных регистров, которые часто фигурируют в ошибках блокировок (например, регистры партионного учета).
Управляемые блокировки позволяют разработчику явно указать, какие данные и на какой период должны быть заблокированы с помощью метода Заблокировать() менеджера блокировок. Это предоставляет более тонкий контроль и может помочь предотвратить многие типы взаимоблокировок, если блокировки накладываются в правильном порядке и на нужные объекты. Например, мы можем блокировать записи проблемного регистра перед их чтением и изменением.
Пример использования менеджера блокировок (концептуально, требует адаптации под конкретный код):
МенеджерБлокировок = Новый МенеджерБлокировок;
ЭлементБлокировки = МенеджерБлокировок.Добавить("РегистрНакопления.ПартииТоваровНаСкладах");
ЭлементБлокировки.УстановитьРежим(РежимБлокировкиДанных.Исключительный);
ЭлементБлокировки.УстановитьЗначение("Склад", Склад); // Пример блокировки по измерению
ЭлементБлокировки.УстановитьЗначение("Номенклатура", Номенклатура); // Пример блокировки по измерению
МенеджерБлокировок.Заблокировать();
// Выполняем операции чтения и записи с регистром
// После завершения транзакции блокировки автоматически снимаются
Применение управляемых блокировок требует глубокого понимания логики работы системы и может быть сложным в реализации и поддержке.
Зависания проведения документов, даже без явных ошибок блокировок, могут быть вызваны неоптимальными планами запросов, что часто связано с механизмом "Parameter sniffing".
SQL Server оптимизатор запросов при первом выполнении запроса с параметрами компилирует план выполнения, основываясь на значениях этих параметров. Если эти значения нетипичны (например, выбрана номенклатура, по которой мало движений), то полученный план может быть неэффективным для последующих вызовов с другими, более распространенными параметрами (например, популярная номенклатура с тысячами движений). В итоге, запросы, которые должны выполняться быстро, начинают "тормозить", увеличивая время удержания блокировок и вероятность конфликтов.
Использование локальных переменных в SQL-запросах (или хранимых процедурах): Мы можем скопировать значения входных параметров во внутренние локальные переменные внутри запроса. Это заставит оптимизатор строить план без учета конкретного значения параметра, основываясь на общей статистике по данным, что часто приводит к более универсальному и стабильному плану.
Опции запросов (Query Hints): Мы можем использовать хинты, такие как OPTION (OPTIMIZE FOR UNKNOWN), чтобы оптимизатор не использовал значение первого параметра для построения плана, или OPTION (OPTIMIZE FOR (@parameter = value)), чтобы указать типичное значение для оптимизации.
Регулярное и адекватное обновление статистики: Убедимся, что автоматическое создание и обновление статистики включено (AUTO_CREATE_STATISTICS ON и AUTO_UPDATE_STATISTICS ON). Дополнительно, в нерабочее время можно выполнять полное обновление статистики.
Изучение и оптимизация запросов 1С: Используя технологический журнал и SQL Server Profiler (или Extended Events), мы можем выявить самые медленные запросы, посмотреть их планы выполнения и при необходимости переписать их в 1С для большей эффективности.
Проблема с отрицательными остатками в регистре "Партии товаров на складах" может значительно усугублять проблемы производительности и блокировок.
В 1С партионный учет (ФИФО, ЛИФО) требует, чтобы при списании всегда были доступны партии товаров. Если система разрешает отрицательные остатки, то при списании программа вынуждена выполнять более сложные алгоритмы: искать партии задним числом, рассчитывать "условную" себестоимость, что увеличивает количество операций чтения и записи в регистр, охват блокируемых данных и общее время выполнения транзакций. Это создает дополнительную нагрузку на базу данных и увеличивает вероятность конфликтов блокировок.
Предотвращение на уровне бизнес-процессов: В первую очередь, необходимо пересмотреть бизнес-процессы, чтобы предотвратить появление отрицательных остатков. Это может включать более строгий контроль поступлений, резервирования и отгрузок товаров.
Корректировка данных: Если отрицательные остатки уже существуют, их необходимо ликвидировать. Это можно сделать с помощью документа "Корректировка регистров" по регистру накопления "Партии товаров на складах", обнулив минуса на дату, предшествующую текущим операциям. Это упростит алгоритмы партионного учета и уменьшит нагрузку.
Регулярное восстановление последовательности: После корректировки данных и для поддержания актуальности партионного учета необходимо регулярно (в нерабочее время) выполнять восстановление последовательности документов. При чистой базе без минусов эта операция будет выполняться быстрее и с меньшим риском блокировок.
Несмотря на все вышеуказанные меры, всегда есть место для оптимизации самого кода 1С:
Разделение транзакций: Если одна транзакция выполняет слишком много операций или затрагивает слишком много разных объектов, рассмотрите возможность ее разделения на более мелкие, чтобы уменьшить время удержания блокировок.
Оптимизация алгоритмов: Внимательно изучите алгоритмы проведения документов, особенно связанных со списанием партий и контролем остатков. Возможно, существуют менее ресурсоемкие способы выполнения тех же операций.
Добавление недостающих индексов: С помощью SQL Server Management Studio мы можем анализировать отсутствующие индексы, рекомендованные SQL Server, и добавлять их. Корректно созданные индексы значительно ускоряют выполнение запросов, уменьшая время, необходимое для поиска и обработки данных, а следовательно, и время удержания блокировок.
Проведение массовых операций в нерабочее время: Операции, такие как массовое перепроведение документов, закрытие месяца, фоновые обмены данными с сайтом, которые могут быть источником блокировок, следует проводить в период минимальной загрузки базы данных.
Решение проблемы конфликтов блокировок и зависаний в 1С:Управление торговлей 10.3 — это многогранный процесс, требующий комплексного подхода. Мы начали с диагностики с помощью технологического журнала 1С и Deadlock Graph SQL Server, что позволило выявить ключевую проблему — неоптимальный уровень изоляции транзакций REPEATABLE READ. Переход на READ COMMITTED SNAPSHOT является критически важным первым шагом.
Дополнительно мы рассмотрели методы оптимизации работы 1С с блокировками (использование ДЛЯ ИЗМЕНЕНИЯ, частичный переход на управляемые блокировки), устранение проблем с Parameter sniffing, а также влияние и методы борьбы с отрицательными остатками. Все эти меры в совокупности позволят значительно улучшить производительность и стабильность работы вашей информационной системы.