Fidel Viegas wrote:

> 1) The Invoice numbers have to be sequential, and no gaps are allowed
> 2) There shall not exist a case where invoice2.timesaved >
> invoice1.timesaved and invoice1.no > invoice2.no
>
> Can someone suggest a good approach to solve this?

Sequences are not a good idea here. I suggest you also use separate
fields for PK and invoice number. As for the number itself, I'd use two
transactions:

T1: inserts a new invoice and all the item records, etc.
T1: commits (saving PK)
T2: get the next number from series and update the row (using PK) and
also write the timestamp in time-saved field.

T2 would do locking, but since it's isolated operation (a simple single
update) it would be a very short transaction. It's probably safe to
assume that other clients would be busy writing in their invoice items
during that time.

If you use read-committed, make sure you handle deadlocks with
try-deadlock-retry loop.

Well, just the idea. I don't know how much invoices/minute are we
talking here?

--
Milan Babuskov
http://www.flamerobin.org