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|
+------------------+       +--------------------------------+

转自: