Database Record-Level Locking with Page-Level Recovery
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems face challenges in balancing concurrency and recovery, as page-level recovery is fast but exclusive access reduces concurrency, while record-level recovery is time-consuming due to individual reapplication of modifications.
Innovation Solution
A hybrid approach that combines record-level locking for concurrency with a specialized page-level recovery strategy, using companion pages to store incremental updates and writing the database page to the audit trail only when exclusive access is regained, allowing for efficient recovery without blocking other transactions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If page-level recovery is used, then recovery speed is improved, but concurrency is reduced due to exclusive access requirements
Solution Approach 1:
The patent segments the database page into multiple records and applies different locking strategies to different records. Record-level locks are used during transaction processing to allow concurrent access to different records on the same page, while page-level recovery is enabled by storing the complete page image in the audit trail. This segmentation resolves the contradiction by allowing fine-grained concurrency during transactions while maintaining fast page-level recovery capability.
Solution Approach 2:
The patent dynamically adapts the locking strategy based on the operational phase. During transaction processing, record-level locks are applied to enable concurrency. During recovery operations, the stored page images are applied without requiring exclusive access. This dynamic adaptation allows the system to optimize for concurrency during normal operation and for recovery speed when needed, resolving the contradiction between these two requirements.
2Productivity
If record-level recovery is used, then concurrency is improved, but recovery time increases due to individual reapplication of modifications
Solution Approach 1:
The patent performs preliminary action by capturing the complete page image in the audit trail at the time of transaction commit. This pre-stored page image contains all record modifications made during the transaction. During recovery, this pre-captured page image can be directly applied without needing to reapply individual modifications, significantly reducing recovery time while maintaining record-level concurrency benefits.
Solution Approach 2:
The patent merges the advantages of record-level locking (for concurrency) with page-level recovery (for speed) by combining both approaches into a hybrid system. The audit trail stores both the page image and the locking information, allowing the recovery process to leverage the complete page state while the locking mechanism ensures concurrency during transactions. This merging resolves the contradiction by achieving both fast recovery and high concurrency.
3Reliability
If exclusive access to page is maintained until COMMIT, then data consistency is ensured, but other transactions are blocked from accessing the page
Solution Approach 1:
The patent segments the exclusive page-level access into multiple record-level accesses. Each record on the page can be accessed by different transactions concurrently as long as they are accessing different records. The locking mechanism is applied at the record level rather than at the page level, allowing multiple transactions to proceed simultaneously on different records while maintaining consistency through the audit trail. This segmentation resolves the contradiction by enabling higher throughput while preserving data consistency.
Solution Approach 2:
The patent introduces the audit trail as an intermediary mechanism that mediates between transactions. Instead of requiring exclusive access to the page for the entire transaction duration, the audit trail captures the page state at commit time and uses this intermediary information to ensure consistency. This allows transactions to proceed concurrently without blocking each other, while the audit trail intermediary ensures that data consistency is maintained without requiring continuous exclusive access.
Data Source
AI summary
Disclosure of approaches for processing database transactions against a database. In one approach, a first transaction is received that specifies an operation for changing state of a first record stored in a first database page. In processing the operation, the state of the record is changed, and information is stored in a companion page. The information includes a transaction identifier, data describing the specified operation, a page identifier of the first page, a before look and an after look of the first record for an update operation, and an after look of the first record for an insert operation. In response to a commit of the first transaction, a process determines whether a second transaction, that specifies a change in state for a second stored in the first page, is in-process. In response to determining that the second transaction is in process, the companion page is stored in an audit trail. In response to determining that no such second transaction is in process, a transaction identifier of the first transaction and associated data of the first page are stored in the audit trail.