Sequences, cache and rollback

[Former Member]

Hello all,

i am using a sequence number to autoincrement invoices number in a small app i am developing but i have detected that inside a transaction if i have an error and i do a rollback the sequence number is not reset to the old value, searching in the old maxdb mailing list i have seen to that cache sequence value in a database crash can be jumped so i suppose it is not a good idea use sequence numbers to any column that need to be really secuencial without jumps, anyone has any idea better that do before the new insert an "select MAX(column) from table" before?

[lbreddemann]

Hi Soraya,

I thought this discussion had been ended and burried deeply a long, long time ago

(you may want to check this brilliant thread at asktom <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4343369880986">Sequences</a>)

Ok, let's get through this once more...

Why do you really, really need a number that is counted up without any gaps?

What is the exact business requirement for this?

Usually there is no written-in-stone requirement for such a number. People think it is, but in reality it's no problem to have gaps within the number ranges.

What happens in a office when somebody put his coffe cup just on the current invoice form that already has the number preprinted? Nobody would even consider to "reuse" this number.

It doesn't matter at all - the invoice is just written on the next form.

If you really need such a number, than there's no way to avoid serialisation on a single point: your number generator - in your case the select max(column) - query.

But this approach is the way to hell if you want to have some kind of scability and performance in your system. It will make your multiuser-highperformance-enterprise-database a single-user-MS ACCESS-like thing. Don't go down this road - use the force, luke!

With a sequence on the other hand you get gaps - that's sure. But you get the number VERY quickly and can be sure that these numbers are unique.

MaxDB makes it very easy to use such sequences - just define the DEFAULT value of your , say ID field to be SEQUENCE.

All you have to do now is to use the DEFAULT keyword for the ID field whenever you create an invoice. The database will do the rest for you.

Details on this can be found in the documentation: <a href="http://maxdb.sap.com/currentdoc/d0/7638a1d21c11d2a97400a0c9449261/content.htm">DEFAULT Specification</a>

Best regards,

Lars

[Former Member]

i am using in the same table a serial to the primary key, i know i dont need a number without gaps there but in the invoice number i need it because law in Spain, if i have gaps in invoice numbers i will have to have a good excuse, like an office that was burn to the ground.

[lbreddemann]

HI Sorarya,

then there is no other way than having the number stored somewhere and checked it check time you want to create an invoice entry.

Be cautious about the select max(id)... approach.

What will happen if you run two invoice creation on in the same time?

T1 ---- get max() =10 -------- fill in invoice data --- commit
T2 -------- get max () = still 10 ---- fill in invoice data --- commit => duplicate invoice number!

Therefore I think a better option would be to actually store this number in a specific table.

Anytime a process needs a number, it will have to increase/update it as well.

When the increase/update of the number is done in the same transaction as the insertion of the invoice data, then of course all other invoice creation will have to wait until the number-table is available again.

BUT: you are on the safe side concerning the gap-freeness of your numbers.

Hope that helps anyway.

Lars