On Wed, Sep 5, 2018 at 10:07 AM, Gabor Boros mlnglsts@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

2018. 09. 02. 17:03 keltezéssel, 'Christian Giesen' chris@...
[firebird-support] írta:

>
> I have a need to generate unique sequential invoice/credit note numbers.
> I fully understand the usage of generators to produce unique identifiers
> in tables. However, according to Firebird documentation the use of
> generators does NOT guarantee sequential numbering.

Right.  Generators are outside transaction control.  Once they're allocated, they're gone.

Rolling back the transaction that created the value does not reset the generator so it

leaves a hole in the sequence.

Selecting max(whatever) from ...  doesn't work either, except single user, because two 

concurrent transactions can see the same max value.  Creating monotonically increasing

serial number with no gaps requires serializing concurrent transactions. Anything else

is going to risk leaving gaps.  

I use and suggest a table instead of generator. More flexible and
transaction controlled solution. If you need more than one (and
independent) sequential number just use same table with more records.

 The simplest way to create monotonically increasing serial numbers is to create a

table A with one long integer column.  Store an initial value in it.  For your application,

start a transaction, update table A setting the column to itself plus one and returning

the value of the column.  If the update fails, rollback the transaction and start a new

one.  When the update succeeds, you've got your serial identifier.  If there's an error

in subsequent database activities, roll the transaction back and retry the whole

thing.  The roll back will reset the serial identifier column.

Good luck,

Ann