mail@xxxxxxxxxx (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@xxxxxxxxxxx>, on comp.lang.objective-c
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
