Database Table Key Column Replication Without Exclusive Locks
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems face challenges in replicating tables across multiple servers without disrupting access to the table, particularly in generating unique key values that do not conflict across the replication domain, especially when adding new records or recovering from server restarts.
Innovation Solution
A method is introduced where a database management system generates unique key values for both existing and new records using specific algorithms, storing these values in a key column added to the table without requiring exclusive access, utilizing counters to avoid collisions and ensure consistency across the replication domain.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a key column is added to a table to store unique key values for replication, then data integrity and uniqueness across replication domain are improved, but table access disruption and system complexity increase
Solution Approach 1:
The system generates key values for existing records before adding the key column to the table. This preliminary action ensures that key values are prepared in advance, allowing the column to be added without disrupting ongoing table access or requiring exclusive locks during the key generation process.
Solution Approach 2:
The patent extracts the key value generation process from the table schema modification process. By separating these operations, the system can generate key values independently using triggers or application logic, then add the key column separately without requiring exclusive access to the table during both operations.
2Reliability
If key values are generated for existing records during table replication, then uniqueness across replication domain is achieved, but processing time and system resources increase
Solution Approach 1:
The system performs key value generation as a preliminary action before the actual table replication begins. Existing records are processed in advance to generate their key values, so that when replication starts, the key column can be added and existing data can be populated without blocking the replication process.
Solution Approach 2:
The patent implements dynamic key value generation where the system adapts its behavior based on whether records are new or existing. Triggers automatically distinguish between insert operations for new records versus updates for existing records, applying appropriate key generation logic dynamically to minimize processing overhead.
3Reliability
If exclusive access is required to add a key column to a table, then data consistency is ensured, but application access and replication operations are blocked
Solution Approach 1:
The system adds the key column to the table schema in advance, before replication begins. By performing this schema modification as a preliminary action during a maintenance window or with minimal locking, the system avoids the need to acquire exclusive locks during replication operations, thereby maintaining application access and productivity.
Solution Approach 2:
The patent introduces an intermediary mechanism (such as a trigger or application-level logic) that handles key value generation without requiring direct modification of the table schema during operation. This intermediary allows the system to maintain data consistency while avoiding exclusive access requirements during active replication and application access.
Data Source
AI summary
Techniques are disclosed for adding a key column to a table to be replicated, where the key column stores key values for use in replication. In one embodiment, a request is received to add a table to a replication domain, such that the table is replicated from a first database server to at least a second database server. Key values are generated for both existing records and new records subsequently requested to be inserted into the table, respectively. Each generated key value is unique across the replication domain. Further, the key column may be added to the table without requiring exclusive access to the table.