Thread: How to generate unique invoice numbers for each day

Invoice numbers have format  yymmddn

where n is sequence number in day staring at 1 for every day.

command

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.

How to get unique invoice number for some day in 8.1+ when multiple users
create new invoices ?

Andrus.

----- Original Message -----

From: Jorge Godoy

To: Andrus Moor

Cc: pgsql-general@postgresql.org

Sent: Saturday, January 15, 2011 8:41 PM

Subject: ***SPAM*** Re: [GENERAL] How to generate unique invoice numbers foreach day

Use a sequence.

--
Jorge Godoy     <jgodoy@gmail.com>

2011/1/15 Andrus Moor <kobruleht2@hot.ee>

Invoice numbers have format  yymmddn

where n is sequence number in day staring at 1 for every day.

command

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.

How to get unique invoice number for some day in 8.1+ when multiple users create new invoices ?

Andrus.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

If the gaps (user gets a number from a sequence and then rollbacks the
transaction) are not a problem, then the sequences (reset every day) are
probably the best solution.

If the gaps are a problem (which is usually the case with invoicing
systems), then you need to manage that on your own, e.g. using a table
with one row for sequence (but that might be a bottleneck with multiple
users and a lot of invoices).

Tomas

Dne 15.1.2011 20:32, Jorge Godoy napsal(a):
> Why would you do that?
>
> You can always reset the sequence at the end of the day.
>
> --
> Jorge Godoy     <jgodoy@gmail.com <mailto:jgodoy@gmail.com>>
>
>
> On Sat, Jan 15, 2011 at 17:09, Andrus Moor <kobruleht2@hot.ee
> <mailto:kobruleht2@hot.ee>> wrote:
>
>     There are 365 days in year.
>     Do you really think pre-creating sequence for every day for every
>     year is best solution ?
>
>     Andrus.
>
>         ----- Original Message -----
>         *From:* Jorge Godoy <mailto:jgodoy@gmail.com>
>         *To:* Andrus Moor <mailto:kobruleht2@hot.ee>
>         *Cc:* pgsql-general@postgresql.org
>         <mailto:pgsql-general@postgresql.org>
>         *Sent:* Saturday, January 15, 2011 8:41 PM
>         *Subject:* ***SPAM*** Re: [GENERAL] How to generate unique
>         invoice numbers foreach day
>
>         Use a sequence.
>
>         --
>         Jorge Godoy     <jgodoy@gmail.com <mailto:jgodoy@gmail.com>>
>
>
>         2011/1/15 Andrus Moor <kobruleht2@hot.ee <mailto:kobruleht2@hot.ee>>
>
>             Invoice numbers have format  yymmddn
>
>             where n is sequence number in day staring at 1 for every day.
>
>             command
>
>             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.
>
>             How to get unique invoice number for some day in 8.1+ when
>             multiple users create new invoices ?
>
>             Andrus.
>
>             --
>             Sent via pgsql-general mailing list
>             (pgsql-general@postgresql.org
>             <mailto:pgsql-general@postgresql.org>)
>             To make changes to your subscription:
>             http://www.postgresql.org/mailpref/pgsql-general
>
>
>

----- Original Message -----

From: Jorge Godoy

To: Andrus Moor

Cc: pgsql-general@postgresql.org

Sent: Saturday, January 15, 2011 9:32 PM

Subject: Re: [GENERAL] How to generate unique invoice numbers foreach day

Why would you do that?

You can always reset the sequence at the end of the day.

--
Jorge Godoy     <jgodoy@gmail.com>

On Sat, Jan 15, 2011 at 17:09, Andrus Moor <kobruleht2@hot.ee> wrote:

There are 365 days in year.

Do you really think pre-creating sequence for every day for every year is best solution ?

 

Andrus.

----- Original Message -----

From: Jorge Godoy

To: Andrus Moor

Cc: pgsql-general@postgresql.org

Sent: Saturday, January 15, 2011 8:41 PM

Subject: ***SPAM*** Re: [GENERAL] How to generate unique invoice numbers foreach day

Use a sequence.

--
Jorge Godoy     <jgodoy@gmail.com>

2011/1/15 Andrus Moor <kobruleht2@hot.ee>

Invoice numbers have format  yymmddn

where n is sequence number in day staring at 1 for every day.

