Eugene Zhilkin дата публикации 31-05-2005 08:33 Computer Programming Solutions
TECHNICAL INFORMATION SHEET
Jason Wharton
(Перевод и комментарии - Eugene Zhilkin, 30.05.2005
Исходный текст и код взяты из архива http://www.ibobjects.com/docs/ti_AuditableSeries.ZIP
31.05.2005 - Корректировки в терминологии, за что отдельное спасибо Александру Веникову)
Иногда перед программистами стоит задача обеспечения непрерывности последовательной нумерации
определенных документов или однозначной идентификации номенклатуры (артикулов, товаров и т.п.), создаваемых в их компаниях.
В данной статье освещаются некоторые вопросы систем последовательной нумерации и приводится
пример подобной системы, использующей определенные возможности СУБД InterBase и библиотеки IB Objects.
Рекомендуется изучать данную статью одновременно с изучением демонстрационного проекта Test_Series,
который находится в каталоге ../TechTopics (см. приложение к статье). Для компиляции проекта необходим Delphi 4 или выше. Исполнимый файл, с помощью которого вы можете присоединиться с тестовой базой данных, включена с целью демонстрации
().
Генераторы InterBase идеальны для выработки значений полей первичных ключей, поскольку
однажды сгенерированное значение не может быть сгенерировано вновь.
В отличии от последовательностей, которые вы ведете в таблицах самостоятельно
(),
генераторы внетранзакционны. Две одновременные транзакции не могут получить один и тот же номер
(). Первичные ключи должны быть атомарны (),
то есть они не должны ничего значить как данные (), а только
предназначены для обеспечения уникальности. Целостность уникальности по значению первичного ключа
обеспечивается до тех пор, пока ваши метаданные защищены от доступа неавторизованным пользователем -
SYSDBA () или владельцем (), который решил сбросить
генератор и сломать целостность вашего первичного ключа!
Разработчики иногда спрашивают о возможности использования генераторов для
создания серии последовательных
номеров, например номеров документов ().
В некоторых странах
законодательство до сих пор требует, чтобы каждый генерируемый номер документа был предопределен
непрерывной «чистой» последовательностью ().
Использование генераторов для этих целей имеет существенный недостаток, поскольку транзакция может получить номер,
а значит использовать его, и если случится откат транзакции, этот номер станет неиспользуемым ().
Сброс генераторов НИКОГДА не является выходом из ситуации, поскольку самый последний
сгенерированный номер будет, скорее всего, большим, нежели «потерянный», и сброс генератора ни в коем случае
не гарантирует от генерации номеров - дубликатов впоследствии.
Поддерживать чистую, непрерывную последовательность немного труднее,
поскольку такая задача требует ведения таблицы учета не только номеров, которые вы создали в
последовательности, но и тех, которые в силу ряда причин вы хотите выбросить и использовать заново.
Однако, номер, который представляется одному пользователю «свободным» может не быть таковым,
поскольку другой пользователь уже выбрал и использует его внутри неподтвержденной
() транзакции.
Одним из методов решения такой проблемы является откладывание присвоения
последовательных номеров до того момента существования документа, когда отмена номера будет
уже невозможна (EZ: подразумевается, видимо, невозможность отмены или удаления документа в силу
бизнес-логики программы и самой предметной области).
Например, если нумерация документов откладывается до момента распечатки, к моменту распечатки
вы уже пройдете возможность потери распределенного номера при откате вставки строки
заголовка (). Документы печатаются пакетами,
включающими подтвержденные () заголовки, и номера документов в
строгой последовательности присваиваются каждому документу, как только он распечатан
().
Системы, требующие чистую нумерацию документов, обычно также требуют корректного учета
удаленных или отмененных документов. Единственной целью чистой последовательности является выявление
неучтенных пропусков, которые аудитор может уверенно распознать как нарушение в учетной системе.
Поэтому ведение журнала присвоения всех номеров () является
обязательным требованием для чистых последовательностей. В ином случае нет никакого смысла в их ведении.
Зачастую подход с отложенной нумерацией непрактичен, поскольку требуется
присвоение номеров один за другим в момент создания инвойса (). Такое требование типично для задач электронной коммерции, т.е.
при продаже товаров почтой с оплатой по кредитной карточке или наложенным платежом, и при режиме
налогообложения налогом на добавленную стоимость в момент продажи и/или оказания услуг.
()
Вы можете использовать генераторы для генерации чистой последовательности
обращаясь осторожно с потерянными номерами. Но только НИКОГДА не используйте проверяемые
номера документов в качестве первичного ключа! ()
Ниже приводится набор требований к системе нумерации инвойсов, которая
использует генератор с записью номеров инвойсов в таблицу, позволяющей безопасно использовать
утраченные номера повторно, и в которую записываются все инвойсы, которые были отменены (удалены)
после подтверждения. ().
В рассматриваемом примере подразумевается, что пользователи обычно не
могут удалять инвойсы после их подтверждения. Однако инвойс может быть «вычищен»
() авторизованным
пользователем, если оператор случайно ввел один и тот же инвойс дважды или создал инвойс не
на того клиента. Вычищенные инвойсы более не существуют () и поэтому
номера, присвоенные им, могут быть использованы заново.
Такой процесс также требует повторного использования любых номеров
инвойсов, которые были сгенерированы в транзакции, которая впоследствии была отменена.
Во всех иных случаях, при отмене подтвержденного инвойса система создаст
соответствующие журналы. Номер инвойса в таком случае не разрешается использовать заново для целей аудита.
Для минимизации количества пропусков (), которые могут случиться,
интерфейсы ввода и обработки данных попытайтесь запрограммировать таким образом, чтобы новые документы
СНАЧАЛА прошли все иные проверки, и только потом им был присвоен последовательный номер.
Например, в приложениях электронной коммерции проверьте кредитную карточку перед вызовом post.
Если же вы предпочитаете создать инвойс и зарезервировать номер вне зависимости от проверки кредитной
карточки, будьте готовы () к последующей отмене ().
Вы можете изучить данный пример в демонстрационном проекте Series. В демонстрационном проекте
содержится скрипт (test_series.sql), с помощью которого вы можете самостоятельно создать
базу данных для тестирования.
База данных содержит две таблицы:
CREATE TABLE INV_HEADER(
INV_ID INTEGER NOT NULL,
CUST_ID INTEGER NOT NULL,
INV_DATE DATE NOT NULL,
INV_NUMBER INTEGER,
INV_OPERATOR VARCHAR(128),
STATUS VARCHAR(20),
CONSTRAINT PK_INV_HEADER PRIMARY KEY(INV_ID));
CREATE TABLE INV_LOG(
LOG_ID INTEGER NOT NULL,
INV_NUMBER INTEGER,
INV_ID INTEGER,
STATUS VARCHAR(20),
REASON VARCHAR(50),
OPERATOR VARCHAR(128),
GEN_DATE DATE,
LOGDATE DATE,
CONSTRAINT PK_INV_LOG PRIMARY KEY(LOG_ID));
CREATE GENERATOR GEN_INV_HEADER_ID;
CREATE GENERATOR GEN_INV_LOG_ID;
CREATE GENERATOR GEN_INV_NUMBER;
| |
Добавляем уникальные индексы, которые помогут осуществлять контрольные выборки, а также гарантируют уникальность номеров инвойсов:
CREATE UNIQUE INDEX UN_INV_HDR_INV_NUMBER
ON INV_HEADER(INV_NUMBER);
CREATE UNIQUE INDEX UN_INV_LOG_INV_NUMBER
ON INV_LOG(INV_NUMBER);
| |
Триггерам Before Insert в таблицах Invoice Header и Invoice Number Log, которые работают с номером договора, даем большие номера позиций, а прочим триггерам - меньшие, например:
CREATE TRIGGER BI_INSERT_INV_HDR
FOR INV_HEADER
ACTIVE BEFORE INSERT POSITION 8 AS
BEGIN
IF (NEW.INV_ID IS NULL) THEN
NEW.INV_ID = GEN_ID(GEN_INV_HEADER_ID, 1);
IF (NEW.INV_DATE IS NULL) THEN
NEW.INV_DATE='NOW';
NEW.INV_OPERATOR = USER;
END
CREATE TRIGGER BI_INSERT_INV_LOG
FOR INV_LOG
ACTIVE BEFORE INSERT POSITION 9 AS
BEGIN
IF (NEW.LOG_ID IS NULL) THEN
NEW.LOG_ID = GEN_ID(GEN_INV_LOG_ID, 1);
NEW.OPERATOR = USER;
END
| |
Последний Before Insert триггер () используется для присвоения номера новому
заголовку инвойса (invoice header). Во-первых, он проверяет наличие доступных номеров для повторного
использования, и если они есть - блокирует наименьший. Пока одна транзакция удерживает блокировку, ни
одна другая транзакция не сможет выполнить SELECT MIN(), для этого убедитесь, что у транзакции установлен
флаг LockWait в true для избежания взаимной блокировки (deadlock). ()
Если же ни один номер для повторного использования не свободен, то триггер получает новый номер с помощью генератора.
CREATE TRIGGER BI_INSERT_INV_NUM
FOR INV_HEADER
ACTIVE BEFORE INSERT POSITION 9 AS
DECLARE VARIABLE INV_NO INTEGER;
BEGIN
INV_NO = 0;
SELECT MIN(INV_NUMBER) FROM INV_LOG
WHERE STATUS='AVAILABLE'
INTO INV_NO;
IF (INV_NO > 0) THEN
UPDATE INV_LOG SET STATUS=STATUS
WHERE INV_NUMBER= :INV_NO;
ELSE
BEGIN
INV_NO = GEN_ID(GEN_INV_NUMBER, 1);
INSERT INTO INV_LOG(INV_NUMBER, GEN_DATE)
VALUES(:INV_NO, 'NOW');
END
NEW.INV_NUMBER = :INV_NO;
END
| |
В триггере After Insert, запись в журнале ()
обновляется по первичному ключу Invoice Header и ее статус изменяется, делая ее недоступной.
Когда транзакция завершена, блокировка на «доступном для повторного использования» номере инвойса
освобождается без дополнительного на то программирования.
CREATE TRIGGER AI_INSERT_INV_NUM
FOR INV_HEADER
ACTIVE AFTER INSERT 1O AS
BEGIN
UPDATE INV_LOG SET STATUS='USED',
INV_ID = NEW.INV_ID,
REASON ='',
OPERATOR = USER,
LOGDATE = 'NOW'
WHERE INV_NUMBER = NEW.INV_NUMBER;
END
| |
Еще две процедуры (или наборы SQL запросов, если вы их предпочитаете вызывать из вашего приложения) и триггер необходимы для работы с инвойсами, которые отменены, прерваны или удалены, соответственно.
Первая - процедура отмены инвойса:
CREATE PROCEDURE SP_CANCEL_INVOICE (INV_NO INTEGER, RSN VARCHAR(50))
AS
BEGIN
UPDATE INV_LOG
SET STATUS = 'CANCELLED',
REASON = :RSN
WHERE INV_NUMBER = :INV_NO;
UPDATE INV_HEADER
SET STATUS = 'CANCELLED'
WHERE INV_NUMBER = :INV_NO;
END | |
Вызовите следующую хранимую процедуру (или вызовите в виде SQL запроса из вашего приложения) для очистки номера инвойса, когда вставка заголовка инвойса прервана:
CREATE PROCEDURE SP_FREE_INV_NUMBER(INV_NO INTEGER)
AS
BEGIN
UPDATE INV_LOG
SET INV_ID = NULL,
STATUS = 'AVAILABLE',
REASON = 'INVOICE ABORTED',
OPERATOR = USER,
LOGDATE = 'NOW'
WHERE INV_NUMBER = :INV_NO;
END | |
И наконец, триггер After Delete в таблице Invoice Header выполняет точно такую же операцию, если запись удаляется:
CREATE TRIGGER AD_DELETE_INV_HDR
FOR INV_HEADER
ACTIVE AFTER DELETE POSITION 9 AS
BEGIN
UPDATE INV_LOG
SET INV_ID = NULL,
STATUS='AVAILABLE',
REASON='INVOICE DELETED',
OPERATOR = USER,
LOGDATE='NOW'
WHERE INV_ID=OLD.INV_ID;
END
| |
Запустите утилиту IB_WISQL и загрузите скрипт Test_Series.sql в меню Script.
Отредактируйте оператор CREATE DATABASE - укажите расположение файла базы данных, и если необходимо -
измените параметры User и Password.
( )
Если вы создаете базу данных InterBase 6/Firebird в диалекте 3, измените тип данных для домена D_IDENTIFIER - поставьте тип NUMERIC(18,0). Переменные и параметры типа INTEGER в определении хранимых процедур и триггеров, отмеченные в скрипте, также должны быть изменены.
Во время выполнения скрипта () будут созданы несколько
«утерянных» () записей в журнале для симуляции заголовков инвойсов (), которые
никогда не были подтверждены в базе данных. Не пытайтесь их удалять - они необходимы для демонстрации.
Если вам необходимо создать больше потерянных номеров, удалите некоторые записи из INV_HEADER, сохранив
предварительно их номера (INV_NUMBER), а затем измените состояние (поле 'STATUS') в соответствующих
записях журнала на 'USED' используя для этого утилиту IB_WISQL.
Раз у вас уже появилась база, откройте проект Test_Series в Delphi 4 или 5
()
и установите правильно строку соединения в свойствах IB_Connection ().
() You may like to open the script in the Delphi IDE, using the *.sql filter to have a nice, readable, code-sensitive layout in the editor. You can set your editor options to show the Delphi Editor while the application is running.
Запустите приложение.
Переведите набор данных () Invoice Header в режим
вставки кликнув на символ вставки (зеленый символ «плюс») на тулбаре. Просто впишите целочисленное
значение в элемент редактирования, озаглавленный 'Customer ID' и кликните символ Post
(символ «галочка» {}).
Просмотрите результаты работы триггеров после обновления датасетов.
На этом этапе приложение генерирует новые значения номеров инвойсов INV_NUMBER для каждого
создаваемого инвойса. На данном этапе значения полей INV_ID и LOG_ID будут совпадать.
Для отмены инвойса укажите причину в соответствующем поле выбора «Reason», выберите инвойс
() и кликните на кнопку Cancel Committed Invoice, расположенную на тулбаре.
Это кнопка черным восклицательным знаком, перечеркнутым красным крестом.
Следите за тем, что случится с обоими датасетами, когда они обновятся. Обратите внимание,
что состояние в журнале у данного номера инвойса изменилось с 'USED' (использован) на
'CANCELLED' (отменен) и высветилась причина отмены. Состояние инвойса также изменилось на
'CANCELLED' (отменен).
В данном приложении вы можете удалять любой заголовок инвойса (). Для целей демонстрации мы не выполнили некоторые требования,
описанные ранее. Ваше реальное приложение должно проверять права пользователей на удаление инвойсов.
Выберите заголовок инвойса среди тех, которые не помечены как отмененные (не 'CANCELLED').
(Извините, но возможности множественного выбора нет!) Нажмите на кнопку Delete, расположенную на тулбаре
(красный символ «минус»), и нажмите OK в диалоге подтверждения.
Обратите внимание, что заголовок инвойса пропал, но запись в журнале о нем осталась: состояние записи
изменилось на «доступна» ('AVAILABLE'), а поле «причина» (Reason) поменялось на «инвойс удален»
('INVOICE DELETED').
Удалите еще пару-тройку инвойсов.
Теперь добавьте несколько новых заголовков инвойсов (Invoice Headers). Обратите внимание, как триггер
Before Insert (position 9) () повторно использует «освобожденные»
номера инвойсов в строгой FIFO последовательности (). Это производится для того, чтобы номера
оставались внутри диапазона любых вновь появившихся значений.
().
Проследите за тем, как с появлением новых инвойсов они используют повторно номера и как изменяется
состояние последних в журнале.
Освободите еще немного номеров инвойсов, удалив несколько заголовков.
Теперь откройте еще одно приложение, запустив EXE из каталога проекта. Выстройте окна двух приложений так,
чтобы вы смогли наблюдать и заголовки и журнал в обоих приложениях.
В первом приложении начните вводить новый заголовок, но не выполняйте post.
Повторите свои действия во втором приложении, но на этот раз попытайтесь выполнить post.
Вы получите сообщение о взаимной блокировке «Deadlock» (). Это не проблема - сообщение показывает, что выполненная блокировка в триггере Before Insert
первого приложения работает.
Выполните post в первом приложении. Теперь выполните post во втором. «Deadlock» пропал.
В вашем реальном приложении, вы наверняка захотите организовать цикл с задержкой
(), повторяющий вставку и тихо
отлавливающий ошибки конфликта так, что пользователь не будет знать о существовавшем конфликте.
Задержки взаимной блокировки будут незаметны пользователям, поскольку их длительность очень непродолжительна,
возможно, лишь доля микросекунды.
Для новичков в InterBase: вы сейчас рассматриваете важное свойство программирования на InterBase -
управление многопользовательским конкурентным доступом. Некоторые клиент-серверные СУБД требуют
от клиентского приложения выполнять явные блокировки в случаях, когда может возникнуть конфликт
(). С помощью InterBase, вы можете быть
пессимистичными настолько, насколько вам это нужно () - InterBase отслеживает явные блокировки и применяет их, если конфликт действительно возникает.
В приведенном примере, транзакция, вставляющая новый заголовок инвойса, выполняется с уровнем изоляции
tiCommitted. Это означает, что транзакция может учитывать (и видеть) только те записи, которые уже были
подтверждены в момент, когда «постится» новая запись. Если иная транзакция уже обновила строку, но
обновление не было подтверждено, новая транзакция не увидит это обновление. Поэтому гарантируется,
что первая транзакция будет единственным кандидатом на повторное использование минимального и доступного
номера инвойса.
Когда транзакция «постит» новую запись, вначале срабатывают триггеры Before Insert, в частности - тот, что с
позицией 9. Этот триггер просматривает журнал на наличие хотя бы одной записи с номерами инвойсов, доступными
к повторному использованию. Если такие записи есть, триггер выбирает наименьший номер и выполняет холостой
update () - SET STATUS=STATUS. Такая операция заставляет InterBase
заблокировать набор допустимых записей (). Поскольку у каждой пользовательской транзакции
указан признак LockWait в TRUE, для всех остальных попыток захвата наименьшего номера возникнет ситуация
взаимной блокировки (). Установленный признак LockWait указывает
серверу БД на то, что при возникновении блокировки транзакция желает подождать разрешения конфликта
().
В обычной ситуации многопользовательской работы зачастую не требуется установки LockWait,
поскольку длинная транзакция одного пользователя может заблокировать большое количество строк на
неопределенное время (). Это идеально для данной конкретной задачи, поскольку конфликт, если и возникнет,
гарантированно будет моментально-кратким. Как только отработают триггеры After Insert и обновления будут
записаны в БД, блокировка исчезнет. Ждущая транзакция (ваше второе приложение) сможет теперь двигаться
дальше и безопасно найти и взять следующий номер, доступный для повторного использования.
Обычно вам нет необходимости специально программировать взаимные блокировки. Даже наоборот, вы
программируете так, чтобы избегать их и поддерживать многопользовательскую работу (). Однако
данная задача - тот самый случай, для которого эти взаимные блокировки и были созданы - для создания
сериализации, когда сериализация является абсолютным требованием к задаче. Они ()
излечивают обычный недочет в дизайне систем генерации номеров, которые некоторые из вас унаследовали
от Paradox, Access и других однопользовательских приложений. Вот как это происходит.
В файловых базах () данных и даже в некоторых очень известных клиент-серверных СУБД,
вся работа строго сериализуется. Пользовательские конфликты обязательно обрабатываются строгим
пессимистическим блокированием. Поскольку в случае многопользовательского режима это может привести
к полному ступору, разработчики избегают явных блокировок. В процедурах генерации номеров, нарушение
уникальности неизбежно (). В результате, разработчик может удалить ограничение уникальности
(), чтобы предотвратить нарушения (). В результате
имеем некорректные данные. Соответствующее использование возможностей механизма блокировок в
InterBase позволяет этого избежать.
Слева внизу на форме в таблице представлен список всех номеров инвойсов, которые
присутствуют в журнале ().
Список формируется следующим запросом:
SELECT LOG_ID, INV_NUMBER FROM INV_LOG
WHERE (NOT (INV_NUMBER IN (SELECT INV_NUMBER FROM INV_HEADER)))
AND STATUS <> 'AVAILABLE' | |
Иными словами, это просто записи таблицы журнала, оставшиеся после удаления заголовков,
исключая те, чье состояние осталось таким же, каким оно было, когда была попытка (неуспешная)
подтвердить транзакцию добавления нового заголовка. Как вы можете видеть, в журнале у таких
записей указано значение поля INV_ID родительского заголовка, который «умер», но записи еще
не готовы к присвоению новому родителю. Поскольку их состояние (поле Status) все еще
установлено в 'USED' , триггер на вставку на таблице заголовков INV_HEADER не видит их.
Хранимая процедура SP_FREE_INV_NUMBER как раз предназначена для спасения этих номеров.
Чтобы вызвать ее, просто выберите потерянный номер в табличке и нажмите кнопку 'Recycle'.
Это вызовет хранимую процедуру с передачей выбранного номера INV_NUMBER, которая установит Status в
состояние 'AVAILABLE', очистит несуществующий INV_ID и в поле Reason укажет соответствующее сообщение
('INVOICE ABORTED').
В вашем приложении, если вы не смогли полностью избавится от возможности отката вставки
заголовка (),
вы можете легко создать обработчик исключений, который выполнит невидимое для пользователей обновление,
сходное тому, которое производится в процедуре SP_FREE_INV_NUMBER.
И как всегда, если у вас есть замечания, предложения или вопросы - пожалуйста, обращайтесь ко мне лично,
или на список рассылки на сервере, посвященном IBO
().
** 5 ноября 2004, Сделаны изменения в коде триггера, за что
спасибо Рэймонду Кеннингтону (Raymond Kennington)
Джейсон Вартон (Jason Wharton)
http://www.ibobjects.com
jwharton@ibobjects.com
Copyright November 2000 Computer Programming Solutions - Mesa AZ
Вопросы, которые подняты в самом начале статьи - описание проблем, возникающих в системах генерации строгих последовательностей номеров - в целом правильные.
А вот реализация, на мой взгляд, подкачала. Код - «корявый». Так на Delphi допустимо писать новичкам. И то не всегда.
Запутанно выглядит описание блокировок. Сначала Джейсон утверждает, что сразу в момент insert в датасете (на клиенте) производится блокировка триггером на сервере. Но пример такой взаимной блокировки не работает. И вполне понятно почему - потому что insert производится в момент вызова post. А затем он сам же говорит, что триггеры срабатывают в момент post.
Следующая ошибка - в описании причин появления утерянных (“orphaned”) записей в журнале. Джейсон искренне верит, что запись в журнале, созданная в триггере Before Insert, каким-то чудом останется в этом журнале при откате транзакции вставки. На момент написания Джейсоном статьи не существовало, да и на момент перевода, насколько мне известно - не существует в Interbase механизма, аналогичного автономным транзакциям Oracle. Исключение - внетранзакционные внешние таблицы, однако в связи с тем, что в журнале записи обновляются, такой журнальная таблица не может быть внешней. А из этого вытекает еще третья проблема - нерешенность с «ушедшими вперед» генераторами. Впрочем, об этом чуть ниже.
Очевидно, чтобы получить запись в журнале при отсутствии записи в заголовке в результате отката вставки, необходимо работать на клиенте в двух транзакциях - в первой процедура получает следующий порядковый номер и регистрирует его в журнале (после чего делается commit), а вторая собственно вставляет заголовок с уже полученным в первой транзакции номером. При этом откат первой транзакции возможен только в случае взаимной блокировки при получении номера. А откат второй (при соблюдении правила, что везде, где мы создаем заголовки, мы пользуемся единым механизмом) возможен только из-за нарушения каких-либо бизнес-правил, существенных в предметной логике.
Теперь об ушедших вперед генераторах. Если при получении нового номера не было доступных для повторного использования номеров в журнале, и использовался генератор, то при откате такой транзакции ни в журнале, ни в таблице заголовков такой номер не сохранится. Как этот номер восстановить? Очевидно - придумать еще одну кнопку в интерфейсе и новую процедуру, которая должна взять текущее значение генератора, и в цикле, уменьшаясь на 1 до достижения 0 искать получаемые номера в журнале. При их отсутствии - вставлять записи в журнал, как будто эти номера потеряны. Можно, создавая их, сразу делать доступными.
Евгений Жилкин.
30.05.2005
К материалу прилагаются файлы:
[INTERBASE] [Генераторы]
Обсуждение материала [ 01-06-2005 06:21 ] 5 сообщений |