How Oracle guarantees read-consistent second bounce

The principle of database consistency reading for Oracle

In an Oracle database, Undo has three main functions:provides a consistent read (consistent read), ROLLBACK TRANSACTION (Rollback
Transaction) and instance recovery (Instance Recovery)。

Consistent reads are relative to dirty reads (Dirty
Read). Assuming that there are 10,000 records in a table T, it takes 15 minutes to get all the records. The current time is 9 points, and a user a issues a query statement: SELECT * FROM
T, the statement is executed at 9:15. When user a executes the SQL statement to 9:10, another user B sends out a DELETE command that deletes and submits the last record in the T table. So how many records will the a user return by 9:15?

If 9,999 records are returned, a dirty read has occurred, and if 10,000 records are still returned, a consistent read has occurred. It is clear that in
9 o'clock when a query is issued at that point in time, there are 10,000 records in table T, except that it takes 15 minutes for all records to be retrieved because I/O is relatively slow. For Oracle
Database, there is no way to implement dirty reads, you must provide a consistent read, and this consistent read is not blocking the user's DML implementation of the premise.

So how does the undo data achieve consistent reading? Or for the above example. When user A makes a query statement at 9 points, the server process will 9
The SCN number at that point in time is recorded, assuming that the SCN number is SCN9.00. Then 9 o'clock the whole time the SCN9.00 must be greater than or equal to the ITL slots recorded in the head of all data
SCN number (the largest of the SCN numbers, if there are multiple ITL slots).

Note:
ITL(Interested Transaction
List) is an integral part of the Oracle data block that records all occurrences of the block, an ITL can be thought of as a record, and at a time, a transaction can be recorded (including commits or uncommitted transactions). Of course, if the transaction has already been committed, then the ITL position can be reused, because ITL resembles the record, so sometimes it is called an ITL slot.

When the server process scans the data block of the table T, it compares the SCN number in the ITL slot in the size of the scanned data to the scn9:00, which is larger. If the SCN number of the data is smaller than SCN9.00, then the data block is not updated after 9 o'clock, it can directly read the data, otherwise, if the data block ITL slot is larger than the SCN9.00, then the data block is updated after 9 o'clock, the data in the block is not 9 points at that point in time data, is to use the undo block.

9:10, b user updated the last record of table T and submitted (note that, here, commit or not commit is not the key, as long as User B updates the table T, User A will read the undo data block). Assume that the updated record belongs to the N data block. At this time, the SCN number of the ITL slot on the head of the N data is changed to SCN9.10. When the server process scans to the updated block of data (that is, Block N), it finds that the SCN9.10 in its ITL slot is larger than the SCN9.00 when the query was issued, indicating that the data block was updated after 9 o'clock. So the server process to the head of block N, find the ITL slot where the SCN9.10 is located. Because the address of the corresponding undo block is recorded in the ITL slot, the undo block is found based on that address and the
The modified data in the undo block is taken out and combined with the data rows in the N block to construct the data block content at the point in time before the 9:10 update, so that the data block is called the CR block (consistent
Read). For Delete, the undo information is insert, which means that the deleted record is inserted in the constructed CR block. The server process then scans the
CR block, which returns the correct 10,000 records.

Let's continue to complicate the problem. Suppose that at 9:10 B when the user deletes the last record and commits it, immediately after 9:11, the C user inserts 2 records in the same data block (that is, Block N). How does Oracle achieve consistent reading at this time (assuming that the initrans of table T is 1, that is, there is only one ITL
Slots)? Because we already know that transactions require an ITL slot, the ITL slot can be reused as long as the transaction commits or rolls back. In other words, the ITL slot is already recorded as SCN9.11, not SCN9.10. When the ITL slot is overwritten, how can Oracle's server process retrieve the original data?

The secret is that when Oracle records the undo data, it not only records the data before the change, but also records the ITL information in the head of the data that was before the changed data. Therefore, 9:10 B when the user deletes the record (in block N, and assumes that the ITL information for the n block is [undo_block0
/
SCN8.50]), Oracle will place the pre-changed data (that is, insert) in the undo block (assuming that the undo block address is Undo_block1), while the undo Block records information about the pre-deleted ITL slots (that is, [undo_block0
/SCN8.50]). After the record is deleted, the ITL information for the n number block changes to [UNDO_BLOCK1/
SCN9.10]; by 9:11, when the C user inserts two records into the n block, Oracle puts the pre-inserted data (that is, the delete two record) into the undo block (assuming the address of the undo block is Undo_block2). And the information of the ITL slots at 9:11 (i.e. [undo_block1
/SCN9.10]) is also recorded in the undo block. After inserting two records, the information for the ITL slot of the n number block is changed to [Undo_block2/
SCN9.11].

Then when the server process that executes the query scans to block N, it finds that SCN9.11 is greater than SCN9.00, and then it is specified in the ITL slot
The undo block is found at Undo_block2. The ITL information recorded in the Undo block is found to be [undo_block1/
SCN9.10], where SCN9.10 is still greater than SCN9.00, the server process continues to find the undo block based on the Undo_block1 recorded in the ITL. The ITL information recorded in the Undo block is found to be [undo_block0
/
SCN8.50], when the SCN8.50 in ITL is less than the SCN9.00 when the query is issued, stating that the undo block contains the appropriate undo information, so the server process is no longer looking for, but the n block, Undo_block2 and Undo_ Block1 data together to build a CR block. Copy the current n data into the CR block and then rollback the 9:11 transaction in the CR block, that is, delete two records in the CR block, and then back up the 9:10 transaction, that is, insert the deleted record in the CR block, and build the data at 9 o'clock.
This is how Oracle finds the entire list of undo blocks in nested layers until it finds that the SCN number in the ITL slot is less than or equal to the SCN number at the time the query was issued. Normally, the SCN number recorded in the current undo block is smaller than the SCN number recorded in the previous undo block.

However, during the lookup process, you may find that the SCN number of the ITL slot recorded in the current undo block is larger than the SCN number recorded in the previous undo block. This scenario indicates that the data in the currently found undo block has been overwritten by other transactions because the transaction was committed or rolled back, so we can no longer find the SCN number at the point that is less than or equal to the time the query was issued, and Oracle throws a very classic error--ora-1555, That's snapshot.
Too an old error.

The above description can be described in the diagram:

rolling back a transaction is a change made by issuing the rollback command to undo DML after DML is executed. Oracle uses the undo record recorded in the ITL slot
The block's address finds the undo block, then extracts the value before the change and puts it into the data block, which rolls back the changes made to the transaction.

instance recovery occurs after the Smon process finishes rolling forward and opening the database. Smon process will go to see Undo
The segment header (the so-called head is the first block in the Undo segment) Records the transaction table (each transaction when using the Undo block, first in the undo block where
Segment's head records an entry that records information about the transaction, including whether it is committed, and so on, that neither commits nor rolls back, but that the transaction that was aborted when the instance crashed is all rolled back.

Original address: http://www.cnblogs.com/Ronger/archive/2012/05/09/2492160.html

How Oracle guarantees read-consistent second bounce