Skip to Main Content
  • Questions
  • Document Number Generation - LastNumber Updation - Locking problem

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Janaki.

Asked: October 13, 2004 - 10:49 am UTC

Answered by: Tom Kyte - Last updated: June 28, 2005 - 10:07 am UTC

Category: Database - Version: 9.2.0.1.0

Viewed 1000+ times

You Asked

Dear Sir,

We have a User Interface where "Document Number Generation" info is captured with the following info - Transaction, DocNumGenCode, Prefix, Suffix, effective From Date, Effective To Date.
We maintain these captured information in a DocNoGen Table along with "LastNumberGenerated".
Sample Data:
Trans ="Issue",DocGenCode="Code1",Prefix="ISU_",Suffix = "_2004",
Eff_From_Date = "01/01/2004", Eff_To_Date = "31/12/2004",
LastNumberGenerated = 0

Whenever a transaction is created, a DocNumGenCode will be selected.
Based on the selected DocNumCode and the transaction date, we pick up the prefix, suffix and generate the document no with "Prefix + [(LastNumberGen + 1) stuffed with reqd Zeroes] + Suffix". And then we update the LastNumber for that DocGenCode.
Sample Transaction:
Issue Transaction Creation Steps:
a)Selected DocGenCode is "Code1",
b)Entered issue details
c)Clicked "CREATE". System generated IssueNo as "ISU_000001_2004"

PROBLEM:
This 'DocNoGen' table gets frequently locked due to the updation of LastNumber column which happens during the click of "CREATE" button. And when the wait time for the lock release is more [for transactions having lot of process], we get "Transaction Timed Out" Errors in other transactions that are waiting for the lock to be released.

WORKED OUT OPTIONS AND THEIR RESULT:
Since the transaction scope for the entire CREATE transactions cannot be shortened due to complex logics and functionalites, we used PRAGMA AUTONOMOUS transaction in the common SP where we updated "Last Generated Number". This worked. BUT, if there is any error thrown subsequent to lastnumber generation, the lastnumberGen would be updated but document won't get created. As such, the document number generated would be discontinous.
For e.g., after "ISU_000001_2004", while creating another issue transaction, if any application error is thrown, system will update the lastnumber as 2. Next time, when issue is created successfully, "ISU_000003_2004" would be generated.

We tried using SEQUENCE NUMBER concept, that again generates discontinous number during the ERROR cases.

Customer is specific about the document number to be generated in a continous way and at the same time he wants the system to work without problems in concurrent mode.

Could you please suggest any other possible work out...

Thanks a lot (in advance),
KRT, Chennai


and we said...

<quote>
This 'DocNoGen' table gets frequently locked due to the updation of LastNumber
column which happens during the click of "CREATE" button.
</quote>

Yah, no kidding -- that should be somewhat "an obvious side effect of trying in vain to give sequential numbers to something as an artifact of some paper based system from the 1940's"

<quote>
we used PRAGMA AUTONOMOUS
transaction in the common SP where we updated "Last Generated Number". This
worked. BUT, if there is any error thrown subsequent to lastnumber generation,
the lastnumberGen would be updated but document won't get created.
</quote>

Not only that -- but hey, what if the end user decided to terminate the transaction, or just never commit it.

So, if you want, need, demand for whatever "truly justified business reason" -- then you have to live with "we all wait in a single line for the electronic equivalent of a little old lady sitting at the 'assign document numbers' desk to assign document numbers one by one" (and when she goes to lunch (locks) or whatever, we'll patiently wait...."


If your business needs actually turn out to be "oh, well, we used to do it like that but there really isn't any business reason -- just makes us 'feel good about ourselves' -- then you can use things that go fast -- like sequences. Your "autonomous transaction" is nothing more than a "really slow implementation of an oracle sequence"


(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.


So, customer will have to live with (to me anyway) the *obvious* issue of locking.

(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)


and you rated our response

  (18 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Moving Docno generation to the end

October 14, 2004 - 4:04 am UTC

Reviewer: Janaki from Chennai, India

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..]


Tom Kyte

Followup  

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

October 15, 2004 - 2:19 am UTC

Reviewer: Janaki from Chennai

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.

Tom Kyte

Followup  

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

October 15, 2004 - 11:31 am UTC

Reviewer: sPh from Saint Louis, Missouri USA

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

Tom Kyte

Followup  

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

October 15, 2004 - 4:35 pm UTC

Reviewer: David Aldridge from Colorado Springs

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.

Tom Kyte

Followup  

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

October 16, 2004 - 10:16 am UTC

Reviewer: Janaki from Chennai

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


Tom Kyte

Followup  

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

October 16, 2004 - 12:16 pm UTC

Reviewer: Alberto Dell'Era from Milan, Italy

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 :)

Tom Kyte

Followup  

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!!)

October 16, 2004 - 12:30 pm UTC

Reviewer: Serge Shmygelsky from Ukraine

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?

Tom Kyte

Followup  

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.

October 16, 2004 - 1:20 pm UTC

Reviewer: Serge Shmygelsky from Ukraine

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

October 16, 2004 - 1:29 pm UTC

Reviewer: Oleksandr Alesinskyy from Germany

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.

Tom Kyte

Followup  

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.

October 16, 2004 - 2:11 pm UTC

Reviewer: Alberto Dell'Era from Milan, Italy

>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 ;)

Tom Kyte

Followup  

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!)

October 16, 2004 - 5:00 pm UTC

Reviewer: Alberto Dell'Era from Milan, Italy

>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 :)

Tom Kyte

Followup  

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.

October 16, 2004 - 6:08 pm UTC

Reviewer: Alberto Dell'Era from Milan, Italy

>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 :)

Tom Kyte

Followup  

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 ?

October 18, 2004 - 5:19 am UTC

Reviewer: Kim Berg Hansen from Middelfart, Denmark

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

October 20, 2004 - 12:29 pm UTC

Reviewer: Christo Kutrovsky from Ottawa, ON Canada

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.



Tom Kyte

Followup  

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

October 21, 2004 - 10:10 am UTC

Reviewer: Christo Kutrovsky from Ottawa, ON Canada

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.

Tom Kyte

Followup  

October 21, 2004 - 2:42 pm UTC

user based queues are called AQ in Oracle.

42

October 22, 2004 - 2:13 pm UTC

Reviewer: Scot from Florida

"Ford, you're turning into a penguin. Stop it!"

Skip Locked

May 10, 2005 - 8:39 am UTC

Reviewer: Steven Cooper from England

Tom,

Is Skip Locked still unsupported / undocumented.

Metalink Note_id :1999556.8

Thanks
Steve

Tom Kyte

Followup  

May 10, 2005 - 9:19 am UTC

yes

How to ...

June 28, 2005 - 9:13 am UTC

Reviewer: Peter Turányi from Slovakia

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.

Tom Kyte

Followup  

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