RE: Locking
- From: "Powell, Mark" <mark.powell2@xxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 17 Nov 2011 21:16:46 +0000
When your query reads the table block it will see the row is locked and follow
the information in the ITL to the undo segment for the change. Also you query
knows the database SCN at the time it started.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Paul Harrison
Sent: Thursday, November 17, 2011 4:11 PM
To: fuzzy.graybeard@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Locking
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.)
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
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
