Parallel Poll Threads for Database Synchronization Latency
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
As data sizes in relational databases grow, accessing and retrieving data becomes challenging, and enterprises face difficulties in synchronizing data between relational and non-relational databases during migration, requiring efficient methods to maintain data consistency and reduce latency.
Innovation Solution
The method involves a single sync table where multiple threads are assigned to process data in parallel, using a primary key hash to calculate a sequence ID and thread number for ordered replication, ensuring that dependent updates are handled by the same thread and maintaining ordering in the target database.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If data is synchronized sequentially from source database to target database, then data consistency is maintained, but synchronization latency increases
Solution Approach 1:
The patent divides the data synchronization process into multiple independent threads, where each thread handles a specific portion of the sync table (partitioned by thread number). This segmentation allows parallel processing of different data subsets while maintaining consistency within each partition, thereby reducing overall synchronization latency without compromising data consistency.
Solution Approach 2:
The patent implements preliminary actions by pre-calculating and storing sequence IDs and thread numbers in the sync table before actual data replication. Triggers automatically assign these values when rows are inserted or updated, ensuring that parallel threads can immediately process data in the correct order without requiring complex coordination during synchronization, thus reducing latency while maintaining consistency.
2Productivity
If multiple threads process data in parallel from the sync table, then synchronization speed increases, but maintaining update order becomes difficult
Solution Approach 1:
The patent pre-calculates sequence IDs and thread numbers for each row in the sync table using triggers before parallel processing begins. Each row is assigned a specific thread number based on its sequence ID, ensuring that when multiple threads process data in parallel, the ordering information is already embedded in the data structure, allowing correct reassembly at the target database without complex inter-thread coordination.
Solution Approach 2:
The patent uses the sync table as an intermediary structure that stores data with embedded sequence IDs and thread numbers. This intermediary table acts as a buffer between the source database and target database, allowing parallel threads to read and process data independently while the sequence ID serves as a mediator to ensure correct ordering when data is written to the target database.
3Reliability
If a single thread processes all sync operations, then update ordering is maintained, but processing throughput is limited
Solution Approach 1:
The patent segments the sync table processing into multiple threads, with each thread responsible for a specific range of sequence IDs or a specific thread number. This segmentation allows simultaneous processing of multiple data subsets while maintaining ordering within each segment, thereby increasing overall throughput without completely losing the ordering guarantee that a single thread would provide.
Solution Approach 2:
The patent performs preliminary assignment of thread numbers and sequence IDs to each row in the sync table using database triggers. This pre-organization of data allows multiple threads to process data in parallel while knowing exactly which rows they should handle and in what order, eliminating the need for a single thread to sequentially process all operations and thereby increasing throughput while maintaining ordering.
Data Source
AI summary
Database synchronization by initiating a transaction to synchronize data from a source database to a target database. The source database issues a trigger that provides a sequence identification (ID) and calculates a thread number that is employed for syncing the rows from the source database to the target database based on a primary key hash. The sequence identification (ID) and the thread number from the source database is inserted into a synchronization (SYNC) table. Data synchronization may also include picking the rows that are allotted their thread number from the SYNC table using polling threads. The rows from the SYNC table may be replicated into target database using a sequence provided by the sequence identification and thread number.