Re: How to generate unique invoice numbers for each day

Thank you.

> 2. In point 2. add FOR UPDATE
> 3. Use READ COMMITED TRANSACTION ISOLATION LEVEL
>
> Don't lock tables, You wrote you can generate invoices for few days
> backward,
> so you don't need locking whole table.
>
> Don't use seqences, as sequence value will don't get back when transaction
> is
> rolled back (You need to prevent gaps).
>
> Locking with UPDATE, or FOR UPDATE is much more portable.
>
> If you generate invoices in massive operation, probably when process runs
> no
> one will be able to create invoice, but you don't need to create multi
> thread
> application.

> In any approach preventing gaps, locking is required. This is real life
> situation; imagine you have two coworkers and then they need to create
> invoices, so they looks in ledger (or a last day copy of ledger in their
> offices; international company, but no Internet, only fax and telephone)
> and
> checks last number used, what should be done next?

Using read commited isolation level requires knowing in start of transaction
will it perform new invoice adding or not. This requires changing program
logic a lot.
Currently script which creates day seq numbers runs inside transaction .
Transaction starter does not know will special isolation required or not.
Changing blindly all transactions to use this isolation level decreases
perfomance and may lead to deadlocks.

In my case I can assume that transaction newer fails since business rules
are verified and this is simple insert (inrare cases if it fails due to
disk failure etc then gaps are allowed).
Can this knowledge used to create simpler solution ?

Andrus.