Commit, Rollback, Undo & Redo: Unveiling the Intricacies of Maintaining Data Integrity & Consistency in Oracle Database
Oracle Relational Database has a comprehensive system to ensure data integrity and consistency. Various processes and mechanisms are in place to safeguard these aspects. In this article, we will delve into the following:
1. How a transaction happens in the Oracle relational database
2. How Oracle handles rollbacks, whether initiated by the client or caused by fatal errors
3. The roles of UNDO and REDO
4. Factors affecting rollback performance
Let's start with the transaction process and what happens in the backend when a DML statement executes.
Transaction Execution in Oracle Database
When a DML statement is executed, the data to be modified in the table is first read into the Buffer Cache if it is not already there. The Data Buffer is then modified by a process called the Database Writer (DBWn), which marks the modified buffers as dirty. This means the version of data in the buffer cache is different from the data in the disk and needs to be written to the disk.
Before the DBWn process writes the dirty buffer to the actual data files, it ensures that the associated redo entry is written to the disk. This protocol is termed Write Ahead Logging (WAL).
UNDO and REDO in Oracle Database
When a DML operation starts, both UNDO and REDO logs are generated. When you update an existing record, the UNDO stores the original image of the data before the update. Initially, UNDO data is stored in the UNDO Buffer and then in the UNDO Tablespace. Simultaneously, REDO logs are generated in the Redo Log Buffer and later flushed into Redo Log Files and archived.
Understanding the Difference between UNDO and RED
To illustrate the difference, imagine you have a deck of cards in a specific order. If someone mixes the cards in a new order, Oracle collects two types of data:
1. The original order of the cards before each change (UNDO).
2. The changes made to the cards in sequence (REDO).
If a rollback is needed, Oracle uses the UNDO to restore the original data and the REDO to organize the rollback process. REDO logs ensure the rollback process itself is recoverable in case of a system failure during rollback.
Summary of Key Points
1. Generation of Redo and Undo: Both are generated once a DML operation begins, irrespective of whether it is committed or not.
2. Rollback Process: If a rollback is initiated by the client or due to a system crash, the database uses the UNDO data to revert changes.
3. Role of Redo in Rollback: During a rollback, REDO is also used because UNDO only contains the original value, not the sequence of changes.
4. Redo Creation during Rollback: Additional REDO is created for the rollback process, ensuring it is recoverable if interrupted.
During this process, if the Redo Log Buffer fills up by one-third, its contents are written to the Redo Log Files. If the system crashes while the Redo Log Buffer is being written, the database will resume the process upon restart by reading the Redo Log Files' header.
Factors Affecting Rollback Performance
Oracle does not provide a direct way to speed up rollbacks, nor is there a formula to calculate the exact time required. Rollback duration depends on several factors:
1. Undo Segment Management
2. Concurrency
3. Redo Log Processing
4. Transaction Size and Complexity
5. System Load
6. Associated Objects like Indexes and Triggers
Why Rollbacks Can Take Longer than DML Operations
Essentially, rollback is a logical operation, similar to DML operations (insert, update, delete). However, rollbacks sometimes take longer than the original DML operation due to several reasons:
1. Parallelism: Updates might use parallelism, which is not the case for rollbacks.
2. UNDO Data on Disk: If the transaction is large, UNDO data might be flushed to disk, increasing I/O during rollback as this data must be fetched from disk instead of memory.
3. Associated Objects: Indexes, triggers, and other associated objects slow down rollbacks as they also need to be updated.
4. System Load: The overall system load can impact rollback performance.
Critical Situations Impacting Rollback
Consider a scenario where a session updates a large amount of data over several hours, using parallel hints and updating a table with multiple indexes. During this process, a significant amount of UNDO data is generated, which may be written to disk if the transaction is prolonged. If a rollback is triggered due to a failure, object locks will not be released until the rollback is complete.
Setting the parameter FAST_START_PARALLEL_ROLLBACK to high allows parallel processing for transaction recovery, which can help in some cases. However, aborting the database and starting it with this parameter set high can worsen the situation if the data to be recovered is vast. The SMON process, responsible for recovery, may allocate all resources to complete the rollback, causing the database to hang. So always let the Rollback takes its OWN TIME.
Conclusion
The ideal way to handle rollbacks is to allow them to take their own time. Oracle's robust mechanisms ensure data integrity and consistency, even during complex transactions and rollbacks.
By understanding these processes and mechanisms, database administrators and developers can better manage and optimize Oracle databases for reliability and performance.
Please feel free to reach out to me with any suggestions or insights you may have. Happy learning and exploring the depths of Oracle Database management!
Thanks to Google Gemini for the AI illustration Images.
References:
Rollback Is Taking A Long Time After Session Killed (Doc ID 1060831.6)
Parallel Rollback may hang database, Parallel query servers get 100% cpu (Doc ID 144332.1)
Database Hangs Because SMON Is Taking 100% CPU Doing Transaction Recovery (Doc ID 414242.1)
https://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm#sthref612
Factors Affecting Rollback Speed - Ask TOM (oracle.com)
difference between redo log buffer and undo tablespace - Oracle Forums
Prakashvel M
Oracle Database Administrator
Cisco Systems
Comments
Post a Comment