Two Phase Database Table Move Reducing Lock Delays
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In distributed database systems, large-scale redistribution of tables between host systems is impractical and leads to delays due to complex algorithms and cross-host communication, causing performance degradation and table-lock issues.
Innovation Solution
A two-phase move operation is implemented, where source tables are replicated to a destination host system without locking, allowing continuous access and query processing during the first phase, and roles are switched in the second phase to ensure seamless data migration and reduce cross-host communication.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If tables are redistributed between host systems using traditional methods, then data distribution can be adjusted, but table locks are imposed and query access is delayed
Solution Approach 1:
The table redistribution process is segmented into two distinct phases: Phase 1 creates replica tables at the destination host while the source tables remain accessible, and Phase 2 performs a brief role switch. This segmentation allows most of the operation to proceed without locking, minimizing query access delays while still achieving data distribution adjustment.
Solution Approach 2:
In Phase 1, replica tables are created in advance at the destination host system while the source tables remain unlocked and accessible for queries. This preliminary action prepares the data distribution change without imposing locks, allowing queries to continue uninterrupted until the final role switch.
2Adaptability or versatility
If tables are moved using complex redistribution algorithms, then optimal distribution can be achieved, but system complexity increases
Solution Approach 1:
The complex redistribution process is divided into two simple, well-defined phases with clear role transitions. Tables transition from SOURCE role to REPLICA role in Phase 1, then switch roles in Phase 2. This segmentation simplifies the control logic compared to traditional single-phase algorithms while maintaining optimal distribution capabilities.
Solution Approach 2:
The system uses dynamic role assignment where tables can be SOURCE or REPLICA depending on the phase. This dynamic approach allows flexible management of data distribution without requiring complex static algorithms, as the system adapts its behavior based on the current phase and table role.
3Quantity of substance
If cross-host communication is used during table migration, then data can be transferred between hosts, but query performance degrades
Solution Approach 1:
The data transfer operation is segmented into Phase 1 where replicas are created and Phase 2 where roles switch. During Phase 1, cross-host communication occurs for creating replicas but source tables remain accessible without locks. This segmentation isolates the communication overhead to a specific phase, maintaining query performance throughout most of the migration process.
Solution Approach 2:
The replica table acts as an intermediary during the migration process. Data is transferred to create the replica, but the original source table remains in place and accessible for queries. This intermediary approach allows data transfer without forcing queries to use cross-host communication, preserving query performance.
Data Source
AI summary
A two phase move technique for moving groups of tables may reduce cross-host communication and length of table locks. A group including a first table and a second table may be moved to the destination host system. This is done by creating a third table replicating the first table and creating a fourth table replicating the second table on the destination host, and replicas of other tables in the group. The tables in the group are not locked against modifications during the creation of the replica tables. After the creation of the replicas, roles of the original tables and the created tables are switched such that the original tables are set to the replica role and the created tables stored on the destination are set to the source role. The original tables are dropped after the switching of the roles.