command

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.

How to get unique invoice number for some day in 8.1+ when multiple users create new invoices ?

Andrus.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

In that case you have to manage the IDs on your own, the sequences won't
help you in this (unless you really create one sequence for each day,
which does not seem like a good solution to me).

A really simple solution might be to do a BEFORE INSERT trigger that
checks the last ID inserted for the day, locks that ID somehow (e.g. the
record with the ID) so that you don't get duplicate IDs and uses that ID.

Another solution is to manage the IDs on your own in a separate table,
i.e. create a table

CREATE TABLE invoice_numbers (
   invoice_date DATE NOT NULL PRIMARY KEY,
   invoice_last INTEGER NOT NULL DEFAULT 0
);

and then something like

BEGIN;

   UPDATE invoice_numbers SET invoice_last = (invoice_last + 1)
    WHERE invoice_date = '2010-11-...' RETURNING invoice_last;

   ... use the new invoice_last value

COMMIT;

But again, this might be a concurrency bottleneck if there's a lot of
invoices created at the same time by different users (or if the
transaction is long).

Tomas

Dne 15.1.2011 21:04, Andrus Moor napsal(a):
> Invoices can entered also some days forward or back. Users enters
> invoice date and expected program to generate next sequential number for
> this day.
> Different users can enter invoices for different days.
>
> Andrus.
>
>
>     ----- Original Message -----
>     *From:* Jorge Godoy <mailto:jgodoy@gmail.com>
>     *To:* Andrus Moor <mailto:kobruleht2@hot.ee>
>     *Cc:* pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>
>     *Sent:* Saturday, January 15, 2011 9:32 PM
>     *Subject:* Re: [GENERAL] How to generate unique invoice numbers
>     foreach day
>
>     Why would you do that?
>
>     You can always reset the sequence at the end of the day.
>
>     --
>     Jorge Godoy     <jgodoy@gmail.com <mailto:jgodoy@gmail.com>>
>
>
>     On Sat, Jan 15, 2011 at 17:09, Andrus Moor <kobruleht2@hot.ee
>     <mailto:kobruleht2@hot.ee>> wrote:
>
>         There are 365 days in year.
>         Do you really think pre-creating sequence for every day for
>         every year is best solution ?
>
>         Andrus.
>
>             ----- Original Message -----
>             *From:* Jorge Godoy <mailto:jgodoy@gmail.com>
>             *To:* Andrus Moor <mailto:kobruleht2@hot.ee>
>             *Cc:* pgsql-general@postgresql.org
>             <mailto:pgsql-general@postgresql.org>
>             *Sent:* Saturday, January 15, 2011 8:41 PM
>             *Subject:* ***SPAM*** Re: [GENERAL] How to generate unique
>             invoice numbers foreach day
>
>             Use a sequence.
>
>             --
>             Jorge Godoy     <jgodoy@gmail.com <mailto:jgodoy@gmail.com>>
>
>
>             2011/1/15 Andrus Moor <kobruleht2@hot.ee
>             <mailto:kobruleht2@hot.ee>>
>
>                 Invoice numbers have format  yymmddn
>
>                 where n is sequence number in day staring at 1 for every
>                 day.
>
>                 command
>
>                 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.
>
>                 How to get unique invoice number for some day in 8.1+
>                 when multiple users create new invoices ?
>
>                 Andrus.
>
>                 --
>                 Sent via pgsql-general mailing list
>                 (pgsql-general@postgresql.org
>                 <mailto:pgsql-general@postgresql.org>)
>                 To make changes to your subscription:
>                 http://www.postgresql.org/mailpref/pgsql-general
>
>
>

On Sat, 15 Jan 2011, Andrus Moor wrote:

> There are 365 days in year. Do you really think pre-creating sequence for
> every day for every year is best solution ?

Andrus,

   I just saw this thread so my idea may not work for you. What I'd do is use
the Julian date (that is, the sequential day from 1-365/6) followed by 1, 2,
or 3 digits depending on how many invoices are generated each day. Makes
sorting and comparisons simple.

Rich

On 2011-01-15, Andrus Moor <kobruleht2@hot.ee> wrote:
> Invoice numbers have format  yymmddn
>
> where n is sequence number in day staring at 1 for every day.
>
> command
>
> 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.
>
> How to get unique invoice number for some day in 8.1+ when multiple users
> create new invoices ?

