Moving Docno generation to the end
Janaki, October 14, 2004 - 4:04 am UTC
Thanks for the response. We could now confirm the fact if customer wants to achieve very good concurrency, he has to accept 'gaps' in number generation.
We have a practical difficulty in moving the document number generation process to the very last since the generated document data gets stored in the main table and subsequently, with reference to this document no, all other processing starts [like generating the IssueNo and updating the stock with reference to the IssueNo and on stock updation, if qty goes below the reorder level, system has to automatically generate a "PurchaseOrder" document based on the issue reference etc..]
October 14, 2004 - 10:06 am UTC
could be solved by having this magical document number be a unique ATTRIBUTE of a document, not the key linking the tables together. lets you update a document number as well in the even you want to.
LastNum Updation case
Janaki, October 15, 2004 - 2:19 am UTC
Ya, as you say, storing "Display Document No" [Continuous no which is to be maintained as an attribute] along with "system Doc No" [Discontinuous no] is the only possible way to maintain concurrency as well meet the meet the customer's requirement of continuous number generation. We have already built the system. The impact of going for this change would be too high.
The entire discussion was really satisfactory. Thanks so much for all the suggestions.
October 15, 2004 - 11:23 am UTC
The impact of going for this change would be too high.
and what is the impact of NOT going for it :)
Way to work around the business issue
sPh, October 15, 2004 - 11:31 am UTC
> (i've written about this soooo many times
> -- i have yet to have anyone present
> that bona-fied business reason for "gap
> free numbers" for anything.
Tom,
I have run into government jurisdictions where there is actually a law on the books stating that computer records must match serialized pre-printed forms. Clearly in that case there was an actual business requirement.
However, on doing more research we found in every case that there was a process for applying for an "exemption" to the law, and that what was really needed was to submit the right form to the right dude along with... Then the "requirement" magically went away!
sPh
October 15, 2004 - 12:15 pm UTC
fine, so the end user is inputting the number -- you are NOT generating the number.
Don't see any issue there, the computer isn't assigning numbers, the number is data input (probably wrong many times I would guess :) by an end user.
if the forms were pre-printed -- they had the numbers, by definition, the database wasn't generating them, the application wasn't generating them.
Now what? how or why would that cause a serialization issue
(and what happened when someone spilt coffee on the stack of forms -- destroying some of them UTTERLY beyond repair???)
David Aldridge, October 15, 2004 - 4:35 pm UTC
I think that it's important to acknowledge that the problem here is not the "sequential no-gap document number" requirement, nor the requirement on concurrency for the document generation, it is only the combination of _both_ of these.
I would think that it would be pretty rare that you couldn't get by with a system that allows concurrent generation of documents, but then relies on a serial process in the background to assign the no-gap numbers to commited documents with something like ...
[code]
Update doc
Set doc# = rownum+(Select Max(doc#) From doc)
where doc# is null;
commit;
[/code]
... running in the background every so-many seconds or minutes.
Not suitable for all situations, but workable in many of them I'd bet, and it's another alternative to consider.
October 15, 2004 - 6:06 pm UTC
that one gets my vote -- i mentioned it above sort of -- very similar to:
(i suppose you could wait to generate the document id until immediately before
the COMMIT. you don't need the document id until then -- move your logic so the
doc id generation is the very very very last step)
Concurrency And Continuous Number
Janaki, October 16, 2004 - 10:16 am UTC
Customer is insisting that when they see gaps in number, it looks like something is wrong and hence it is certainly not acceptable...
We searched Net to find answers to the following
1) Whether people follow that 'System No' and 'Display
No' [i.e. our discussion w.r.t. maintaining the
GeneratedNo as Attribute] as a standard approach to
ensure this Concurrency + Continuous number..
2) Whether any major ERP vendors has faced similar
problem of "Gaps" in number generation [basically
who has not followed #1].
but could not get any satisfying answers to the above from NET.
Can we get any reference to site where we can cross-check how others have implemented this number generation in a complex applications catering to a huge number of users (850+) but ensures concurrency and continuous number.
Thanks..
October 16, 2004 - 11:00 am UTC
the answers are obvious.
think about it.
these two concepts:
o gap free numbers
o scalable, concurrent
are by their very definition mutually exclusive
Look at my URL, you know what I use for a unique ID? A purposely non-increasing, random, yet unique number (i don't want anyone to be able to GUESS what the numbers for some unpublished question is so I make the numbers random, they skip all around).
Ask the customer, so what happens when someone spilt coffee on a stack of invoices in the olden days?
Or a tablet fell behind the desk for 5 years and no one could find it, causing a gap?
If you want "the appearance of gap free numbers" and to be "scalable, concurrent", you have exactly ONE CHOICE.
This gap free number is assigned immediately before you commit the transaction that generates it (or immediately after). This gap free number is not the key of the object - it is a unique attribute that keeps end users happy and feeling like they've done something good (eg: a placebo).
Why would a gap in a sequence look like something is wrong? That is (to me) just silly.
AQ
Alberto Dell'Era, October 16, 2004 - 12:16 pm UTC
What about an AQ with a sequence of values pre-inserted ?
You can't miss a number due to the transactional nature of dequeue - if a tx rollbacks, server crashes, etc the next tx will pick the number.
And it should be very scalable - not as scalable as a sequence, but very scalable anyway.
BTW in Italy, for each missing invoice number, you get an around $1000 fine, even if it was just a bona-fide error, and you weren't stealing any tax money at all. I agree, that's the only case I know that needs gap-free - but quite compelling (100 numbers lost --> $100,000 lost :)
October 16, 2004 - 12:57 pm UTC
umm -- in italy make the "invoice number" one of those "display attributes we generate after the fact"
Still want to know what happens to the guys without computer that SPILL COFFEE. I'll betcha it isn't really "real".
When the auditors come in, fine -- update t set ino = rownum; commit;
No gaps.
There is nothing that says "this artifical human number to make us feel good is or must be the primary key" of this object. It is an attribute of an invoice -- it can be assigned after the fact -- heck, when you goto mail them even. does not mean they need to be assigned at invoice generation time in any way.
AQ would be "ugly", either just before commit or as a background serial job just after commit -- to assign this attribute of an invoice (not a key!!)
Serge Shmygelsky, October 16, 2004 - 12:30 pm UTC
Hi Tom,
your sentence
'A purposely non-increasing, random, yet unique number (i don't want anyone to be able to GUESS what the numbers for some unpublished question is so I make the numbers random, they skip all around)' brought me some new ideas I've never thought about before. It could be considered as additional security mechanism for some applications. Thanks. One can always find one more unexpected useful side in your answers :-).
But the question is: how can you do that? Using DBMS_RANDOM? I do understand that DBMS_RANDOM should provide RANDOM numbers, but - is there any probability of getting the same value?
October 16, 2004 - 1:01 pm UTC
there is a time component in there, a random number, and a sequence. 2 of them are fixed width, the third is not.
The sequence assures me "unique"
The other two assure me to a degree "not easily guessable"
sys_guid() would have been a good alternative as well -- i just prefered a number that day.
Serge Shmygelsky, October 16, 2004 - 1:20 pm UTC
Thanks. Sorry for bothering you because I've already found it by 'Search AskTom'. I didn't follow best practices: first - search, then - ask :-)
Deared gaps and spilled cofee
Oleksandr Alesinskyy, October 16, 2004 - 1:29 pm UTC
Hi Tom,
In Germany requirement for gap-free invoice numbers is enforced by law as well. So Italy is not alone. Guess some more EU countries have the same regulation. Concerning the spilled coffee - there are at least 2 solutions from which guy can select depending on jurisdiction. 1st - he cat take another sheet of paper and print the same invoice with the same number once more. 2nd - he may take a fresh pile of paper and print plump deed about spoiled invoice.
October 16, 2004 - 3:30 pm UTC
Fine -- but you do all understand that this magical mystical invoice number is not, should not be the primary key right?
You can assign it later.
You can assign it last.
You do not have to
a) generate invoice number (an attribute of an invoice, like "customer" is
b) generate rest of invoice data
c) holding locks for long times
You would:
a) create an invoice, using a primary key generated using whatever highly scalable concurrent techniques you wanted (eg: a SEQUENCE)
b) propagate this primary key all over the place, whereever it needs be
c) at your LEISURE, assign this invoice number
There is no reason to make the invoice number be the primary key in your database. Auditors never see it -- it is just a number.
And if there were a technique for the spilt coffee (remember, invoices can pre-printed on tablets with numbers already embedded) -- it would work for the electronic system as well. Just fill in the gap whenever you like with "plump deed"(whatever that is)
The point is -- you never need a gap free sequence for a primary key in your tables -- NEVER. You might have an attribute in that table that you want to follow some business rule -- FINE. But you don't have to serialize on it, you don't have to scale down for it, you can build something that works without it being a roadblock.
Alberto Dell'Era, October 16, 2004 - 2:11 pm UTC
>Still want to know what happens to the guys without computer that SPILL COFFEE.
They make an head dash for the Invoice Registry and mark that entry as "destroyed", and if they don't know the number, they immediately start some sort of cross-check to find out.
Such dedication is motivated by the Auditors being Military people (really, they wear uniforms and weapons during inspections) - with an incredible love for checking any "inconsistency" in accounting records, missing invoice numbers being their favourite check :(
So, a poor technician cannot discuss the requirement for no-gaps - that's not going to be dropped, S/He's more likely to be dropped instead ;)
October 16, 2004 - 3:37 pm UTC
fine -- we can mark things destroyed in the database too.
but at the end of the day -- these invoice numbers (every invoice I've sent out for freelance stuff is "#1" -- i've been thinking 42 would be better though) should be assigned as an ATTRIBUTE
I guess the problem is people using them as the primary key and an invoice being the parent record in a parent detail type of relationship -- leads to obvious issues with scaling.
The primary key should be something else.
The invoice number is a derived attribute -- after the fact. assign it IMMEDIATELY prior to committing, assign it shortly thereafter -- whatever. Do NOT assign it at the beginning of a 5 minute transaction (or even a 30 second or 5 second one!)
Alberto Dell'Era, October 16, 2004 - 5:00 pm UTC
>but at the end of the day -- these invoice numbers(every invoice I've sent out
>for freelance stuff is "#1" -- i've been thinking 42 would be better though)
>should be assigned as an ATTRIBUTE
But I agree, absolutely, it's another variation of the "sequence-generated versus real-life (name, surname, invoice numbers) pks" debate, and you won't find any db designed by me that doesn't use sequences for pks!
What I wanted to convey was simply that the "invoice number on the upper left corner of the first screen", even if technically a nonsense, and really just a placebo, it's something that very often the Brass wants, probably because they fear the Italian Accounting Airborne Marine Corps - and they don't even care to listen to technicians.
I'm well-known for pushing hard my (technical) point of view at meetings, to the point of fighting if the need arises ("Jeez you're persistent" is a phrase i hear often), but sometimes, all i can do is fall back, and think of ways to minimize the impact on my beloved dbs - AQ being the most-scalable solution i could think of - hence my "question" above.
I think we can sum up by saying that we share the same technical view - which is the most important thing on asktom I believe.
(btw i thought you preferred 65 or 68 as inos :)
October 16, 2004 - 5:49 pm UTC
I've recently re-read all of the "hitchhiker" books (got a single unabridged volume of them). 42 brings a grin remembering the stories.
Alberto Dell'Era, October 16, 2004 - 6:08 pm UTC
>I've recently re-read all of the "hitchhiker" books (got a single unabridged
>volume of them). 42 brings a grin remembering the stories.
Ahhh ... you're not alone - Dave Ensor, "Tales of the Oak Table", page 38 :)
October 16, 2004 - 6:30 pm UTC
LOL.
just did 17 of the Asimov books too -- the entire robot series and all of the foundation series and a couple other ones. This time around I'm keeping them as I read them ;)
Does Tom ever sleep ?
Kim Berg Hansen, October 18, 2004 - 5:19 am UTC
Let's see...
Tom works - presumable almost normal workday at Oracle...
Tom answers lots and lots of questions and reviews...
Tom gives seminars, teaches, etc...
Tom travels...
Tom spends time with family...
Tom reads lots of sci-fi (all my favorites :-)...
Is there time left over for sleep ? ;-)
Tom is SuperMan of the IT World !
OK, what about this
Christo Kutrovsky, October 20, 2004 - 12:29 pm UTC
I was thinking how I would implement the "printed invoices" in the database.
I guess the reason why people want to use this "gap free" number as a PK is because that's what everyone is using and they want to avoid having 2 "unique numbers" in the table and joining on a "separate" number. i.e. having to lookup the "generated" number, and not use the already unique 'invoice' number in detail tables.
Now as far for the solution. What if you just "pre-printed" the "gap free" numbers in the database, in a "preprinted" IOT table. Then when you need an invoice number you would select the min(gapfree) number that you can LOCK (with a select for update skip locked and a cursor).
Then if successfull, you would "delete" that row, and use this number. At the end of your transaction if you commit, then this "gapfree" number is deleted forever.
If you rollback, then the row is "returned" automatically, and the next person that needs a number would select it.
Every so often (say once a minute or once an hour) you would check how many numbers are "preprinted" and generate more as necessary. (you don't want to generate 1 million .. you just want ot be ahead by so many).
This is a tradeoff of "concurency and gap free numbers" versus "the numbers are assigned in the order received". Just because you have to have gapfree numbers doesn't mean they have to be sequencial, to the minute.
Does it really matter if you "submited" your invoice at 10:31am or 10:40am? Does it really matter if someone else submited invoices before you, and his invoice number is larger then yours ?
Think of it like that. You have 1 (one) book of invoice sheets. 10 people are back from a business trip and need to fillup invoices.
2 choices.
1. Pass arround the book of invoices to each person in sequence, allowing them to fillup all the invoices they need and submit them.
This will take quite a while, say 5 minutes per invoice, 3 invoices per person, that's 150 minutes (not counting the time to pass arround the book).
2. Have each person take a certain number of sheets, fill them up on their own, and then submit them.
This will take 15 minutes per person (again) but all of them will be doing it at the same time, thus total time = still 15 minutes.
Of course, some one could've taken more sheets then needed and return a few, or someone can make a mistake and scratch a sheet.
Not sure about returned sheets (the military tech guy can maybe fill in?) but for the scratched ones you need to mark them as "scratched".
To facilitate this, you can have an autonomus procedure that will take a number (from the same gapfree IOT table) but mark it as "scratched", commit the transaction, and then your session can re-update the field as "taken normally" or simply delete it. That way if your session fails for whatever resons, the "scratched" record will remain. (may need to rebuild the IOT every so often in this case, as you will likelly have 1 record per block for the remaining scratched records)
or you could have a "scratched" records table, where each number is writen (via autonomus transactions), and at the end of your session you just delete all scratched records for you session.
Then, if you really really need to submit them in order (don't see why but .. anyways) you can a "submit pending" status of your invoice (or whatever) until everyone who has taken a number before you submits (not that I see any benefit in this). But in this case you will have to implement some kind of timeout .. and it gets really complicated.
Everyone reading, please put your opinions, i am curious if this is going to ever help someone.
October 20, 2004 - 4:36 pm UTC
please don't use "skip locked" -- it is magic, it doesn't always work, it is not documented how exactly it works and it is subject to change.
you would
for x in (select ROWID rid from t order by whatever )
loop
select ... from t where rowid = x.rid for update nowait
if error, continue on
else you got it
end loop
skip locked
Christo Kutrovsky, October 21, 2004 - 10:10 am UTC
Thanks for pointing this out, i never noticed that "skip locked" is not in the documentation. It's something very convenient when implementing user based queues, that I took it as a natural extention.
Too bad. So the implementation will have a little more code.
October 21, 2004 - 2:42 pm UTC
user based queues are called AQ in Oracle.
42
Scot, October 22, 2004 - 2:13 pm UTC
"Ford, you're turning into a penguin. Stop it!"
Skip Locked
Steven Cooper, May 10, 2005 - 8:39 am UTC
Tom,
Is Skip Locked still unsupported / undocumented.
Metalink Note_id :1999556.8
Thanks
Steve
May 10, 2005 - 9:19 am UTC
yes
How to ...
Peter Turányi, June 28, 2005 - 9:13 am UTC
Hello,
I am not very wise of this discussion. :9)
But
1) "gap free numbers" is important in bussines
2) I agree with Tom that "doc id generation is the very very very last step". Doc number generation is last step too. Using Doc number as id is crazy - because of client can use same Doc number every year once or client can store two or more heap of documents with same Doc number marked by id_heap.
Our system using procedure to generate "gap free numbers".
1. try lock document counter
2. if counter free increment and store new value
and return value
3. if counter locked return error
Procedure is called by Pre-insert trigger.
So if counter is locked no document is inserted.
If Doc number is generated and no other problem then transaction is completed and counter is unlocked.
If Doc number is generated and document is rolled back then counter is rolled back too.
There is no posibility to delete inserted document.
I think that time to lock - increment - unlock counter is so short that concurency is no problem.
June 28, 2005 - 10:07 am UTC
1) no they are not. they are artifacts of a paper based system and even then, they didn't exist (spill coffee on a stack of invoices that haven't been used).
if you have an artificial artifact of a paper based system, it is something you can assign way after the fact, like when you close the books.
on a tiny system, perhaps your concurrency issues are not a problem, but on a real one, with any amount of load, well, I'd disagree.
Otherwise, if what you say were true, we'd never have to tune latching, or free buffer waits, or IO, or anything. They are all "small". but when you have lots of them, they add up