Целостность данных при длительных запросах

[Eldar9x]

Здравствуйте.

Есть запрос к InventSum, который делается через Query. Запрос представляет собой связку InventSum->InventTable->InventDim.
Есть второй запрос к InventTrans, который делается через Query. Запрос представляет собой связку InventTrans->InventTable->InventDim.
Есть третий запрос к InventSettlement, который делается через Query. Запрос представляет собой связку InventSettlement->InventTrans->InventTable->InventDim.

Запросы используются для получения остатков на определенную дату, с заданными группировками. По сути, это переработка классов InventSumDate* с той лишь разницей, что позволяют получать остатки на дату не раздельно для каждой номенклатуры, а в цикле с любой группировкой. То есть вначале получаем текущие остатки из InventSum, затем вычитаем проводки InventTrans. Это позволяет выводить остатки на дату по большому списку номенклатур с максимальный скоростью, так как кол-во запросов в базу сводится к минимуму.

Проблема возникает в следующем. В то время пока перебираются в цикле записи первого Query (по InventSum), кто-то создает проводку в InventTrans. Затем происходит выборка из InventTrans c помощью второго Query (уже с учетом этой созданной проводки). Таким образом, далее мы уже будем вычитать эти самые проводки из "старого" InventSum и получим неверные данные. Каким образом получить консистентные данные по InventSum, InventTrans и InventSettlement?

ps: обрамление этих трех запросов с помощью ttsbegin;/ttscommt; не помогает.

ps2: удалось снизить интервал времени между этими тремя запросами, переместив итерацию по queryRun-м в самый конец, уже после выполнения запроса в БД.

ps3: возможно, поможет обрамление (дополнительно к аксаптовым ttsbegin;ttscommit) userConnection.ttsbegin()/userConnection.ttscommit(). Но, вроде как, для курсоров нужно делать setConnection(), а здесь это не применимо, так как работа идет с Query.

[raz]

Ничего не поможет.
Мы эту проблему решали так:
Делали два запроса по InventSum в начале и в конце расчетов, по позициям, которые изменились пересчитывали еще раз, и так до тех пор, пока все не совпадало. Иногда это может приводить к довольно долгому расчету, если паралельно разносятся несколько больших журналов.

[mazzy]

Цитата:

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

ps: обрамление этих трех запросов с помощью ttsbegin;/ttscommt; не помогает.

версия то какая? и что у вас с оптимистическими блокировками?

[Eldar9x]

Цитата:

Ничего не поможет.
Мы эту проблему решали так:
Делали два запроса по InventSum в начале и в конце расчетов, по позициям, которые изменились пересчитывали еще раз, и так до тех пор, пока все не совпадало. Иногда это может приводить к довольно долгому расчету, если параллельно разносятся несколько больших журналов.

жесть какая-то... А я ведь еще и сомневался, что расхождения возникают именно по этой причине. Хотя время вывода отчета и время возникновения проводок совпадало. Тоже были мысли, в разных потоках что ли начинать запросы в InventSum и InventTrans, чтобы минимизировать время между ними. И ведь тогда получается, что классы InventSumDate* также могут давать неверный результат, хотя вероятность в этом случае уже мизерная?

Цитата:

версия то какая? и что у вас с оптимистическими блокировками?

это 3-ка. В InventSumTTS Admin галка Active отключена, а CheckMode = Оптимистичный. Какие настройки на SQL сервере - не выяснял.

[Vadik]

Цитата:

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

версия то какая? и что у вас с оптимистическими блокировками?

А при чем тут это если отчет несколькими запросами рассчитывается и сериализуемых блокировок на INVENTTRANS / INVENTSUM в процессе рассчета (я надеюсь) не накладывается ?

Eldar9x, вариантов два:
- примите как факт что некоторые (как правило незначительные) расхождения при рассчете остатков по складу "задним числом" (иначе обошлись бы INVENTSUM и INVENTTRANS не шерстили бы) на рабочей БД теоретически могут быть by design
- стройте рядом с рабочей (OLTP) базой данных AX свой DWH (с блэкджеком и шлюхами) и выгружайте данные в него (что в принципе тоже не так просто как может показаться, особенно при работе 24x7)

[Eldar9x]

Цитата:

сериализуемых блокировок на INVENTTRANS / INVENTSUM в процессе рассчета (я надеюсь) не накладывается ?

Нет. Это просто выборка данных с помощью Query, безо всяких там "forupdate".

