1 2 Previous Next 23 Replies Latest reply: Oct 2, 2009 7:20 AM by 6363 RSS

    sequence numbers - with no gaps!

    ChrisS.
      I seek some advice from anyone with first hand experience in this area.

      My need is simple; generate a new ID number for every order for use as the primary key.

      Obviously this simple exercise could be a bottleneck as volumes ramp up, so I was pleased to find the Oracle feature to generate such numbers (efficiently presumably). However, upon closer reading it was apparent that aborted transactions would result in the loss of the number allocated to to that order if another transaction had obtained the next sequence number already (as one would want it to, rather than serializing transactions).

      I don't want to have gaps in the sequence as the system needs a high degree of auditability. I am thinking of utilising the Autonomous Transaction feature to write the newly acquired sequence number to the order file with a flag indicating that the record is incomplete, and that record is to be skipped by normal system applications (but available to audit reports which will interpret the reason correctly), then returning to the far more extensive main transaction which updates many tables.

      The other alternative is to control sequence numbers within the application itself, or try to get the sequence number as the very last step before writing the new order record, but this probably won't guarantee gapless reliability either. The ID itself has no significance, and it doesn't matter if an ID was used out of strict date/time sequence, so I really don't want to execute these updates serially.

      So, my question is this:
      a) do most people use the builtin sequence number feature?
      b) if so, is there a standard technique for avoiding or accounting for gaps?
      c) if the application must take over this task, should the counter for the last used ID be in a single row table to minimise bottlenecks caused by the thisrecord being locked? There will be several such counters for different aspects of the application, and i thought of having each in its own row in a small table, but I'm concerned about the whole table getting locked and freezing out otherwise-unrelated updates.

      Thanks for any advice you may have.
      CS
        • 1. Re: sequence numbers - with no gaps!
          Toon Koppelaars
          user8821725 wrote:
          I don't want to have gaps in the sequence as the system needs a high degree of auditability.
          So you are implying that "high degree of auditability" implies (requires) "gapless sequence keys"?
          I do not see this correlation.
          The ID itself has no significance, and it doesn't matter if an ID was used out of strict date/time sequence, so I really don't want to execute these updates serially.
          Well here you have it. You now say "no significance". Well then were is the correlation with auditability?
          So, my question is this:
          a) do most people use the builtin sequence number feature?
          Yes.
          b) if so, is there a standard technique for avoiding or accounting for gaps?
          No. And everything you "build-yourself", will be flawed (buggy and/or causing serialization points you do not want).
          c) if the application must take over this task, should the counter for the last used ID be in a single row table to minimise bottlenecks caused by the thisrecord being locked? ...
          Sorry, no further comments.
          • 2. Re: sequence numbers - with no gaps!
            Hemant K Chitale
            Gap-Free Sequences as a requirement for Auditability has been addressed frequently in forums, email lists, discussion groups, asktom.oracle.com and metalink.


            Imagine Paper Invoices :
            a. Hand Written Invoices : What happens if the clerk writing out the invoice makes a mistake (writes the wrong product code/name and/or wrong price) such that he has to "tear up and throw away" the invoice ? You have a "gap" in invoices sent to customers. This gap can be explained and auditors can understand it.
            b. Printed : The paper jams as it comes out of the printer and has to be discarded (or the printer fails and doesn't print the invoice at all). Can this be explained and understood ?


            So why can't Oracle Sequences as Invoice numbers be explained ? Most auditors should be happy with the explanation "the system generates the invoice numbers and guarantees uniqueness".

            Potential for collusion and fraud does exist. But management takes a judgement call on the cost of alternatives.


            Hemant K Chitale
            • 3. Re: sequence numbers - with no gaps!
              ChrisS.
              I'm obviously from a different era than the previous respondents. We used to write systems with reprint functions specifically to avoid paper jams from consuming things we wished to account for. And hand-written invoices? I thought IT was supposed to be an improvement over those processes, so they ought not be the benchmark. We used to write systems that would generate sequence numbers, and we did it well enough.

              I working with a team who has the job of upgrading a system I wrote 25 years ago and which has run continuously since, at multiple sites, using nothing but Cobol and ISAM files. Which is not to say I prefer re-inventing code, or wouldn't have loved to have the luxury of fantastic feature-rich products like Oracle and ApEx back in the early 80s. But if they fell short of the firm mandatory requirement then we'd have to fall back on our assembler language skills or whatever we did in those days.

              But I think that fobbing off auditors who would like to know whether the missing number was caused by a legitimate rollback or a hacker or rogue user is less easily done when you are accounting for the extra nuclear missile which you are almost certain should be here somewhere, than if we were selling widgets.

              Thanks anyway
              • 4. Re: sequence numbers - with no gaps!
                Hemant K Chitale
                If you are building 100 nuclear missiles in a year, you do want Gap Free Numbering.

                If you are generating a million invoices in a year, you do not want the performance overheads of any workarounds that attempt to guarantee Gap Free Numbering.

                As I said, the Business has to make a judgement call. The General in charge of Nuclear Missiles knows as much about Nuclear Missiles as the VP in charge of Sales of Widgets knows about Widgets. Each makes his own decision, to meet his business requirements.


                Hemant K Chitale
                • 5. Re: sequence numbers - with no gaps!
                  Sven W.
                  user8821725 wrote:
                  I seek some advice from anyone with first hand experience in this area.

                  My need is simple; generate a new ID number for every order for use as the primary key.

                  Obviously this simple exercise could be a bottleneck as volumes ramp up, so I was pleased to find the Oracle feature to generate such numbers (efficiently presumably). However, upon closer reading it was apparent that aborted transactions would result in the loss of the number allocated to to that order if another transaction had obtained the next sequence number already (as one would want it to, rather than serializing transactions).

                  I don't want to have gaps in the sequence as the system needs a high degree of auditability.
                  One of the very very few reasons for me to have a requirement for "gapless" sequences is if you have a kind of checking mechanism outside of the database that is implemented using this gapless number. I had such a requirement once, when exchanging files with another bank where we had to ensure that each file was transferred correctly and in the right order. Your "high degree of auditability" might be a similiar requirement. Just remember as long as you are still inside the database you can use other mechanisms to increase auditability.
                  I am thinking of utilising the Autonomous Transaction feature to write the newly acquired sequence number to the order file with a flag indicating that the record is incomplete, and that record is to be skipped by normal system applications (but available to audit reports which will interpret the reason correctly), then returning to the far more extensive main transaction which updates many tables.
                  Hm. I don't see why you want to use Autonomous Transactions for this. It might be useful to lessen locking conflicts , depending how your future sequence mechanism will be built.

                  >
                  The other alternative is to control sequence numbers within the application itself, or try to get the sequence number as the very last step before writing the new order record, but this probably won't guarantee gapless reliability either. The ID itself has no significance, and it doesn't matter if an ID was used out of strict date/time sequence, so I really don't want to execute these updates serially.
                  I would go for generating the sequence as the very last step. It needs to be set to NOCACHE of cause. This will garantee that the sequence fetched from mySeq.nextval is gapless. The problem is then in your application. Your application must be built in a way that it ensures to use this sequence, or at least to create a noticable alert when this generated sequence can't be used anymore. In essence this means. Each unhandled (database) error that happens after you fetched the next sequence value must be raised in a way that informs all relevant people about this error.

                  Example: It might be that you use the sequence to write an order file. What happens if halfway through writing the file your disk runs out of space? This can be such an issue where a sequenced value will be lost. Your organisation must take care of those things.

                  >
                  So, my question is this:
                  a) do most people use the builtin sequence number feature?
                  Yes. But for some tasks a sequence is not the best way, for example if you want a numbering for each item of your order. This "sequence" needs to restart with every new order.
                  b) if so, is there a standard technique for avoiding or accounting for gaps?
                  NOCACHE => Avoids gaps during generation
                  Database Trigger => Tries to use the sequence as late as possible
                  c) if the application must take over this task, should the counter for the last used ID be in a single row table to minimise bottlenecks caused by the thisrecord being locked? There will be several such counters for different aspects of the application, and i thought of having each in its own row in a small table, but I'm concerned about the whole table getting locked and freezing out otherwise-unrelated updates.
                  This is a possibility and the issues you describe are why we avoid such a solution in general. Autonomous transactions help to reduce the locking conflict, but they increase the chance that you produce gaps.

                  >
                  Thanks for any advice you may have.
                  CS
                  Oracle can guarantee to produce gapless sequences. But it can't guarantee that your application uses such a sequence in a gapless way.
                  • 6. Re: sequence numbers - with no gaps!
                    twrz
                    Hemant K Chitale wrote:
                    Gap-Free Sequences as a requirement for Auditability has been addressed frequently in forums, email lists, discussion groups, asktom.oracle.com and metalink.


                    Imagine Paper Invoices :
                    a. Hand Written Invoices : What happens if the clerk writing out the invoice makes a mistake (writes the wrong product code/name and/or wrong price) such that he has to "tear up and throw away" the invoice ? You have a "gap" in invoices sent to customers. This gap can be explained and auditors can understand it.
                    b. Printed : The paper jams as it comes out of the printer and has to be discarded (or the printer fails and doesn't print the invoice at all). Can this be explained and understood ?


                    So why can't Oracle Sequences as Invoice numbers be explained ? Most auditors should be happy with the explanation "the system generates the invoice numbers and guarantees uniqueness".

                    Potential for collusion and fraud does exist. But management takes a judgement call on the cost of alternatives.


                    Hemant K Chitale
                    Not at all. There are countries, in which law regulations do not allow making gaps in invoices numeration.
                    • 7. Re: sequence numbers - with no gaps!
                      Hemant K Chitale
                      Even a NOCACHE sequence doesn't guarantee Gap Free Numbers.

                      You'd need to add more checking in the application code OR write a seperate "check invoice numbers" procedure to periodically "audit" the numbers.
                      • 8. Re: sequence numbers - with no gaps!
                        21205
                        Database Trigger => Tries to use the sequence as late as possible
                        .. but a database trigger can fire multiple times in the same transaction

                        http://tkyte.blogspot.com/2005/08/something-different-part-i-of-iii.html

                        Edited by: Alex Nuijten on Oct 1, 2009 9:43 AM
                        • 9. Re: sequence numbers - with no gaps!
                          Boneist
                          If your primary goal is auditing, then purely having gap-free sequences is not going to fit that aim.

                          Instead, you should be using one or more of the many methods already provided by Oracle to audit inserts/deletes/etc on the tables you're interested in. Your database application should be secure, so that only approved methods can do the amendments, etc.

                          Gap free sequences? Not an audit requirement at all, as far as I'm concerned.
                          • 10. Re: sequence numbers - with no gaps!
                            BluShadow
                            user8821725 wrote:
                            So, my question is this:
                            a) do most people use the builtin sequence number feature?
                            Yes
                            b) if so, is there a standard technique for avoiding or accounting for gaps?
                            People have tried techniques, but there is no method that can 100% guarantee gap free sequences.
                            You can create methods for re-use of numbers for records that have been deleted or where a gap may have occured due to error or system crash, but at any single point in time there is still a chance that there may be a gap.
                            c) if the application must take over this task, should the counter for the last used ID be in a single row table to minimise bottlenecks caused by the thisrecord being locked? There will be several such counters for different aspects of the application, and i thought of having each in its own row in a small table, but I'm concerned about the whole table getting locked and freezing out otherwise-unrelated updates.
                            The point of sequences is to prevent the locking issues that occur when people try and do this manually using their own sequence number table. Good design should always use sequences and not try and implement a table record method of it.
                            • 11. Re: sequence numbers - with no gaps!
                              6363
                              twrzodak wrote:
                              >
                              Potential for collusion and fraud does exist. But management takes a judgement call on the cost of alternatives.
                              Not at all. There are countries, in which law regulations do not allow making gaps in invoices numeration.
                              Which countries?
                              • 12. Re: sequence numbers - with no gaps!
                                6363
                                user8821725 wrote:

                                So, my question is this:
                                a) do most people use the builtin sequence number feature?
                                Yes.
                                b) if so, is there a standard technique for avoiding or accounting for gaps?
                                No, the only way to have no gaps is to use a counter and lock a table and essentially build a system that supports a maximum of one user at a time. Putting technical concepts aside storing a row counter in a table is non-relational and an inherently inefficient concept that has no efficient technical solution.
                                c) if the application must take over this task, should the counter for the last used ID be in a single row table to minimise bottlenecks caused by the thisrecord being locked? There will be several such counters for different aspects of the application, and i thought of having each in its own row in a small table, but I'm concerned about the whole table getting locked and freezing out otherwise-unrelated updates.
                                It doesn't matter if a gap free counter is a mandatory requirement the system will be inefficient and will scale to only a single user.
                                • 13. Re: sequence numbers - with no gaps!
                                  twrz
                                  Pointless wrote:

                                  Which countries?
                                  e.g. Poland, but it's only digression in this discussion, a proof of a need to sometimes implement continuous numbering, without gaps
                                  • 14. Re: sequence numbers - with no gaps!
                                    Marwim
                                    Three months ago there has been a similar discussion

                                    Re: consecutive numbers

                                    A (in my opinion) valid argument has been that e.g. a legally necessary gapless invoice number does not mean that the internal ID has to be gapless.

                                    Regards
                                    Marcus
                                    1 2 Previous Next