Atomic Database Transaction Processing for Metadata Consistency
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems automatically commit operations after each DDL statement, leading to undesirable behavior, such as intermediate states being visible to other clients, and complex rollback requirements, especially when multiple DDL statements or mixed DDL and DML statements are involved, which can result in errors or corrupted data.
Innovation Solution
Implementing a database system that allows atomic commit or rollback of transactions including DDL statements, enabling database clients to control when commit operations occur, and providing versioning and deadlock detection mechanisms to manage metadata entity changes and ensure data integrity.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If the database system automatically commits after each DDL statement, then the metadata changes are immediately visible to other clients, but intermediate states become visible leading to data inconsistency and complex rollback requirements
Solution Approach 1:
The patent merges multiple DDL statements into a single atomic transaction unit. Instead of committing each DDL statement individually, the system groups them together and commits them as one unit, ensuring that either all changes are applied or none are applied. This eliminates intermediate visible states and simplifies rollback operations.
Solution Approach 2:
The system performs preliminary actions by creating a transaction context before executing DDL statements. The transaction is prepared and validated beforehand, and only after all statements are ready does the system commit the entire transaction. This preliminary preparation prevents partial commits and associated complexity.
2Productivity
If multiple DDL statements are executed in sequence with automatic commit after each, then each statement is immediately applied, but intermediate states are visible to other clients causing errors
Solution Approach 1:
Multiple DDL statements are merged into a single atomic transaction. The system executes all statements within the transaction context and commits them together, preventing intermediate visibility to other clients while maintaining processing efficiency through batch operation.
3Reliability
If the system allows compound transactions with DDL statements, then atomic commit is achieved, but version management and cache invalidation complexity increases
Solution Approach 1:
The system implements feedback mechanisms through version timestamps. When a metadata entity is updated in a committed transaction, a version timestamp is generated and sent to worker nodes. This feedback loop automatically triggers cache invalidation at worker nodes when version mismatches are detected, managing complexity through automated feedback-driven synchronization.
Solution Approach 2:
Version timestamps act as intermediaries between the coordinator node and worker nodes. Instead of directly managing complex version synchronization, the system uses version timestamps as a mediator to signal changes and trigger appropriate cache invalidation actions at worker nodes, simplifying the overall version management process.
Data Source
AI summary
Technologies are described for facilitating transaction processing within a database environment. A commit protocol provides for the atomic commit or rollback of a transaction that includes an operation that modifies a metadata entity of the database system and one or more other operations that modify metadata entities or data records of the database system. Innovations are provided for detecting and resolving deadlocks that may arise during transaction processing. Innovations are also provided for providing versioning of metadata entities, including invalidating metadata entities cached at a slave node during the commit at a master node of a transaction that creates a new version of the metadata entity.