Цитата:

- примите как факт что некоторые (как правило незначительные) расхождения при рассчете остатков по складу "задним числом" (иначе обошлись бы INVENTSUM и INVENTTRANS не шерстили бы) на рабочей БД могут быть by design
- стройте рядом с рабочей (OLTP) базой данных AX свой DWH (с блэкджеком и шлюхами) и выгружайте данные в него (что в принципе тоже не так просто как может показаться, особенно при работе 24x7)

Остатки (кол-во и суммы) должны сходиться до нескольких знаков после запятой. И какие-то внешние ср-ва использовать нельзя, нужно обойтись стандартными. Плюс еще требование скорости.

[Eldar9x]

Имеет ли смысл в данной ситуации делать следующее:

UserConnection userConnection = new UserConnection();
ttsbegin;
userConnection.ttsbegin();
selectRemains(); // выбираем остатки с помощью Query из InventSum и InventTrans
userConnection.ttcommit();
ttscommit;

?

[gl00mie]

нет, смысла не имеет userConnection, если к нему не привязывать запросы или курсор, ни на что не влияет. АОС и так держит пул этих соединений с БД, и одно из них вы используете в выборке. "Вложенное" соединение - это просто ещё одно соединение из пула, поддерживаемого АОСом, как если бы ещё одна клиентская сессия запустилась бы и что-то читала из БД.

[Eldar9x]

Ясно, спасибо. А каким образом привязать userConnection к запросу (к Query) ?

[gl00mie]

Честно говоря, не знаю, ни разу не видел примера использования отдельного соединения с Query/QueryRun - только с табличными курсорами и обычными select'ами либо с прямыми SQL-запросами.

[raz]

Цитата:

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

Ясно, спасибо. А каким образом привязать userConnection к запросу (к Query) ?

Можно попробовать сделать как обычно, userConnection привязать к курсору, а курсор привязать к QueryRun.

[twilight]

На одном из проектов для ускорения подсчета остатков на дату на паллетах делали табличку, где хранились изменения остатков на дату: код паллеты, остаток, дата начала, дата окончания. Места она занимает немного, вставляются записи быстро и остаток на дату можно получить элементарным запросом с фильтром по дате. Можно то же самое сделать и для общего случая.

[ice321i]

Если версия 4 и выше, то можно использовать в первом запросе таблицу inventSumDelta.
Пример использования смотри в классе InventUpd_Reservation метод updateReserveMore

[fed]

Я этого никогда не пробовал, но можно попытаться. Начиная с версии SQL 2005 есть такой специальный уровень изоляции транзакций: SNAPSHOT ISOLATION. Если ты в скрипте на T-SQL пишешь SET TRANSACTION ISOLATION SNAPSHOT, а потом начинаешь транзакцию через вызов BEGIN TRANSACTION, то в системе включается версионная машина и твои запросы не видят ВСЕ изменения данных, которые были сделаны после того как ты выполнил вызов BEGIN TRANSACTION. То есть - консистентное состояние сохраняется не в рамках одного запроса, а для всех запросов внутри транзакции.
Есть, правда, некоторые "НО":

1. Поддержку этого режима надо включать на уровне базы данных.ALTER DATABASE SET ALLOW_SNAPSHT_ISOLATION ON. Это некоторый оверхид создает.
2. Главная проблема: Насколько я понимаю, до тех пор пока транзакция в коде на X++ не начата, все пользовательские сесии шарят одно соединение и если ты просто так туда залезешь через Connection/Statement и засунешь режим изоляции SET TRANSACTION ISOLATION SNAPSHOT, то этот режим включиться не только для твоей сесии, а вообще для всех пользователей в системе. Если же ты вызовешь ttsbegin, то система тебе выделит отдельную сесию, в которой ты можешь безбоязненно менять уровень изоляции. Но вот поскольку ты уже находишься в режиме транзакции, то смена режима изоляции на SNAPSHOT уже не возможна...

В общем - я бы попробовал все-таки тупо засовывать уровень изоляции через connection/statement, а потом вызвал бы connection.ttsbegin(), чтобы начать транзакцию. Есть большая надежда (хотя я и не уверен), что менежеру соединений в AOS хватит ума выделить отдельное соединение в тот момент когда ты connection вызываешь, а не в тот момент когда ты транзакцию начинаешь. Кроме того, помнится мне говорили, что то самое разделяемое соединение используется ТОЛЬКО логикой на формах, а если ты пишешь запрос в классе, то система использует выделенное соединение, которое прикрепляется к пользовательской сесии до тех пор, пока класс не отработает...

