Materialized View Update Conflict Resolution via Query Filtering
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Snapshot isolation in databases can lead to update conflicts and inconsistencies in materialized views, particularly when transactions are allowed to proceed without proper precautions, resulting in reduced database throughput and potential anomalies in data consistency.
Innovation Solution
The solution involves hiding updates to materialized views from transactions that should not see them by using additional information associated with the views, which the query engine employs to exclude data from the results, ensuring data consistency without aborting transactions, thereby maintaining the integrity of the materialized views.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If snapshot isolation is implemented to reduce update conflicts, then database throughput is improved, but data consistency in materialized views deteriorates
Solution Approach 1:
The system performs preliminary actions by recording update information in additional data structures before the transaction completes. This includes storing update identifiers, timestamps, and other metadata that will later be used to filter query results, ensuring consistency is maintained proactively rather than reactively
Solution Approach 2:
Additional information data structures act as an intermediary between the materialized view updates and the query results. This intermediary layer filters and manages the visibility of updates, allowing the system to maintain snapshot isolation while ensuring that transactions only see consistent data states
2Productivity
If transactions are allowed to proceed without delays in snapshot isolation, then productivity is improved, but update conflicts and data inconsistencies increase
Solution Approach 1:
The system creates copies of update information in additional data structures rather than directly modifying the materialized view during transactions. This copying mechanism allows updates to be recorded without blocking transactions, and the copies are later used to filter query results appropriately
Solution Approach 2:
Update information including identifiers and metadata is recorded in advance in additional data structures before transactions complete. This preliminary recording allows the system to maintain high throughput while ensuring consistency through post-processing filtering
3Ease of operation
If materialized view updates are made visible within transactions, then ease of operation is improved, but data consistency deteriorates due to inconsistent data access
Solution Approach 1:
Additional information data structures serve as an intermediary that controls the visibility of materialized view updates. The query engine uses this intermediary information to filter results, ensuring that transactions see only consistent data states while maintaining ease of query operation
Solution Approach 2:
The system applies different visibility rules to different data within the materialized view based on transaction context. Updates are selectively hidden or shown depending on the transaction's snapshot time and the specific data being accessed, maintaining consistency while allowing appropriate data accessibility
Data Source
AI summary
Aspects of the subject matter described herein relate to reducing update conflicts when maintaining views. In aspects, a transaction in snapshot isolation may update a materialized view. Instead of being able to view the update within the transaction, the update is hidden to prevent the transaction from obtaining inconsistent data. When a transaction is not supposed to see an update to a materialized view that occurs within the transaction, additional information associated with the materialized view may indicate this. When a query of the materialized view is made within the transaction, the query engine may use the additional information to exclude data from the update in the result.