Re: Locking

  • From: Hans Forbrich <fuzzy.graybeard@xxxxxxxxx>
  • To: Paul Harrison <cure@xxxxxxxxxxxxx>
  • Date: Thu, 17 Nov 2011 16:38:15 -0500

SCN is an internal 'time stamp'.  It reflects some change to the system, 
be it DML or other.  It is recorded in many many places including in the 
header of each data file and in the header of each data block and in the 
transaction's undo header.

Many many many activities record the SCN at the time when they implement 
a change.  Each activity will record the SCN in a place of it's own 
choosing, relative to it's purpose.  For example, when the data file is 
refreshed from SGA, the data file header is updated to the SCN at the 
time of that refresh.

And many many many other activities will reference the recorded SCN to 
determine whether they need to take some action to compensate for those 

In your example, session 1 changes a row and records the SCN at which 
that change is made in the undo segment in the area attached to the 
transaction.  (The transaction ID is recorded in the block along with 
the fact that the row is changed.)  Let's say that change is recorded 
but not committed.  While we are at it, let's say that change (both the 
current data block and the undo data block) is then flushed down to disk 
in the data files, but still not committed.

Another process comes along and asks to see the record.  It looks at the 
block header and sees there is a transaction, and notes the transaction 
number.  It flips over to the UNDO to determine state of the transaction 
and finds that it is still uncommitted (or was changed after the query 
started based on relative SCNs).  It then takes a copy of the entire 
block (that copy is marked as 'read consistent', not current), asks for 
the undo information whether from disk or from memory, applies it, and 
either repeats the process if that revised block is still 'newer' than 
the query or actually reads the block to fulfill the query.   
(Read-consistent blocks can be shared between queries to avoid unneeded 

PLEASE read the concepts manual and Tom Kyte's book for more information 
- avoids duplicating that knowledge here over the next few months. :-)


On 17/11/2011 4:11 PM, Paul Harrison wrote:
> SCN is only to log changes.  Only dml queries are assign SCN?
> Let's say I update 1 row(update table set name = 'you where id = 2) and do
> not commit.  The redo buffer will contain 2 change vectors(undo and redo)
> and will eventually flush to disk even without a commit.
> Oracle will change the undo block&  the table block in SGA....
> Now, let's say I start another session and query select name form table
> where id =2...  How will oracle know that I need to read the undo block in
> memory instead of the table block(database buffer cache) ?
> Thanks,
> Paul
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Hans Forbrich
> Sent: Thursday, November 17, 2011 2:52 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: Locking
> Each query has an internal start record we call an SCN.
> Any update is made to the live in-memory record and the time (SCN) and
> sufficient information to rollback each row is recorded in an undo area.
> Bottom line is that a query will  (effectively) compare the query SCN to the
> row update SCN and either use that row or will rebuild that row to the way
> it looked at the start of query.  In this mode locks are irrelevant and
> effectively ignored.
> (Locks are really only necessary to avoid concurrent updates and are held at
> the row level.  No escalations occur.)
> On 17/11/2011 2:52 PM, Paul Harrison wrote:
>> Hi All,
>> Let's say I have 2 sessions open... session one updates a table and
>> does not commit and can view the new updated data.  session 2 is able
>> to read the old data.  How can session 2 read the old data if session
>> 1 has an exclusive write lock on the table. Exclusive lock happens when
> updating a table?
>> Thanks,
>> Paul
>> --
> --


Other related posts: