Database Cursor Snapshot Semantics via Statement Sequence Numbers
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In-memory database systems face challenges in ensuring that deleted rows are not visible to cursors, especially in scenarios with numerous concurrent transactions, where multi-version concurrency control mechanisms may fail to provide accurate point-in-time consistent views.
Innovation Solution
Assigning statement sequence numbers (SSN) to database rows and using temporary timestamps with transaction identifiers, while monitoring active cursors to prevent final timestamps from being associated with rows until all cursors are closed, ensures that deleted rows are not visible to cursors.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If multi-version concurrency control (MVCC) mechanisms are adopted to provide point in time consistent views, then concurrent transaction handling capability is improved, but deleted rows may become visible to cursors causing data inconsistency
Solution Approach 1:
The patent applies preliminary action by assigning temporary timestamps to rows before transactions commit, and by preventing final timestamp assignment until all cursors are closed. This proactive timing management ensures that deleted rows remain invisible to cursors throughout the transaction lifecycle, resolving the data consistency issue while maintaining high concurrent transaction handling capability.
2Speed
If final timestamps are assigned immediately upon transaction commit, then transaction processing speed is improved, but deleted rows become visible to open cursors causing inconsistent views
Solution Approach 1:
The patent uses preliminary action by assigning temporary timestamps immediately upon transaction commit (maintaining speed) while postponing the critical final timestamp assignment until cursor closure. This two-stage timestamp assignment ensures both fast transaction processing and consistent cursor views, as the temporary timestamp prevents premature visibility while the delayed final timestamp maintains performance.
3Reliability
If cursors are monitored to prevent final timestamp association until cursors are closed, then data consistency is improved, but system complexity increases
Solution Approach 1:
The patent implements feedback by establishing a monitoring mechanism that tracks cursor status and provides feedback on when all cursors are closed. This feedback loop triggers the final timestamp assignment, ensuring data consistency while automating the complexity management. The system automatically adjusts timestamp assignment based on real-time cursor state information.
Data Source
AI summary
A database upon opening each of at least one cursor for a transaction, assigns each statement forming part of a transaction a statement sequence number (SSN) involving at least one row of the database. Thereafter, temporary timestamps are assigned to the at least one row using a transaction identifier and the corresponding SSN. Further, active cursors are monitored such that a final timestamp is prevented from being associated with each row until it has no remaining cursors. Related apparatus, systems, techniques and articles are also described.