I have an oracle table from which rows are retrieved every 10 mins. Some other application puts rows into the same table. There is a timestamp field and I am planning to use that inorder to retrieve new rows. I am concerned about transactions. There might be a point where that application inserts a row when I try to retrieve and so, I will miss that row no matter I check the timestamp. Can somebody throw some light on this?
Also, when I retrieve rows and process, there might be a situation where the processing is faulty and therefore, that row has to be fetched again sometime.
I am thinking of using a new table. Please suggest some ideas regarding my design.
I am very interested in knowing such scenarios and I would like to know how to deal with such situations at enterprise level ( I wonder what would be the design in time critical applications where multiple applications put and retrieve rows on a single table using timestamps).