Database Lock Management Optimizing Savepoint Recovery
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face inefficiencies in recovery processes due to blocking of pages during savepoints, leading to increased write I/O operations and prolonged recovery times, especially when handling multiple lock requests.
Innovation Solution
The system estimates the time required for non-exclusive and exclusive lock requests, allowing parallel execution of non-exclusive locks if they finish sooner and delaying them if they take longer, thereby optimizing lock management and reducing page blocking during savepoints.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If non-exclusive lock requests are processed sequentially, then lock management is simple, but recovery time increases and productivity decreases
Solution Approach 1:
The system dynamically adjusts lock execution based on estimated completion times. Non-exclusive locks with shorter estimated execution times are allowed to execute in parallel, while those with longer times are delayed. This dynamic approach optimizes recovery speed without requiring complex static scheduling structures.
Solution Approach 2:
The system performs preliminary estimation of lock execution times using historical data and code location analysis before making scheduling decisions. This preliminary action enables the system to proactively determine which locks can execute in parallel, reducing recovery time without adding runtime complexity.
2Reliability
If pages are blocked during savepoints, then data consistency is maintained, but write I/O operations increase and time is lost
Solution Approach 1:
The system dynamically determines page blocking duration based on estimated lock completion times. By allowing non-exclusive locks to execute in parallel when their estimated completion time is shorter, the system minimizes page blocking duration while maintaining data consistency through controlled parallel execution.
Solution Approach 2:
The system uses historical execution data and code location information to self-estimate lock completion times without external intervention. This self-service capability enables automatic optimization of page blocking duration based on learned patterns, reducing time loss while preserving consistency.
3Device complexity
If non-exclusive locks are delayed until after exclusive locks, then locking order is simple, but recovery time increases
Solution Approach 1:
The system performs preliminary estimation of lock execution times using historical data before executing locks. This preliminary action enables the system to make informed decisions about lock execution order, allowing non-exclusive locks to proceed before exclusive locks when appropriate, thereby improving recovery efficiency without significantly complicating the locking protocol.
Solution Approach 2:
The system changes the execution parameter of lock requests based on estimated completion times. Non-exclusive locks with shorter estimated times are granted higher execution priority and allowed to proceed before exclusive locks, while those with longer times maintain traditional ordering. This parameter-based approach improves recovery efficiency with minimal protocol complexity.
Data Source
AI summary
A request is received for a first non-exclusive lock to read a page in a database. Thereafter, execution of the at least one non-exclusive lock is initiated. Later, a request for an exclusive lock to modify the page in the database is received. Subsequently, a request is received for a second non-exclusive lock. In response, an amount of time to finish the first request and the second request is estimated. The second non-exclusive lock is allowed to execute in parallel to the first non-exclusive if the determined amount of time to finish the second non-exclusive lock is less than an estimated amount of time needed by the first non-exclusive lock. Alternatively, execution of the second non-exclusive lock is delayed to after the execution of the exclusive lock if the determined amount of time to finish the second non-exclusive lock is greater than an amount of time needed by the first non-exclusive lock.