This discussion is archived
12 Replies Latest reply: Jul 6, 2012 2:14 AM by John Stegeman RSS

Missing Primary Key

782404 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points