On Wed, Sep 5, 2018 at 10:07 AM, Gabor Boros mlngl...@bgss.hu [firebird-support] <firebird-support@yahoogroups.com> wrote:
> 2018. 09. 02. 17:03 keltezéssel, 'Christian Giesen' ch...@xt.co.za > [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
