0

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.

cdummy
  • 455
  • 1
  • 4
  • 14

1 Answers1

2

No. As soon as you run the SQL an Oracle server will maintain a snapshot of data for your session, so, any further commit will not be visible in a reading SQL.

Maxim Balaganskiy
  • 1,524
  • 13
  • 25
  • Could you please explain the role of SCN in this? Figure 13-1 Transactions and Read Consistency http://docs.oracle.com/cd/B19306_01/server.102/b14220/consist.htm The m2 block will have much recent SCN however it cant find any rollback for that. So how oracle decide over this issue? – cdummy May 15 '14 at 14:22
  • Oracle assigns an SCN to each transaction, even to the reading one. So, when reading blocks the engine will skip any blocks with the higher SCN. If the block is replaced and has a higher SCN the engine looks for the previous block in a rollback segment. The blocks are automatically put to the RBS when you write the data. The longer your reading session is running the more data gets to the RBS because Oracle needs it to give you a consistent snapshot. As for the brand new blocks, like m2, Oracle does know that there was no data previously and therefore no need to go query the RBS. – Maxim Balaganskiy May 16 '14 at 03:58