RE: Locking
Date: Thu, 17 Nov 2011 15:11:26 -0600
Message-ID: <002001cca56d$781f7e90$685e7bb0$_at_austin.rr.com>
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:11:26 CST