Transactional Schema Changes with Atomic DDL Execution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing online schema change mechanisms for distributed databases result in asynchronous execution of DDLs after a schema change transaction commits, leading to potential DDL failures, partial commits, and non-atomicity, as well as non-visible schema changes for subsequent write operations.
Innovation Solution
Implement transactional schema changes by generating a synthetic descriptor with an added schema element that is physically compatible with the current descriptor version, using child transactions to manage schema changes in a series of compatible states, ensuring atomicity and visibility during the schema change transaction.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If schema changes are implemented asynchronously after transaction commit, then data availability is improved, but transactional atomicity and consistency are compromised
Solution Approach 1:
The system performs preliminary actions by creating the new schema version and making it available before the transaction commits. The descriptor is updated to include the new schema version in advance, allowing immediate visibility to write operations while maintaining transactional atomicity through coordinated updates.
Solution Approach 2:
The system introduces an intermediary mechanism (the descriptor with version tracking and schema state management) that coordinates between the transaction commit and schema change propagation. This intermediary ensures that schema changes are visible immediately while maintaining consistency across the distributed system.
2Reliability
If schema changes are made visible immediately for subsequent write operations, then transactional consistency is improved, but system complexity increases
Solution Approach 1:
The system manages schema changes by changing parameters of the descriptor (version number, schema state flags) rather than restructuring the entire schema management system. This allows immediate visibility of schema changes through parameter updates while keeping the underlying complexity manageable through version-based control.
Solution Approach 2:
The system makes the schema dynamic by allowing multiple versions to coexist and be switched between based on transaction state. The descriptor can transition between different schema versions dynamically, enabling immediate visibility changes without permanent structural modifications that would increase complexity.
3Adaptability or versatility
If multiple schema versions are maintained during transition, then online schema changes are enabled, but risk of partial commits and DDL failures increases
Solution Approach 1:
The system prepares cushioning measures by maintaining the old schema version alongside the new version during transition. This allows the system to fall back to the proven old version if the new schema or DDL operations fail, preventing partial commits and ensuring commit reliability while enabling online schema changes.
Solution Approach 2:
The system creates a copy of the schema in a new version rather than modifying the existing one in place. This copying approach allows the new schema to be tested and validated independently, and if failures occur, the original schema copy remains intact and functional, preventing partial commit states.
Data Source
AI summary
Methods and systems for executing transactional schema changes at a database are provided. The method includes receiving, at a gateway node of a cluster of database nodes, a statement corresponding to a schema of a database stored by the cluster, wherein a parent transaction comprises the statement, the statement comprises a schema change operation to modify the schema, and a first version of one or more versions of a descriptor comprises the schema. The method includes generating, by the parent transaction and using one or more child transactions, a version of a synthetic descriptor comprising an updated schema with an added schema element configured to be available for the parent transaction. The method includes writing the synthetic descriptor as an updated version of the descriptor, wherein the updated version comprises the updated schema with the added schema element in a publicly available state. The method includes committing the parent transaction.