Active Database Query Maintenance via Incremental Updates

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing concurrency control techniques in database management systems often lead to inefficiencies and wasted work due to conflicts between long-running queries and transactions, particularly when updates occur during query execution, as they may require aborting transactions or locking out conflicting transactions, reducing concurrency and increasing wait times.

Innovation Solution

The implementation of an incremental maintenance process using sensitivity indexes and change-oracles to efficiently update materialized views, allowing active transactions to revise their execution based on changes made by committed concurrent transactions, rather than aborting and restarting, thereby saving work and maintaining concurrency.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If lock-based concurrency control techniques are used to prevent conflicting updates, then data consistency is maintained, but transaction wait times increase and concurrency decreases

Engineering Contradiction:

Improvedata consistencyVSAvoidtransaction wait time

Core Design Contradiction:

ReliabilityVSLoss of time

Solution Approach 1:

The system performs preliminary conflict detection by checking timestamps of data items before allowing a transaction to proceed. This preliminary action identifies potential conflicts early, allowing the system to prevent wasted work on transactions that would inevitably fail, thereby reducing overall transaction wait time while maintaining data consistency through timestamp-based validation.

Inventive Principle:

Principle #10Preliminary action

2Productivity

If optimistic concurrency control is used to reduce locking overhead, then concurrency improves, but work waste increases due to frequent transaction abortions

Engineering Contradiction:

ImproveconcurrencyVSAvoidwasted computational work

Core Design Contradiction:

ProductivityVSLoss of energy

Solution Approach 1:

The system implements feedback through timestamp tracking of all data items. Before a transaction commits, the system checks whether any data items the transaction accessed have been modified by other transactions (detected through timestamp comparison). This feedback mechanism allows the system to identify conflicts before they cause transaction abortion, preventing wasted computational work while maintaining high concurrency through optimistic execution.

Inventive Principle:

Principle #23Feedback

3Speed

If materialized views are used to improve query performance, then query speed increases, but maintenance complexity increases when underlying data changes

Engineering Contradiction:

Improvequery retrieval speedVSAvoidquery maintenance complexity

Core Design Contradiction:

SpeedVSDevice complexity

Solution Approach 1:

The system segments the maintenance of materialized views by tracking individual data item timestamps and their relationships to view computations. When underlying data changes, the system identifies only the specific portions of the materialized view affected by the change (segmented maintenance) rather than re-computing the entire view. This segmentation approach reduces maintenance complexity while preserving query performance benefits.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The system dynamically adapts the maintenance strategy for materialized views based on the nature and scope of data changes. By using timestamp-based change detection, the system determines whether full re-computation or incremental updates are needed, making the maintenance process dynamic rather than static. This reduces overall maintenance complexity while ensuring view accuracy.

Inventive Principle:

Principle #15Dynamics

4Duration of action of moving object

If long-running queries are allowed to execute without interruption, then query completeness improves, but conflicts with concurrent transactions increase

Engineering Contradiction:

Improvequery execution completenessVSAvoidtransaction conflicts

Core Design Contradiction:

Duration of action of moving objectVSObject-generated harmful factors

Solution Approach 1:

The system performs preliminary timestamp recording when a long-running query begins execution. This preliminary action captures the state of data items at query start, allowing the query to proceed without immediate conflict detection overhead. The recorded timestamps are later used to verify whether the query results remain valid, enabling query completeness while providing a mechanism to detect and handle conflicts that arise during execution.

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS10430409B2Maintenance of active database queries

Publication Date: 2019.10.01 INFOR US LLC

AI summary

An aspect includes a method for maintaining active queries. The method includes executing a query based on data items in at least two relations in a database. The executing includes outputting a query result and control information associated with the query. The query result and the control information are recorded. A notification that at least one of the data items has been updated subsequent to the executing is received. The query result is modified, responsive to the control information, to reflect the data items that were updated subsequent to the executing.