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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
