Subscription-Based Change Data Capture Using Database Triggers
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current mechanisms for replicating data from source systems to target systems are inefficient, especially with a large number of subscribers and high update rates, often leading to system locking issues during data insertion or update operations.
Innovation Solution
A system utilizing a Change Data Capture (CDC) engine with database triggers, master and subscriber logging tables, and a delta queue module to decouple data replication, allowing for adaptable configurations and separate handling of insert, delete, and update operations without locking, enabling efficient data replication across multiple subscribers.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If typical data replication mechanisms are used, then data updates can be captured and provided to subscribers, but the system locks when attempting to insert new records while sending previously related records, causing operations to cease
Solution Approach 1:
The patent segments the data replication process into distinct components: a CDC engine that captures changes, a logging table that stores change records, and a separate replication process that sends data to subscribers. This segmentation allows the CDC engine to commit records to the logging table without locking, while the replication process independently retrieves and sends data to multiple subscribers, eliminating the blocking issue.
Solution Approach 2:
The patent introduces a logging table as an intermediary between the CDC engine and subscribers. The CDC engine writes change records to this intermediate storage without direct interaction with subscribers, allowing concurrent operations without locking. The replication process then reads from this intermediate storage and distributes data to subscribers independently.
2Quantity of substance
If mechanisms copy data for multiple subscribers, then subscribers receive updates, but the process becomes significantly slow and inefficient with greater numbers of subscribers and higher rates of updates
Solution Approach 1:
The patent implements preliminary action by having the CDC engine capture and commit change records to the logging table immediately, before subscriber notification. This allows the system to prepare data in advance in an efficient, lock-free manner, then distribute it to multiple subscribers without blocking, thereby maintaining high replication speed even with many subscribers.
Solution Approach 2:
The patent allows the CDC engine to perform excessive action by capturing and logging all data changes at full speed without waiting for subscriber processing capacity. This ensures no data is lost, and the logging process operates at maximum efficiency regardless of the number or processing speed of subscribers.
3Productivity
If the rate of updates increases, then more data changes are captured, but the system may lock when attempting to insert new records while sending previously related records
Solution Approach 1:
The patent segments the high-rate update processing into independent stages: rapid capture by the CDC engine, immediate logging to the change table, and separate batched distribution to subscribers. This segmentation allows the system to maintain high update rates while ensuring stability, as each stage operates independently without blocking others.
Data Source
AI summary
Disclosed herein are system, method, and computer program product embodiments for replicating data from a source database table to a target database table. An embodiment operates by maintaining a master logging table in communication with a source database table and a subscriber logging table. Thereafter, a copy of a first modification of data of the source database table is provided to the master logging table as a record, where the first record includes the copy of the first modification of data. Subsequently, upon determining that the first record in the master logging table is committed, a copy of the first record is provided to the subscriber logging table. And after identifying a first target database associated with the master logging table, the first record is sent to the first target database.