Database Index Recovery Using Transaction Log Reversal
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database recovery methods face challenges in restoring a consistent database index to a prior point-in-time, especially when uncommitted transactions are present, leading to extended outages and difficulties in finding suitable recovery points.
Innovation Solution
The method involves obtaining an index copy at a log point preceding uncommitted transactions, sorting and applying transaction log records to the index copy in a single pass, and reversing the effects of uncommitted updates by marking or removing pseudo-delete indicators to restore each key/RID entry to its last committed state.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If traditional database recovery methods are used to restore indexes to a prior point-in-time, then data consistency is achieved, but recovery time is extended and system outage is prolonged
Solution Approach 1:
The patent applies preliminary action by pre-identifying and marking uncommitted transactions in the transaction log before the actual recovery process. By using a two-pass approach where the first pass identifies uncommitted transactions and marks them, the system prepares the recovery data in advance, allowing the second pass to efficiently apply only committed transactions without needing to analyze transaction status during recovery. This preliminary preparation significantly reduces recovery time while maintaining data consistency.
2Reliability
If traditional index recovery methods are used that require separate rollback operations, then uncommitted transactions are removed, but CPU usage and processing overhead increase
Solution Approach 1:
The patent merges the identification of uncommitted transactions and the application of committed transactions into a unified two-pass recovery process. Instead of performing separate rollback operations to remove uncommitted transactions and then applying committed transactions, the system combines these operations by marking uncommitted transactions during the first pass and efficiently applying only committed transactions during the second pass. This merging eliminates redundant processing and reduces CPU overhead.
Solution Approach 2:
The patent extracts and marks uncommitted transactions during the first pass, separating them from the normal transaction application process. By using a dedicated marking mechanism (such as setting a pseudo-delete indicator) during the first pass, the system isolates uncommitted transactions from the recovery process, allowing the second pass to focus solely on applying committed transactions without needing to perform separate rollback operations.
3Productivity
If the index copy is obtained at a log point that includes uncommitted transactions, then recovery speed is improved, but index consistency with the recovered database is compromised
Solution Approach 1:
The patent applies preliminary action by performing a first pass through the transaction log to identify and mark all uncommitted transactions before the actual index recovery is performed. This preliminary identification and marking of uncommitted transactions allows the system to obtain an index copy at a log point that includes uncommitted transactions (improving recovery speed) while ensuring consistency is maintained through the subsequent marking and filtering process in the second pass.
Solution Approach 2:
The patent introduces a pseudo-delete indicator as an intermediary mechanism that marks uncommitted transactions in the index without permanently applying them. This intermediary marking allows the system to work with an index copy that includes uncommitted transactions for speed, while the pseudo-delete indicators serve as a buffer to prevent these uncommitted changes from affecting the final consistent state. The intermediary marker enables efficient processing while maintaining the ability to exclude uncommitted transactions from the recovered index.
Data Source
AI summary
A method to efficiently create a consistent image of a database index at any point-in-time by reversing the effect of uncommitted updates by applying database transaction logs to an index page set in a single pass. The created image can be used to recover the database index to a point in time that is consistent with its associated table, or for physical migration where data and indexes are migrated to another database that may or may not be on the same DBMS and the indexes and data must be consistent as of the same point in time.