Наши проекты:
Журнал · Discuz!ML · Wiki · DRKB · Помощь проекту |
||
ПРАВИЛА | FAQ | Помощь | Поиск | Участники | Календарь | Избранное | RSS |
[3.140.186.241] |
|
Данный раздел не предназначается для обсуждения, здесь собираются только вопросы-ответы по тематике баз данных. Если есть желание обсудить какие-то вопросы, связанные с тематикой баз данных - перейдите в соответствующие разделы "Базы данных: общие вопросы" или "Базы данных: SQL". |
Сообщ.
#1
,
|
|
|
В базах данных, имеющих отношение к делопроизводству, часто возникает потребность в последовательной генерации номеров документов. При этом существует условие, что номера документам присваиваются последовательно, и есть требование использовать все номера. То есть, если существуют документы с №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), и не заложить подобных "мин замедленного действия". Я знаю парочку. Первый способ. Понятно, что нужно что-то вроде генератора, но в контексте транзакции, чтобы можно было откатить значение, и одновременно не дать доступ другим, пока не решится, утверждена транзакция или нет. Делается таблица 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 растет с каждым откатом документа. Что, впрочем, косвенно показывает, сколько документов не получилось вставить. Хотя второй способ я на практике не испытывал, думаю, он имеет право на существование. |
Сообщ.
#2
,
|
|
|
Я не понимаю - а почему нельзя сначала выполнить проверки, и при не-прохождении откатить, а при прохождении - присвоить номер? то есть номер присваивается не сразу, а только после того, как пройдены все возможные точки отката транзакции...
|
Сообщ.
#3
,
|
|
|
Теоретически все точки отката пройдены, когда сделан commit
Практически - да, можно вставить запись, а потом сделать апдейт, присвоив номер. Но и тут иногда возможны накладки с уникальностью. Нумерация, к примеру, может начинаться каждый год заново, можно найти еще несколько условий, когда это просто неудобно или ненадежно. Структура обычно развивается, и лучше дать отдельный объект "нумератор", чтобы не пришлось отслеживать изменения. Лучше уж обеспечить атомарность, тем более что это несложно. |
Сообщ.
#4
,
|
|
|
Цитата Romkin @ и тут иногда возможны накладки с уникальностью. Нумерация, к примеру, может начинаться каждый год заново, можно найти еще несколько условий, когда это просто неудобно или ненадежно. Структура обычно развивается, и лучше дать отдельный объект "нумератор", чтобы не пришлось отслеживать изменения. Лучше уж обеспечить атомарность, тем более что это несложно. Накладок с уникальностью я, признаться, себе даже представить не могу, если процесс вычисления номера и обновления блокирует таблицу перед вычислением и ожидает отпускания при обнаружении блокирования другой транзакцией обновления номера. Атомарность присутствует - процесс занесения записи представляет собой один атом, а присвоения этой записи номера - другой атом. В случае же каких-то проблем с присвоением (получением номера) запись хоть и занесена, но всё ещё не валидна - очень удобно, кстати, бывает, сродни регистрации документа после длительного процесса его согласования - долго-долго он был просто бумажкой, с которой каждый делал что считает нужным, а потом в мгновение ока превратился в окончательный официальный документ. Кто будет во втором атоме считать номер - простой запрос или "нумератор",- при этом совершенно фиолетово. Отдельный объект даже лучше, особенно если есть какие-то выпендроны... скажем, документ записан в базу 31.12 в 23.59, а номер ему затребован уже 01.01 в 00.02 - нумератор обнаруживает, что последний номер датирован предыдущим, а не текущим по часам сервера, годом, и начнёт новую нумерацию, выдав номер 0 (или 1). |
Сообщ.
#5
,
|
|
|
Цитата Akina @ Накладок с уникальностью я, признаться, себе даже представить не могу, если процесс вычисления номера и обновления блокирует таблицу перед вычислением и ожидает отпускания при обнаружении блокирования другой транзакцией обновления номера. Атомарность присутствует - процесс занесения записи представляет собой один атом, а присвоения этой записи номера - другой атом. В случае же каких-то проблем с присвоением (получением номера) запись хоть и занесена, но всё ещё не валидна - очень удобно, кстати, бывает, сродни регистрации документа после длительного процесса его согласования - долго-долго он был просто бумажкой, с которой каждый делал что считает нужным, а потом в мгновение ока превратился в окончательный официальный документ. В принципе все верно. Однако, где есть один документ без номера - там их станет много. У меня, допустим, наложено требование уникальности на номер, или сочетание (номер, год). Два документа с одинаковым номером или его отсутствием не пройдут. И отказываться неудобно: это отмена контроля и переложение его на оператора. Иногда это просто неприемлемо. Также документ "без номера" - а как на него ссылаться, в разговоре, например? Как на нечто, что было создано тогда-то? Можно предложить кучу способов, но зачем усложнять? То, что я написал, я написал, видя, что проблемы есть. Если поможет - хорошо |
Сообщ.
#6
,
|
|
|
Цитата Romkin @ Однако, где есть один документ без номера - там их станет много. У меня, допустим, наложено требование уникальности на номер, или сочетание (номер, год). Два документа с одинаковым номером или его отсутствием не пройдут. И отказываться неудобно: это отмена контроля и переложение его на оператора. Иногда это просто неприемлемо. Также документ "без номера" - а как на него ссылаться, в разговоре, например? Как на нечто, что было создано тогда-то? Я некогда - правда на другой СУБД, но не суть - реализовывал подобную схему. Что было: во-первых, два диапазона номеров - для чистовиков (то, что ты в конечном итоге получаешь) и для черновиков. Собственно документ, которому не присвоен номер постоянного учёта, и есть черновик. Заморачиваться я тогда не стал, номер был длинным знаковым целым, постоянные номера присваивались последовательными положительными зеначениями, а черновые - рандомными отрицательными. Дублирование номеров было исключено - заморачиваться на проверки я не стал, просто объявил его уникально индексированным и отлавливал ошибки (кстати, за три с лишним года ошибок дубблирования не помню ни одной). Это заодно решало и проблему ссылки на документ при совместной (не одновременной!) работе с ним. И контроль на месте, и оператору никакой нагрузки. А от немеряного количества черновиков система была защищена процессом периодического обслуживания базы, при котором в числе прочих операций выполнялось удаление черновиков, последняя корректировка которых была более чем месяц назад (но и после этого их можно было без проблем вынуть из бекапа). Само собой я некоторые тонкости опускаю, некоторые адаптирую под обсуждаемую проблему - но по сути всё было именно так, как написано. |