How to Ensure read consistency in Oracle

 

In this case, a large query starts to query a large table in the FTS, and then another session updates a data entry at the end of the table, submits the data, and updates the data N times, because the DML of multiple transactions makes the XId and lock information on the block disappear, the XId on the record row has been set to 0, that is, the ITL information cannot be found, in this case, how does Oracle find the before image of the transaction?

According to the data dumped by dump, there is no clue.

Simple Consistency Verification Test

Session 1:

SQL> Create Table t nologging as select rownum A, AA. * From dba_objects AA, dba_objects BB where rownu
M & lt; 1000000;

Table created.

SQL> Create index t_index on T (a) nologging;

Index created.

SQL> select max (a) from T;

Max ()
----------
999999
SQL> declare
2 V1 number;
3 V2 number;
4 begin
5 V1: = 0;
6 V2: = 0;
7 For C in (select * from T) loop
8 if C. A> V1 then
9 V1: = C.;
10 end if;
11 end loop;
12 dbms_output.put_line (to_char (V1 ));
13 end;
14/

Now I Open Session 2:
Session 2:
SQL> Update t set a = a + 1 where A = (select max (a) from t );

1 row updated.

SQL> commit;

Commit complete.

SQL> Update t set a = a + 1 where A = (select max (a) from t );

1 row updated.

SQL> commit;

Commit complete.

SQL> Update t set a = a + 1 where A = (select max (a) from t );

1 row updated.

SQL> commit;

Commit complete.

SQL> Update t set a = a + 1 where A = (select max (a) from t );

1 row updated.

SQL> commit;

Commit complete.

SQL> Update t set a = a + 1 where A = (select max (a) from t );

1 row updated.

SQL> commit;

Commit complete.

SQL> Update t set a = a + 1 where A = (select max (a) from t );

1 row updated.

SQL> commit;

Commit complete.

SQL> Update t set a = a + 1 where A = (select max (a) from t );

1 row updated.

SQL> commit;

Commit complete.

SQL> select max (a) from T;

Max ()
----------
1000006

SQL> Update t set a = a + 1 where A = (select max (a) from t );

1 row updated.

SQL> commit;

Commit complete.

SQL> Update t set a = a + 1 where A = (select max (a) from t );

1 row updated.

SQL> commit;

Commit complete.

SQL> Update t set a = a + 1 where A = (select max (a) from t );

1 row updated.

SQL> commit;

Commit complete.

SQL> Update t set a = a + 1 where A = (select max (a) from t );

1 row updated.

SQL> commit;

Commit complete.

SQL>

At this time, Session 1 is still not completed.

Output results in a moment:
999999

PL/SQL procedure successfully completed.

If the dump data is updated in session 2, the dump result cannot provide sufficient information. Then I am confused about how consistency is maintained at this time. Before image should come from the rollback segment. However, how Does Oracle know what data is related to the rollback segment? Where is this information stored? Does the content dumped by dump actually cover up some content?

That is to say, even if we read data from a row, there is no lock and no Xid information, but Oracle will still retrieve the related before image in the rollback segment, and confirm that the block is clean? Is the cost too high?

 

Another test shows that

If one of the data in the same block is updated, even if only the data that is not updated is queried, or even data is directly queried by rowid through the index, we can imagine that we don't just look at the updated data at a glance, but it still produces Cr blocks.

SQL> Create Table T1 (a number, B number );

Table created.

SQL> insert into T1 select rownum, rownum from t where rownum & lt; 11;

10 rows created.

SQL> Create index t1_index on T1 ();

Index created.

Sys updates the B field here.
SQL> Update Rainy. T1 Set B = 0 where a = 1;

1 row updated.

SQL> select count (*) from x $ BH where State = 3;

Count (*)
----------
50



SQL> select * from T1 where a = 2;

A B
--------------------
2 2

