On Friday 23 November 2001 03:28 am, Andreas Pohl wrote:

> Brad,
>
> there are two possibilities to get next invoice number:
> - within transaction control by selecting max(nr)
> - outside transaction control by using generator value

There is a third way which is by using an id generation table like I outlined
in my email. This is within transaction control and works perfectly in the
rollback situation.

> Both ways will fail in case of rollback transaction. Generator value cannot
> be set back safely in a multi user environment.

Using an id table doesn't fail when using rollbacks.

> Max(nr) or next_ nr_procedure will provide different results if more then
> one user is creating invoices and something goes wrong between calling
> trigger/statement for calculating invoice nr and closing transaction.

Using an id table and doing a dummy update first (to cause locking) and then
getting the current value and incrementing it works fine. It is using the
fact that the database *has* to handle two users trying to update data at the
same time (part of the ACID requirements).

> Maybe you are a lucky men and there is only a small time frame between
> posting values and closing transaction, but in WANs or slow LANs...

No its not luck - the design just works and you missed it in your
considerations above. I tested on a local machine with 10 copies of a
program that just created 1,000 invoices as fast as it could. When done I
verified that all the numbers were used and none skipped. I also tested with
some of the test programs doing rollbacks so that I could see if this was
working properly too. This *really* hammered the database. It was all local
access so no network speed issues and the load was huge. I'm very confident
that this code works properly.

--
Brad Pepers
brad@...