Альтернативная стратегия управления блокировками

20.05.19

База данных - HighLoad оптимизация

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

Работа с данными в 1С ориентирована на объектный подход и реализована при помощи принципов объектно ориентированного программирования. Для ссылочных типов данных используются объекты, а для значимых типов данных используются наборы записей. Все операции изменения данных выполняются при помощи этих объектов.

Ссылка = Справочник.Номенлатура.НайтиПоКоду("1234");
Объект = Ссылка.ПолучитьОбъект();

Набор = РегистрСведений.КурсыВалют.СоздатьНаборЗаписей();

Одновременно с этим, в качестве хранилища данных 1С опирается на использование реляционных баз данных, которые реализуют иной подход, а именно оперируют наборами данных. В литературе западных авторов, например Itzik Ben-Gan, эти два образа мышления противопоставляются как "iterative thinking" (итеративное мышление) и "thinking in sets" (мышление наборами). Принципиальная разница заключается в том, что 1C оперирует объектами, входящими в наборы, а SQL оперирует наборами объектов как единым целым.

В конечном итоге это порождает так называемую проблему объектно-реляционного рассогласования интерфейсов (не нашёл лучшего перевода для object-relational impedance mismatch). В ООП её принято решать при помощи технологии объектно-реляционного преобразования (object-relational mapping - ORM). Часто реализация ORM API налагает свои требования и ограничения по работе с данными. Набор этих ограничений и особенностей использования API зависит от целей и задач, преследуемых разработчиками, но, как правило, тех или иных потерь функциональности при работе с РСУБД не избежать.

Реализация ORM 1С имеет существенные ограничения. В первую очередь это касается использования табличных операторов, а во вторую — возможностей определения и использования различных стратегий блокирования ресурсов (записей таблиц). Многим компаниям эти ограничения кажутся неприемлемыми и они не стесняются работать с СУБД напрямую. Если несколько лет назад это казалось чем-то из ряда вон выходящим, то в последнее время я наблюдаю повышенный интерес к этой теме. Похоже, что это становится трендом.

 

Табличные операции (в отличие от построчных).

 

Для выполнения табличных операций в 1С имеется только одна возможность — наборы записей. Для ссылочных данных эта возможность недоступна. При этом наборы записей ограничены своим отбором, за рамки которого выйти нельзя. Например, если мы хотим изменить данные регистра сведений подчинённого регистратору, то мы просто обязаны сделать это для каждого документа по отдельности, задав значение свойства отбора "Регистратор". Кроме этого, обновление (update) значений записей набора реализовано таким образом, что сначала 1С выполняет удаление (delete) записей по заданному отбору, а затем делает вставку (insert). Таким образом, если нам нужно изменить только несколько записей в наборе, то необходимо будет выполнить операции удаления и вставки для остальных записей набора в любом случае.

Разумеется такая работа с данными избыточна. Поэтому в некоторых особо важных с точки зрения 1С случаях платформа работает с таблицами как с единым набором данных. Например это происходит при обновлении номера сообщения в таблицах регистрации изменений или при пересчёте итогов.

 

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

- свёртка или архивирование данных за прошлые периоды;

- массовая замена ссылок или значений (исправление или пересчёт данных);

- нестандартный пересчёт итогов;

- перенос данных из одной базы в другую;

- сверка документов двух баз данных.

 

На Инфостарте есть достаточно много статей по этим темам. Я не хочу повторяться. Могу лишь только сказать, что в некоторых случаях средствами 1С некоторые задачи было просто невозможно выполнить (от слова "совсем"). В остальных случаях это было неприемлемо для бизнеса с точки зрения времени выполнения. Не редко при работе с СУБД напрямую удавалось получать прирост производительности в десятки раз при этом часто без остановки работы пользователей в рабочей базе. То, что раньше выполнялось часами, начинало выполняться за считанные минуты.

 

В последнее время в сети, на YouTube например, можно встретить выступления на тему "Твой ORM обманывает тебя" (your ORM is lying you) или "Почему ORM это анти-паттерн" (why ORM is anti-pattern). Я хочу сказать, что для каждой задачи — свой инструмент. Для ввода данных и оперативной работы пользователей технология ORM вполне пригодна, но для интенсивных вычислений и обработки данных нет.

 