[anikulichev]

Цитата:

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

Поддержку этого режима надо включать на уровне базы данных.ALTER DATABASE SET ALLOW_SNAPSHT_ISOLATION ON. Это некоторый оверхид создает.

Кстати говоря в документе "Planning database configuration for Microsoft Dynamics AX" есть прямое указание на включение этого режима.

[fed]

Цитата:

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

Кстати говоря в документе "Planning database configuration for Microsoft Dynamics AX" есть прямое указание на включение этого режима.

Не совсем. Там требуют включать Read Committed Snapshot Isolation. В этом режиме, в режиме Read Committed включается версионная машина (ВНУТРИ ОДНОГО ЗАПРОСА) не ставяться блокировки чтения. SNAPSHOT ISOLATION - это отдельный режим изоляции транзакций и для его поддержки надо другую опцию у базы данных включать.

[raz]

Цитата:

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

Можно попробовать сделать как обычно, userConnection привязать к курсору, а курсор привязать к QueryRun.

Рабочий пример. НО! Решить проблему не поможет, решение есть выше.

static void edd_testUserConnection(Args _args)
{
    UserConnection          userConnection;
    InventTable             inventTable;
    InventSum               inventSum;
    
    Query                   query;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qdr;
    QueryRun                queryRun;
    
    boolean                 useAlterConnection = true;
    int                     i;
    ;
    query = new Query();
    qbds  = query.addDataSource(tableNum(InventSum));
    qdr   = qbds.addRange(fieldNum(InventSum, ItemId));
    qbds.addSelectionField(fieldNum(InventSum, PhysicalInvent), SelectionField::Sum);
    qbds.addSortField(fieldNum(InventSum, ItemId));
    qbds.orderMode(OrderMode::GroupBy);

    if (useAlterConnection)
    {
        userConnection = new UserConnection();
        inventSum.setConnection(userConnection);
    }
    
    while select inventTable
    {
        i++;
        
        qdr.value(queryValue(inventTable.ItemId));
        
        queryRun = new QueryRun(query);
        queryRun.setCursor(inventSum);
        queryRun.next();
        
        inventSum = queryRun.getNo(1);
    
        info(strfmt("%1: %2", inventTable.ItemId, inventSum.PhysicalInvent));
        
        sleep(1000);
        
        if (i >= 20)
        {
            break;
        }
    }
}

ЗЫ. Результат видно в активных пользователях.

[Владимир Максимов]

Как вариант

1. В таблице InventTrans установить свойства

CreatedDate = Yes
CreatedTime = Yes

2. Непосредственно перед выборкой в InventSum сохранить в переменные systemDateGet() и TimeNow()

3. В запрос по InventTrans непосредственно перед выборкой добавить примерно такое условие (пишу в псевдокоде для Query надо будет адаптировать)

where 
(...)
AND (InventTrans.createdDate < systemDateGet() 
        OR (InventTrans.createdDate = systemDateGet() AND InventTrans.createdTime <= timeNow()))

Т.е. отсечь все складские проводки, которые могут быть созданы после начала выборки в InventSum по дате и времени создания.

Вопрос только в том, какое именно время записывается в CreatedTime. В смысле, это время SQL-сервера, AOS или клиента? Поскольку, если это все 3 физически разных компьютера, то их время может различаться. Если не ошибаюсь, то это должно быть время AOS. Т.е. timeNow() надо вычислять на стороне сервера

[S.Kuskov]

Цитата:

Сообщение от Владимир Максимов

Как вариант

1. В таблице InventTrans установить свойства

CreatedDate = Yes
CreatedTime = Yes

тогда уж ещё и ModifiedDate и ModifiedTime. Во время формирования первого запроса могут быть разнесены ранее созданные поводки. И что делать с этими изменёнными проводками? заново пересчитывать данные по таким номенклатурам?

[Владимир Максимов]

Цитата:

Сообщение от S.Kuskov

тогда уж ещё и ModifiedDate и ModifiedTime. Во время формирования первого запроса могут быть разнесены ранее созданные поводки. И что делать с этими изменёнными проводками? заново пересчитывать данные по таким номенклатурам?

Да. Наверное лучше использовать Modified вместо Create. Разумеется, если в процессе выборки не выполняется закрытие склада