This content has been marked as final. Show 11 replies
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'.
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.
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.
kev374 wrote: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.
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.
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.
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.
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.
kev374 wrote:That's not how oracle itself keeps track of the order of transactions .... it uses a sequential number, aka SCN.
. . . 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.Can you spell 'backlog'?
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