Стратегии блокирования.

 

Реализация ORM 1С диктует свою стратегию блокирования. Текущим стандартом является режим управляемых блокировок, что на уровне MS SQL Server означает изменение поведения, принятого по умолчанию, уровня изоляции транзакции READ COMMITTED на READ COMMITTED SNAPSHOT или "версионник". По сути для управления блокировками платформа 1С предлагает использовать только объект "БлокировкаДанных".

 

Другими словами из всего многообразия вариантов блокировок, которые есть в MS SQL Server было оставлено всего две. Одна на чтение — READ COMMITTED SNAPSHOT, а вторая для обновления данных (UPDLOCK), которая соответствует уровню изоляции REPEATABLE READ.

 

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

 

Я уже писал о таком хинте, как ROWLOCK, в своей статье на Инфостарт "T-SQL + 1С: как правильно удалять очень много записей".

 

Сегодня я расскажу о хинте READPAST.

 

Изначально этот хинт был нужен для того, чтобы на базе таблиц можно было организовать работу с очередями произвольного доступа. Работает этот хинт так, что когда мы выполняем операцию чтения, обновления или удаления, используя READPAST, мы пропускаем все заблокированные записи. Их как-будто бы нет для нас. При этом наша транзакция не блокируется другими транзакциями.

 

Рассмотрим пример: допустим нам необходимо пересчитать и изменить значения какого-нибудь реквизита во всех документах. Пусть это будет его статус оплаты, например. При этом пользователи интенсивно работают с документами и периодически блокируют документы. Наша задача обновить те документы, которые доступны в данный момент времени, без ожидания на блокировках. Нам не принципиально обновить статус прямо сейчас, мы можем запустить обработку несколько позже ещё раз. При этом, избегая блокировок ожидания, мы всё-таки делаем полезное дело, обновляя оперативную информацию, и в какой-то степени экономим ресурсы СУБД по управлению блокировками.

 

Демонстрационная таблица "docs" состоит из трёх колонок "id" дентификатор документа), "sum" (сумма документа) и "status" (0 — ждём оплату, 1 — оплачено).

 

 

Создадим новый запрос в SQL Server Management Studio, который будет пользователем, который меняет сумму документа c id = 1. Обратите внимание, что транзакцию я начинаю явно, но не завершаю её. Мы завершим её потом вручную.

 

 

Теперь в новом окне Management Studio создадим второй запрос, в котором попробуем обновить статус документов с id > 0.

 

 

Выполнение этого запроса "зависнет", ожидая завершения транзакции № 1. Таймаут установлен на 10 секунд. Посмотрим в каком состоянии находятся данные в текущий момент времени:

 

 

Как мы видим транзакиця № 1 обновила сумму документа с id = 1, но статус этого документа не был обновлён. Более того статус ни одного документа не был обновлён, так как транзакция № 2 "зависла" на первой строке. Если подождать истечения 10 секунд в окне транзакции № 2, то в результате мы увидим ошибку.

 

 

Вернёмся в первое окно и откатим транзакцию № 1, выполнив в этом окне следующую инструкцию.

 

 

Теперь используем хинт READPAST.

Снова выполним запрос транзакции № 1, не завершая транзакцию.

Перейдём в окно транзакции № 2, но перед выполнением запроса добавим хинт READPAST.

 

 

Выполним этот код и обнаружим, что команда выполнилась без "зависания". Проверим результат работы.

 

 

Все значения статуса обновлены, кроме записи, которая до сих пор заблокирована транзакцией № 1.

Наша задача выполнена =)

 

Это был очень простой пример. Однако мне вспоминается такая задача: менеджеры по продажам интенсивно оформляют заказы с обязательным резервированием товара на складах компании. Постоянные блокировки, вылеты по таймауту, ну и конечно же если весь документ не провёлся, то по заказу вообще ничего не зарезервировалось — нужно начинать всё сначала. Жалобы менеджеров на программистов, куча потерянного времени самих менеджеров на решение вопросов блокировок между собой в частном порядке, возможно упущенные продажи и даже клиенты.

 

