> 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

>