Because the last time we looked at Oracle's transactional consistency, when we consulted the data in the middle, I saw that this place was related to the rollback segment. So the following is a list of simple knowledge. Deeper is no longer digging deep, personal feelings for the consistency and isolation level of the transaction is the development manager should understand, but the specific mechanism of rollback paragraph should be the DBA's Learning category.
Rollback segment (ROLLBACK SEGMENT): The rollback segment is in the middle of a transaction, recording the pre-modification state, and adding a timestamp-like SCN to the recording process. Its main role is two:
- The original data can be retrieved from the rollback segment when the transaction has an error rollback.
- Achieve read consistency. When the read operation reads to a row and finds that the data has been modified, the data in the rollback segment is returned based on the SCN of the read time.
The rollback segment also has an important role in mistakenly deleting data recovery. About the rollback segment caused byORA-01555 error. Online saying:
- 1, at 1 o'clock, a user a issued a select * from table1, at this time regardless of future table1 how to change, the correct result should be user A will see at 1 o'clock this time content. There is no doubt about this.
- 2. At 1:30, a user B performed the update command, updating the record in line No. 40 million of the Table1 table, when user A's full table scan has not reached No. 40 million. There is no doubt that this time, the No. 40 million line of this record is written to the rollback section, I assume that the rollback segment RBS1, if User A's full table scan reached the No. 40 million row, it should be correct to read from the rollback segment RBS1 1 o'clock time content.
- 3, at this time, User B will he just do the operation commits, but at this point, the system can still give user A to provide the correct data, because the No. 40 million line of record content is still in the rollback section RBS1, the system can be based on the SCN to the rollback section to find the correct data, but we note that A significant change has taken place in the No. 40 million row of records recorded in RBS1: This is the No. 40 million row of data in the rollback segment RBS1 that is likely to be overwritten at any time, as this record has been submitted!!!
- 4, because the user A's query time is long, and the business has been continuously, RBS1 rollback segment is used by a number of different tracnsaction, this rollback section of the extent loop to the No. 40 million row of data extent, because this record has been marked submitted, So this extent can be covered by other transaction!
- 5, to 1:40, user A's query finally to the No. 40 million line, and at this time has appeared 4th said the situation, need to rollback section RBS1 to find data, but has been covered out, so 01555 appeared.
My understanding: The above statement is roughly correct, but there is a misunderstanding, giving the impression that once a transaction commits, the data in the rollback segment it occupies may still be needed (to ensure read consistency), but if a new transaction is made to handle the same piece of data at this time, This rollback segment will be rewritten. In fact, this is not the case, the new transaction will have different SCN in the rollback section, will not directly rewrite the useful rollback segment, the reason is 01555, because the rollback segment space is limited, once the time is too long, or the execution of other complex transactions caused by the rollback segment is not enough, the old rollback segment will be cleared. So to avoid 01555, the main thing is to enlarge the rollback segment size, optimize time-consuming queries, and reduce the unnecessary transactions that occupy too much of the rollback segment (such as CLOB or a transaction on a BLOB field).
Simple understanding of Oracle Rollback segments
