DML 原理-Stages in Processing DML Statements

http://www.lslnet.com/linux/dosc1/47/linux-327395.htm
-
1.在做commit 時，交易只會寫入redo log ，但不會寫到datafile 去

2.在commit 同時，table/row lock 會被釋放，rollback segment lock 也會被釋放

3.datafile 寫入時機 : 在checkpoint 產生時，才會將已更動的block(dirty block)
寫回到datafile 去

4.在任何insert/update/delete的DML動作後，資料庫的SCN number 都會一直增加。
-
Stages in Processing DML Statements
At this point, meet yet another “behind-the-scenes” player in Oracle
transaction processing—the rollback segment. The rollback segment is a
database object in Oracle that stores old versions of data being changed by
DML statements issued by the user process. Rollback segments only store the
old values, not the new values—the new values are stored in the object
itself.

With this in mind, return to the processing of DML statements. There are
several differences between how Oracle processes select statements and how it
processes DML statements such as update, insert, and delete. Though the
operations involved in executing DML statements fall into the same general
pattern as those for select statements shown in Figure 6-2, the specific flow
of operation in processing DML statements is as follows:

1.Parse statement
The RDBMS creates a parse tree, or execution plan, for the statement and
places it in the library cache. This is a list of operations the RDBMS uses
to process the data change. If a parse tree already exists for this
statement, the RDBMS can omit this step.

2.Execute statement
The RDBMS performs all processing to execute the DML statement. For update or
delete statements, the server process will retrieve the data from disk into
the buffer cache, implicitly acquire a lock on the data to be changed, and
then make the specified data change in the buffer cache. A lock is an Oracle
internal resource that one user process acquires before updating or deleting
existing data to prevent other users from doing the same thing. For insert
statements, the server process will retrieve a block from disk that has
enough space available to house the new row of data, and will place that new

row into the block. Also, part of executing the DML statement is writing the
old and new versions of the data to the rollback segment acquired for that
transaction. A lock must be acquired on the rollback segment to write changes
to a rollback segment as well.

3.Generate redo information
Recall from the prior lesson that the redo log buffer stores redo or data
change information produced as the result of DML operations running in user
sessions. After issuing DML statements, the user process must write a redo
entry to the redo log buffer. In this way, Oracle can recover a data change
if damage is later done to the disk files containing Oracle data.

Stages in Processing COMMIT Statements
As discussed in Unit I, issuing a commit statement ends the current
transaction by making permanent any data change the user process may have
issued to the Oracle database. A rollback statement discards the data change
in favor of how the data appeared before the change was made. The rollback
segment is how Oracle manages to offer this functionality. By keeping a copy
of the old data in the rollback segment for the duration of the transaction,
Oracle is able to discard any change made by the transaction until the commit
statement is issued.

Before proceeding any further, make sure you understand the following
important point—issuing commit has no effect on when Oracle copies that data
change in the buffer cache to disk. Thus, a commit statement does not somehow
trigger DBWR activity. Only a checkpoint, a timeout, or a need for room in
the buffer cache for blocks requested by users will make DBWR write dirty
blocks to disk. With that fact in mind, what exactly does processing a commit
statement consist of? The following list tells all:

-Release table/row locks acquired by transaction
All row locks (or even table locks, if any were acquired) are released by
issuing commit statements. Other users can then modify the rows (or tables)
previously locked by this user.

-Release rollback segment locks acquired by transaction
Changes to rollback segments are subject to the same locking mechanisms as
other objects. Once the change is committed, the space to hold both old and
new versions of data for that transaction in the rollback segment is
available for another user’s transaction.

-Generate redo for committed transaction
Once the commit takes place, a redo entry is generated by the user process
stating that all the changes associated with that transaction have now been
committed by the user.

Note that Oracle takes no special action related to redo information as the
result of that commit, other than to indicate that the transaction has been
committed. How does Oracle know which DML statement redo entries to associate
with each transaction? The answer is the system change numbers (SCNs).

An SCN is an ID that Oracle generates for each and every transaction that a
user process engages.
Every redo entry for every data change lists the change made and the SCN the
change is associated with. The redo entry for the commit also identifies the
SCN and simply notes that this SCN has been committed.
Thus, Oracle can keep easy track of the status of every transaction via the SCN.