Partition Level Locking for Concurrent Database Operations
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems apply a table level lock during partition operations, which prevents concurrent operations on other partitions, leading to inefficiencies and bottlenecks in data processing.
Innovation Solution
Implementing a partition level lock that allows operations on one partition while blocking only that partition, while allowing concurrent operations on other partitions, along with schema lock mechanisms, activity log management, and efficient global index rebuilding.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a table level lock is applied during partition operations, then data consistency is ensured, but concurrent operations on other partitions are blocked
Solution Approach 1:
The patent segments the table into multiple partitions and applies locks at the partition level rather than at the table level. This allows independent locking of individual partitions, enabling concurrent operations on different partitions simultaneously. The segmentation principle resolves the contradiction by isolating lock scopes so that data consistency is maintained within each partition while overall system productivity increases through parallel processing.
Solution Approach 2:
The patent implements local locking where each partition has its own lock state independent of other partitions. This local quality approach ensures that lock conditions are evaluated and applied locally to each partition, allowing one partition to be locked for maintenance while other partitions continue to accept operations. This resolves the contradiction by making lock behavior partition-specific rather than table-wide.
2Reliability
If a table level lock is applied during partition split, then data integrity is protected, but operations on other partitions must wait
Solution Approach 1:
The patent segments the locking mechanism to operate independently on each partition. When a split operation is performed on one partition, only that specific partition is locked, while other partitions remain accessible. This segmentation eliminates the waiting time for operations on unaffected partitions while maintaining data integrity through proper locking during the split process.
Solution Approach 2:
The patent implements dynamic lock management where lock states are continuously adjusted based on operational needs. Locks are acquired and released dynamically at partition boundaries, allowing operations to proceed as soon as a partition is ready rather than waiting for the entire table operation to complete. This dynamic approach reduces waiting time while preserving data integrity through controlled access periods.
3Manufacturing precision
If row level locks are used for every operation, then operation precision is maintained, but system complexity increases
Solution Approach 1:
The patent merges multiple row level locks into a single partition level lock when operations affect multiple rows within the same partition. This combining approach maintains operation precision by ensuring consistent locking for all rows in the affected partition while significantly reducing the number of individual lock management operations. The merging principle resolves the contradiction by aggregating fine-grained locks into coarser-grained locks that are easier to manage.
Data Source
AI summary
Techniques of implementing partition level operations with concurrent activities are disclosed. A first operation can be performed on a first partition of a table of data. The first partition can be one of a plurality of partitions of the table, where each partition has a plurality of rows. A first partition level lock can be applied to the first partition for a period in which the first operation is being performed on the first partition, thereby preventing any operation other than the first operation from being performed on the first partition during the period the first partition level lock is being applied to the first partition. A second operation can be performed on a second partition of the table at a point in time during which the first operation is being performed on the first partition.