Database Upgrade Key Locking via Select Triggers
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing zero downtime upgrade procedures face challenges in managing write accesses to shared tables, as changes made by the production system can invalidate the upgrade results, requiring locks on entire tables rather than specific keys, which is inefficient and difficult to bypass at the database layer.
Innovation Solution
The implementation of select triggers that read keys from production tables and write them into a lock table, preventing applications from writing to those keys by using freeze triggers, allowing for precise locking of specific keys rather than entire tables, thereby managing write accesses efficiently during upgrades.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If locks are applied to entire tables during upgrade procedures, then data consistency is ensured, but system performance and operational efficiency deteriorate due to excessive locking scope
Solution Approach 1:
The patent segments the locking scope from entire tables to specific key ranges within tables. The upgrade procedure identifies and locks only the specific key ranges that are being read or modified, rather than locking all keys in the table. This segmentation maintains data consistency for the affected keys while allowing other keys to remain accessible, thus improving operational efficiency during upgrade procedures.
2Reliability
If locks are applied to entire tables during upgrade procedures, then data consistency is ensured, but the complexity of managing locks increases
Solution Approach 1:
The patent introduces a lock management system that tracks and manages locks at the key range level rather than table level. The system maintains a data structure that records which key ranges are locked and by which upgrade procedures, enabling precise control and easier management of locks compared to blanket table-level locking.
Solution Approach 2:
The patent implements a feedback mechanism where the lock management system continuously monitors and updates the state of locked key ranges. When upgrade procedures complete or when key ranges are no longer needed for upgrading, the system automatically releases the corresponding locks, providing real-time feedback on lock status and reducing manual management complexity.
3Productivity
If zero downtime upgrade procedures are implemented, then system availability is improved, but the risk of data inconsistency increases due to concurrent write accesses
Solution Approach 1:
The patent applies preliminary locking actions on key ranges before the upgrade procedure reads or modifies them. The lock management system pre-identifies and locks the specific key ranges that will be affected by the upgrade, ensuring that no concurrent write operations can occur on these keys during the upgrade process, thus preventing data inconsistency while maintaining zero downtime operation.
Solution Approach 2:
The patent introduces a lock management system as an intermediary between the upgrade procedures and the database operations. This intermediary layer coordinates access to key ranges, mediating between upgrade reads/writes and production system operations to ensure data consistency without requiring complete system downtime.
Data Source
AI summary
As part of an upgrade procedure transitioning from a source system to a target system, table entries used by both the source system and the target system can be selectively locked on a row-by-row basis using a lock table and one or more select triggers. Related apparatus, systems, techniques and articles are also described.