Database Committed Change Detection via Reference Timestamps
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems face inefficiencies in determining whether changes to data blocks are committed or uncommitted, leading to slowdowns, especially in clustered environments, due to the lack of inline data indicating transaction status and the overhead of maintaining centralized committed transaction data.
Innovation Solution
The approach involves computing a reference timestamp by determining a minimum value of start timestamps for uncommitted transactions across database instances, allowing readers to infer committed changes by comparing this timestamp to the 'last modified' timestamp of data blocks, and synchronizing timestamps across instances to ensure consistency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If readers determine committed changes by finding which transaction made changes and looking up transaction status in a separate table, then read consistency is maintained, but reader performance slows down substantially
Solution Approach 1:
The patent divides the determination process into two parts: data blocks contain only the last transaction ID that modified them, while transaction status information is stored separately in a transaction table. This segmentation allows readers to quickly identify the last modifying transaction from the data block without inline status flags, then efficiently look up its status, thereby maintaining read consistency while improving reader performance.
2Productivity
If inline data indicating committed status is stored in data blocks, then reader performance improves, but memory overhead and computing overhead increase significantly
Solution Approach 1:
The patent extracts the transaction status determination logic from the data block structure itself. Instead of storing committed status flags inline in each data block (which would increase memory overhead), the patent stores only the last transaction ID in the data block header and maintains transaction status information separately in the transaction table. This extraction reduces data block overhead while enabling efficient status determination through the transaction ID lookup.
3Reliability
If centralized data indicating committed transactions is maintained and updated synchronously, then transaction commit status is accurately tracked, but transaction commit times are prolonged
Solution Approach 1:
The patent implements preliminary action by having transactions record their IDs and status information in the transaction table before and during commit processing. The transaction table is pre-configured to store transaction ID, start timestamp, and commit status fields. When a transaction commits, its status is updated in the transaction table, and this information becomes immediately available for readers to determine committed changes by comparing transaction IDs, thereby accurately tracking commit status without prolonging commit times.
Data Source
AI summary
A minimum value (MV) is computed for start timestamps that each correspond to an uncommitted transaction. In an embodiment, the MV is computed for a pluggable database that is open on at least first and second instances of a database. The MV is computed for the first instance as of a first current timestamp (CT). The MV and the first CT are communicated to a second instance that has a second CT. If the first and second CTs are equal, the second instance store the MV. If the first CT is bigger, the second CT also becomes equal to the first CT. If the first CT is smaller, the MV is discarded, and the first CT becomes equal to the second CT. In an embodiment, if the MV remains unchanged for a predetermined time period, a start timestamp corresponding to the MV is advanced to a current or future timestamp.