AX 2012 R3. Включить OCC на InventSum и выжить

[kgksoft]

И снова хочу поделиться success-story.
Готовились к черной пятнице и проводили нагрузочные тестирования. Заметили блокировки при большом числе web-вызовов по операциям резервирования по заказам на продажу на InventSum. Нагрузка была такая, что даже номерную серию SalesId пришлось на последовательности в базе MSSQL перевести. Пришла в голову идея таки включить OCC (оптимистические блокировки) на InventSum.

Если посмотреть код на InventUpdateOnhand.ttsNotifyPreCommit, то можно заметить, что COMMIT выполняется логично, но крайне избыточно.

1. Вставляются новые строки InventSumDelta -> InventSum (причем только ключевые поля без данных). Если строка DELTA-таблицы одна, то строка InventSum даже вставляется (см.п 3)
2. Блокируются строки InventSum по наличию в InventSumDelta
3. Далее идет обновление строк InventSum. Тут 2 варианта
   - если строка InventSumDelta была одна, то читаем пессимистически ее и выполняем ей суммирование количеств из InventSumDelta и WRITE (Insert для той самой строки из п.1). Тут без пессимистической блокировки было не обойтись
   - если строк InventSumDelta по нашей транзакции несколько, то ту строится динамический SQL и обновляются строки InventSum на основании группового запроса в InventSumDelta. Тут красиво читаются данные из InventSum и обновление происходит как поле базы += значение из InventSumDelta. InventSum.RecVersion не обновляется. В базе на измененные строки наложена блокировки. Никто не может теперь их читать. Все правильно
4. Далее дополнительно проводится проверка на отрицательные остатки. И если что-то не так, то идет откат транзакции

Схема красивая рабочая с минимальными блокировками в конце комита, но ... Хочется включить оптимистические блокировки. Если в лоб на таблице InventSum включить OCC, то получаем проблему, когда групповой запрос не обновляет RecVersion, а единичный обновляет измененные строки из-за того, что вычитал старые данные, обновил в памяти кол-во и не упал на обновлении из-за совпадающего RecVersion. Ну и дополнительные выборки с пессимистиком, только чтобы залочить строки перед групповым обновлением.

Чего удалось достичь:

1. При вставке новых записей InventSumDelta->InventSum вставляются всегда, даже если строка в InventSumDelta одна. Причем вставляются уже с данными для одной строки Delta-таблицы.
   

           if (inventSumDeltaCnt == 1)
           {
               select ItemId, InventDimId,
                   #InventSumDeltaMax
               from inventSumDelta
                   group by ItemId, InventDimId
                   where inventSumDelta.ttsId          == this.ttsId() &&
                         inventSumDelta.IsAggregated   == NoYes::No
                       notexists join inventSum
                       where inventSum.ItemId         == inventSumDelta.ItemId &&
                             inventSum.InventDimId    == inventSumDelta.InventDimId;
           }