use a sequence, reset the sequence each night.

--
⚂⚃ 100% natural

This is an very common and well understood problem. Take a look at this:

http://www.varlena.com/GeneralBits/130.php

--
Regards,
Peter Geoghegan

Dne 16.1.2011 03:29, Jasen Betts napsal(a):
> On 2011-01-15, Andrus Moor <kobruleht2@hot.ee> wrote:
>> Invoice numbers have format  yymmddn
>>
>> where n is sequence number in day staring at 1 for every day.
>>
>> command
>>
>> 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.
>>
>> How to get unique invoice number for some day in 8.1+ when multiple users
>> create new invoices ?
>
> use a sequence, reset the sequence each night.

This was already proposed but he can't do that as the users may issue
invoices for different days (not just for 'today'). Which means the user
may create an invoice for monday, then another one for tuesday and then
another one for monday again. A single sequence can't solve this.

So either he has to create a sequence for each day, but he'll get a lot
of sequences (and it's a bit messy), and there will be gaps (not sure if
this is acceptable).

Or he can use the solution proposed in the General Bits 130 (which is
basically the solution I've already proposed) and maintain gapless
sequences using a table.

Tomas

On 01/15/2011 12:13 PM, Andrus Moor wrote:
> Invoice numbers have format yymmddn
>
> where n is sequence number in day staring at 1 for every day.
>
> command
>
> 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.
>
> How to get unique invoice number for some day in 8.1+ when multiple users create new invoices ?
>
> Andrus.
>

I understand this is a business rule, and you cant change it.

My opinion is you are doing it wrong.  Don't encode more than one bit of data into one column.  You have two bits of
data,date and invoice number.  Use TWO columns!  Let the invoice number auto-inc and never reset it.  You want to find
invoiceson some day, query the date field.  You want to find a specific invoice, query the invoice number.  Dont
combinethe two.  Why write a bunch of bug ridden code that encodes and decodes the two fields into one? 

But I know... you cant change it.  Someone up the chain thinks its just the bee's knees.  I had to say it though.  Its
beensaid.  I will go in peace. 

-Andy

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 ?

using sequence approach:

Application checks for sequence name in form invoiceno_yyyymmdd
if sequence does not exist it is created. For concurrent adding second
caller gets sequence exists exception and in this case this query can
re-executed.
Next value is obtained from sequence

sequneces older than 1 year should be purded automatically since max 1 year
backward numbers may created.
Which is best solution ?

using new record approach:

1. lock table
2. in this day sequnece does notr exist, add it
3. get next value for day, increment number in this table
4. unlock the table.

Which command should be used to obtain exclusise write access to table (lock
some reosurce or semaphore) ?

Andrus.

On 01/16/2011 11:00 AM, Andrus Moor wrote:
> 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.
OR... can you go back to your customer and tell them they wont like this.  Really really they should let you do it
correctly. I find people dont change because they dont have to, not because there is an actual reason.  Many times,
givena description of how hard and how messy something will be to code, I have convinced people that a simple business
changeand simple code is really the best approach.  But I have hit walls.  Things I could not change, but I did try. 


> Is it reasonable/how to implement the following:

Sorry, I have no idea.

-Andy

>> Yes. This is customer requirement and I cannot change it.
> OR... can you go back to your customer and tell them they wont like this.
> Really really they should let you do it correctly.  I find people dont
> change because they dont have to, not because there is an actual reason.
> Many times, given a description of how hard and how messy something will
> be to code, I have convinced people that a simple business change and
> simple code is really the best approach.  But I have hit walls.  Things I
> could not change, but I did try.

My Visual FoxPro application  works OK in this case.
I used FLOCK() to lock invoice header table (FLOCK() waits indefinitely
until lock is obtained and reads fresh data from disk),

used

SELECT MAX( CAST( SUBSTRING(invoiceno,8) AS INT ) )+1
FROM invoices
WHERE date= m.invoice_date

to get next free number, inserted invoice and unlocked the table.

Customer expects Postgres to be more powerful than FoxPro . He don't
understand why this stops working after upgrade.

Andrus.

On 16 Jan 2011, at 18:56, Andrus Moor wrote:

> My Visual FoxPro application  works OK in this case.
> I used FLOCK() to lock invoice header table (FLOCK() waits indefinitely until lock is obtained and reads fresh data
fromdisk), 
>
> used
>
> SELECT MAX( CAST( SUBSTRING(invoiceno,8) AS INT ) )+1
> FROM invoices
> WHERE date= m.invoice_date
>
> to get next free number, inserted invoice and unlocked the table.

If you really need gapless sequences, then you would do something rather similar in Postgres.

Instead of the whole database file you only lock the file for the table containing the sequence. That's called
serialization,something you can't get around if you require gapless sequences. The documentation does a much better job
atexplaining serialization than I could, I'm sure. 
That's also why people were suggesting ways around that requirement, as it's a costly feature with regards to database
performance.

There are blogs about how to create gapless sequences in Postgres, so I won't go into detail about them.

> Customer expects Postgres to be more powerful than FoxPro . He don't understand why this stops working after upgrade.


And he is right too! To the customer the database is probably that thing that they put their data in and that does all
themagic stuff for them. To you it's that thing that you implement all that magic stuff in! Half of what the customer
callshis database is what you implemented. 

Your customer doesn't care about details like that though, he cares about a working system. The database (the part that
wecall the database) does it's thing rather well in 99.99% of the cases - usually when the database (as seen from the
customer'spoint of view) doesn't do what it's supposed to do, the problem is in the business logic that you put in that
database.

