Oracle proof: uncommitted transactions may also be written into data files by dbwn

When learning Oracle, many people think that dbwn writing is related to commit. This is incorrect. In fact, there is no relationship between dbwn writing and whether the transaction is commit. Now, it turns out that an uncommitted transaction may also be written into a data file.

Session 1:

sys@ORCL> select xidusn,xidslot,xidsqn,ubafil,ubablk from v$transaction;no rows selected

There is no transaction at this time.

Session 2:

hr@ORCL> select * from t;        ID NAME---------- ----------         1 a         2 b         3 chr@ORCL> update t set name='d' where id=1;1 row updated.

HR initiates a transaction.

Session 1:

sys@ORCL> /    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK---------- ---------- ---------- ---------- ----------        12         10         14          6         88

At this time, the transaction information is queried.

Session 1:

hr@ORCL> select dbms_rowid.rowid_relative_fno(rowid) fno,                                  2             dbms_rowid.rowid_block_number(rowid) blk,  3             t.*  4        from t;       FNO        BLK         ID NAME---------- ---------- ---------- ----------         4        404          1 d         4        404          2 b         4        404          3 c

Next, let's compare the content of ID = 1 modified by transactions in the memory and data files respectively:

1) dump out the data in the memory

sys@ORCL> alter system dump datafile 4 block 404;System altered.

Some content is extracted as follows:

tab 0, row 0, @0x1f90tl: 8 fb: --H-FL-- lb: 0x2  cc: 2col  0: [ 2]  c1 02col  1: [ 1]  64tab 0, row 1, @0x1f88tl: 8 fb: --H-FL-- lb: 0x0  cc: 2col  0: [ 2]  c1 03col  1: [ 1]  62tab 0, row 2, @0x1f80tl: 8 fb: --H-FL-- lb: 0x0  cc: 2col  0: [ 2]  c1 04col  1: [ 1]  63end_of_block_dumpEnd dump data blocks tsn: 4 file#: 4 minblk 404 maxblk 404

64 is the data modified by our firm. Note: At this time, our transaction has not been committed.

2) dump the data in the data file

sys@ORCL> alter system dump datafile '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_8050fkdh_.dbf' block 404;System altered.

Some content is extracted as follows:

block_row_dump:tab 0, row 0, @0x1f90tl: 8 fb: --H-FL-- lb: 0x2  cc: 2col  0: [ 2]  c1 02col  1: [ 1]  64tab 0, row 1, @0x1f88tl: 8 fb: --H-FL-- lb: 0x0  cc: 2col  0: [ 2]  c1 03col  1: [ 1]  62tab 0, row 2, @0x1f80tl: 8 fb: --H-FL-- lb: 0x0  cc: 2col  0: [ 2]  c1 04col  1: [ 1]  63end_of_block_dumpEnd dump data block from file /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_8050fkdh_.dbf minblk 404 maxblk 404

Did you see it? There is also a 64 in the data file, which indicates that the dirty block modified by the transaction has been written into the data file by dbwn.

Let's take a look at the commit status of the transaction:

sys@ORCL> select xidusn,xidslot,xidsqn,ubafil,ubablk from v$transaction;    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK---------- ---------- ---------- ---------- ----------        12         10         14          6         88

It can be seen that the transaction has not been committed.

The proof is complete.

Therefore, the writing of dbwn has nothing to do with whether the transaction is committed. Memory is used to improve performance, rather than to put dirty blocks. That is, as long as Oracle is idle, it will flush these dirty blocks into the data file.

These uncommitted transactions are written into the data file, even if they are already in your data file. The data will be rolled back as needed in the transaction recovery phase, and the data will no longer be used, that is, it is already junk data.

Note: 64 is the hexadecimal number of the d ASCII code.