Deferred Timestamping for Temporal Database Rows
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In multi-transaction scenarios, existing database systems face challenges in efficiently timestamping modified rows in temporal base tables until the transaction is committed, as current methods require revisiting and modifying rows, which is an expensive operation.
Innovation Solution
The method involves modifying the temporal row in the base table according to the transaction by creating a copy of the row with modifications, setting Temporal Kilroy Bits, and associating the row ID with a transaction identifier in a temporal work table. Upon committing the transaction, the method searches the temporal work table to determine the row ID and saves the commit-time into the temporal row, allowing for deferred timestamping and reducing the need for repeated access to the temporal work table.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If timestamping is performed immediately when modifying temporal rows in multi-transaction scenarios, then timestamp accuracy is improved, but system performance deteriorates due to repeated row access and modification operations
Solution Approach 1:
The patent applies preliminary action by recording row identifiers and transaction identifiers in a temporary table during the transaction processing phase, before the actual commit-time timestamping occurs. This allows the system to prepare all necessary timestamping information in advance, so that when the transaction commits, the timestamp can be applied efficiently without requiring repeated row access or modification operations, thus resolving the contradiction between timestamp accuracy and system performance
2Measurement precision
If the temporal work table is frequently accessed to update timestamp information, then timestamping accuracy is improved, but computational overhead increases
Solution Approach 1:
The patent creates a copy of the temporal work table structure as a temporary table that stores row identifiers, transaction identifiers, and timestamp information. Instead of repeatedly accessing and updating the original temporal work table, the system performs operations on this copied structure, which reduces computational overhead while maintaining timestamping accuracy. The copy can be efficiently updated and processed without affecting the original table's integrity or requiring frequent re-accesses
Data Source
AI summary
A base table temporal row affected by a transaction is timestamped. The temporal row has a row ID. The temporal row in the base table is modified according to the transaction. The row ID for the modified temporal row is associated with an identifier for the transaction in a temporal work table. Upon committing the transaction: the temporal work table is searched to determine the row ID of the temporal row modified by the transaction, and a commit-time is saved into the temporal row identified by the row ID.