Dynamic Table Reconfiguration via Temporary Copy and Lock Switching
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database processing engines require offline reconfiguration when tables or portions of tables are reassigned, leading to inaccessible databases during the process, which is not ideal as it cannot be aborted once initiated.
Innovation Solution
Implementing an online and dynamic table reconfiguration method using a new 'ALTER TABLE MAP' operation that allows table movement between network locations while maintaining database accessibility by using READ and EXCLUSIVE locks, enabling quick rollback and minimal downtime.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If offline reconfiguration process is used to move tables between distributed nodes, then table reconfiguration can be completed, but the database becomes inaccessible during the process
Solution Approach 1:
The patent creates a new map representing the future table location before actually moving the table data. This preliminary mapping allows the system to prepare the destination node and validate the reconfiguration plan before committing to the data transfer, enabling rollback capability and maintaining accessibility throughout the process.
Solution Approach 2:
The patent introduces a temporary table as an intermediary structure at the destination node. The original table data is copied to this temporary table first, allowing the system to verify the copy's integrity and the new map's correctness before switching from the original table to the temporary table, thus maintaining database accessibility.
Solution Approach 3:
The patent implements dynamic lock management where the lock type changes from READ lock during data copying to EXCLUSIVE lock during the final switch. This dynamic adjustment of locking mechanisms allows the system to maintain flexibility and accessibility during different phases of the reconfiguration process.
2Productivity
If batch table movement is performed late at night with little database activity, then table reconfiguration can be completed, but the process cannot be aborted once initiated
Solution Approach 1:
The system creates a new map and copies table data to a temporary table before making any permanent changes to the database structure. This preliminary preparation phase allows the operation to be aborted at any point by simply not completing the final switch, providing adaptability while still enabling full reconfiguration completion.
Solution Approach 2:
The patent creates a copy of the original table data in a temporary table at the destination node rather than moving data directly. This copying approach allows the original table to remain accessible and provides a rollback mechanism where the system can discard the temporary table copy if the reconfiguration needs to be aborted.
3Reliability
If READ lock is obtained during table data movement, then database accessibility is maintained, but data consistency during the move process becomes challenging
Solution Approach 1:
The patent uses a temporary table as an intermediary structure where the table data is copied during the reconfiguration process. The temporary table acts as a buffer that isolates the copying operation from the original table, allowing READ locks to be maintained on the original table while ensuring data consistency through the intermediary structure.
Solution Approach 2:
The patent extracts the table data copying operation into a separate phase that occurs under READ lock, distinct from the final commit phase that occurs under EXCLUSIVE lock. This separation allows the copying to proceed with accessibility maintained while the final consistency check and switch occur atomically when no other operations can interfere.
Data Source
AI summary
A table reconfiguration command is received. A READ lock is placed on the table while original table data for the table is moved from a first network location to a second network location into a temporary table according to a new map for the table. An EXCLUSIVE lock on the table is obtained, a data dictionary is modified with the new map for the table, the original table data at the first network location is deleted, and the temporary table is renamed to be the table at the second network location; then, the EXCLUSIVE lock is released.