Row-ID Resynchronization for Minimal Table Lock Duration
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database management systems face challenges in efficiently detecting lost replication changes and resynchronizing replica tables with source tables during restart operations, particularly in asynchronous table replication, leading to increased downtime and performance overhead.
Innovation Solution
The implementation of a row-ID column-based system for lost change detection and resynchronization, where each row has a unique row-ID column that is incremented for each write transaction, allowing for efficient comparison and synchronization between source and replica tables without the need for a persistent replication log queue, and minimizing table lock duration during resynchronization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the database management system locks the source table during lost change detection and resynchronization, then data consistency is ensured, but system availability decreases and downtime increases
Solution Approach 1:
The patent segments the resynchronization process into two distinct phases: a locking phase that briefly acquires the table lock to ensure consistency, and an unlocked phase that performs the actual data comparison and resynchronization using row-ID columns. This segmentation allows the system to maintain data consistency while minimizing the duration of table locking, thereby improving system availability during resynchronization operations.
Solution Approach 2:
The patent implements preliminary action by acquiring the table lock only for the minimal necessary time to capture the current state of the source table, then releasing the lock before performing the resynchronization. This preliminary locking action ensures data consistency without extending the locked state, allowing the system to maintain higher availability during the resynchronization process.
2Reliability
If the database management system performs comprehensive lost change detection, then data integrity is ensured, but performance overhead increases
Solution Approach 1:
The patent replaces the traditional mechanical approach of comprehensive table scanning with a more efficient mechanism based on row-ID columns. Instead of scanning entire tables to detect lost changes, the system uses row-ID comparisons to identify and resynchronize only the specific rows that have changed, significantly reducing the computational overhead and improving performance while maintaining data integrity.
Solution Approach 2:
The patent extracts the essential identification information (row-ID columns) from the comprehensive table data and uses this extracted information for lost change detection. By taking out only the necessary row identifiers rather than processing entire tables, the system reduces performance overhead while ensuring data integrity through targeted resynchronization of affected rows.
3Reliability
If the database management system uses traditional replication log queues, then lost changes can be tracked, but device complexity increases
Solution Approach 1:
The patent uses row-ID columns as a simplified copy mechanism to track lost changes, replacing the complex persistent replication log queue system. Instead of maintaining separate log structures and replay mechanisms, the system copies and compares row-ID values between source and replica tables to detect and resynchronize lost changes, significantly reducing device complexity while maintaining reliable change tracking.
Data Source
AI summary
Disclosed herein are system, method, and computer program product embodiments for minimizing table lock duration during lost change resynchronization between a source table and a replica table. An embodiment operates by detecting a restart at a source node or a replica node. A source table at the source node is then locked based on the detection. A database transaction is then created for a lost change resynchronization operation and the source table is unlocked. The database transaction is then compared to another database transaction based on its timestamp. Based on the comparison, either the database transaction for the lost change resynchronization operation or the other database transaction is executed.