Database Exclusive Lock Timeout for Savepoint Recovery
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database recovery systems face inefficiencies in managing exclusive locks during savepoints, leading to prolonged page blocking and increased write I/O operations, which can hinder rapid recovery and high availability.
Innovation Solution
The solution involves selectively timing out exclusive locks in a database based on various statistics such as time since the last savepoint, redo log activity, log segment blocking, log filling level, modified pages, and threads waiting for consistent changes, allowing non-exclusive locks to proceed and enabling restart of exclusive locks after processing these locks.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If exclusive locks are held until completion during savepoints, then data consistency is ensured, but page blocking time increases
Solution Approach 1:
The patent applies dynamics by making the lock behavior adjustable rather than fixed. The system can dynamically switch between holding exclusive locks until completion (for consistency) or timing them out after a threshold (for performance). This allows the system to adapt lock behavior based on current workload conditions, resolving the contradiction between consistency and blocking time.
Solution Approach 2:
The patent changes the parameter of lock timeout threshold to resolve the contradiction. By introducing a configurable timeout parameter, the system can balance between holding locks long enough to ensure consistency and releasing them to reduce blocking. This parameter adjustment allows optimization of the trade-off between reliability and time loss.
2Reliability
If exclusive locks wait for non-exclusive locks during savepoints, then lock completion is ensured, but recovery speed decreases
Solution Approach 1:
The patent applies the skipping principle by allowing non-exclusive locks to skip waiting for exclusive locks when the exclusive lock has timed out. This enables non-exclusive operations to proceed independently, rushing through the savepoint process faster while still ensuring eventual lock completion through restart mechanisms, thus improving recovery speed.
Solution Approach 2:
The patent applies preliminary action by timing out the exclusive lock before requiring non-exclusive locks to wait for its completion. This preliminary timeout action allows the system to prepare for recovery by releasing blocking locks in advance, reducing the overall recovery time while maintaining data consistency through subsequent restart and retry mechanisms.
3Reliability
If savepoints wait for all locks to release, then data integrity is maintained, but write I/O operations increase
Solution Approach 1:
The patent applies partial action by not waiting for all locks to release before completing the savepoint. Instead, the system waits only for locks that are necessary for data integrity, allowing non-critical locks to timeout and release independently. This partial waiting approach reduces unnecessary write I/O operations while maintaining sufficient data integrity, optimizing the trade-off between reliability and energy loss.
4Productivity
If exclusive locks are restarted after timeout, then page access continues, but lock management complexity increases
Solution Approach 1:
The patent applies feedback by implementing a retry mechanism that monitors lock status and automatically restarts timed out exclusive locks. This feedback loop detects when a lock has timed out and triggers appropriate restart actions, maintaining page access continuity while managing complexity through systematic automated control rather than manual intervention.
Data Source
AI summary
Execution of an exclusive lock of a page in a database is initiated. Thereafter, subsequent to the initiation of the execution of the exclusive lock, at least one non-exclusive lock for the page in the database is received. The execution of the exclusive lock is selectively timed out based on at least one savepoint statistic. While the exclusive lock is timed out, at least one of the received non-exclusive locks is executed. Further, the exclusive lock can be restarted after the execution of the at least one of the received non-exclusive locks.