Re: Locking
Date: Thu, 17 Nov 2011 16:38:15 -0500
Message-ID: <4EC57EC7.2010200_at_gmail.com>
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 changes.
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 rebuilds.)
PLEASE read the concepts manual and Tom Kyte's book for more information - avoids duplicating that knowledge here over the next few months. :-)
/Hans
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Hans Forbrich
> Sent: Thursday, November 17, 2011 2:52 PM
> To: oracle-l_at_freelists.org
> 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.)
>
> HTH
>
> 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
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 17 2011 - 15:38:15 CST