Database Transaction Submission Groups for Integrity and Latency

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Traditional batch processing methods for database data movement are inefficient, leading to temporary transaction and referential inconsistencies, and performance issues due to single-threaded replication systems that break up workloads by table, resulting in stale data and violated integrity constraints.

Innovation Solution

A computer-readable medium with executable instructions that processes a continuous stream of database transactions by forming batches, combining similar operations into submission groups, identifying dependencies, and applying them synchronously with the transaction stream, utilizing multi-row operations and parallel processing to maintain transaction and referential integrity.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Use of energy by moving object

If traditional batch processing is used to move data between databases, then resource usage is reduced, but data staleness increases and transaction consistency is violated

Engineering Contradiction:

Improveresource usageVSAvoiddata staleness

Core Design Contradiction:

Use of energy by moving objectVSLoss of time

Solution Approach 1:

The system dynamically adjusts the replication approach by combining batch processing for some operations with near-real-time processing for others. The batch processor continuously monitors transaction queues and dynamically determines when to switch between batch mode and near-real-time mode based on data freshness requirements and system load conditions.

Inventive Principle:

Principle #15Dynamics

Solution Approach 2:

The system performs preliminary actions by pre-processing and grouping transactions into submission groups before applying them to the target database. This allows the system to prepare optimized batch operations in advance while maintaining the ability to quickly apply changes when needed, reducing both resource usage and data staleness.

Inventive Principle:

Principle #10Preliminary action

2Reliability

If single-threaded replication is used to preserve transaction order, then transaction integrity is maintained, but productivity decreases

Engineering Contradiction:

Improvetransaction integrityVSAvoidreplication speed

Core Design Contradiction:

ReliabilityVSProductivity

Solution Approach 1:

The system segments the replication process into multiple independent components: transaction capture, batch formation, submission group creation, and application. Each component can operate independently and in parallel where possible, allowing multi-threaded processing while maintaining transaction integrity through proper sequencing mechanisms.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The system introduces an intermediary batch processor that acts as a mediator between the source database transactions and the target database. This intermediary maintains transaction queues and submission groups, ensuring proper ordering and integrity while enabling parallel processing of independent transaction batches.

Inventive Principle:

Principle #24Intermediary (Mediator)

3Productivity

If workloads are broken up by table for parallel processing, then productivity increases, but referential integrity is violated

Engineering Contradiction:

Improveprocessing parallelismVSAvoidreferential integrity

Core Design Contradiction:

ProductivityVSReliability

Solution Approach 1:

The system merges transactions from multiple tables into unified submission groups that preserve referential integrity. By combining related operations across different tables into atomic submission groups, the system enables parallel processing of independent groups while ensuring that dependent operations are applied together, maintaining both productivity and reliability.

Inventive Principle:

Principle #5Merging (Combining)

4Loss of time

If continuous stream processing is used for near real-time replication, then data freshness is improved, but resource usage increases

Engineering Contradiction:

Improvedata freshnessVSAvoidresource usage

Core Design Contradiction:

Loss of timeVSUse of energy by moving object

Solution Approach 1:

The system implements periodic action by processing transactions in structured batches at optimized intervals. Rather than continuously processing every transaction immediately, the system accumulates transactions into batches and applies them periodically, reducing resource overhead while maintaining acceptable data freshness through controlled batch intervals.

Inventive Principle:

Principle #19Periodic action

Solution Approach 2:

The batch processor serves multiple functions simultaneously: it captures transactions, forms batches, creates submission groups, manages transaction queues, and applies changes to the target database. This multi-functionality reduces the need for separate dedicated systems for each operation, lowering overall resource usage while maintaining near-real-time replication capabilities.

Inventive Principle:

Principle #6Universality (Multi-functionality)

Data Source

PatentUS8032885B2Method and medium for combining operation commands into database submission groups

Publication Date: 2011.10.04 ORACLE INT CORP

AI summary

A computer readable medium storing executable instructions includes executable instructions to: receive a continuous stream of database transactions; form batches of database transactions from the continuous stream of database transactions; combine batches of database transactions with similar operations to form submission groups; identify dependencies between submission groups to designate priority submission groups; and apply priority submission groups to a database target substantially synchronously with the receipt of the continuous stream of database transactions.