3

I am writing some data loading code that pulls data from a large, slow table in an oracle database. I have read-only access to the data, and do not have the ability to change indexes or affect the speed of the query in any way.

My select statement takes 5 minutes to execute and returns around 300,000 rows. The system is inserting large batches of new records constantly, and I need to make sure I get every last one, so I need to save a timestamp for the last time I downloaded the data.

My question is: If my select statement is running for 5 minutes, and new rows get inserted while the select is running, will I receive the new rows or not in the query result?

My gut tells me that the answer is 'no', especially since a large portion of those 5 minutes is just the time spent on the data transfer from the database to the local environment, but I can't find any direct documentation on the scenario.

Jrud
  • 1,004
  • 9
  • 25
  • 4
    Read about [multiversion read consistency](https://docs.oracle.com/database/121/CNCPT/consist.htm#GUID-4BD4DFD6-DAEA-41B2-BB56-7135568F0548) and isolation levels, particularly [statement-level read consistency](https://docs.oracle.com/database/121/CNCPT/consist.htm#GUID-6A252EAE-7900-47DC-BD6A-D5376A6B7608). However... you need to look at the difference between when the batch is *inserted* and when it's *committed*, and what happens if your timestamp falls in that gap. – Alex Poole May 02 '17 at 18:21
  • Exactly. I'm trying to determine if I can take the timestamp from the time I start the select, or after the select returns, or if neither will work and I need to use other strategies to prevent inserts and selects from happening at the same time. – Jrud May 02 '17 at 18:32

1 Answers1

8

"If my select statement is running for 5 minutes, and new rows get inserted while the select is running, will I receive the new rows or not in the query result?"

No. Oracle enforces strict isolation levels and does not permit dirty reads.

The default isolation level is Read Committed. This means the result set you get after five minutes will be identical to the one you would have got if Oracle could have delivered you all the records in 0.0000001 seconds. Anything committed after you query started running will not be included in the results. That includes updates to the records as well as inserts.

Oracle does this by tracking changes to the table in the UNDO tablespace. Provided it can restrict the original image from that data your query will run to completion; if for any reason the undo information is overwritten your query will fail with the dreaded ORA-1555: Snapshot too old. That's right: Oracle would rather hurl an exception than provide us with an inconsistent result set.

Note that this consistency applies at the statement level. If we run the same query twice within the one transaction we may see two different results sets. If that is a problem (I think not in your case) we need to switch from Read Committed to Serialized isolation.

The Concepts Manual covers Concurrency and Consistency in great depth. Find out more.

So to answer your question, take the timestamp from the time you start the select. Specifically, take the max(created_ts) from the table before you kick off the query. This should protect you from the gap Alex mentions (if records are not committed the moment they are inserted there is the potential to lose records if you base the select on comparing with the system timestamp). Although doing this means you're issuing two queries in the same transaction which means you do need Serialized isolation after all!

APC
  • 144,005
  • 19
  • 170
  • 281