Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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...

share|improve this question
    
Just thinking out loud here - maybe an autonomous transaction function in a trigger that updates :new.insert_time, or something along those lines? –  FreshPhilOfSO Apr 24 '13 at 15:08
    
(By that I mean a row level trigger that gets the current time stamp from an autonomous function called in the trigger). –  FreshPhilOfSO Apr 24 '13 at 15:16
2  
when you bulk insert with sysdate or systimestamp the time is generated as of the beginning of the statement so oracle makes sure that all the records that were inserted in the same statement will have the same timestamp. if you use seq with cach on top of that as your unique identifier you should be fine. –  haki Apr 24 '13 at 17:41
    
Another approach -- see rp0428's suggestion to augment the final digits of the timestamp: forums.oracle.com/forums/… –  eebbesen Apr 24 '13 at 21:51
    
Here's an answer to the question 'How does Twitter generate IDs?'. Not the solution to my problem, however, since it doesn't guarantee commit-time ordering. –  eebbesen Jun 20 '13 at 19:14
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
1  
Here's how I implemented Tyler's solution:1. Client requests n records using the record ID I gave them for the last request (or 0 if they want to start from the beginning) as the marker 2. I use the ID to find the timestamp 3. Select records that meet one of the following conditions a) have a timestamp greater than the record associated with the ID b) have the same timestamp as the record associated with the ID and have an ID greater than the passed in ID –  eebbesen Apr 24 '13 at 23:59
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.