Alternative to sequence and timestamp: uniquely ordering records in time

Question

Oracle 11gR2 Exadata

I'm required to uniquely identify when records are created in time. Sequence caching means I cannot use a sequence-based ID and batching inserts means that all records inserted in one batch will have the same timestamp value (even using TIMESTAMP(9)). Akin to Twitter's since_id concept.

The best alternatives I've ideated so far

• creating an additional sequence used for each unique timestamp
• not batching inserts of the records to force a unique timestamp for each record
• not caching the sequence, although there has been some discussion that this won't solve the problem under Exadata

Here's my requirement: I have an API that allows users to supply a sequence as a marker and request all records since that time. For example, they request 1000 records with a marker of 7 and they'll get 1000 records from my table with an ID greater than 1007. As an example let's say the numerically greatest ID of the returned 1000 records is 2045 so we return 2045 as the marker Later the clients request 1000 records with a marker of 2045 expecting to get the next batch of 1000 and a new marker.

Pretty straightforward way to allow them to get all of the records in whatever size works for them without missing any. However, due to sequence caching across multiple Exadata nodes, at the time the client requests 1000 records with a marker of 1007, a record with an ID of 2020 may not have been created. Therefore, when they do the next request using the marker of 2045, they will have missed record 2020 forever. Using the ID to get the timestamp of the associated record solves this, but then I must make sure to always insert records into the table individually to guarantee unique timestamps.

Assumptions:

• Not a way to get separate timestamps for individual records on a bulk/batch insert
• Multiple nodes may cause insertion of records to be non-time-sequential even with NOCACHE on the sequence (e.g., a record with sequence value 180 could be written with a systimestamp greater than that of a record with sequence value 179)

Hopefully I just haven't hit on the correct terms to search for existing answers. I feel that this is a problem that should have been solved by some pattern(s) for years. I think Twitter has solved it...

Answer 1

I recommend ordering by timestamp. As long as the only records with identical timestamps are inserted in the same transaction, you can use the sequence-based ID as a secondary ordering.

Answer 2

Well, as it turns out I needed to use a synchronous process to assign an id to each record after the initial insert was complete.

As was pointed out to me, what I was really looking for was a commit sequence. Unfortunately even timestamp would not provide that because it is generated at insert time, not at commit time. So there could be situations where a timestamp for one group of records (A) might before a second group of records (B) but group B could actually be committed to the database first. Then I'd not have solved the original issue.

In the end we used what is essentially a synchronized home-implemented sequence via a procedure that used table locking (on the 'sequence' table) and skip locks. We therefore don't have to slow down our insert process by single-threading it, but we're guaranteed an ID that is guaranteed to be in a commit order of when the ID was committed to the record.