Page-Level RLQ Tracking to Reduce Database Lock Contention
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database locks in high-concurrency scenarios lead to contention and performance degradation due to prolonged acquisition and release of locks, especially for row locking queries (RLQs), which affect data integrity and consistency.
Innovation Solution
Implementing page-level noRLQ flags to track the presence of RLQs, allowing data manipulation commands to execute without acquiring locks when no RLQs are detected, and using transaction identifiers (TIDs) to mark and release locks efficiently.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If database locks are used to control access to data in concurrent environments, then data integrity and consistency are ensured, but locking queries cause contention and performance degradation
Solution Approach 1:
The patent segments the locking mechanism by introducing page-level noRLQ flags to track the presence of row locking queries. This allows the system to make granular decisions at the page level about whether to allow lock acquisition, thereby reducing unnecessary lock contentions while maintaining data integrity where needed.
Solution Approach 2:
The patent implements preliminary action by setting noRLQ flags in advance on pages that are known to be accessed by row locking queries. This pre-marking allows subsequent data manipulation commands to quickly determine whether locks are needed without performing full lock acquisition procedures, thus improving performance while maintaining reliability.
2Reliability
If locks are acquired for every data manipulation command, then data consistency is maintained, but lock acquisition and release operations increase
Solution Approach 1:
The patent introduces dynamic behavior by making the locking mechanism adaptive based on the noRLQ flag state. When the flag indicates no row locking queries are present, the system dynamically decides not to acquire locks, reducing operational complexity. When the flag indicates RLQs are present, the system dynamically acquires locks to maintain consistency, thus balancing both requirements.
Solution Approach 2:
The noRLQ flag acts as an intermediary mechanism between the data manipulation command and the lock acquisition process. This intermediary provides quick information about whether locks are likely to be needed, allowing the system to skip unnecessary lock acquisition operations while still maintaining data consistency through the flag-based tracking mechanism.
3Measurement precision
If row locking queries are tracked at the row level, then precise control is achieved, but tracking overhead increases
Solution Approach 1:
The patent merges the tracking function from the row level to the page level by using noRLQ flags stored at the page level. This consolidation reduces tracking overhead significantly while maintaining sufficient precision to determine whether locks are needed, as the flag aggregates the presence of row locking queries across all rows in the page.
Solution Approach 2:
The noRLQ flag serves multiple functions: it tracks the presence of row locking queries, determines lock acquisition necessity, and enables performance optimization decisions. This multi-functionality reduces the need for separate tracking mechanisms, thereby reducing overall system complexity while maintaining precise control where needed.
Data Source
AI summary
Systems, methods, apparatuses, and program products are disclosed for granular tracking of locking queries. Upon receiving a data manipulation command associated with a row of a page, a flag associated with the page is checked to determine whether a row locking query (RLQ) may be associated with the page. If no RLQ is associated with the page, the data manipulation command is executed without acquiring a lock. If an RLQ may be associated with the page, it is determined whether an active lock is associated with the page. If no active lock is associated with the page, the flag is updated to indicate that no RLQ is associated with the page and the data manipulation command is executed without acquiring a lock. If an active lock is associated with the page, page-level and row-level locks are acquired on the page and the row, respectively, before executing the data manipulation command.