Multi-Stage Table Update Query Segmentation for Transaction Log Management
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Transactional databases face significant memory and disk space challenges due to large temporary transaction logs during table-wide updates, which can exceed available space and require substantial overhead for real-time log growth.
Innovation Solution
A system that splits queries into sub-queries when the estimated transaction log space exceeds a threshold, performing mini-commit operations for each sub-query and finalizing with a commit operation, allowing for efficient management of transaction logs by partitioning tables and using conditional mini-commit operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a table-wide update query is executed on a large table, then the entire table can be updated, but the transaction log space required exceeds available memory or disk space
Solution Approach 1:
The patent divides a table-wide update query into multiple smaller sub-queries, each updating a subset of rows. This segmentation allows the transaction log space requirements to be divided proportionally, so that each sub-query generates a manageable amount of log data that fits within available memory or disk space, while collectively achieving the complete table update.
2Reliability
If transaction logs are maintained in memory or temporary storage, then data integrity can be maintained, but huge amounts of memory or disk space are consumed
Solution Approach 1:
By segmenting the update operation into sub-queries, the patent reduces the volume of transaction logs that need to be held in memory or temporary storage at any one time. Each sub-query generates a smaller portion of the total log data, allowing the system to maintain data integrity through transaction logging while consuming significantly less peak storage resources.
3Quantity of substance
If the transaction log space is increased to accommodate large table updates, then more space is available for logs, but the overhead for real-time log growth becomes substantial
Solution Approach 1:
The patent avoids the need to increase overall transaction log space capacity by segmenting updates into smaller operations. This eliminates the need for complex real-time log growth management mechanisms, as each sub-query's log requirements are predictable and manageable within existing log space allocations.
Solution Approach 2:
Instead of preparing for the full table update all at once (which would require excessive log space planning), the patent performs partial updates through sub-queries. This allows the system to manage log space incrementally and predictably, reducing the complexity of log growth management.
Data Source
AI summary
One embodiment of the present invention provides a system that facilitates performing multi-stage table updates. During operation, the system receives a query at a query processor, wherein executing the query causes an update to an entire table in a database. Next, the system estimates an amount of transaction log space required to execute the query. If the amount of transaction log space is greater than a pre-determined threshold, the system splits the query into a set of sub-queries, wherein an amount of transaction log space required by each sub-query in the set of sub-queries is less than the pre-determined threshold. For each sub-query in the set of sub-queries, the system executes the sub-query, and performs a mini-commit operation for the sub-query, wherein updates which comprise the mini-commit operation are not exposed to a user. Finally, when mini-commit operations have been performed for all of the sub-queries, the system performs a commit operation for the query.