On Feb 19, 2008 5:16 PM, Milan Babuskov <milanb@...> wrote:

> 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?

Hi Milan,

Thanks for the tips. I have implemented the way you just described.

There aren't that many invoices/minute. It is a very small number of users.

Thanks for taking the time to answer.

Fidel.