Oracle Database ensures consistent read and no dirty read through undo

No dirty reads occur. User A has updated the table and has not submitted the table. User B queries the table. unsubmitted updates cannot appear in the user's query results.

Instance simulation: the data in the test table is as follows:

1. User a query: SQL> select * from test;
ID name
--------------------
1
2 B

2. User B: Update Test Set Name = 'C' where id = 1; not submitted

3. User A queries again: SQL> select * from test;
ID name
--------------------
1
2 B

The data block information is queried.

Select ID, rowid, dbms_rowid.rowid_relative_fno (rowid) FN, dbms_rowid.rowid_block_number (rowid) BK from test order by ID
At this time, dump data blocks (data blocks in the memory) alter system dump datafile 4 block 6717,

ITL Xid UBA flag lck scn/FSC
0x01 0x0003. 02d. 00000323 0x008001c5. 0297.0d C --- 0 SCN 0x0000. 0022cf82
0x02 0x0006. 020.00000320 0x0080048c. 017d. 03 ---- 1 FSC 0x0000.00000000
 
....
Block_row_dump:
Tab 0, row 0, @ 0x1f90
TL: 8 FB: -- H-FL -- LB: 0x2 cc: 2
Col 0: [2] C1 02
COL 1: [1] 43
Tab 0, Row 1, @ 0x1f88
TL: 8 FB: -- H-FL -- LB: 0x0 cc: 2
Col 0: [2] C1 03
COL 1: [1] 42
End_of_block_dump

It is found that the value of ID = 1 has been changed, 43 (The hexadecimal value corresponds to C), so why does user a get the value of?

This is because Oracle finds that the data contains LB: 0x2, which corresponds to ITL. From the records where LTL is 0x02, the flag is ----, which indicates that the transaction is marked and the data is locked, it needs to be read from the UBA (UNDO block address) of the Undo segment.

In the Undo segment, the corresponding block information is 0x0080048c. Here the number is in hexadecimal notation. convert it to hexadecimal notation:

Select to_number ('0080048c', 'xxxxxxxxxxxxxxxxxxxx') from dual, the value is 8389772.

Obtain the data block information through the following statement: Select dbms_utility.data_block_address_file (8389772), dbms_utility.data_block_address_block (8389772) from dual;

Dump its content

....

Kdo op code: 21 row dependencies disabled
Xtype: xaxtype kdo_kdom2 flags: 0x00000080 bdba: 0x01001a3d hdba: 0x01001a3b
Itli: 2 ISPAC: 0 maxfr: 4858
Vect = 3
COL 1: [1] 41
End dump data blocks TSN: 1 file #: 2 minblk 1164 maxblk 1164

It is found that the value is 41 (hexadecimal is a), so user a sees the value as.

Assume that a user a sends a query data with a large amount of data to a table at 6 points, it takes 15 minutes to completely query the results. During this period, at 06:10, user B updated and submitted the data. User A's query result is still the table data, user B's updated data does not appear in user a's query results, which is consistent read.

Instance simulation:

1. You can query a report that needs to run for a long time:

SQL> variable C refcursor;

SQL> exec open: C for select * from test;
PL/SQL procedure successfully completed.

[Open cursor but do not fetch data here, pause, and ask user B to update data]

2. User B: Update Test Set Name = 'C' where id = 1; and submit.

3. SQL> Print: C;
ID name
--------------------
1
2 B

User B updates and submits the data, but finds that the result set obtained by user A is still the data result in the table at the beginning of execution.

The principle is that user a saves the current SCN at the beginning of execution, and compares the saved SCN with the SCN of the data block at each read from the data block, if it is found that the SCN of the data block is smaller than the saved SCN, the data remains unchanged and is directly read from the data block. If the data block is large, it needs to be read from the Undo segment. Select dbms_flashback.get_system_change_number from dual; the current SCN is displayed.

If a query takes a long time, and the query results are changed in the query phase, and the data corresponding to the Undo segment has been cleared, the famous ORA-013555 in Oracle will occur: snapshot too old (snapshot too long) error.

If a piece of data is updated and submitted multiple times in a time period, the block placed in the Undo segment records the block address of the corresponding block that was last placed in the Undo segment, in this way, you can always find the data block in the Undo segment that is smaller than the SCN recorded at the start of the query.

Http://apps.hi.baidu.com/share/detail/23920116 has a more detailed explanation