MVCC Snapshot Reads Using Commit TxID Visibility Checks
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing record-based multi-version concurrency control (MVCC) mechanisms in database management systems face inefficiencies in achieving snapshot read consistency due to the need for backfilling transaction commit LSNs and checking transaction tables, leading to performance bottlenecks and increased storage requirements.
Innovation Solution
The use of a system-wide Commit_TxID (Commit_TransactionID) optimization, which avoids replacing transaction IDs in records and reduces the need for backfilling commit LSNs, allowing efficient visibility checking by comparing transaction IDs with the Commit_TxID and current LSN, minimizing the need to consult the transaction table for records that are already committed.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If backfilling of commit LSN is performed to records that are changed, then snapshot read consistency is achieved, but storage overhead and processing time increase
Solution Approach 1:
The patent extracts the commit LSN information from the transaction log and stores it directly in the record header when a record is created or updated. This eliminates the need for subsequent backfilling operations, as the commit LSN is already embedded in the record itself. The extraction principle resolves the contradiction by pre-positioning the necessary consistency information without adding processing overhead later.
Solution Approach 2:
The commit LSN is copied to the record header in advance, during the record creation or update operation, rather than performing the backfilling operation later when needed for consistency checks. This preliminary action ensures that snapshot read consistency can be achieved immediately without additional processing time, resolving the contradiction between reliability and time loss.
2Ease of operation
If entire transaction table is copied by the reader, then visibility checking is simplified, but storage requirements and memory usage increase
Solution Approach 1:
The patent extracts only the necessary commit LSN information from the transaction table and embeds it directly in the record header. Readers only need to access this embedded commit LSN field in the record, rather than copying or accessing the entire transaction table. This extraction principle maintains ease of visibility checking while dramatically reducing storage and memory requirements.
3Reliability
If transaction ID replacement with commit LSN is performed, then read consistency is improved, but device complexity and processing overhead increase
Solution Approach 1:
The patent applies partial action by copying only the commit LSN field to the record header rather than replacing the entire transaction ID or performing comprehensive transaction table management. This partial approach achieves the necessary read consistency improvement while minimizing the added complexity and processing overhead, resolving the contradiction between reliability and device complexity.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
System and method embodiments are provided for consistent read in a record-based multi-version concurrency control (MVCC) in database (DB) management systems. In an embodiment, a method in a record-based multi-version concurrent control (MVCC) database (DB) management system for a snapshot consistent read includes copying a system commit transaction identifier (TxID) and a current log record sequence number (LSN) from a transaction log at a start of a reader without backfilling of a commit LSN of a transaction to records that are changed and without copying an entire transaction table by the reader; and determining whether a record is visible according to a record TxID, the commit TxID and a current LSN, wherein a transaction table is consulted only when the record TxID is equal to or larger than a commit TxID at a transaction start.