Проблему было решено решить путём построчного резервирования при помощи наборов записей 1С — каждая запись табличной части заказа резервировалась в цикле по отдельности. Допускалось частичное резервирование. Количество фактически зарезервированного товара визуально отображалось в документе. Менеджер мог через какое-то время вернуться к заказу и повторить попытку зарезервировать товар по тем строкам, по которым это не удалось сделать сразу. Это конечно же не исключало ожидания на блокировках, но жизнь стала лучше. Хотя, честно говоря, не намного.

 

Эту проблему можно решить, используя хинт READPAST. При этом одним запросом мы можем обновить записи регистра, выполнить контроль отрицательных остатков и, если остатка не хватает, то зарезервировать товар под ноль, и всё так же одним запросом вернуть результат его работы. Для этого нужно использовать свою какую-нибудь таблицу для хранения оперативных остатков и резервов по товарам, но я думаю, что можно "прикрутиться" и к таблице итогов регистра. Для хранения оперативных итогов там есть запись с периодом равным "5999-11-01 00:00:00". Предлагаю для самостоятельного изучения следующий запрос:

 

 

Написание запросов SQL для 1С это достаточно сложная работа. Чтобы облегчить этот труд я занимаюсь разработкой инструмента, который решает большинство вопросов, возникающих при этом:

- получение метаданных 1С и их сопоставление с именами SQL;

- получение метаданных SQL по типам данных полей таблиц и их индексам;

- создание и редактирование запроса SQL в терминах 1C визуальными средствами (дизайнер запросов);

- генерация SQL кода для выполнения по результатам работы дизайнера запросов.

Рабочее название проекта 1C#. Адрес проекта на GitHub: https://github.com/zhichkin/Z

Мне будет очень приятно, если кто-то заинтересуется проектом и составит мне компанию =)

 
Демо gif-ка дизайнера запросов

 

См. также

Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    2339    spyke    25    

38

Анализируем SQL сервер глазами 1С-ника

HighLoad оптимизация Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих зааросов на sql, ожиданий, конвертация запроса в 1с и рекомендации где может тормозить

1 стартмани

15.02.2024    7347    149    ZAOSTG    66    

95

Удаление строк из таблицы значений различными способами с замером производительности

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    5760    doom2good    48    

63

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    8574    ivanov660    6    

75

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

Казалось бы, КОРП-системы должны быть устойчивы, быстры и надёжны. Но, работая в рамках РКЛ, мы видим немного другую картину. Об основных болевых точках КОРП-систем и подходах к их решению пойдет речь в статье.

15.11.2023    4998    a.doroshkevich    20    

72

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    15954    skovpin_sa    14    

97

Как эффективно настроить autovacuum в Postgres для 1С

HighLoad оптимизация Администрирование СУБД Платформа 1С v8.3 Бесплатно (free)

Кто не любит убирать мусор? Думаю, практически все, а вот в Postgres это обязательный ритуал для эффективной работы. Как эффективно настроить уборку за 1С в Postgres, можно прочитать в этой статье и еще раз задуматься о бесплатности Postgres.

05.08.2023    4971    1CUnlimited    5    

51
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. пользователь 20.05.19 14:23
(0) темы про SQL Server на ИС всегда хорошо :)

+

Тема интересная. А за репо на GitHub отдельный плюс)
botokash; acanta; +2 Ответить
2. gzharkoj 494 21.05.19 08:34 Сейчас в теме
Вам бы отдельной статьей оформить описание вашего инструмента, не каждый долистает до конца и просто не заметит, на мой взгляд вещь с большим потенциалом. Особенно если хотите, чтобы кто-нибудь присоединился к разработке.
bugtester; wowik; +2 Ответить
4. zhichkin 1429 21.05.19 11:14 Сейчас в теме
(2) Согласен. Как раз готовлю такую статью. Скорее всего их будет несколько.
3. Dach 371 21.05.19 09:34 Сейчас в теме
Спасибо за интересный материал!

"Все значения статуса обновлены, кроме записи, которая до сих пор заблокирована транзакцией № 1.

Наша задача выполнена =)"

А как узнать в этом же контексте, какая запись обновлена не была? Это можно как-то сделать? Может эти данные где-то хранятся? Типа статистики какой-нибудь. Хотелось бы понять, на каких записях мы не смогли обновиться, получить их и закэшировать куда-нибудь. Или только потом делать Select с проверкой на нужные условия, как в примере?

