Database Table Migration via Triggered Segmented Logging
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current data migration processes between tables often result in business downtime, leading to revenue loss and reputation damage due to the inability to maintain uninterrupted software updates and upgrades.
Innovation Solution
A method involving a trigger mechanism that updates and copies data entries from a source table to a logging table and then to a target table in separate transactions, allowing for zero downtime maintenance by segmenting and locking content to ensure transactional consistency and parallel usage of the source table during the migration process.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If data migration is performed using traditional methods, then data can be migrated from source table to target table, but business downtime occurs during the migration process
Solution Approach 1:
The data migration process is segmented into multiple phases: initial data copy, trigger-based change capture, and final synchronization. The source table is divided into segments that can be locked individually during copying, allowing other segments to remain accessible. This segmentation enables continuous business operations while migration proceeds in the background.
Solution Approach 2:
The target table is created and pre-configured with the same structure as the source table before migration begins. An empty target table is prepared in advance, and the migration infrastructure (triggers, logging tables) is set up beforehand. This preliminary setup allows the migration to proceed without interrupting business operations.
2Reliability
If traditional data migration methods are used, then data can be updated in the source table, but consistency between source and target tables cannot be maintained during parallel operations
Solution Approach 1:
A logging table is introduced as an intermediary between the source and target tables. Triggers on the source table capture changes and write them to the logging table. The target table is then updated by reading from the logging table, ensuring consistency without complex direct synchronization. This intermediary layer simplifies the consistency maintenance mechanism.
Solution Approach 2:
Triggers are implemented on the source table to detect and log any data changes. This feedback mechanism continuously monitors the source table and automatically captures updates, inserts, and deletes. The logged changes are then applied to the target table, ensuring both tables remain consistent without manual intervention.
3Reliability
If the source table is locked during copying to ensure consistency, then transactional integrity is maintained, but business operations on the source table are blocked
Solution Approach 1:
The source table is divided into segments that can be locked and copied individually. Only the segment currently being copied is locked, while other segments remain accessible for business operations. This segmented locking approach maintains transactional consistency for the copied portion without blocking overall table access.
Solution Approach 2:
The locking strategy is dynamic rather than static. Locks are applied temporarily only to the specific segment being copied and are released immediately after copying completes. This dynamic locking allows the system to adaptively manage access, ensuring consistency during copy operations while maximizing availability during idle periods.
Data Source
AI summary
Techniques for migrating data in a database include identifying an update to one or more data entries of a source table; determining that a status of a trigger associated with the source table indicates to update a logging table, and in response: updating, in a first transaction, (i) the one or more data entries of the source table, and (ii) one or more data entries of the logging table based on the user input, and copying content of the source table to a target table; and determining that the status indicates to update the target table, and in response: updating, in a second transaction, (i) the one or more data entries of the source table and (ii) one or more data entries of the target table based on the user input, and updating the content of the target table based on the one or more data entries of the logging table.