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?