A B
--------------------
2 2

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose
1 0 Table Access (by index rowid) of 't1'
2 1 index (range scan) of 'T1 _ Index' (NON-UNIQUE)

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
5 consistent gets
0 physical reads
52 redo size
424 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
1 rows processed

SQL>

SQL> select * from T1 where a = 2;

A B
--------------------
2 2

Sys Query
SQL> select count (*) from x $ BH where State = 3;

Count (*)
----------
51



SQL> select * from T1 where a = 2;

A B
--------------------
2 2

SQL>

Sys Query
SQL> select count (*) from x $ BH where State = 3;

Count (*)
----------
52

SQL>


SQL> select * from T1 where a> 8;

A B
--------------------
9 9
10 10

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose
1 0 Table Access (by index rowid) of 't1'
2 1 index (range scan) of 'T1 _ Index' (NON-UNIQUE)

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
6 consistent gets
0 physical reads
52 redo size
462 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (Disk)
2 rows processed

SQL>

Sys Query
SQL> select count (*) from x $ BH where State = 3;

Count (*)
----------
54

SQL>


This proves a process, that is, the generation of Cr. We thought it was like this:
A row is found to contain Xid information (Xid> 0), ITL is found based on Xid, and rollback segment information is found based on ITL ...... Generate Cr block

It seems that this is wrong!

Cr is not generated because the data viewed has been changed.Consistent readAs long as there is an active transaction in the block, or the block SCN> query SCN will generate a Cr block.
If there are no active transactions but no commit SCN is generated (the FSC entry in ITL is 0), the delay block cleanout is generated first.

So there is no clear answer to the question about how Cr locates the Undo block from the block (ITL is easy to understand, but not yet ?), The transaction table is just a structure of the chain table header ......

 

After half a day, I found that

In itl
0x1 Xid: 0x0005. 040.00000117 UBA: 0x0080233e. 01f9. 11 -- U-10 FSC 0x0000. 0272a3b7

Xid is the transaction table information of the current transaction.
The address of the rollback segment of the UBA transaction

Ignore the fact that the block changes recorded in the rollback segment, except for the data changes and the ITL changes.

Therefore, Cr blocks can be generated based on the current ITL information. Cr blocks contain the original ITL information, so that CR blocks are generated based on before ITL information. This Cr block contains before ITL, in this way, repeat the process until the commit SCN is smaller than the block of the query SCN or there is no ITL. The CR generation process ends. If no block is found, the system returns the 1555 error.

Of course, it is not necessarily because the queried record contains Xid information. This is still true. The lock on the row mainly applies to DML.

 

By the way, it may be nonsense.
Select is based on the time point, DML is not based on the time point, that is, the current mode, what is currently seen, regardless of the time point consistency and the past is what

SQL> select count (*) from T;

Count (*)
----------
999999

SQL>
SQL> DESC t
Name null? Type
-----------------------------------------------------------------------------------
A number
Owner varchar2 (30)
Object_name varchar2 (128)
Subobject_name varchar2 (30)
Object_id number
Data_object_id number
Object_type varchar2 (18)
Created date
Last_ddl_time date
Timestamp varchar2 (19)
Status varchar2 (7)
Temporary varchar2 (1)
Generated varchar2 (1)
Secondary varchar2 (1)

SQL> select max (a) from T;

Max ()
----------
1000010

SQL> select rowid from t where a = 1000010;

Rowid
------------------
Aaahhxaajaaahinaax

The table is not indexed!
Open Session 1 and execute
SQL> Update t set owner = 'qqqqqqqq' where a = 1000010;

Open Session 2 now and execute

SQL> Update t set owner = 'wwwwwwww ', a = 1000011 where rowid = 'aaahhxaajaaahinaax ';

1 row updated.

SQL> commit;

Commit complete.

This is the process in which Session 1 is still being executed. The result will be returned in a moment:
SQL> Update t set owner = 'qqqqqqqq' where a = 1000010;

0 rows updated.

SQL>

This is a major difference between query mode and current mode.
Query mode -------- consistent gets
Current Mode -------- db block gets