So to summarise: What's failing her isn't the database, it's you.

Where you failed, heck, I don't know... You probably didn't get all the requirements right, or you forgot to test this
particularpart of the application, or whatever. Don't fuss about it too much though, it's almost impossible to not fail
somewherewith complicated applications like this. Consider it a lesson learned. 

Just don't blame the database for it, especially not on a mailing-list about that database ;)

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4d333dbf11702139115944!

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

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.

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

Tomas Vondra wrote on 16.01.2011 23:41:
> 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.

Hmm, a single transaction cannot deadlock itself as far as I know.
A deadlock can only happen between two different transactions (T1 locks R1, waits for R2, T2 locks R2 waits for R1)

Regards
Thomas

mail@smogura.eu (Radosław Smogura) writes:
> 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?

In this case, I don't imagine it's necessary to do terribly much
explicit locking.

The crucial thing to 'serialize' is the "ownership" of the unique
invoice number, and a PRIMARY KEY constraint does that perfectly nicely,
and with downsides that are pretty much unavoidable.

- In the simple, no-conflict case, the value is generated by querying
  the highest value for the specified day, adding 1 to it, and in the
  face of no conflicting requests going in at the same time, having
  everything succeed.

- On the other hand, if two workers are trying to generate invoices
  concurrently, both compute the highest value, adding 1, both getting
  the same value.

  The first worker clicks on whatever does the COMMIT, the work
  succeeds, and their invoice is successfully stored.

  The second worker selects whatever does the COMMIT, and, since an
  invoice is already committed with the same invoice number, the request
  fails based on the violation of the primary key.

  The solution to that problem is to resubmit the request, querying for
  a new invoice number, which (hopefully!) succeeds.  And there's not
  much possible as an alternative to this resubmission.

If this all is turning out badly, then I suggest a third possibility,
namely to:
  - Initially use a sequence (or similar) to generate an invoice number
    in that is sure to be unique, but which doesn't conform to
    expectations.

    Suppose...  We set up a sequence, and initially assign invoice ID
    values based purely on that sequence.  As it contains no '-'
    characters, it never conflicts with the IDs that we *want*.  And as
    increased values are assigned automatically, two workers entering
    data never fight over specific values.

  - Then, have a single process that comes in afterwards and rewrites
    the invoice IDs in accordance with policy.

    In effect, this process looks for all invoices where the ID is
    purely numeric.  And it renumbers them into the format desired.
    Since this renumbering takes place within a single process, it
    doesn't "fight" with other processes that might be holding onto
    those not-yet-properly-formatted IDs.

    The "magic" still needed is to run that rewrite process.  Something
    needs to invoke it periodically to fix up the IDs.  Left as exercise
    to the reader ;-).
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
"Seriously, I think you really need to find out who took your sense of
humor, beat the crap out of  him, and take it back. It's getting old."
-- Michael J Ash <mikeash@csd.uwm.edu>, on comp.lang.objective-c