Partitioned Table Uniqueness Check via Optimistic Insertion
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In multi-node partitioned database landscapes, verifying the uniqueness of values in non-primary key columns across multiple partitions is inefficient, leading to substantial data traffic and latency due to the need for frequent inter-node calls and exclusive locks, which can result in deadlocks and decreased performance.
Innovation Solution
An optimistic approach is implemented where a record is inserted into one partition without initially verifying its uniqueness, using a write lock on that partition while avoiding read locks on others, and subsequently verifying the uniqueness across all partitions, undoing the insertion if it violates the constraint.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If uniqueness verification is performed across all partitions during insertion, then data integrity is maintained, but data traffic and latency increase substantially
Solution Approach 1:
The patent applies preliminary action by performing uniqueness verification only within the local partition during insertion, rather than waiting until the end to verify globally. This preliminary local check allows the transaction to proceed quickly if no conflict is found, while still maintaining the option to perform full verification later if needed, thus reducing latency while preserving data integrity.
Solution Approach 2:
The patent segments the uniqueness verification process into two distinct phases: a fast local verification phase that checks only the inserting partition, and a slower global verification phase that checks all partitions. This segmentation allows the system to achieve low latency for the common case (no conflicts) while maintaining reliability for the rare case of conflicts, resolving the contradiction between speed and accuracy.
2Reliability
If exclusive locks are acquired on all partitions during insertion, then uniqueness is ensured, but deadlocks occur and performance decreases
Solution Approach 1:
The patent applies local quality by making the locking behavior adaptive to the local situation rather than uniformly locking all partitions. Specifically, it acquires exclusive locks only on partitions that contain conflicting values, while using shared locks or no locks on partitions with no conflicts. This localized locking approach maintains uniqueness constraints where needed while avoiding deadlocks and performance degradation in conflict-free scenarios.
Solution Approach 2:
The patent introduces dynamic locking behavior that adjusts the scope and duration of locks based on the actual data state. Instead of static pre-acquired locks on all partitions, the system dynamically determines which locks are necessary during the verification process and releases them promptly after use. This dynamic approach reduces lock contention and eliminates deadlocks while preserving constraint enforcement.
3Reliability
If frequent inter-node calls are made to verify uniqueness, then global constraint compliance is achieved, but data traffic increases substantially
Solution Approach 1:
The patent applies partial action by performing only the necessary portion of uniqueness verification - specifically, checking only those partitions that contain values matching the inserted record's non-primary key column. Rather than universally verifying against all partitions, the system performs partial verification targeted at relevant partitions only, reducing data traffic while maintaining constraint compliance through selective inter-node calls.
Data Source
AI summary
An insertion of a record into a table that includes a primary key column and a second column that includes a global uniqueness constraint across all of a plurality of data partitions across which the table is split is initiated without checking that a value of the record in the second column is globally unique by contacting other partitions the one partition to which the record is to be added to. The insertion can be processed, at least in part by implementing a write lock on the one partition but without implementing a read lock on the other partitions. The write lock on the one partition can be released after the insertion is completed, after which the validity of the insertion can be verified, for example by examining the other parts and a delta partition corresponding to the table. The insertion can be undone if the insertion was not valid.