0

This is a classic concurrent read and write challenge:

We are publishing records to and Atom Hopper feed on an Oracle database via Hibernate, and our partner is reading them. If we publish records 1 through 5, then 5 is the head (i.e. last published). Our partner asks for a page of the feed starting with entry 1, and receives entries 1-5. The next time they ask, they will be asking for entries newer than entry 5. The client keeps track of which entries have been read.

The problem is, while they read, we happen to publish an entry number 6, which gets a marginally older timestamp than number 5 in the "created" column. Because it was committed after the read, our partner never gets nr. 6. They get 7, 8 and 9 that we have published in the meantime, all with newer timestamps than nr. 5.

So how does one avoid missing entries like nr. 6? We let the database set the timestamp as a first measure, but there is still a chance that we might commit an "older" entry while they read, as there is a non-negligable time between when the records gets the timestamp and when it's committed. Would it be possible to lock the database page for reading or something?

I'm thinking we may have to change client behaviour, i.e. ask for entries newer than, say number 4 in case there was a commit in between. Would using sequences instead of timestamps be any better in terms of catching commits like nr. 6?

Programmer Trond
  • 303
  • 2
  • 15
  • If I could only make sure that records in the database have a timestamp column that matches the commit order, and no duplicate timestamps... I guees this would be achieved if database inserts and reads were treated sequentially instead of in parallell... – Programmer Trond Apr 23 '16 at 09:48
  • Using a sequence will most probably give the same problem, since a session can get a sequence value but not commit its value until later. – Jeffrey Kemp Apr 26 '16 at 06:56
  • I suspect that either the client or the server will need to remember the complete list of IDs that it has retrieved so far. One way is to use Oracle AQ: have all inserts/updates pushed onto a queue, and the interface would pull from the queue. – Jeffrey Kemp Apr 26 '16 at 06:59
  • Oracle AQ... That would mean abandoning Atom Hopper, I think. Would Oracle AQ commit the record as soon as it is read by the consumer? We may have multiple consumers in the future, so would they have to have one queue each? Anyway, it looks like we're gonna post to AtomHopper in one thread, so that inserts and commits won't be interleaved. I'm hoping the performance will be good enough. I have heard that ORA_ROWSCN is set at commit time, so I think we could have switched to sorting by ORA_ROWSCN instead of a timestamp, if the table was created with ROWDEPENDECIES... – Programmer Trond Apr 26 '16 at 10:36
  • With Oracle AQ you can define the "consumer(s)" of the queue however you like - e.g. a consumer might write the IDs to another table, or it might be Atom Hopper itself calling a procedure on your database (I don't know Atom Hopper's capabilities though). – Jeffrey Kemp Apr 27 '16 at 12:34

0 Answers0