Re: Question on underlying mechanics of UNDO processing
Date: Thu, 1 Dec 2022 20:12:16 +0000
Message-ID: <CO1PR01MB67093CC8D6F752ADDD1A72B5CE149_at_CO1PR01MB6709.prod.exchangelabs.com>
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.html>
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:
- A DELETE statement is running on node#3. The statement is deleting around 99 million records from table T1. The program running this statement has no commit baked into the process.
DELETE FROM T1 WHERE TRUNC(TIMESTAMP) < :1 – 1
- An INSERT statement runs on node #1 inserting records into table T1:
INSERT INTO T1 ( LOG_SEQUENCE, …..)
- 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?
- 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.
- 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 Thu Dec 01 2022 - 21:12:16 CET