Oracle Database ensures consistent read and no dirty read through undo

The Oracle database uses undo to ensure consistent read and dirty read. User A updates the table and does not submit 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 queries: SQL> select * from test; id name ---------- -------- 1 A 2 B2, user B: update test set name = 'C' where id = 1; www.2cto.com 3 is not submitted, user A queries again: SQL> select * from test; id name ---------- 1 A 2 B query the data block Information 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/Fsc0x01 0x0003. 02d. 00000323 0x008001c5. 0297.0d C --- 0 scn 0x0000. 0022cf820x02 0x0006. 020.00000320 0x0080048c. 017d. 03 ---- 1 fsc 0x0000.00000000 .... Block_row_dump: tab 0, row 0, @ 0x1f90tl: 8 fb: -- H-FL -- lb: 0x2 cc: 2col 0: [2] c1 02col 1: [1] 43tab 0, row 1, @ 0x1f88tl: 8 fb: -- H-FL -- lb: 0x0 cc: 2col 0: [2] c1 03col 1: [1] 42end_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 A? This is because Oracle found that this data has lb: 0x2 corresponds to ITL, and the flag is ---- from the record where ltl is 0x02, which indicates that there is a transaction tag, the data is locked, and the uba (undo block address) from the undo segment is required). In the undo segment, the corresponding block information is 0x0080048c, And the number here is in hexadecimal notation. First, convert it to hexadecimal notation: www.2cto.com select to_number ('0080048c', 'xxxxxxxxxxxxxxxxxxxxxx ') obtain the value 8389772 from dual and 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: 0x01001a3bitli: 2 ispac: 0 maxfr: 4858 vect = 3col 1: [1] 41End 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: www.2cto.com 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. [Here, cursor is enabled but data is not retrieved, paused, and user B is asked to update data.] 2. User B: update test set name = 'C' where id = 1; and submit. 3. SQL> print: c; id name ---------- 1 A 2 B user B updates the data and submits the data, however, it is found that the result set obtained by user A is still the data result in the table at the beginning of execution. The principle of www.2cto.com is that user A will save the current SCN at the beginning of execution. Each time he reads data from the data block, it will compare the saved SCN and the SCN of 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 relative block placed in the undo segment last time, 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. Author kkdelta