"Проблему было решено решить путём построчного резервирования при помощи наборов записей 1С — каждая запись табличной части заказа резервировалась в цикле по отдельности. Допускалось частичное резервирование."

Мы похожим образом сделали. У нас все строки, которые отвалились на блокировках - запоминаются в специальный кэш, а из него уже циклами добавляются до тех пор, пока все-таки не добавятся (не более N проходов). Тут вся соль в том, что надо в таких системах дробить крупные и длинные транзакции на более мелкие и короткие.
5. zhichkin 1429 21.05.19 11:38 Сейчас в теме
(3) До начала выполнения запроса у Вас есть ссылки, на номенклатуру например, или какой-то отбор. В предложении OUTPUT можно вернуть значения этих ссылок - это то, что обновилось. Осталось вычесть это из того что было до отправки запроса.

По правде говоря, на продакшене я бы реализовал задачу резервирования, описанную в статье, на очередях. Очередью можно сделать таблицу SQL. Вот отличная статья по этому поводу: https://rusanu.com/2010/03/26/using-tables-as-queues/ В какой-то степени, если я правильно понял суть, Вы именно это и реализовали, но назвали "специальный кэш".
6. Dach 371 21.05.19 11:55 Сейчас в теме
(5) Именно так. Товар, который не удалось "поставить на резерв" по причине ожидания на управляемой блокировке 1С - падает в "очередь", откуда затем система (в конце алгоритма) несколько раз повторно пытается обработать его тем же самым способом (рекурсивный вызов).

Алгоритм рабочий, но ожидание на блокировке - это же какое-то время (причем довольно длительное, зависит от настройки базы). А вот с помощью хинта READPAST как оказывается - можно эту проблему решить... И это выглядит очень заманчиво с точки зрения повышения пропускной способности системы на предмет большего резервирования товаров за единицу времени. Правда, блокировка остатков накладывается же на стороне ORM, то есть 1С, объектом БлокировкаДанных... То есть, это же не совсем объектная блокировка, о которых Вы ведете речь в статье. Как на стороне СУБД при выполнении прямого запроса понять, что данный набор измерений регистра (Склад+Номенклатура+...+...) действительно заблокирован? Есть над чем подумать, одним словом.
7. zhichkin 1429 21.05.19 16:46 Сейчас в теме
(6) Хочу немного уточнить кое что:
ORM - это то, что "укладывает" объекты в памяти в реляционную базу данных.
БлокировкаДанных - это объект в памяти сервера 1С. Он ничего не блокирует на уровне СУБД.
На уровне СУБД блокировка выполняется в момент НаборЗаписей.Записать() - это ORM.

У Вас по сути реализована работа с очередью в памяти (in-memory queue). Тут есть такой нюанс при работе с объектами в памяти - они могут потеряться при "падении" или "зависании", например, фонового задания, в котором всё это, вероятно, работает. Если это имеет значение, то нужно подумать о реализации сохраняемых очередей (persistent queue). Об этом я писал в прошлом моём сообщении.

Узнать какие блокировки наложены в момент выполнения запроса нельзя. Я думаю, что без этого можно обойтись. Это вопрос реализации.

Если очень хочется узнать кто и что блокирует до выполнения запроса, то в SQL Server можно посмотреть сюда sys.dm_tran_locks
8. Dach 371 21.05.19 17:52 Сейчас в теме
(7) очередь у нас как раз сохраняется - это регистр сведений. Суть не в том, сохраняется ли очередь или нет. Товар в очередь падает только в том случае, если его на первом проходе цикла добавления в резерв - не удалось добавить (по причине тайм-аута на управляемой блокировке 1С - кто-то другой уже добавляет в этот момент этот же товар).

"ORM - это то, что "укладывает" объекты в памяти в реляционную базу данных.
БлокировкаДанных - это объект в памяти сервера 1С. Он ничего не блокирует на уровне СУБД." - это я и имел ввиду, когда обратил внимание на то, что в статье речь об объектных блокировках, которые отражаются на уровне СУБД.

Но в классических алгоритмах, для блокировки остатков перед чтением используется БлокировкаДанных, а это блокировка именно на уровне 1С, а не СУБД. И это не просто "память", это менеджер управляемых блокировок, чисто 1с-ный процесс. Как еще нам предотвратить неизменность остатков на время, пока мы с ними работаем? Фактически, никак.
Вот в чем суть. Мы на уровне 1С какие-то ограничения наложили, а СУБД об этом не в курсе.

