An introduction to the principle of Oracle database consistency reading

In Oracle databases, Undo has three main roles: providing consistent read (consistent read), ROLLBACK TRANSACTION (Rollback Transaction), and instance recovery (Instance Recovery).

Consistency reading is relative to dirty read (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, a user A query statement: SELECT * from T, the statement completed at 9:15. When user a executes the SQL statement to 9:10, another user B issues a delete command that deletes and submits the last record in the T table. So how many records will a user return by 9:15?

If 9,999 records are returned, dirty reads occur, and if 10,000 records are still returned, a consistent read is noted. Obviously, there are 10,000 records in table T when the query is issued at that point in the 9 o'clock, but it takes 15 minutes to retrieve all the records because I/O is relatively slow. For an Oracle database, there is no way to implement dirty reads, you must provide consistent reading, and the consistency read is implemented without blocking the user's DML.

So how does the undo data achieve consistency reading? Or for the above example. When user A makes a query at 9, the server process records the SCN number at the point at 9, assuming the SCN is SCN9.00. Then the 9 o'clock SCN9.00 must be greater than or equal to the SCN number (the largest of which, if there is more than one ITL slot), that is recorded in the ITL in all the header parts of the data.

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

When the server process scans the data blocks of the table T, it compares the SCN number in the ITL in the header of the scanned data to the scn9:00, which is larger. If the data block is smaller than the SCN9.00, the chunk is not updated after 9 o'clock, and the data can be read directly; otherwise, if the data block ITL's SCN is larger than the SCN9.00, the block is updated after 9 o'clock, The data in the block is not the 9 point of time data, so the use of the undo block.

9:10, the B user updates the last record of table T and submits (note that here, submitting or not submitting is not the key, as long as User B updates the table T, user A reads the undo data block). Assume that the updated record belongs to an n-Number data block. So this time, the SCN number of the ITL slot in the n number data is changed to SCN9.10. When the server process scans the updated block of data (that is, the n block), it finds that the SCN9.10 in its ITL is greater than the SCN9.00 when the query was issued, indicating that the block was updated after 9 o'clock. The server process then went to the head of the N block and found the ITL where the SCN9.10 was located. Because the address of the corresponding undo block is recorded in the ITL slot, the undo block is found at that address, the modified data in the undo block is taken out, and the data row in the N block is combined to construct the block of data at that point in time before 9:10 is updated. Such blocks of data are called CR blocks (consistent Read). For Delete, the undo information is the insert, which means that the constructed CR block inserts the deleted record. The server process then scans the CR block to return the correct 10,000 records.

Let us continue to complicate the matter. Suppose that at 9:10 the B user deletes the last record and commits it, followed by 9:11, C users insert 2 records in the same data block (that is, n blocks). How does Oracle implement conformance reading this time (assuming that the initrans of table T is 1, that is, only one ITL)? Because we already know that transactions need to use the ITL slot, the ITL can be reused as long as the transaction commits or rolls back. In other words, the ITL record is already SCN9.11, not SCN9.10. When the ITL is overwritten, how can Oracle's server process retrieve the original data?

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

The secret is that when Oracle records the undo data, it records not only the data before the change, but also the ITL information in the header of the data before the change. Therefore, at 9:10 when a user deletes a record (in n block and assumes that the ITL information for the n block is [undo_block0/scn8.50]), Oracle puts the changed data (that is, insert) into the undo block (assuming that the undo block address is the Undo _BLOCK1), and record the deletion of the former ITL (i.e. [undo_block0/scn8.50]) in the Undo block. After the record is deleted, the ITL information for the n block changes to [undo_block1/scn9.10], and by 9:11 the C user inserts two records into the n block. Oracle places the data before insertion (that is, delete two records) into the undo block (assuming the address of the undo block is Undo_block2) and the information of the ITL at 9:11 (that is, [undo_block1/scn9.10] ) is also recorded in the undo block. After inserting two records, the information for the ITL slot of the n block is changed to [undo_block2/scn9.11].

When the server process that executes the query scans to n blocks, the SCN9.11 is found to be greater than SCN9.00, and the Undo block is found at the Undo_block2 specified in the ITL slot. The ITL information recorded in the undo block is [undo_block1/scn9.10], where the SCN9.10 is still greater than SCN9.00, and the server process continues to locate the undo block based on the Undo_block1 recorded in ITL. found that the ITL information recorded in the undo block is [undo_block0/scn8.50], when ITL's SCN8.50 is less than the SCN9.00 when the query is issued, which means that the undo block contains the appropriate undo information, so the server process is no longer looking, but the n block , Undo_block2, and undo_block1 data together to build CR blocks. Copy the current n data into the CR block, then in the CR block back to the 9:11 transaction, that is, in the CR block to delete two records, and then back to the 9:10 transaction, that is, in the CR block inserted deleted records, so as to build a 9 o'clock data. This is how Oracle finds the entire undo block's list in a nested way, until it finds that the SCN number in the ITL is less than the SCN number when the query was issued. Normally, the SCN number recorded in the current undo block is smaller than the SCN recorded in the previous undo block.

However, during the lookup process, you may find that the SCN number recorded in the current undo block is larger than the SCN number recorded in the previous undo block. This situation indicates that because the transaction was committed or rolled back, the data in the currently found undo block was overwritten by other transactions, so we could no longer find the SCN number that was less than the point at which the query was made, and Oracle would throw a very classic error--ora-1555. That is snapshot too old mistake.

The rollback transaction is a rollback command to undo the changes to DML after the DML is executed. Oracle uses the address of the undo block recorded in the ITL to locate the undo block and then remove the changed value from it and put it in the data block to rollback the changes made to the transaction.

Instance recovery occurs after the Smon process finishes rolling and the database is opened. The Smon process goes to the transaction table that records the Undo segment head (the so-called head is the first block in the Undo segment) (When each transaction uses the Undo block, first record an entry in the head of the undo segment where the undo block is located. This entry records the information related to the transaction, including whether to commit, etc., which neither commits nor rolls back, but is rolled back when the instance crashes with an abnormally terminated transaction.