Strange Oracle problem

Question

I tried to put it in a sentence but it is better to give an example:

SELECT * FROM someTable WHERE id = someID;

returns no rows
...
some time passes (no inserts are done to the table and no ID updates)
...

SELECT * FROM someTable WHERE id = someID;
returns one row!

Is it possible that some DB mechanism prevents first SELECT to return row?

Oracle log has no errors.

No transactions are rolled back when two selects are executed.

Answer 1

You can't see uncommitted data in another session. When did the commit happen?

EDIT1: Are you the only one using this database? Or did/do you have multiple sessions?

I think in another session you or someone else has inserted this row, you do your select and you don't see this row. After that a commit happens in the other session (maybe implicit because a session is closed) and then you see this row when you select again.

I can think of other explanations, but I first want to know are you only one using this database.

Answer 2

With read consistency as provided by Oracle, you should not see a row appear like that. If you are running in some mode with automatic commits, so that each statement is a self-contained transaction, then read consistency is not being violated. Which program are you using to access the database? I agree with the other observations; the row should not appear if your session is not inserting it and no other session is active at the same time. I don't know of a DBMS that indulges in spontaneous data generation.

Answer 3

Don't you have scheduled jobs in that Oracle?