Re: How to generate unique invoice numbers for each day

Dne 16.1.2011 22:44, Andrus Moor napsal(a):
> 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.

I really am not sure what you mean by this. The isolation levels are
implemented in the database, you don't need to change the application at
all. And there are only two isolation levels in PostgreSQL - READ
COMMITTED and SERIALIZABLE, where the READ COMMITTED is the less
restrictive one (and default).

So everything runs (at least) in READ COMMITTED mode, no matter what you
do. You don't need to change anything.

Yes, locking may in some cases lead to deadlocks, that's true. For
example creating several invoices (for different days) in a single
transaction may lead to a deadlock. But that's a feature, not a bug.

And you can get around this by creating all the invoices in the same
order (e.g. sorted by date) - this prevents 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 ?

Locking when updating the very same value is inevitable. If you update
the same row from two sessions, one of them has to wait until the other
one commits or rolls back. You can't get around this in a transactional
environment.

In Oracle you could solve this using an autonomous transaction, but
there's nothing like that in PostgreSQL.

So if you don't want to use the approach proposed in General Bits 130
(the one with gapless sequences implemented using a table), the only
option I'm aware of is to create one sequence for each day.

Tomas