A study of Oracle commit

Oracle is still more commonly used, so I studied Oracle COMMIT, here to share with you, I hope to be useful. The user can issue a commit,oracle commit trigger LGRW only if the last block of all rows affected by the SQL statement is read into DB buffer and the redo information is written to redo LOG buffer, but does not force immediate dbwn to release all The corresponding DB buffer block on the lock, but in the next period of time Dbwn still write this statement involved in the data block case, the table head of the row lock, not in the commit immediately release, in fact, to wait until the corresponding DBWN process end will be released.

A user request to lock another user's already-commit resource there is an opportunity to be unsuccessful. After Oracle commit is issued, the "front image" in the rollback segment is identified as committed. The DML statement produces a SCN number, which is written to the head of the data block when the dbwn is triggered, and the commit produces a SCN number, which is also written to the head of the data block. The head of a block of data stores only the latest SCN number,

After a commit, this transaction slot can be used by another transaction. If the user roolback, the server process reconstructs the corresponding modified copy based on the transaction list and the SCN and the rollback segment address of the block in the data file block and DB buffer, and uses the original values to restore the modified but uncommitted changes in the current data file. If there are multiple "front images", the server process finds the rollback segment address of a "front image" in the head of a "front image" until the oldest "front image" under the same transaction is refactored. Once a commit has been made, the user cannot rollback, which ensures that subsequent actions that have not been fully completed after the commit DBWN process have been guaranteed.

Here we refer to the role of the checkpoint, Ckpt trigger, in the following situations:

1. When the log group switch occurs

2. When Log_checkpoint_timeout, Log_checkpoint_interval, Fast_start_io_target, fast_start_mttr_target parameter settings are met

3. When you run alter system Switchlogfile

4. When you run Alter Systemckeckpoint,

5. When running Altertablespacetbs_namebegin backup[end backup]

6. When running Altertablespace[datafile] offline

7. When the system shuts down normally

A full checkpoint occurs only in 4.7 of two cases. When a full checkpoint occurs, the system records the checkpoint SCN corresponding to the checkpoint and records the latest redo byte address (Redo byte (RBA)) of the log file corresponding to the DB buffer that was modified at that time. The DBWN process then writes the dirty buffer in db buffer that occurred prior to this redo byte address (RBA) to the data file (the reason that the redo byte address (RBA) is marked because of the time between the checkpoint and the checkpoint is complete. The system is still constantly being modified, The DB buffer dirty buffer generated by these modifications, as well as the log entries, will not affect the consistency result of the final confirmation of this checkpoint, that is, the final confirmation of the system prior to this checkpoint SCN is consistent.

Finally, the Checkpoint SCN and RBA are updated to the control file, Checkpoint SCN is updated to the head of each data file, indicating that the current database is consistent. Log switching does not result in a complete misconduct, for example, there are three log file groups, when a log switch occurs when checkpoints occur, and log switching is generally because the current LGWR is writing redo log, that is, LGWR when just write full 2nd log immediately triggered checkpoints, The system then begins to check whether the data corresponding to the Redo project recorded in the 3rd log has been written to the data file from DB buffer (regardless of whether the transaction has been committed), and if not written, the checkpoint triggers the DBWN process to write the buffer blocks to the data file, which apparently lgwr to wait for it. , the checkpoint also lets the DBWN process write a modified DB buffer in the 2nd log to the data file, and then continue LGWR the process until the LGWR process writes all the buffers (including uncommitted redo information) that existed in redo log buffer before the LGWR trigger to the Redo log file , the checkpoint updates the data file and controls the file head SCN. In fact, LGWR waiting is not the completion of CKPT, but wait for ckpt triggered DBWN process completion.