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~~' river14ap...@gmail.com [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]