Consider i am offering a lottery for all those who enroll themselves with thier friends using sql*plus before 12 o clock. There is no column to store the timestamp say just a list of names in each row. A person m1 inserted his ticket already before 11:59 and commited.
He starts updating his (row) details at 11:59 adds his maid to his lottery list (only needs to commit). Another person m2 running insert statement starts his first insertion (for himself alone) at 11:59 (only needs to commit). Its a huge table with 20 million rows.
Its 12 now. Consider this as read committed isolation level.
The lottery batch started, moving registered users to some other tables. After that ( very possible next nano second), both m1 and m2 commits.
Will my select statement in batch add m1 with m1's maid and m2 to the lottery winners list or not?
Consider scn(system change number), phantoms and non-repeatable reads while answering.