12 Replies Latest reply: Jul 6, 2012 4:14 AM by John Stegeman RSS

    Missing Primary Key

    782404
      Hi,

      I have this situation where sometimes the primary key is missing or jumps sequence during record saving. It happens occasionally but I do not know when and how it happens.
      The primary key is generated automatically by sequence. For example, last month after the primary key 351 is being saved into table CUSTOMER, the next record is using primary key number 400.

      Earlier this week, the situation happened again. Primary key 904 was recorded into CUSTOMER table. But, the next primary key was 950. I do not know where are primary keys from 905 until 949 went missing.

      I hope anyone could show me how to rectify this.


      Thank you. :-)
        • 1. Re: Missing Primary Key
          fjfranken
          user13281540 wrote:
          Hi,

          I have this situation where sometimes the primary key is missing or jumps sequence during record saving. It happens occasionally but I do not know when and how it happens.
          The primary key is generated automatically by sequence. For example, last month after the primary key 351 is being saved into table CUSTOMER, the next record is using primary key number 400.

          Earlier this week, the situation happened again. Primary key 904 was recorded into CUSTOMER table. But, the next primary key was 950. I do not know where are primary keys from 905 until 949 went missing.

          I hope anyone could show me how to rectify this.

          What database version ?
          What database type ( is it RAC )?

          Please post the syntax of the sequence and possibly an example INSERT statement

          And maybe look into this: http://lmgtfy.com/?q=oracle%20sequence%20cache%20gap

          Thanks
          FJFranken
          • 2. Re: Missing Primary Key
            Karan Kukreja
            Hi ,
            While the gurus reply , meantime just a possible suggestion :

            could it be that they are created but the records are deleted ?


            Also share the command with which you have created the sequence ..

            Regards
            Kk
            • 3. Re: Missing Primary Key
              fjfranken
              Kk wrote:
              Hi ,
              While the gurus reply , meantime just a possible suggestion :

              could it be that they are created but the records are deleted ?


              Also share the command with which you have created the sequence ..

              Regards
              Kk
              Didn't even think of that ...lol :-)
              • 4. Re: Missing Primary Key
                Hemant K Chitale
                Sequences used Cached values. If a sequence is unused for some time, the cached values may get aged out of the shared pool, resulting in a seeming "loss" of intermediate values (the next call to the sequence will cache values above those just lost).
                Cached values are also lost at shutdown.
                Another situation is when a sequence is incremented but the transaction is not committed but is instead rolled back (either manually or because of some other failure). The incremented sequence value does not get rolled back.

                Sequences are designed to give Unique names. By default, they are not intended to guarantee consecutive numbers.
                Even if you define a sequence as a NOCACHE sequence, you can still lose the last incremented value on a transaction rollback.


                note: A Sequence as a Primary Key is an artificial PK (a "synthetic PK") but is sometimes necessary.

                Hemant K Chitale

                Edited by: Hemant K Chitale on Jul 6, 2012 4:16 PM

                Edited by: Hemant K Chitale on Jul 6, 2012 4:17 PM
                • 5. Re: Missing Primary Key
                  782404
                  Hi,

                  The database version is Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

                  I am not sure whether it is RAC or others.

                  This is the syntax
                  CREATE SEQUENCE "CLIENT"."SEQ_CUSTOMER_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 13154 NOCACHE ORDER CYCLE ;

                  This is the insert statement. I insert the record using Hibernate

                  entityManager.persist(cust.addCustomer(authenticator.getUserLogon().getUserId(), "New Customer Application="+Long.toString(fid), "New Customer", ipAdd));

                  The records are not deleted.


                  It is very difficult to explain this to client.


                  Thank you
                  • 6. Re: Missing Primary Key
                    782404
                    Hi,

                    The database version is Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

                    I am not sure whether it is RAC or others.

                    This is the syntax
                    CREATE SEQUENCE "CLIENT"."SEQ_CUSTOMER_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 13154 NOCACHE ORDER CYCLE ;

                    This is the insert statement. I insert the record using Hibernate

                    entityManager.persist(cust.addCustomer(authenticator.getUserLogon().getUserId(), "New Customer Application="+Long.toString(fid), "New Customer", ipAdd));

                    The records are not deleted.


                    It is very difficult to explain this to client.


                    Thank you
                    • 7. Re: Missing Primary Key
                      782404
                      Hi,

                      The database version is Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

                      I am not sure whether it is RAC or others.

                      This is the syntax
                      CREATE SEQUENCE "CLIENT"."SEQ_CUSTOMER_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 13154 NOCACHE ORDER CYCLE ;

                      This is the insert statement. I insert the record using Hibernate

                      entityManager.persist(cust.addCustomer(authenticator.getUserLogon().getUserId(), "New Customer Application="+Long.toString(fid), "New Customer", ipAdd));

                      The records are not deleted.


                      It is very difficult to explain this to client.


                      Thank you
                      • 8. Re: Missing Primary Key
                        fjfranken
                        user13281540 wrote:
                        Hi,

                        The database version is Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

                        I am not sure whether it is RAC or others.

                        This is the syntax
                        CREATE SEQUENCE "CLIENT"."SEQ_CUSTOMER_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 13154 NOCACHE ORDER CYCLE ;
                        OK, no cache.
                        When using RAC it is known that nodes are not aware of the other nodes sequence values.

                        With this information I stick to the explanation given by Hermant.
                        You can use that to explain the client

                        Success!!
                        FJFranken
                        • 9. Re: Missing Primary Key
                          John Stegeman
                          Oracle sequences will always and forever have gaps
                          Oracle sequences will always and forever have gaps
                          Oracle sequences will always and forever have gaps
                          Oracle sequences will always and forever have gaps
                          Oracle sequences will always and forever have gaps

                          for various reasons:

                          caching and aging out of the shared pool
                          rollbacks

                          you CANNOT eliminate gaps in an Oracle sequence.

                          The only way to get a gapless series of numbers is to serialize (i.e. only allow one transaction at a time.
                          • 10. Re: Missing Primary Key
                            782404
                            Thank you everyone.

                            Perhaps the next database design would still be able to retain the primary key sequence whatever happens.


                            Thanks. :)
                            • 11. Re: Missing Primary Key
                              gopi130
                              Hi,


                              for example you try to insert using this statement SEQ_CUSTOMER_ID.NEXTVAL not commit the statement.then you put rollback the statement but your sequence generated nex value not rollback the sequences order.
                              another reason you select sequence but some error will come that time also sequences increaase
                              • 12. Re: Missing Primary Key
                                John Stegeman
                                the ONLY way to eliminate gaps is to serialize.

                                By the way: if you insert into this table frequently, getting rid of NOCACHE on your sequence will speed things up. As you have seen, even NOCACHE cannot eliminate gaps.