Parallel CDC Stream Processing with Row Lock Avoidance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Change data capture (CDC) systems face inefficiencies in processing and writing data to change tables due to serial processing, which leads to increased network delays and resource consumption, and the issue of row locks when writing in parallel.
Innovation Solution
Processing CDC streams in parallel by decoding and reassembling events with shared transaction identifiers, combining events that write to the same row into single operations, and using prepared statements to write to change tables efficiently, thereby reducing the number of events and avoiding row locks.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If CDC data is written to the change table in series, then data accuracy is ensured, but network delay increases and resource consumption increases
Solution Approach 1:
The patent segments CDC events into batches and processes them in parallel using multiple worker threads. Each worker thread handles a portion of the events independently, allowing concurrent writes to the change table. This segmentation enables parallel processing while maintaining data accuracy through proper transaction management and ordering within each batch.
Solution Approach 2:
The system dynamically adjusts the batch size and number of worker threads based on system load and performance metrics. The parallel processing architecture allows the system to adaptively optimize between serial processing (for accuracy-critical operations) and parallel processing (for throughput-critical operations), resolving the contradiction between reliability and time loss.
2Productivity
If CDC data is written to the change table in parallel, then processing speed improves, but row locks occur causing delays
Solution Approach 1:
The patent segments events into batches assigned to different worker threads, ensuring that each thread processes a distinct subset of events. This segmentation reduces the probability of row lock conflicts by distributing write operations across multiple threads that can operate independently on different data partitions.
Solution Approach 2:
The system implements partial parallelization by processing events in batches rather than fully parallelizing all operations simultaneously. This controlled approach to parallelism allows the system to achieve improved processing speed while managing row lock conflicts through batched, coordinated writes that reduce contention compared to fully simultaneous parallel operations.
3Reliability
If multiple events writing to the same row are processed separately, then data integrity is maintained, but the number of write operations increases
Solution Approach 1:
The patent merges multiple events that target the same row into a single batched write operation. By grouping these events within the same transaction batch, the system maintains data integrity through atomic transaction processing while reducing the total number of individual write operations. This combining approach eliminates redundant round trips to the database and improves write operation efficiency.
Solution Approach 2:
The system performs preliminary analysis of incoming CDC events to identify and group events that write to the same row before executing write operations. This preliminary sorting and batching ensures that related events are processed together in efficient batch operations, maintaining data integrity while optimizing write performance by reducing operation count.
Data Source
AI summary
Methods and systems described herein may include processing a change data capture stream in parallel and optimizing the write operations contained in the change data capture stream such that the write operations may be written to a change table in parallel. Processing the change data capture stream may include decoding the transaction data contained in the change data capture stream in parallel. After the transaction data in the change data capture stream has been decoded, the transaction data may be analyzed to identify write operations that write to the same row. Write operations that write to the same row may be merged into a single write operation. The merging of write operations may be performed until each write operation writes to a different row. The write operations may then be written to the change table in parallel.