I inherited our production RAC, and like everything else surprises appear from time to time. It’s just the nature of the job.
The application the database supports utilize sequence numbers for primary key. Which is not idea since there are no natural primary keys defined. The use of the sequence isn’t the real issue, the fact that they are used for ordering is. On the surface sequence defined with order are selected in an incrementing fashion. So the latest one should be the highest number, but not necessarily. And it’s the not necessarily that is the gotcha.
In a single instance database the sequence appear to have a nice order. Sequence request are occurring from only one instance. Unless the process (or multiple processes) are firing off request for sequences chances are the higher the sequence number the newer the record. So if you have an application that is receiving say receipts from end users and assigning a sequence number it may be safe to assume the higher number is the latest if there was a re-submission. Notice the use of “safe to assume” — it’s not a guarantee. However, in a RAC environment the chances that the sequences are not nicely ordered with the highest being the newest record receive even if ordered is set on the sequence How is this possible?
There are at least one instance, but more than likely more than one instance performing the same function. Now imagine the request for a sequence occurring from more than one instance. Each of these instances cache the sequence (avoid performance implications do not set the nocache option). So now if there is a re-submission the possibility that it occurs from a different instance that has a lower sequence number cached is possible.
A sequence is nothing more than a number generated by Oracle. We can provide some order but we can’t guarantee order. A sequence has no meaning it’s a number that is given meaning within the application by being assigned to a record within a table. It tells us nothing about the record. It’s just a number. So why would use this number to order the records by? To order records we need an attribute that has meaning. An attribute that definitely identifies a record as being the newest, or oldest. An attribute that tells us we received this record before we received the other record or vice versa. So what type of attribute would provide us order?
TIMESTAMP. A timestamp has the meaning that is needed when ordering a group of records based on when they were received. Now when we have two records with all the same identification from an end-user, and we need to ensure we use the latest one submitted we can guarantee this by sorting on a timestamp that identifies when it was received. An entity that provides for critical data based on submission, say refunds we need to ensure that we have the latest receipt. We can only provide this guarantee by using a timestamp.
Sequences have provided developers with a false sense of security that they can order by the sequence and guarantee the order is correct. In a single instance database this may have provided for the illusion that sequences guaranteed order. However, with a RAC implementation this illusion meets reality.
It’s not a RAC issue, it’s a design issue. It doesn’t just occur within RAC. A misunderstanding of sequences leads to the wrong attribute used for ordering whether RAC or single instance.