2. Строки вообще не блокируются перед обновлением. По крайней мере при резервировании товара
   
   

   protected void lockInventSum()
   {
       InventSum           inventSum;
       InventSumDeltaDim   inventSumDeltaDim;
   
       /*
       if (!this.parmDoOnhandCheck()) //!doSummarizedOnhandCheck && !doDirectOnhandCheck && !checkOnHandForWHSItems
       */
       if (!doSummarizedOnhandCheck && !doDirectOnhandCheck)
       {
           return;
       }

3. При обновлении работает исключительно групповая процедура (прямой SQL) на основании групповой подвыборки InventSumDelta с оптимизациями
   • - для одной строки InventSumDelta, которая была вставлена ранее вызов вообще не выполняется
   • - если строка одна и обновляется, то подвыборка-InventSumDelta не выполняется. Все есть табличной переменной InventSumDelta и в динамический SQL подставляются константы
   • - обновлятся RecVersion в строках InventSum
     RecVersion = ' = FLOOR(RAND()*2147483647)+1'

В итоге блокировки InventSum ушли и подсистема резервирования (по сути любые операции обновления InventSum) стала выдерживать гораздо большие нагрузки от веб-сервисов. Если случаются коллизии при резервировании, когда 2 клиента пытаются одновременно зарезервировать последнюю единицу товара, то транзакция откатится на этапе проверки отрицательных остатков (как было и раньше).
Огромная торговая компания розница + интернет магазин в черную пятницу отлично себя чувствовали при онлайн резервировании.

[trud]

Цитата:

Сообщение от kgksoft

Заметили блокировки при большом числе web-вызовов по операциям резервирования по заказам на продажу на InventSum. Нагрузка была такая, что даже номерную серию SalesId пришлось на последовательности в базе MSSQL перевести. Пришла в голову идея таки включить OCC (оптимистические блокировки) на InventSum.

Поздравляю с решением проблемы, но описание не очень понятно.
1. О каком кол-ве операций(в час к примеру) идет речь
2. Почему не использовали предварительное выделение номеров
3. Как я понял вы сделали обновление на SQL вне зависимости от кол-ва строк. OCC это же сво-во в АХ, как оно влияет вообще на ?
4. Какой объем InventSum если выполнить следующий скрипт

select count(*) as number, Closed,ClosedQty from InventSum (nolock)
Group by Closed, ClosedQty

[kgksoft]

Цитата:

Сообщение от trud

Поздравляю с решением проблемы, но описание не очень понятно.
1. О каком кол-ве операций(в час к примеру) идет речь
2. Почему не использовали предварительное выделение номеров
3. Как я понял вы сделали обновление на SQL вне зависимости от кол-ва строк. OCC это же сво-во в АХ, как оно влияет вообще на ?
4. Какой объем InventSum если выполнить следующий скрипт

О, хоть кому-то интересен мой "прорыв года".

1. около 5 вызовов в секунду, но это тяжелых корзин (1-5 позиций), логика резервирования довольно тяжелая 0,5-10 секунд на вызов. Физически описываемая выше операция естественно в самом конце на комите.
2. Предварительное выделение было включено, но оно работает в пределах одного АОСа, а у меня их много и расходуются номера не оптимально при больших предварительных значениях. К тому же уже написан функционал по быстрой настройке любой номерной серии на использование 64-бит или номерных серий MSSQL. Это намного быстрее, чем стандарт с его блокировкой строки.
3. Да, ОСС это свойство таблицы. И кода, который выбирает InventSum forUpdate не так много. Главное даже не это свойство, а то что не нужно вообще делать выборку для блокировки. Во время тестирования наблюдались блокировки на вызовах select updlock, которые пытались залочить строки для обновления. После переделки блокировки ушли.
   Происходило две операции (Блокировка и Обновление), а теперь блокировка происходит на уровне SQL одной операцией update qty += delta.
   По идее, та же проблема должна быть и с кодом по обновлению WHSInventReserve, но проблемы с этой таблицей не наблюдал и отложил переделку до следующего раза.
4. 
   number---Closed---ClosedQty
   884676---0---0
   17619789---0---1
   2699511---1---1

[trud]

Цитата:

Сообщение от kgksoft

Во время тестирования наблюдались блокировки на вызовах select updlock, которые пытались залочить строки для обновления. После переделки блокировки ушли.
Происходило две операции (Блокировка и Обновление), а теперь блокировка происходит на уровне SQL одной операцией update qty += delta.

А о каких операциях идет речь? Разве в стандарте кто-то лочит InventSum, кроме этого финального триггера? или это какой-то кастомный функционал?
Т.е. в ttsNotifyPreCommit происходит блокировка (с pessimistic lock) InventSum в разрезе ItemId-InventDimId, далее по этому же ключу происходит обновление(при этом используются 2 ветки - прямой SQL и одна запись). Т.е. от того что вы просто поменяете все на прямой SQL думаю ничего принципиально не изменится, все равно будут накладываться блокировки в разрезе ItemId-InventDimId(что правильно).

[kgksoft]

Цитата:

Сообщение от trud

А о каких операциях идет речь? Разве в стандарте кто-то лочит InventSum, кроме этого финального триггера? или это какой-то кастомный функционал?
Т.е. в ttsNotifyPreCommit происходит блокировка (с pessimistic lock) InventSum в разрезе ItemId-InventDimId, далее по этому же ключу происходит обновление(при этом используются 2 ветки - прямой SQL и одна запись). Т.е. от того что вы просто поменяете все на прямой SQL думаю ничего принципиально не изменится, все равно будут накладываться блокировки в разрезе ItemId-InventDimId(что правильно).

Все верно. Ничего нестандартного в процессе резервирования не использовали. Но вставка, блокировка + обновление медленнее, чем одна операция по обновлению. Это высоконагруженные веб-сервисы и тут каждая лишняя операция в базу данных дорога. А у меня в базе доходит до 90000 операций в секунду.

В моем случае было много параллельных операций по резервированию и разрезервированию товара. Товар и аналитики могли пересекаться. Именно при большой нагрузке с интернета и розницы происходили небольшие блокировки на InventSum. В стандарте все верно написано, но с моими исправлениями эта подсистема позволяет держать большие нагрузки по параллельной работе с одинаковыми аналитиками. Обновление InventSum перестало быть самым узким местом при резервирование большого потока заказов.

Тестировали протоком реальных заказов JMeter, потом шла операция разрезервирования этих заказов. Скорость была около 300 вызовов в минуту. Повторюсь внутри не только резервирование, а и построение цепочек заказов (закупки + перемещения + заказы на продажу).

[trud]

Цитата:

Сообщение от kgksoft

Все верно. Ничего нестандартного в процессе резервирования не использовали. Но вставка, блокировка + обновление медленнее, чем одна операция по обновлению. Это высоконагруженные веб-сервисы и тут каждая лишняя операция в базу данных дорога. А у меня в базе доходит до 90000 операций в секунду.

Ну вот тут не сходится математика. т.е. у вас 5 заказов в секунду, 90к операций, вы оптимизировали 5 обновлений-вставок 1 записи, и говорите что это как-то заметно должно повлиять?

Цитата:

Сообщение от kgksoft

В моем случае было много параллельных операций по резервированию и разрезервированию товара.

Но ведь параллельные операции InventSum не блокируют, она блокируется в самом конце, т.е. важно сколько параллельных завершений транзакций было и сколько выполнялась проверка остатков в этом завершение транзакции. Вы это замеряли?
Если у вас до 5 позиций, то это проверка должна выполняться мнгновенно и никаких блокировкок быть не должно, ну т.е. вы сами посчитайте, 5 заказов в секунду, наихудший случай, когда в каждом одна и таже аналитика и номенклатура, завершение к примеру идет 100мс (что довольно долго) и то не получается блокировок
НО
В начальных версиях 2012 R3 существовал баг, когда из-за неправильных индексов по InventSumDelta, InventSumDeltaDim эта проверка остатков выбирала неправильный план и выполнялась довольно долго. тогда да, на любой серьезной нагрузке система вставала
Проблема собственно описана https://axology.wordpress.com/2013/1...umdelta-table/ решением было удаление индексов(всех кроме 1) на указанных таблицах

[kgksoft]

Цитата:

Сообщение от trud

Ну вот тут не сходится математика. т.е. у вас 5 заказов в секунду, 90к операций, вы оптимизировали 5 обновлений-вставок 1 записи, и говорите что это как-то заметно должно повлиять?

90k это операций в activity-мониторе. Подозреваю, что это очень большой показатель, который говорит об общей загруженности базы. Т.е. даже если нет блокировок, то такое кол-во запросов довольно тяжело дается серверу. Они грузят процессор (30-40%) и память базы данных. Я вообще убрал запрос, который делал пессимистическую блокировку на основании уникальных аналитик InventSumDelta и вставку-обновление в случае новой одной комбинации. Возможно всему виной таблица InventSumDelta, но у меня там все индексы из R3 + один мой (ItemId, IsAggregated, InventDimId).

Цитата:

Сообщение от trud

Но ведь параллельные операции InventSum не блокируют, она блокируется в самом конце, т.е. важно сколько параллельных завершений транзакций было и сколько выполнялась проверка остатков в этом завершение транзакции. Вы это замеряли?
Если у вас до 5 позиций, то это проверка должна выполняться мнгновенно и никаких блокировкок быть не должно, ну т.е. вы сами посчитайте, 5 заказов в секунду, наихудший случай, когда в каждом одна и таже аналитика и номенклатура, завершение к примеру идет 100мс (что довольно долго) и то не получается блокировок
НО
В начальных версиях 2012 R3 существовал баг, когда из-за неправильных индексов по InventSumDelta, InventSumDeltaDim эта проверка остатков выбирала неправильный план и выполнялась довольно долго. тогда да, на любой серьезной нагрузке система вставала
Проблема собственно описана https://axology.wordpress.com/2013/1...umdelta-table/ решением было удаление индексов(всех кроме 1) на указанных таблицах

При расчете нужно не забывать, что есть еще WHSInventReserve, который обновляется по тому же механизму после InventSum и 100мс могут превратиться в нечто большее.

Удалять индексы не стану, но автообновление статистики наверное отключу. Спасибо за статью.

[sonik]

Вообще это странно. Неужели у вас такое количество одновременных резервирований по одинаковому сочетанию номенклатуры и аналитики?
Мне кажется дело в другом. Срабатывает страничная эскалация блокировок. У нас было такое на старте. Нужно отключить page lock на всех индексах inventsumdelta. Ну и план гайдов добавить, чтобы оптимизатор не сбивался с планами запросов.
По мне включение occ на остатках это очень опасный ход. Ведь в этом суть блокировок - не дать продать в минус, если в этот момент кто-то уже продает то же самое.

[trud]

Цитата:

Сообщение от kgksoft

Возможно всему виной таблица InventSumDelta, но у меня там все индексы из R3 + один мой (ItemId, IsAggregated, InventDimId).

Скорее всего в этом проблема. Работа с данной таблицей всегда должна происходить в рамках сессии т.е. если вам понадобился индекс где нет TTSID, то ваши планы начинают свое выполнение не с этой таблицы(это и приводит к тормозам). Т.е. если у вас 5 строк, то при выполнение join сложность выборки всегда должна равняться 5 строкам. Но если SQL выбирает неправильный план и у вас начинается выборка с InventDim(ключевое слово тут paremeters sniffing), то тут скорее всего и начинаются тормоза, ибо судя по размеру InventSum данных у вас много и сканирование может уйти в тысячи записей
Т.е. решение тут - с InventSumDelta и InventSumDeltaDim удаляете все индексы(включая индекс по RecId), оставляете один кластерный - это уберет проблему выбора индекса, так как он будет 1
Это должно помочь. Ну или можете заморочиться с план гайдами как написал sonik

[Alexius]

А кроме Романа Долгополова (db / RDOL) никто не пытался бороться с блокировками на InventSumDelta, преобразованием ее в TempDB ? В АХ 2012 это проще, чем в АХ 2009.

Цитата:

Сообщение от db

... В моем случае темповыми стали InventSumDelta и LedgerBalancesTransDelta. Результат - ПОЛНОЕ отсутствие блокировок при обновлении запасов в наличии и балансов по ГК. Полное означает не облегчение, а устранение проблемы на корню и возможность многопоточной разноски документов в тысячи реально параллельных не ждущих друг друга потоков. И такие странные методы стоили достигнутого результата

Временные SQL таблички в ax2009

[kgksoft]

Цитата:

Сообщение от sonik

Вообще это странно. Неужели у вас такое количество одновременных резервирований по одинаковому сочетанию номенклатуры и аналитики?
Мне кажется дело в другом. Срабатывает страничная эскалация блокировок. У нас было такое на старте. Нужно отключить page lock на всех индексах inventsumdelta. Ну и план гайдов добавить, чтобы оптимизатор не сбивался с планами запросов.
По мне включение occ на остатках это очень опасный ход. Ведь в этом суть блокировок - не дать продать в минус, если в этот момент кто-то уже продает то же самое.

да, у меня вся розница и интернет магазин подключены к онлайн резервированию и др.операциям. Внутренние операции и от WMS тоже идет поток. Страничные блокировки у меня на ВСЕХ!!! индексах в базе отключены. У MSSQL памяти много, могу позволить себе (есть конечно сложности с невозможностью делать REORGANIZE индекса, но обхожусь ONLINE ребилдом + партиционированием таблиц + порогом фрагментации в 20%). С отрицательными проблем нет. По сути я даже не вмешивался в этот процесс. Стандарт и так излишне оптимистичен при резервировании с одной и той же аналитики. И разруливает только постпроверкой отрицательных когда все везде обновлено и списано.

Цитата:

Сообщение от trud

Скорее всего в этом проблема. Работа с данной таблицей всегда должна происходить в рамках сессии т.е. если вам понадобился индекс где нет TTSID, то ваши планы начинают свое выполнение не с этой таблицы(это и приводит к тормозам). Т.е. если у вас 5 строк, то при выполнение join сложность выборки всегда должна равняться 5 строкам. Но если SQL выбирает неправильный план и у вас начинается выборка с InventDim(ключевое слово тут paremeters sniffing), то тут скорее всего и начинаются тормоза, ибо судя по размеру InventSum данных у вас много и сканирование может уйти в тысячи записей
Т.е. решение тут - с InventSumDelta и InventSumDeltaDim удаляете все индексы(включая индекс по RecId), оставляете один кластерный - это уберет проблему выбора индекса, так как он будет 1
Это должно помочь. Ну или можете заморочиться с план гайдами как написал sonik

Планы запросов мониторятся и неправильные планы давно прибиты план-гайдами. Удалить все индексы, вариант. MSSQL-туповат, но не настолько чтобы все время ошибаться. Попробую пока вариант с отключенной автоматической статистикой на индексах InventSumDelta.

Цитата:

Сообщение от Alexius

А кроме Романа Долгополова (db / RDOL) никто не пытался бороться с блокировками на InventSumDelta, преобразованием ее в TempDB ? В АХ 2012 это проще, чем в АХ 2009.Временные SQL таблички в ax2009

Очень интересно, но и тут есть беда. У меня есть товары, которые продаются по серийным номерами (пускай будут подписки на какую-нибудь услугу). Суть в том, что из-за врожденной оптимистичности при выборе остатка при резервировании система подбирает всегда один и тот же остаток товара с одним и тем же серийным номером (сортировки остатка по фифо, партиям, датам прихода). Т.е. одновременно в InventSumDelta двумя параллельными процессами пытается списывать один и тот же товар с одним серийником. И разруливается вся эта проблема только уже на этапе посткоммита при получении отрицательного остатка. И отправляет один из процессов в тяжелый RETRY. Как я это решаю. В процессе резервирования товара при построении цикла по ФИФО пропускаю строки, которые уже есть в InvetSumDelta в других TTSID с хинтом NOLOCK. Это в разы уменьшает проблему подбора товара с одним и тем же серийником. C TEMPDB такой финт будет сложно провернуть

[Alexius]

Цитата:

Сообщение от kgksoft

Суть в том, что из-за врожденной оптимистичности при выборе остатка при резервировании система подбирает всегда один и тот же остаток товара с одним и тем же серийным номером (сортировки остатка по фифо, партиям, датам прихода). Т.е. одновременно в InventSumDelta двумя параллельными процессами пытается списывать один и тот же товар с одним серийником.

ReadPast по одной штучке не спасает ?

[kgksoft]

Цитата:

Сообщение от Alexius

ReadPast по одной штучке не спасает ?

нет. Строка то не блокируется при резервировании. Там строится курсор аля-фифо+по наличию товара и все время подбирается один и тот же товар со склада.

[Alexius]

На сколько я понял, проблема в автоподборе складской аналитики "Серийный номер". Если в процессе автоподбора накладывать блокировки на таблицу серийных номеров с хинтом ReadPast, то проблема попадания одного и того же серийника в разные документы уйдет.

[kgksoft]

Цитата:

Сообщение от Alexius

На сколько я понял, проблема в автоподборе складской аналитики "Серийный номер". Если в процессе автоподбора накладывать блокировки на таблицу серийных номеров с хинтом ReadPast, то проблема попадания одного и того же серийника в разные документы уйдет.

Да, думаю такой вариант подойдет. Если не удалось заблокировать, то перейти к следующему кандидату. Тогда можно и про временный InventSumDelta подумать. Спасибо