This discussion is archived
11 Replies Latest reply: Sep 3, 2013 6:41 AM by Rahul_India RSS

Best practices with sequences and primary keys

606667 Newbie
Currently Being Moderated
We have a table of system logs that has a column called created_date. We also have a UI that displays these logs ordered by created_date. Sometimes, two rows have the exact same created_date down to the millisecond and are displayed in the UI in the wrong order. The suggestion was to order by primary key instead since the application uses an oracle sequence to insert records so the order of the primary key will be chronological. I felt this may be a bad idea as a best practice since the primary key should not be used to guarantee chronological order although in this particular application's case, since it is not a multi-threaded environment, it will work so we are proceeding with it.

The value for the created_date is NOT set at the database level (as sysdate) but rather by the application when it creates the object which is persisted by Hibernate. In a multi-threaded environment, thread A could create the object and then get blocked by thread B which is able to create the object and persist it with key N after which control returns to thread A it persists it with key N+1. In this scenario thread A has an earlier timestamp but a larger key so will be ordered before thread B which is in error.

I like to think of primary keys as solely something to be used for referential purposes at the database level rather than inferring application level meaning (like the larger the key the more recent the record etc.). What do you guys think? Am I being too rigorous in my views here? Or perhaps I am even mistaken in how I interpret this?
  • 1. Re: Best practices with sequences and primary keys
    rp0428 Guru
    Currently Being Moderated
    >
    I like to think of primary keys as solely something to be used for referential purposes at the database level rather than inferring application level meaning (like the larger the key the more recent the record etc.). What do you guys think? Am I being too rigorous in my views here? Or perhaps I am even mistaken in how I interpret this?
    >
    You will have the same 'multi-user' issue regardless of what you use to indicate the order and regardless of whether the value is part of a key or not.

    It all begs the question: what does it mean to be 'FIRST' to insert a record? The first user to grab a sequence number? The first user to COMMIT regardless of the sequence number?

    One user can insert row at 2pm, not commit until 3:30pm, and consume sequence numbers for the rows. Another user could insert at 3pm and commit. The second set of sequence numbers will be higher than the first set but the records committed first.

    Which set of data was 'FIRST' and needs to be reported on first.

    There is nothing wrong with using a SEQUENCE value to order data. That fact that it is also a primary key is rather irrevelant. The more important attribute for a primary key is that it should not be a 'data item': that is, it should not have a valuel that can be interpreted as being 'incorrect' and that needs to be 'corrected'.

    A social security number, or employee id can be entered incorrectly and would then need to be updated the the 'correct' value. Thus, IDs like that should not be used for primary key or referential integrity purposes.

    A sequence number has no meaning to anyone as a value. The value 7 is no more significant than the number 20573. Once assigned and entered it is not 'wrong' and does not need to 'corrected'.
  • 2. Re: Best practices with sequences and primary keys
    606667 Newbie
    Currently Being Moderated
    yes, I realize that the threading issue will be present in a multi-threaded situation which is what my example highlighted :) My point was that this is not a multi-threaded situation so is it reasonable to use primary keys this way, I know it will work - but this is solely from a "best practice" standpoint.
  • 3. Re: Best practices with sequences and primary keys
    rp0428 Guru
    Currently Being Moderated
    Gee - I thought I answered that rather extensively. Maybe you replied before my entire answer had been posted?
  • 4. Re: Best practices with sequences and primary keys
    606667 Newbie
    Currently Being Moderated
    yeah, I saw only the first line of your response!! All I was asking was if one can safely infer chronological order from a primary key and if there is anything wrong with doing this.
  • 5. Re: Best practices with sequences and primary keys
    rp0428 Guru
    Currently Being Moderated
    >
    yeah, I saw only the first line of your response!! All I was asking was if one can safely infer chronological order from a primary key and if there is anything wrong with doing this.
    >
    No - 'chronological order' is the very essence of a sequence. For a single-threaded use the values will keep increasing in order until or unless the sequence hits the maxvalue and CYCLEs back to the start.
  • 6. Re: Best practices with sequences and primary keys
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    kev374 wrote:
    All I was asking was if one can safely infer chronological order from a primary key and if there is anything wrong with doing this.
    If the primary key is a sequence - No (not safe) and yes (it is wrong). As using a sequence is the wrong value/type to use - as rp0428 explained pretty well.

    Question is what does the data model say? What does the nature of that entity's unique identification say? This drives how the entity looks like as persistent data in the data layer. This decides on what the primary key is, and what it contains.

    Inferring logic from a primary key (such as creation sequence) is invariable wrong. Logic should be expressed implicitly - Assumption is the Mother of the Fubar kids.
  • 7. Re: Best practices with sequences and primary keys
    606667 Newbie
    Currently Being Moderated
    Yes, that is what I was thinking as well. A primary key may be incrementally inserted using a sequence but I don't think it's correct to automatically ASSUME that a record with a higher primary key value was created AFTER a record with a lower primary key value even if it works that way currently. If a UI module displays events using the primary key order to mean chronological order then that assumption can be broken by another module added to the application later in time which may be multi-threaded and break this assumption :) I think the chronological order of records should be using a timestamp (i.e. "order by created_date desc" etc.) but perhaps the mid-tier should put in a millisecond delay so two records don't have the same timestamp to the millisecond.
  • 8. Re: Best practices with sequences and primary keys
    John Spencer Oracle ACE
    Currently Being Moderated
    In your first post you said
    Sometimes, two rows have the exact same created_date down to the millisecond and are displayed in the UI in the wrong order.
    Given that the dates are exactly the same, how do you determine that they are in the "wrong" order? Perhaps you can add whatever that is to the orfer by condition.

    John
  • 9. Re: Best practices with sequences and primary keys
    EdStevens Guru
    Currently Being Moderated
    kev374 wrote:
    . . . I think the chronological order of records should be using a timestamp
    That's not how oracle itself keeps track of the order of transactions .... it uses a sequential number, aka SCN.

    (i.e. "order by created_date desc" etc.) but perhaps the mid-tier should put in a millisecond delay so two records don't have the same timestamp to the millisecond.
    Can you spell 'backlog'?
  • 10. Re: Best practices with sequences and primary keys
    rp0428 Guru
    Currently Being Moderated
    >
    I think the chronological order of records should be using a timestamp (i.e. "order by created_date desc" etc.)
    >
    Not that old MYTH again! That has been busted so many times it's hard to believe anyone still wants to try to do that.

    Times are in chronological order: t1 is earlier (SYSDATE-wise) than t2 which is earlier than t3, etc.

    1. at time t1 session 1 does an insert of ONE record and provides SYSDATE in the INSERT statement (or using a trigger).

    2. at time t3 session 2 does an insert of ONE record and provides SYSDATE
    (which now has a value LATER than the value used by session 1) in the INSERT statement.

    3. at time t5 session 2 COMMITs.

    4. at time t7 session 1 COMMITs.

    Tell us: which row was added FIRST?

    If you extract data at time t4 you won't see ANY of those rows above since none were committed.

    If you extract data at time t6 you will only see session 2 rows that were committed at time t5.

    For example if you extract data at 2:01pm for the period 1pm thru 1:59pm and session 1 does an INSERT at 1:55pm but does not COMMIT until 2:05pm your extract will NOT include that data.

    Even worse - your next extract wll pull data for 2pm thru 2:59pm and that extract will NOT include that data either since the SYSDATE value in the rows are 1:55pm.

    The crux of the problem is that the SYSDATE value stored in the row is determined BEFORE the row is committed but the only values that can be queried are the ones that exist AFTER the row is committed.




    About the best you, the user (i.e. not ORACLE the superuser), can do is to

    1. create the table with ROWDEPENDENCIES
    2. force delayed-block cleanout prior to selecting data
    3. use ORA_ROWSCN to determine the order that rows were inserted or modified

    As luck would have it there is a thread discussing just that in the Database - General forum here:
    ORA_ROWSCN keeps increasing without any DML
  • 11. Re: Best practices with sequences and primary keys
    Rahul_India Journeyer
    Currently Being Moderated

    Oh Great.So we should not use  modified_date columns for the chronological order

Incoming Links

Legend

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