>I'm asking this on behalf of a friend here, who thinks his written english wouldn't get his problem across.
>
>There's a requirement that invoice numbers should be unique and continuous, i.e. no gaps at all. So this requires a mechanism which would guarantee no two users get the same number, and that no numbers are skipped.
>
>Presently, he's using a cursor adapter, wraps the whole thing in a transaction, has a table with counters (a dbf). He assumes that the invoice number would be acquired and written at the end of transaction (so if anything goes wrong while saving the parent record and line items, it doesn't even ask for a new number). This should avoid error 1585.
>
>Problems: the number of items may be large; calculation of totals may be complicated (rebates, taxes, transportation etc) and may take some time. Stock should be up-to-date after the transaction (i.e. whatever was sold should not appear as available) and there may be popular items which may appear on many invoices entered on different workstations at the same time.
>
>The trouble is that the record in the table with counters may be locked by someone else; he's getting errors 130 (can't rlock()) or 1585 (update conflict). So he's introduced a 15 times loop, wherein he does a tablerevert() and refresh() of the counter table and tries to rlock() again, waiting for a free pass. Now if he'd lose the loop and just issue a "system busy, try again" message (i.e. introduce an user-timed delay until retry), the users would probably want his head on a platter.
>
>Is there an elegant way to handle this?


Maybe.

An invoice is only an invoice if it has been printed

You could do it in two steps

(1) Write the invoice ( in a transaction) updating the stock tables. The invoice number would be zero here since it has not been printed
The invoice has its pk - invoice lines have their fk

put a candidate index on invoicenumber style - for !empty(invoicenumber)

The customer has now the ability to delete invoices as long as they have not been printed


(2) When printing the invoice for the first time, assign an invoice number ( transaction + counters table)