Oracle read consistency learning notes

1. rollback and Revocation

Refer: In-depth Oracle parsing by eygle

(1) In order to ensure read consistency among multiple users and to roll back transactions, oracle provides the old values before the modification for the modified data.

(2) Redo

Undo: ensure that the transaction can be rolled back or canceled

(3) Before 9i, oracle provides a rollback segment to cancel data. Then, oracle uses the undo tablespace for management.

(4) The following example describes how to ensure rollback before 9i.

Update emp set sal = 4000 where empno = 7788;

Let's take a brief look at the execution process of this statement:

A: Check whether the empno = 7788 record exists in the database buffer cache. If not, it is read to the database buffer cache.

B: The transaction slot is allocated to the transaction table in the corresponding rollback segment of the rollback tablespace. This operation requires redo information to be recorded.

C: read data from the rollback segment or create an old sal = 3000 value in the database buffer cache, which generates redo information and records it in the redo log buffer.

[B and C Ensure the rollback of the transaction, and the transaction can be modified later]

D: Modify sal = 4000. This is the update data change. redo log buffer must be logged.

F: when the user commits the transaction, the commit information is recorded in the redo log buffer, and the transaction is marked as inactive in the rollback segment ).

(5) If a user rolls back a transaction, oracle needs to read the old value from the rollback segment, modify the database buffer cache, and complete rollback, this process will generate redo, so rollback is expensive.

(6) rollback segments are allocated in the undo tablespace. Their functions include rollback transactions, transaction recovery, and read consistency.

For DML:

Insert: the rollback segment only records the rowid of the inserted record;

Update: you only need to record the old value of the updated field for the rollback segment;

Delete: oracle must record the entire row of data.

Therefore, when undo is generated, delete generates the most undo data. We recommend batch deletion and batch submission for large-scale data deletion operations to reduce the occupation and impact on rollback segments.

(7) An important feature that distinguishes oracle from other databases:

With a multi-version architecture, oracle implements the separation of reading and writing, so that writing does not block reading and reading does not block writing.

Multi-version architecture is implemented through consistent reading.

Assume scott's salary is 3000:

A: At t1, We can query session 1 to get 3000;

B: session 2 is updated at T2. However, It is not submitted (the data has been modified in the database buffer cache, And the buffer is dirty)

C: Query session 1 again at t3. Note that oracle does not allow other users to view uncommitted data at this time. oracle needs to perform consistent read through the old values recorded in the rollback segment, restore 3000 to the user, which is the role of consistent read;

D: at the time of t4, session 2 submits the change, and the data modification has been made permanent;

F: at t5, other users will query again. The changed data, that is, 4000, will be displayed.

Notice:

A: each data block header records A submitted SCN. After the data change is submitted, the submitted SCN is modified at the same time, this SCN can be used for consistent read judgment during query.

B: medium. If the query start time is t1, if the submitted SCN of the retrieved data block is smaller than t1, this is the case when session 2 is received by oracle. If the submitted SCN is greater than t1 or the data is locked and modified, the submitted SCN is not recorded, then oracle needs to construct a pre-image through the rollback segment to return the result [session 1 is such a situation], which is the essential meaning of consistent read.

(8) after 9i, oracle introduces the undo tablespace. If Automatic undo tablespace management is selected, oracle dynamically creates and releases the rollback segments and automatically specifies the rollback segments for transactions.

Command: Show parameter undo

The undo_retention parameter is related to the ORA_01555 error.

This parameter can be called: alter system set undo_retention = **;

Undo_retention: the time (in seconds) for retaining the undo information after the transaction is committed );

10 Gb added guarantee control for undo:

Alter tablespace undotbs1 retention guarantee | noguarantee

Differences:

If you cancel the automatic expansion attribute of the undo tablespace:

Alter database datafile '/u01/app/oracle/product/10.2.0/oradata/undotbs'

Autoextend off;

Delete data cyclically

Under guarantee settings, a ORA-30036 error occurs;

Under the noguarantee setting, it can be completed smoothly, because oracle starts Automatic Adjustment to meet the needs of the longest running query.

(9) ORA-01555 causes and solutions

A: cause:

(I) Since rollback segments are used cyclically, after a transaction is committed, the transaction table of the rollback segments occupied by the transaction will be identified as inactive, and the tablespace of the rollback segments can be overwritten and reused. However, when a query needs to use a covered rollback segment to construct a prefix image for consistent read, there will be a famous ORA-01555 error.

II) To be continued ..........

Master 2, hello!
I am not clear a sentence on the 167 page of oracle. The original saying is: if the read block does not meet the read consistency requirements, the server process needs to construct a pre-image and return it to the user through the current block version and rollback segment. How is the 'read consistency 'determined? The modified data must be exclusive locks. How can others read it? Hope to reply. Thank you!

It is determined by SCN.

Read Consistency (Read Consistency) is a key feature of the database, which ensures that the user sees consistent data during the query. That is to say, when a session is modifying data, other sessions will not be able to see the modifications not submitted by the session.

1 2 Next Page