Hierarchical B-tree Index Locking for Concurrency
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional hierarchical locking schemes in database systems face challenges with large databases and indexes, as locking an entire index can be too restrictive, while locking millions of individual leaf pages or keys requires excessive locks, leading to concurrency issues.
Innovation Solution
The implementation of new hierarchical lock modes that allow locking a key, the gap between keys, and combinations thereof, along with key range locking using separator keys and locks on key prefixes, to optimize lock management and concurrency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If an entire index is locked, then consistency is maintained, but concurrency is reduced and many transactions are inhibited
Solution Approach 1:
The index is segmented into multiple levels (index level, partition level, leaf page level, individual key level) allowing transactions to lock only the specific segment they need rather than the entire index. This enables fine-grained locking where a transaction can lock a single key or a small range of keys without blocking other transactions that access different parts of the index, thus maintaining consistency while improving concurrency.
Solution Approach 2:
Different locking granularities are applied to different parts of the index structure based on local needs. The system allows transactions to choose the appropriate lock level (key, page, partition, or index) depending on the scope of their operation, enabling local optimization where small transactions use fine-grained locks and large transactions use coarser locks, maximizing overall system concurrency.
2Productivity
If millions of individual leaf pages or keys are locked, then concurrency is improved, but lock overhead and complexity increase
Solution Approach 1:
The locking system is segmented into hierarchical levels where transactions can acquire locks at different granularities. Instead of managing millions of individual key locks, the system provides intermediate locking levels (pages, partitions) that group multiple keys together, reducing the total number of locks needed while maintaining concurrency control.
Solution Approach 2:
The system adds a hierarchical dimension to lock management, organizing locks from fine-grained (individual keys) to coarse-grained (entire index) levels. This dimensional organization allows transactions to select the appropriate lock level, reducing the complexity of managing individual key locks while preserving concurrency benefits through selective fine-grained locking when needed.
3Use of energy by moving object
If a transaction holds many locks and escalates to an index lock, then computing resources are saved, but other concurrent transactions are blocked
Solution Approach 1:
The locking granularity is made dynamic, allowing transactions to adjust their lock scope based on actual needs. Transactions can start with fine-grained locks on specific keys or ranges and escalate to coarser locks only when necessary, rather than pre-committing to a fixed lock level. This dynamic approach optimizes resource usage while minimizing blocking of other transactions.
Solution Approach 2:
The system performs preliminary locking at the appropriate granularity before transactions begin their operations. By allowing transactions to acquire locks at the minimum necessary level first, the system prevents premature escalation that would block other transactions, while still providing the option to escalate if computing resource optimization becomes necessary.
Data Source
AI summary
Portions of a B-tree index in a database are locked for concurrency control. In one example, hierarchical lock modes are provided that permit locking a key, a gap between the key and the next key, and a combination of the key and the gap. In another example, key range locking may be applied to the B-tree index using locks on separator keys of index nodes. In another example, key range locking may be applied to the B-tree index using locks on key prefixes.