Skip to Main Content
  • Questions
  • read consistency how to maintain which update record

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, ashwan.

Asked: June 23, 2016 - 8:25 am UTC

Last updated: June 24, 2016 - 2:24 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

hi
can you explain when one update statement access a record and select statement want to access the same record , lets assume update takes 10 min(till not committed). what will happen to select statement. select statement can access previous image of data or waiting for new update.

previous image of the record
111 , 3344,4,gghghg,5

update image of the record
111,5555,ghhh,6
let say there is a DB with large update frequently . In this situation all select statements(refer to update records) are waiting for new update. ? or Select statements return with old values until all updates are committed?

thanks


and Connor said...

Its one of the coolest things in Oracle

- readers do not block writers
- writers do not block readers

When we start a query, we record the "current moment in time" (its not actually "time", its the SCN, but time will do for this explanation). As we read the blocks needed, we make sure that they are not more recent than this moment. If they are, it means they were updated after our query began, and hence not appropriate for us to use in our query.

When we encounter such a block, we'll take a copy it in memory, and then wit this copy, we'll "rollback" the change(s) on that block until it has gone far enough "back in time" so that it represents the data as it was when our query began. Then we can use the rows in it, and move on. Notice I havent mentioned committed/uncommitted here. It doesn't really matter - even if an update has been committed, if it started after our query began, we'll still roll it back to get a consistent view of the block

The Concepts guide is a strongly recommended reading guide here

https://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT020


Is this answer out of date? If it is, please let us know via a Comment