На главную
ПРАВИЛА FAQ Помощь Участники Календарь Избранное DigiMania RSS
msm.ru
! информация о разделе
user posted imageДанный раздел не предназначается для обсуждения, здесь собираются только вопросы-ответы по тематике баз данных. Если есть желание обсудить какие-то вопросы, связанные с тематикой баз данных - перейдите в соответствующие разделы "Базы данных: общие вопросы" или "Базы данных: SQL".
Модераторы: Bas, Chow, JoeUser, MIF
  
    > [на редактирование] Последовательная нумерация, Firebird
      В базах данных, имеющих отношение к делопроизводству, часто возникает потребность в последовательной генерации номеров документов. При этом существует условие, что номера документам присваиваются последовательно, и есть требование использовать все номера. То есть, если существуют документы с №3 и №5, то существует (возможно, существовал и был удален) документ с №4.
      Задача не так проста, как кажется. Точнее, она была бы простой, если бы не было откатов транзакций при неудачно составленном документе.
      Типичная процедура присвоения номера делается так:
      Пользователь создает новый документ, и заполняет его. На этом этапе номер еще не присваивается. После заполнения пользователь нажимает "ОК", и документ отправляется в таблицы БД. Открывается транзакция, получается номер, и вся структура документа вставляется в таблицы, с необходимыми проверками. Транзакция подтверждается.
      Если же документ проверку не прошел, транзакция откатывается. В этом случае полученный уже номер надо бы вернуть. Вот тут и возникают трудности.
      Дело в том, что Firebird, равно как и Interbase, версионники. При этом всегда надо рассчитывать, что вставки документов в базу могут идти одновременно от нескольких пользователей, и как взять номер, допустим, из таблицы, так чтобы другие вставки документов не взяли его же, понять не так просто.
      Недавно я познакомился с оригинальным способом такой нумерации:
      1. Создается генератор, например, GEN_NEW_ID
      2. При вставке документа из генератора получают новое значение, GEN_ID(GEN_NEW_ID, 1);
      3. Если при вставке документа возникло исключение, транзакция откатывается. При этом отдельно откатывается и генератор, GEN_ID(GEN_NEW_ID, -1);
      То есть, генератор выдает, например, номер 3, он присваивается документу, если не прошло - генератор откатывается, чтобы в следующий раз снова выдать этот номер.
      Все. Поскольку откаты документа довольно редки, а пользователи создают документы довольно медленно, минут 15-20, то все вроде бы в порядке.
      Однако все дело в том, что делать так нельзя. Как раз генератор находится вне контекста транзакции, а на вставку документа уходит какое-то время, пусть и маленькое. Это значит, что операция получения номера, в отличие от операци вставки документа, не атомарна. Это означает, что есть проблемы :)
      Допустим, два пользователя почти одновременно закончили оформление документов и нажали ОК. Открылась первая транзакция, из генератора документ получил номер, допустим, 3. Тут же за ней второй документ в своей транзакции получил номер 4, все логично. И тут документ №3 не проходит проверку. А второй, №4 - проходит. По алгоритму, программа, пытавшаяся вставить первый документ, откатывает генератор на 1...
      В результате документа №3 нет, зато есть №4, и следующий тоже будет №4. Который, скорее всего, откатится с ошибкой вида "номер не уникален", опять установив генератор на единицу меньше. И так далее :)

      Между тем, существует довольно много способов сделать такую нумерацию в Firebird (и Interbase), и не заложить подобных "мин замедленного действия". Я знаю парочку.

      Первый способ. Понятно, что нужно что-то вроде генератора, но в контексте транзакции, чтобы можно было откатить значение, и одновременно не дать доступ другим, пока не решится, утверждена транзакция или нет.
      Делается таблица
      ExpandedWrap disabled
        create table DOC_NUM (
          DOC_ID integer not null primary key)

      В нее вставляется одна запись, со значением 0. Это и есть наш генератор. Остается только наложить блокировку, и можно делать
      ExpandedWrap disabled
        update DOC_NUM set DOC_ID = DOC_ID + 1;
      , а затем брать новый номер. Если вставка документа провалилась, надо просто откатить транзакцию.
      Обращение к этой таблице надо делать из бокирующей транзакции, с ожиданием, например, с параметрами
      write
      consistency
      lock_read=DOC_NUM
      lock_write=DOC_NUM
      exclusive
      Все, вторая транзакция просто будет ждать, пока первая не утвердится или откатится. По умолчанию wait_timeout 10 секунд, если мне не изменяет память, и этого должно быть вполне достаточно, чтобы документ прошел все проверки. Подробнее о параметрах http://ibase.ru/devinfo/ibtrans.htm

      Второй способ.
      Первый способ хорош для документов, у которых долгое оформление и быстрая вставка. Если же вставки идут часто, а проверка долгая, то может организоваться очередь, в которой последний документ просто не успеет дождаться номера. Это теоретически, я не уверен, что такое бывает. В принципе, особой проблемы нет: пользователь в крайнем случае проведет документ еще раз.
      Но есть возможность избежать очереди, просто немного модифицировав таблицу:
      ExpandedWrap disabled
        CREATE GENERATOR GEN_DOC_NUM_ID;
         
        CREATE TABLE DOC_NUM (
            DOC_ID  INTEGER NOT NULL
        );
         
        ALTER TABLE DOC_NUM ADD CONSTRAINT PK_DOC_NUM PRIMARY KEY (DOC_ID);
         
        SET TERM ^ ;
         
        CREATE TRIGGER DOC_NUM_AUTOINC FOR DOC_NUM
        BEFORE INSERT
        AS
        BEGIN
          IF (NEW.DOC_ID IS NULL) THEN
            NEW.DOC_ID = GEN_ID(GEN_DOC_NUM_ID, 1);
        END
        ^
         
        SET TERM ; ^

      То есть, поле DOC_ID теперь не только первичный ключ, но и автоинкремент.
      Алгоритм такой:
      1. Непосредственно перед вставкой документа надо вставить запись в эту таблицу:
      ExpandedWrap disabled
        insert into DOC_NUM (DOC_ID)
        values (NULL);

      Теперь в ней есть хотя бы один номер, причем не обязательно тот, что нужен. Транзакцию надо подтвердить, и перейти собственно к вставке документа:
      2. Получение очередного номера делается из процедуры:
      ExpandedWrap disabled
        SET TERM ^ ;
         
        CREATE PROCEDURE GET_DOC_NUM
        returns (NEW_DOC_NUM integer)
        AS
        begin
          for select DOC_ID
            from DOC_NUM
            order by DOC_ID
            into :NEW_DOC_NUM
            as cursor NUM
          do begin
            delete from DOC_NUM
            where current of NUM;
         
            suspend;
            exit;
         
            when GDSCODE lock_conflict DO
            begin
            end
          end
        end^
         
        SET TERM ; ^

      Процедуру надо бы выполнять из обычной транзакции READ COMMITTED:
      read_committed
      rec_version
      nowait
      Все замечательно проходит. Здесь смысл в том, что при изменении записи изменение ее другими транзакциями блокируются.
      Курсор в процедуре идет последовательно от самого меньшего номера к самому большему, и пытается их удалить. Если этот номер удален - значит, другая транзакция успела раньше. Конфликт перехватывается и игнорируется, курсор переходит к следующей записи, и пытается удалить уже ее. Если получилось - все прерывается, и возвращается как раз удаленный номер.
      Поскольку каждая операция вставки документа сначала точно вставляет запись в таблицу, то записей должно хватить всем.
      Если вставка документа откатится, то откатится и операция удаления. И номер снова появится в таблице.
      Для надежности можно записать перехват как
      ExpandedWrap disabled
        when GDSCODE lock_conflict,
             GDSCODE lock_timeout,
             GDSCODE deadlock DO

      Недостатком данного способа является то, что количество записей в DOC_NUM растет с каждым откатом документа. Что, впрочем, косвенно показывает, сколько документов не получилось вставить.
      Хотя второй способ я на практике не испытывал, думаю, он имеет право на существование.
      Beware the wild rabbit.
        Я не понимаю - а почему нельзя сначала выполнить проверки, и при не-прохождении откатить, а при прохождении - присвоить номер? то есть номер присваивается не сразу, а только после того, как пройдены все возможные точки отката транзакции...
        Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
        Есть претензии ко мне как к участнику? да ради бога.
        Не нравятся мои ответы? не читайте их.
        В общем, берегите себя. Нервные клетки не восстанавливаются.
          Теоретически все точки отката пройдены, когда сделан commit :)
          Практически - да, можно вставить запись, а потом сделать апдейт, присвоив номер. Но и тут иногда возможны накладки с уникальностью. Нумерация, к примеру, может начинаться каждый год заново, можно найти еще несколько условий, когда это просто неудобно или ненадежно. Структура обычно развивается, и лучше дать отдельный объект "нумератор", чтобы не пришлось отслеживать изменения.
          Лучше уж обеспечить атомарность, тем более что это несложно.
          Beware the wild rabbit.
            Цитата Romkin @
            и тут иногда возможны накладки с уникальностью. Нумерация, к примеру, может начинаться каждый год заново, можно найти еще несколько условий, когда это просто неудобно или ненадежно. Структура обычно развивается, и лучше дать отдельный объект "нумератор", чтобы не пришлось отслеживать изменения.
            Лучше уж обеспечить атомарность, тем более что это несложно.

            Накладок с уникальностью я, признаться, себе даже представить не могу, если процесс вычисления номера и обновления блокирует таблицу перед вычислением и ожидает отпускания при обнаружении блокирования другой транзакцией обновления номера. Атомарность присутствует - процесс занесения записи представляет собой один атом, а присвоения этой записи номера - другой атом. В случае же каких-то проблем с присвоением (получением номера) запись хоть и занесена, но всё ещё не валидна - очень удобно, кстати, бывает, сродни регистрации документа после длительного процесса его согласования - долго-долго он был просто бумажкой, с которой каждый делал что считает нужным, а потом в мгновение ока превратился в окончательный официальный документ.

            Кто будет во втором атоме считать номер - простой запрос или "нумератор",- при этом совершенно фиолетово. Отдельный объект даже лучше, особенно если есть какие-то выпендроны... скажем, документ записан в базу 31.12 в 23.59, а номер ему затребован уже 01.01 в 00.02 - нумератор обнаруживает, что последний номер датирован предыдущим, а не текущим по часам сервера, годом, и начнёт новую нумерацию, выдав номер 0 (или 1).
            Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
            Есть претензии ко мне как к участнику? да ради бога.
            Не нравятся мои ответы? не читайте их.
            В общем, берегите себя. Нервные клетки не восстанавливаются.
              Цитата Akina @
              Накладок с уникальностью я, признаться, себе даже представить не могу, если процесс вычисления номера и обновления блокирует таблицу перед вычислением и ожидает отпускания при обнаружении блокирования другой транзакцией обновления номера. Атомарность присутствует - процесс занесения записи представляет собой один атом, а присвоения этой записи номера - другой атом. В случае же каких-то проблем с присвоением (получением номера) запись хоть и занесена, но всё ещё не валидна - очень удобно, кстати, бывает, сродни регистрации документа после длительного процесса его согласования - долго-долго он был просто бумажкой, с которой каждый делал что считает нужным, а потом в мгновение ока превратился в окончательный официальный документ.

              В принципе все верно. Однако, где есть один документ без номера - там их станет много. У меня, допустим, наложено требование уникальности на номер, или сочетание (номер, год). Два документа с одинаковым номером или его отсутствием не пройдут. И отказываться неудобно: это отмена контроля и переложение его на оператора. Иногда это просто неприемлемо.
              Также документ "без номера" - а как на него ссылаться, в разговоре, например? Как на нечто, что было создано тогда-то?
              Можно предложить кучу способов, но зачем усложнять? То, что я написал, я написал, видя, что проблемы есть. Если поможет - хорошо :)
              Beware the wild rabbit.
                Цитата Romkin @
                Однако, где есть один документ без номера - там их станет много. У меня, допустим, наложено требование уникальности на номер, или сочетание (номер, год). Два документа с одинаковым номером или его отсутствием не пройдут. И отказываться неудобно: это отмена контроля и переложение его на оператора. Иногда это просто неприемлемо.
                Также документ "без номера" - а как на него ссылаться, в разговоре, например? Как на нечто, что было создано тогда-то?

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

                Что было: во-первых, два диапазона номеров - для чистовиков (то, что ты в конечном итоге получаешь) и для черновиков. Собственно документ, которому не присвоен номер постоянного учёта, и есть черновик. Заморачиваться я тогда не стал, номер был длинным знаковым целым, постоянные номера присваивались последовательными положительными зеначениями, а черновые - рандомными отрицательными. Дублирование номеров было исключено - заморачиваться на проверки я не стал, просто объявил его уникально индексированным и отлавливал ошибки (кстати, за три с лишним года ошибок дубблирования не помню ни одной). Это заодно решало и проблему ссылки на документ при совместной (не одновременной!) работе с ним. И контроль на месте, и оператору никакой нагрузки. А от немеряного количества черновиков система была защищена процессом периодического обслуживания базы, при котором в числе прочих операций выполнялось удаление черновиков, последняя корректировка которых была более чем месяц назад (но и после этого их можно было без проблем вынуть из бекапа).

                Само собой я некоторые тонкости опускаю, некоторые адаптирую под обсуждаемую проблему - но по сути всё было именно так, как написано.
                Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                Есть претензии ко мне как к участнику? да ради бога.
                Не нравятся мои ответы? не читайте их.
                В общем, берегите себя. Нервные клетки не восстанавливаются.
                1 пользователей читают эту тему (1 гостей и 0 скрытых пользователей)
                0 пользователей:


                Рейтинг@Mail.ru
                [ Script Execution time: 0,1000 ]   [ 14 queries used ]   [ Generated: 20.11.19, 02:38 GMT ]