Форум на Исходниках.RU https://forum.sources.ru Форум на Исходниках.RU Форум на Исходниках.RU https://forum.sources.ru/index.php?showtopic=272414&view=findpost&p=2266078 Thu, 14 May 2009 17:50:24 +0000 [на редактирование] Последовательная нумерация https://forum.sources.ru/index.php?showtopic=272414&view=findpost&p=2266078 Цитата Romkin @
Однако, где есть один документ без номера - там их станет много. У меня, допустим, наложено требование уникальности на номер, или сочетание (номер, год). Два документа с одинаковым номером или его отсутствием не пройдут. И отказываться неудобно: это отмена контроля и переложение его на оператора. Иногда это просто неприемлемо.
Также документ "без номера" - а как на него ссылаться, в разговоре, например? Как на нечто, что было создано тогда-то?

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

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

Само собой я некоторые тонкости опускаю, некоторые адаптирую под обсуждаемую проблему - но по сути всё было именно так, как написано.]]> Akina Базы данных FAQ https://forum.sources.ru/index.php?showtopic=272414&view=findpost&p=2266051 Thu, 14 May 2009 17:17:49 +0000 [на редактирование] Последовательная нумерация https://forum.sources.ru/index.php?showtopic=272414&view=findpost&p=2266051 Цитата Akina @
Накладок с уникальностью я, признаться, себе даже представить не могу, если процесс вычисления номера и обновления блокирует таблицу перед вычислением и ожидает отпускания при обнаружении блокирования другой транзакцией обновления номера. Атомарность присутствует - процесс занесения записи представляет собой один атом, а присвоения этой записи номера - другой атом. В случае же каких-то проблем с присвоением (получением номера) запись хоть и занесена, но всё ещё не валидна - очень удобно, кстати, бывает, сродни регистрации документа после длительного процесса его согласования - долго-долго он был просто бумажкой, с которой каждый делал что считает нужным, а потом в мгновение ока превратился в окончательный официальный документ.

В принципе все верно. Однако, где есть один документ без номера - там их станет много. У меня, допустим, наложено требование уникальности на номер, или сочетание (номер, год). Два документа с одинаковым номером или его отсутствием не пройдут. И отказываться неудобно: это отмена контроля и переложение его на оператора. Иногда это просто неприемлемо.
Также документ "без номера" - а как на него ссылаться, в разговоре, например? Как на нечто, что было создано тогда-то?
Можно предложить кучу способов, но зачем усложнять? То, что я написал, я написал, видя, что проблемы есть. Если поможет - хорошо :)]]> Romkin Базы данных FAQ https://forum.sources.ru/index.php?showtopic=272414&view=findpost&p=2266002 Thu, 14 May 2009 15:29:06 +0000 [на редактирование] Последовательная нумерация https://forum.sources.ru/index.php?showtopic=272414&view=findpost&p=2266002 Цитата Romkin @
и тут иногда возможны накладки с уникальностью. Нумерация, к примеру, может начинаться каждый год заново, можно найти еще несколько условий, когда это просто неудобно или ненадежно. Структура обычно развивается, и лучше дать отдельный объект "нумератор", чтобы не пришлось отслеживать изменения.
Лучше уж обеспечить атомарность, тем более что это несложно.

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

