Data Staging Table Parallel Reads
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face reduced data throughput and limited parallelism due to locking mechanisms that prevent multiple consumer jobs from processing data changes in parallel, requiring manual configuration and reducing system efficiency.
Innovation Solution
Implementing a staging system that allows consumer jobs to process packages associated with a table key value only if no other packages are currently being processed, using sequence IDs to ensure chronological order and allowing parallel consumption of data changes without locking, thereby decoupling the source and target systems.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If locking mechanisms are used to ensure data consistency between source and target systems, then data consistency is maintained, but data throughput is reduced and parallel processing is prevented
Solution Approach 1:
The patent segments the data processing workflow by introducing a staging table that decouples the producer job from consumer jobs. Packages are written to the staging table independently and can be read by multiple consumer jobs in parallel, eliminating the need for system-wide locking while maintaining data consistency through package version tracking and chronological ordering within each consumer job's processing sequence.
Solution Approach 2:
The staging table acts as an intermediary between the source system and target system. The producer job writes packages to the staging table without waiting for consumer jobs, and consumer jobs read from the staging table independently. This intermediary mechanism enables parallel processing while ensuring data consistency through package metadata tracking and ordered application of changes.
2Reliability
If a single consumer job is associated with each key value to ensure chronological order, then correct application of changes is ensured, but system parallelism is reduced and manual configuration is required
Solution Approach 1:
The patent segments the consumer job processing by allowing multiple consumer jobs to operate in parallel on different packages, while each consumer job independently processes packages in chronological order based on package metadata (creation time, sequence ID). This segmentation eliminates the need to associate a single consumer job with each key value while maintaining correct chronological application of changes through package version tracking.
Solution Approach 2:
The patent uses package metadata parameters (creation timestamp, sequence ID, package version) to enforce chronological ordering without restricting parallelism. Multiple consumer jobs can read and process packages simultaneously by comparing these parameters to determine the correct application order, eliminating manual configuration while ensuring changes are applied in the correct sequence.
3Reliability
If producer job waits for consumer job notification before processing new packages, then data consistency is maintained, but processing speed is reduced
Solution Approach 1:
The patent implements preliminary action by having the producer job write packages to the staging table immediately without waiting for consumer job confirmation. Package metadata (creation timestamp, sequence ID, version) is recorded at the time of writing, establishing chronological order in advance. Consumer jobs independently read and process packages later, maintaining data consistency through metadata validation rather than synchronous waiting.
Solution Approach 2:
The system uses asynchronous feedback mechanisms where consumer jobs process packages independently and update their status without blocking the producer job. The producer job continues writing new packages to the staging table based on source system changes, while consumer jobs process packages at their own pace by reading package metadata to determine processing order, eliminating waiting time while maintaining consistency.
Data Source
AI summary
Systems and methods to read records of a data staging table, where each record of the data staging table is associated with a package identifier, a key value of a record of a first database table, values of one or more non-key fields of the record of the first database table, and a database operation, include reading of one or more records of the data staging table, each of the read one or more records associated with a package identifier indicating the record is not being processed, and not including a same key value as any other record of the data staging table associated with a package identifier indicating the record is being processed, updating the package identifier of each of the read records of the data staging table to a first package identifier indicating that the record is being processed, creating a transaction record of a transaction queue associating the data staging table and the first package identifier, determining that the read one or more records have been processed, and, in response to the determination, deleting the one or more read rows from the data staging table and the transaction record.