commit在oracle中,Oracle中commit的过程

Consider a transaction that updates a million

row table. This obviously visits a large number of database blocks to make the change to

the data. When the user commits the transaction Oracle does NOT go back and revisit these

blocks to make the change permanent. It is left for the next transaction that visits any

block affected by the update to 'tidy up' the block (hence the term 'delayed block

cleanout').

Whenever Oracle changes a database block (index, table, cluster) it stores a pointer in

the header of the data block which identifies the rollback segment used to hold the

rollback information for the changes made by the transaction. (This is required if the

user later elects to not commit the changes and wishes to 'undo' the changes made.)

Upon commit, the database simply marks the relevant rollback segment header entry as

committed. Now, when one of the changed blocks is revisited Oracle examines the header of

the data block which indicates that it has been changed at some point. The database needs

to confirm whether the change has been committed or whether it is currently uncommitted.

To do this, Oracle determines the rollback segment used for the previous transaction

(from the block's header) and then determines whether the rollback header indicates

whether it has been committed or not.

If it is found that the block is committed then the header of the data block is updated

so that subsequent accesses to the block do not incur this processing.

This behaviour is illustrated in a very simplified way below. Here we walk through the

stages involved in updating a data block.

STAGE 1 - No changes made

Description: This is the starting point. At the top of the

data block we have an area used to link active

transactions to a rollback

segment (the 'tx' part), and the rollback segment

header has a table that stores information upon

all the latest transactions

that have used that rollback segment.

In our example, we have two active transaction

slots (01 and 02)

and the next free slot is slot 03. (Since we are

free to overwrite committed transactions.)

Data Block 500 Rollback Segment Header 5

+----+--------------+ +----------------------+---------+

| tx | None | | transaction entry 01 |ACTIVE |

+----+--------------+ | transaction entry 02 |ACTIVE |

| row 1 | | transaction entry 03 |COMMITTED|

| row 2 | | transaction entry 04 |COMMITTED|

| ... .. | | ... ... .. | ... |

| row n | | transaction entry nn |COMMITTED|

+-------------------+ +--------------------------------+

STAGE 2 - Row 2 is updated

Description: We have now updated row 2 of block 500. Note that

the data block header is updated to point to the

rollback segment 5, transaction

slot 3 (5.3) and that it is marked uncommitted

(Active).

Data Block 500 Rollback Segment Header 5

+----+--------------+ +----------------------+---------+

| tx |5.3uncommitted|-+ | transaction entry 01 |ACTIVE |

+----+--------------+ | | transaction entry 02 |ACTIVE |

| row 1 | +-->| transaction entry 03 |ACTIVE |

| row 2 *changed* | | transaction entry 04 |COMMITTED|

| ... .. | | ... ... .. | ... |

| row n | | transaction entry nn |COMMITTED|

+------------------+ +--------------------------------+

STAGE 3 - The user issues a commit

Description: Next the user hits commit. Note that all that

this does is it

updates the rollback segment header's

corresponding transaction

slot as committed. It does *nothing* to the data

block.

Data Block 500 Rollback Segment Header 5

+----+--------------+ +----------------------+---------+

| tx |5.3uncommitted|--+ | transaction entry 01 |ACTIVE |

+----+--------------+ | | transaction entry 02 |ACTIVE |

| row 1 | +--->| transaction entry 03 |COMMITTED|

| row 2 *changed* | | transaction entry 04 |COMMITTED|

| ... .. | | ... ... .. | ... |

| row n | | transaction entry nn |COMMITTED|

+------------------+ +--------------------------------+

STAGE 4 - Another user selects data block 500

Description: Some time later another user (or the same user)

revisits data block 500. We can see that there

is an uncommitted change in the

data block according to the data block's header.

Oracle then uses the data block header to look up

the corresponding rollback segment transaction

table slot, sees that it has been committed, and

changes data block 500 to reflect the

true state of the datablock. (i.e. it performs

delayed cleanout).

Data Block 500 Rollback Segment Header 5

+----+--------------+ +----------------------+---------+

| tx | None | | transaction entry 01 |ACTIVE |

+----+--------------+ | transaction entry 02 |ACTIVE |

| row 1 | | transaction entry 03 |COMMITTED|

| row 2 | | transaction entry 04 |COMMITTED|

| ... .. | | ... ... .. | ... |

| row n | | transaction entry nn |COMMITTED|

+------------------+ +--------------------------------+