Кто будет во втором атоме считать номер - простой запрос или "нумератор",- при этом совершенно фиолетово. Отдельный объект даже лучше, особенно если есть какие-то выпендроны... скажем, документ записан в базу 31.12 в 23.59, а номер ему затребован уже 01.01 в 00.02 - нумератор обнаруживает, что последний номер датирован предыдущим, а не текущим по часам сервера, годом, и начнёт новую нумерацию, выдав номер 0 (или 1).]]> Akina Базы данных FAQ https://forum.sources.ru/index.php?showtopic=272414&view=findpost&p=2265882 Thu, 14 May 2009 13:04:39 +0000 [на редактирование] Последовательная нумерация https://forum.sources.ru/index.php?showtopic=272414&view=findpost&p=2265882 Практически - да, можно вставить запись, а потом сделать апдейт, присвоив номер. Но и тут иногда возможны накладки с уникальностью. Нумерация, к примеру, может начинаться каждый год заново, можно найти еще несколько условий, когда это просто неудобно или ненадежно. Структура обычно развивается, и лучше дать отдельный объект "нумератор", чтобы не пришлось отслеживать изменения.
Лучше уж обеспечить атомарность, тем более что это несложно.]]> Romkin Базы данных FAQ https://forum.sources.ru/index.php?showtopic=272414&view=findpost&p=2265871 Thu, 14 May 2009 12:51:34 +0000 [на редактирование] Последовательная нумерация https://forum.sources.ru/index.php?showtopic=272414&view=findpost&p=2265871 Akina Базы данных FAQ https://forum.sources.ru/index.php?showtopic=272414&view=findpost&p=2265828 Thu, 14 May 2009 12:23:22 +0000 [на редактирование] Последовательная нумерация https://forum.sources.ru/index.php?showtopic=272414&view=findpost&p=2265828 Задача не так проста, как кажется. Точнее, она была бы простой, если бы не было откатов транзакций при неудачно составленном документе.
Типичная процедура присвоения номера делается так:
Пользователь создает новый документ, и заполняет его. На этом этапе номер еще не присваивается. После заполнения пользователь нажимает "ОК", и документ отправляется в таблицы БД. Открывается транзакция, получается номер, и вся структура документа вставляется в таблицы, с необходимыми проверками. Транзакция подтверждается.
Если же документ проверку не прошел, транзакция откатывается. В этом случае полученный уже номер надо бы вернуть. Вот тут и возникают трудности.
Дело в том, что 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), и не заложить подобных "мин замедленного действия". Я знаю парочку.

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

В нее вставляется одна запись, со значением 0. Это и есть наш генератор. Остается только наложить блокировку, и можно делать
    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

Второй способ.
Первый способ хорош для документов, у которых долгое оформление и быстрая вставка. Если же вставки идут часто, а проверка долгая, то может организоваться очередь, в которой последний документ просто не успеет дождаться номера. Это теоретически, я не уверен, что такое бывает. В принципе, особой проблемы нет: пользователь в крайнем случае проведет документ еще раз.
Но есть возможность избежать очереди, просто немного модифицировав таблицу:
    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. Непосредственно перед вставкой документа надо вставить запись в эту таблицу:
    insert into DOC_NUM (DOC_ID)
    values (NULL);

Теперь в ней есть хотя бы один номер, причем не обязательно тот, что нужен. Транзакцию надо подтвердить, и перейти собственно к вставке документа:
2. Получение очередного номера делается из процедуры:
    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
Все замечательно проходит. Здесь смысл в том, что при изменении записи изменение ее другими транзакциями блокируются.
Курсор в процедуре идет последовательно от самого меньшего номера к самому большему, и пытается их удалить. Если этот номер удален - значит, другая транзакция успела раньше. Конфликт перехватывается и игнорируется, курсор переходит к следующей записи, и пытается удалить уже ее. Если получилось - все прерывается, и возвращается как раз удаленный номер.
Поскольку каждая операция вставки документа сначала точно вставляет запись в таблицу, то записей должно хватить всем.
Если вставка документа откатится, то откатится и операция удаления. И номер снова появится в таблице.
Для надежности можно записать перехват как
    when GDSCODE lock_conflict,
         GDSCODE lock_timeout,
         GDSCODE deadlock DO

Недостатком данного способа является то, что количество записей в DOC_NUM растет с каждым откатом документа. Что, впрочем, косвенно показывает, сколько документов не получилось вставить.
Хотя второй способ я на практике не испытывал, думаю, он имеет право на существование.]]> Romkin Базы данных FAQ