"Я думаю, что без этого можно обойтись" - поэтому пока что-то я не вижу вариантов, как "глядя" запросом в СУБД, узнать какой набор данных, по каким разрезам у меня не подлежит изменению.

Поэтому здорово конечно, что есть такой хинт, но практическое применение пока сомнительно (по крайней мере вот в этой конкретной задаче с резервами).

Ситуаций, когда выполняется массовое обновление каких-то полей в таблице и мы можем наткнуться на блокировку СУБД - крайне мало в реальной жизни.
НаборЗаписейРН или НаборЗаписейРС записываются очень быстро, если только там не 100 тыс. строк. Ну а если там 100 тыс. строк - то это явно что-то большое и тяжелое, зачем во время запуска этого большого и тяжелого пытаться еще и другие алгоритмы, обновляющие ту же таблицу запускать?
9. acanta 21.05.19 17:56 Сейчас в теме
Этот тренд имхо очень перспективный. К примеру, часть такого кода может быть вынесена во внешние компоненты и отлаживаться в режиме реального времени, а по окончании тестовой эксплуатации выпущена без исходных текстов.
Вопрос в том, как может выглядеть программа, позволяющая редактировать исполняемый код подключаемой внешней компоненты без перезапуска сервера и пользователей?
10. zhichkin 1429 21.05.19 19:14 Сейчас в теме
(9)
Вопрос в том, как может выглядеть программа, позволяющая редактировать исполняемый код подключаемой внешней компоненты без перезапуска сервера и пользователей?

Поясните, пожалуйста, подробнее что Вы имеете ввиду ? Я так понял вопрос: как сделать так, чтобы новая версия внешней компоненты была загружена в работающий процесс сервера или пользователей без перезагрузки сервера. Верно ?
12. zhichkin 1429 21.05.19 22:20 Сейчас в теме
(8)
Поэтому здорово конечно, что есть такой хинт, но практическое применение пока сомнительно (по крайней мере вот в этой конкретной задаче с резервами).

Согласен. Задача резервирования гораздо сложнее на практике, чем в теории. Одним хинтом её не решишь.
Было бы интересно узнать подробности про Вашу систему:
1. Как хранятся остатки и резервы - в одной таблице или разных.
2. Допускается ли изменение остатков задним числом ? Что происходит с неактуальными резервами в результате этого ? И вообще что делается для обеспечения актуальности остатков и резервов. Насколько это вообще актуально.
3. Какое количество позиций резервируется в единицу времени ?
4. Какое количество конкурирующих между собой пользователей ?
5. Что происходит в момент реализации из резервов если реализация их использует частично ?
Наверное есть ещё какие-то интересные подробности ...
15. Dach 371 22.05.19 09:31 Сейчас в теме
(12) да, там много нюансов и тонкостей, боюсь в одном комментарии не смогу раскрыть, будет время - поделюсь в виде небольшой статьи. Но основная проблема именно в блокировках 1С. Я уже сетовал ранее на то, что нет никаких удобных инструментов для онлайн-контроля 1с-ных блокировок. Кстати, в одном из релизов 8.3.12 метод Заблокировать() стал работать процентов на 15-20 медленнее (вот именно сам метод!) и мы это тут же заметили, как только одна из продуктивных баз перешла на нее. Хотя на тестах, естественно, внимания особо на это не обратили. И лишь потом, специально сравнили несколько релизов и обнаружили просадку. Теперь всегда будем это проверять...
zhichkin; +1 Ответить
11. acanta 21.05.19 19:37 Сейчас в теме
Да, и без перезагрузки сеанса пользователя. Как альтернатива расширениям.
13. zhichkin 1429 21.05.19 22:21 Сейчас в теме
(11) Это очень сложный вопрос. Нужен какой-то менеджер динамической загрузки библиотек. Может быть микросервисы использовать как вариант ?
14. acanta 21.05.19 22:26 Сейчас в теме
(13) возможно. А возможно это одна, постоянно загруженная длл, при каждом выполнении какого-либо модуля подхватвающая его последний, актуальный вариант.
Оставьте свое сообщение