Versioned Database Uniqueness via Secondary Table Segmentation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face performance issues and increased latency due to the need to lock entire database tables to enforce uniqueness constraints for versioned database objects, which limits concurrent data access and application performance.
Innovation Solution
A computer-implemented method that uses a secondary database table to evaluate and enforce uniqueness constraints for attributes of versioned database objects, allowing for concurrent access without locking the main database table, by determining the uniqueness of attributes and managing record counts in the secondary table.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the entire database table is locked to enforce uniqueness constraints for versioned database objects, then uniqueness constraints are enforced reliably, but concurrent data access is limited and latency increases
Solution Approach 1:
The patent divides the database table into multiple partitions based on the unique attribute values. Each partition contains a subset of records grouped by a common prefix or range of the unique attribute. This segmentation allows concurrent access to different partitions without requiring locks on the entire table, while still enforcing uniqueness constraints across all partitions through coordinated insertion and validation processes.
2Reliability
If the entire database table is locked to enforce uniqueness constraints, then data integrity is maintained, but application performance deteriorates
Solution Approach 1:
The database table is segmented into multiple partitions that can be accessed concurrently. Each partition maintains a subset of the data, and uniqueness constraints are enforced at the partition level through localized checks. This segmentation enables multiple applications to access different partitions simultaneously without conflicting, thereby maintaining data integrity while improving application performance.
Solution Approach 2:
The patent introduces an intermediary mechanism (such as a hash table or index structure) that maps unique attribute values to their corresponding partition locations. This intermediary enables the system to determine which partition a record belongs to without locking the entire table, allowing concurrent access while maintaining uniqueness constraints through coordinated insertion and validation processes.
3Productivity
If row-level locking is used to allow concurrent access, then productivity improves, but uniqueness constraint enforcement becomes complex and error-prone
Solution Approach 1:
The patent segments the database table into multiple partitions based on unique attribute values. Each partition can be accessed and modified independently through row-level locking, enabling concurrent data access. The segmentation strategy ensures that uniqueness constraints are naturally enforced at the partition level, simplifying the enforcement mechanism while maintaining high productivity through parallel operations on different partitions.
Data Source
AI summary
Methods and apparatus for ensuring uniqueness of database object attributes are disclosed. An example computer-implemented method includes receiving a request to insert, update or delete a versioned database object having a first identifier (ID) in a main database table. The method further includes determining, based on the request, whether to fire an insert trigger, a delete trigger or an update trigger for the main database table. In the event an insert trigger is fired, the method includes performing, in a secondary database table, a record insertion process. In the event a delete trigger is fired, the method includes performing, in the secondary database table, a record deletion process. In the event an update trigger is fired, the method includes performing, in the secondary database table, at least one of the record insertion process for a post-update versioned database object and the record deletion process for a pre-update versioned database object.