As previous responders have said, generator values are guaranteed to be
unique. However when transactions fail, the generator will skip a value.



Only relying on

NewNo = select max(No) + 1 from Invoice

is also a bad idea because in concurrent insert conditions you are for sure
going to get duplicates. As far as I remember, aggregate functions also do
not use indexes and will get progressively slower over time. This might have
changed though, so I may be wrong on this.



The only reliable way I found is as follows:



* We know that the DB engine will at the very least guarantee
uniqueness of the invoice no if we put a unique constraint on the column.
Even with concurrent inserts that is at least one thing we can fully rely
on. So we create the unique constraint on the column.



* Our insert operation then uses the following logic:



BeginTransaction();

while (true)

{

newInvoiceNo = select max(InvoiceNo) + 1 from INVOICE

Invoice.InvoiceNo = newInvoiceNo;



try

{

InsertInvoice();

Commit();

}

catch (UniqueConstraintException exc)

{

WaitRandomNumberOfMillisecs();

}

catch (Exception exc)

{

Rollback();

throw;

}

}



The effect of above pseudo-code is that it gets the next InvoiceNo as max+1.
When the record is inserted, the unique constraint will make sure we do not
have a duplicate. If we don't then the insert will succeed and the
transaction is committed.

If a duplicate exists, the insert will throw an exception that indicates a
unique constraint violation. The exception handler for that exception will
pause the thread for some (short) time and then retry as part of the next
iteration of the while loop. If the exception was not a unique constraint
violation, then the transaction is rolled back and the exception bubbles up
to the next level in the call stack.

Above code will obviously rety infinite times, but you can add a max retry
count.



I have only ever found, in the case of a duplicate number, one retry is
usually enough for the record to be inserted. Even with multiple clients
using above mechanism, all of them will be successful and have a unique
sequential number. We are using the integrity of the DB engine to ensure
this.



Hope it helps







From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: Monday, 03 September, 2018 13:30
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Sequential auto incremental numbering

> On Sep 3, 2018, at 6:00 AM, 'River~~' river14april@...

[firebird-support] <firebird-support@yahoogroups.com> wrote:

>
> Two ideas that should work on any SQL database so long as you never delete

invoices

Remember that a transaction can roll back, so delete is not your only
challenge.

>
> I amNot a guru for this dbms so if a more specific technique is posted it

is likely to be faster

>
> Note however that while my method may be inefficient, it is more likely to

be portable. You pays your money and you takes your choice

>
>
> With SQL and code in another language:
>
> Start transaction
> Select max (invoice) ...
> Calculate next invoice
> Insert...
> Commit
>

That just narrows the window of chance that two transactions will see the
same next value.

> Or if you want to do it all in SQL then create a view that selects max

invoice plus one, and insert one row at a time from that view into the
table. The other columns would then be presented as calculated values

I think that does nothing to solve the problem of seeing the same max value
from two concurrent transactions.

>
> Either of the above are reasonably efficient if you have an index on the

invoice number, as firebird can easily find the first and last entries from
the index and iirr if the only column retrieved is the indexed value
firebird does not even access the table.

Firebird does access the table to insure that the last index entry is a
record version visible to the current transaction.

Read the article in ibobjects.

Good luck,

Ann

>

[Non-text portions of this message have been removed]