I will sugest to:
1. Delete point 1.
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?
"Andrus Moor" <kobruleht2@hot.ee> Sunday 16 January 2011 18:00:58
> Andy,
>
> >> SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7),
> >> '^[0-9]*'),'')::int),0)+1
> >> FROM invoice
> >> where date= ?invoicedate
> >>
> >> is used to get next free invoice number if new invoice is saved.
> >>
> >> If multiple invoices are saved concurrently from different processes,
> >> they will probably get same number.
> >
> > I understand this is a business rule, and you cant change it.
>
> Yes. This is customer requirement and I cannot change it.
> Is it reasonable/how to implement the following:
>
> 1. plpgsql command obtains some lock
> 2. It uses
>
> SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7),
> '^[0-9]*'),'')::int),0)+1
> FROM invoice
> where date= ?invoicedate
>
> to get next number for invoice date day
>
> 3. It adds new invoice with this numbder to database
>
> 4. It releases the lock.
>
> Or is it better to maintain separate sequence or separate table of free
> numbers for every day ?
>