How Databases Log Data Changes 
The database system logs all transactions that change application data or the database catalog in log entries. The database system uses these log entries to roll back or repeat SQL statements, if necessary.
Example
A power outage has occurred. The data changes that the last transactions executed had only been stored by the database system in the data cache and had not yet been written to the data area with a savepoint. These data changes in the data cache (working memory) were lost due to the power outage and must therefore be repeated by the database system.
More information: Savepoint
Name |
Alternative Name |
Use |
|---|---|---|
Undo Log Entries |
Before Images |
|
Redo Log Entries |
After Images |
|
When Does the System Write Which Log Entries?
At the beginning of a transaction that will change data, the database system writes undo log entries (before images) for all database objects that are to be changed by the transaction to an undo log file in the data area.
Each undo log entry in the undo log file receives an undo log sequence number, starting with 0.
During the transaction the system continuously writes redo log entries (after images) to a log queue in the working memory. A transaction always writes to the same log queue.
When the log queue is full, the log writer writes the redo log entries to the log area.
When writing the redo log entries to the log area, the log writer gives each page a sequence number and a time stamp. Using the sequence numbers, the database system can later determine the definitive sequence of the pages in the log area. The timestamp is required by the database system for it to be able to restore the database to its state at a certain point in time.
The time intervals between the individual write operations from the log queue to the log area are much smaller than the intervals between savepoints.
CautionIf the log area is full, the database system locks all log queues and holds all transactions. Database users cannot perform any further data changes in the database. You then must back up redo log entries from the log area to data carriers so that the database system can again overwrite old redo log entries in the log area (exceptions: overwrite mode is activated for the log area, or automatic log backup is activated).
End of the caution.
Transactions with Intermediate States (Example)
At the end of the transaction, the following situations are possible:
The transaction is closed with a COMMIT.
The database system writes the rest of the redo log entries (after images) from the log queue to the log area. Log pages in the log queue that were not full at the time of writing remain in the log queue. The system fills these log queues and writes them to the log area again during the next write process.
The transaction is retracted with a ROLLBACK.
The database system uses the undo log files to roll back all data changes.
Redo log entries already written to the log area or the log queue remain there and are ignored by the database system.
The database system writes links to the undo log files of the completed transactions in history files. For performance reasons, there are always several history files open at the same time. The database system calculates the number of history files based on the general database parameter MaxUserTasks.
At each savepoint, the database system backs up the updated history files as page chains to the data area.
The garbage collects check the history files in regular intervals of a few seconds. After the garbage collectors have deleted the data that requires deletion, they then also delete the relevant undo log files. This means that the user task does not have to delete the undo log files, and the database system can inform the application sooner that the COMMIT/ROLLBACK command was executed.
More Information
Database Administration, Backing Up Log Entries