В процессе разработки или поддержки высоконагруженных систем на платформе 1С:Предприятие 8 мы часто сталкиваемся с проблемами производительности, связанными с блокировками данных. Одной из наиболее неприятных ситуаций является блокировка всей таблицы (Table Lock) там, где, казалось бы, запрос должен затрагивать лишь небольшое количество строк. Рассмотрим типичный сценарий: при проведении документа «Заказ покупателя» выполняется запрос к виртуальной таблице остатков регистра накопления ЗаказыПокупателей. Несмотря на установленный фильтр по конкретному заказу, СУБД блокирует весь регистр, парализуя работу других пользователей.
В этой статье мы подробно разберем, почему это происходит, как механизмы MS SQL Server взаимодействуют с платформой 1С и какие шаги необходимо предпринять, чтобы обойти блокировку всей таблицы.
Проанализируем ситуацию, описанную разработчиком. Имеется конфигурация «Управление торговлей 10.3», работающая на платформе 8.3 в режиме совместимости 8.2. База данных развернута на MS SQL Server. Проблема возникает в обработке проведения, когда выполняется следующий запрос:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Док.НомерСтроки КАК НомерСтроки,
| Док.Номенклатура КАК Номенклатура,
| ЗаказыПокупателейОстатки.КоличествоОстаток,
| ЗаказыПокупателейОстатки.ДоговорКонтрагента
|ИЗ
| РегистрНакопления.ЗаказыПокупателей.Остатки(&Дата, ЗаказПокупателя = &ДокументСсылка) КАК ЗаказыПокупателейОстатки
| ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказПокупателя.Товары КАК Док
| ПО ЗаказыПокупателейОстатки.ЗаказПокупателя = Док.Ссылка
| И ЗаказыПокупателейОстатки.Номенклатура = Док.Номенклатура
|ГДЕ
| Док.Ссылка = &ДокументСсылка
| И ЗаказыПокупателейОстатки.КоличествоОстаток > 0";
Запрос.УстановитьПараметр("Дата", Дата);
Запрос.УстановитьПараметр("ДокументСсылка", Заказ);
ТаблицаТоваров = Запрос.Выполнить.Выгрузить();
Несмотря на использование параметра &ДокументСсылка в условиях виртуальной таблицы, мониторинг через консоль Текущие блокировки MS SQL Server показывает наложение эксклюзивной блокировки (X) на всю физическую таблицу регистра. Это происходит сразу после выполнения метода Запрос.Выполнить().
Выясним, почему СУБД принимает решение заблокировать всю таблицу вместо отдельных строк. В MS SQL Server существует механизм, называемый Lock Escalation (эскалация блокировок). Когда количество блокировок на уровне строк или страниц в рамках одной транзакции превышает порог в 5000 объектов, SQL Server пытается минимизировать затраты оперативной памяти на хранение структуры блокировок и заменяет тысячи мелких блокировок одной крупной на всю таблицу.
Рассмотрим подробнее: если документ «Заказ покупателя» содержит много строк или если перед выполнением этого запроса в этой же транзакции уже были изменены тысячи записей в других таблицах, запрос к остаткам может стать «последней каплей». СУБД видит, что транзакция потребляет много ресурсов, и выполняет эскалацию. В старых конфигурациях, таких как УТ 10.3, бизнес-логика часто перегружена дополнительными проверками, что увеличивает вероятность такого поведения.
Важным фактором является режим совместимости 8.2. Проанализируем различия: в современных версиях платформы (без режима совместимости) 1С использует более оптимальные параметры взаимодействия с SQL Server. В режиме 8.2 при использовании автоматического режима управления блокировками или даже при неполном переходе на управляемые блокировки, SQL Server может удерживать разделяемые блокировки (S) на чтение до конца транзакции. Эти блокировки конфликтуют с эксклюзивными блокировками (X), возникающими при записи движений документа, что приводит к взаимоблокировкам или эскалации.
Одним из самых эффективных способов решения проблем с блокировками чтения является включение механизма Read Committed Snapshot Isolation на уровне базы данных SQL Server. Рассмотрим, как это работает: при включенном RCSI читающие транзакции не накладывают S-блокировки на строки, а читают согласованную версию данных из tempdb (версионность строк). Это позволяет избежать конфликтов между чтением и записью.
Выполним следующие шаги для включения этого режима (требуется монопольный доступ к базе):
-- Переводим базу в однопользовательский режим для сброса соединений
ALTER DATABASE [ИмяВашейБазы] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- Включаем режим версионности
ALTER DATABASE [ИмяВашейБазы] SET READ_COMMITTED_SNAPSHOT ON;
-- Возвращаем многопользовательский режим
ALTER DATABASE [ИмяВашейБазы] SET MULTI_USER;
Важное замечание: включение RCSI в режиме совместимости 8.2 значительно снижает вероятность блокировок, но стоит помнить, что это увеличивает нагрузку на базу tempdb. Обязательно проверьте наличие свободного места на диске, где расположена системная база tempdb.
Посмотрим на структуру самого запроса. Использование ЛЕВОЕ СОЕДИНЕНИЕ виртуальной таблицы остатков с табличной частью документа может сбить с толку оптимизатор SQL Server. Если статистика индексов устарела, SQL может выбрать план Index Scan (полное сканирование индекса) вместо Index Seek (поиск по индексу).
Проанализируем правильный подход: сначала следует отобрать необходимые данные из документа во временную таблицу, проиндексировать её, и только потом соединять с регистром. Это сделает план запроса стабильным.
// 1. Создаем временную таблицу с товарами документа
Запрос.Текст =
"ВЫБРАТЬ
| Док.Номенклатура КАК Номенклатура,
| Док.Ссылка КАК Ссылка
|ПОМЕСТИТЬ ВтТовары
|ИЗ
| Документ.ЗаказПокупателя.Товары КАК Док
|ГДЕ
| Док.Ссылка = &ДокументСсылка;
|
|////////////////////////////////////////////////////////////////////////////////
|// 2. Получаем остатки, соединяя с временной таблицей
|ВЫБРАТЬ
| ЗаказыПокупателейОстатки.КоличествоОстаток,
| ВтТовары.Номенклатура
|ИЗ
| ВтТовары КАК ВтТовары
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ЗаказыПокупателей.Остатки(&Дата, ЗаказПокупателя = &ДокументСсылка) КАК ЗаказыПокупателейОстатки
| ПО ВтТовары.Номенклатура = ЗаказыПокупателейОстатки.Номенклатура";
Такой подход гарантирует, что СУБД будет искать данные только по конкретному ЗаказПокупателя, используя индекс регистра накопления, и риск эскалации блокировки из-за неэффективного плана запроса значительно снизится.
Если ваша инфраструктура позволяет, рассмотрим возможность повышения режима совместимости конфигурации до 8.3. Это позволит платформе использовать современные механизмы управления транзакциями и более эффективные способы генерации SQL-кода. В режиме 8.3 платформа 1С по умолчанию настраивает сеанс SQL Server так, чтобы блокировки на чтение были минимальными, а механизм управляемых блокировок работал в полную силу.
Однако, это серьезный шаг для старой конфигурации (УТ 10.3), требующий тщательного тестирования всей функциональности, так как могут измениться алгоритмы проведения документов, заложенные разработчиками много лет назад.
Если вышеуказанные меры не помогают, проанализируем ситуацию с помощью Технологического журнала 1С. Настроим сбор события DBMSSQL и TLOCK. В файле настроек logcfg.xml следует указать отбор по длительным блокировкам. Это позволит увидеть точный SQL-запрос, который вызывает TABLE LOCK, и понять, на каком этапе транзакции это происходит.
Также стоит обратить внимание на настройки самого SQL Server. Параметр "Lock Escalation" можно настроить на уровне конкретной таблицы, запретив эскалацию (ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE)), но это крайняя мера, так как она может привести к переполнению памяти сервера (Lock Memory) и падению всей СУБД.
Мы разобрали сложную проблему блокировки всей таблицы при работе с остатками. Для решения задачи мы рекомендуем придерживаться следующего алгоритма:
Использование этих методов позволит вам значительно повысить параллельность работы пользователей и забыть о проблемах с блокировкой целых регистров при выполнении простых запросов.