RE: Question on underlying mechanics of UNDO processing
Date: Fri, 2 Dec 2022 09:01:49 -0500
Message-ID: <2ec301d90656$9f955b60$dec01220$_at_rsiz.com>
AND, (not but), the chances that the freelist/freelist group or bitmap to free space that finds you a block to insert into will be a block undergoing an uncommitted delete on a different node should be low most of the time.
AND, (again, not but) insert append goes high and avoids the issue because it uses empty blocks that no other transaction can be doing an update or delete.
Mr. Powell is correct both that the details of all possible cases are complex and that the high-level overview is a good starting place.
I've just highlighted a couple things that tend to avoid block contention for different rows in the first place.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Powell, Mark
Sent: Thursday, December 01, 2022 3:12 PM
To: oracle-l_at_freelists.org
Subject: Re: Question on underlying mechanics of UNDO processing
Amir, I think you can find most of your answers in the Database Concepts manual
09 Data Concurrency and Consistency
10 Transactions
12 Logical Storage Structures (blocks, extents, and segments)
Oracle Database Database Concepts, 19c
<https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/index.h
tml>
Basically, there is no conflict between the delete of an old row and the insertion of a new row in the same block so there should be no lock contention, however, there are lots of things that may be waited upon: a latch has to be obtained to protect the block from more than one process changing the block contents concurrently but this latch is held only for as long as it takes to insert or delete the row, the transaction has to obtain an ITL in the block before the block contents can be changed, the ITL may have to be allocated, data to be changed has to be copied to UNDO, etc.... A complete correct answer is fairly complex but the high-level overview available via the reference material should provide a start.
Mark Powell
Database Administration
(313) 592-5148
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Hameed, Amir <amir.hameed_at_sleepnumber.com> Sent: Thursday, December 1, 2022 2:16 PM To: oracle-l_at_freelists.org <oracle-l_at_freelists.org> Subject: Question on underlying mechanics of UNDO processing
Hi,
I am trying to understand the internals of UNDO in the following scenario.
The database is a three-node RAC version 11.2.0.4:
DELETE FROM T1 WHERE TRUNC(TIMESTAMP) < :1 - 1
2. An INSERT statement runs on node #1 inserting records into table T1:
INSERT INTO T1 ( LOG_SEQUENCE, ...)
3. At his stage, if the INSERT statement (node#1) is trying to insert a
record into the same data block of T1 where another record is being deleted
by the DELETE (node #3) statement, how will Oracle process it? What kind of
waits/contention the session running INSERT might run into?
4. The process running the DELETE statement (node #3) is killed because
it was taking too long to finish, the UNDO segment has millions of UNDO
blocks that the killed process is now rolling back.
5. At his stage, if an INSERT statement (node#1) tries to insert a
record into a data block of T1 that had a deleted row that is being rolled
back now, how will Oracle process it? What kind of waits/contention the
session running INSERT might run into?
A feedback would be greatly appreciated.
Thank you,
Amir
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 02 2022 - 15:01:49 CET