Spillover Tables for High-Volume Data Store Updates
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
High volume transactions in electronic commerce systems face performance bottlenecks due to the need for exclusive locks on data store tables, which can lead to reduced throughput and data store unavailability during peak activity.
Innovation Solution
Implementing a spillover table that allows for high velocity updates without requiring an exclusive lock, enabling simultaneous transactions by inserting updates into the spillover table and later joining them with the main data table to ensure data integrity and accuracy.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If exclusive locks are used on data store tables to ensure data integrity during updates, then data accuracy is maintained, but system throughput and availability deteriorate during high transaction volumes
Solution Approach 1:
The patent divides the data storage structure into two separate tables: a main data table for storing current values and a spillover table for storing update operations. This segmentation allows concurrent access patterns where the main table remains relatively stable while the spillover table accumulates updates, thereby maintaining data integrity while improving throughput during high transaction volumes.
Solution Approach 2:
The spillover table acts as an intermediary between write operations and the main data table. Instead of directly updating the main table (which requires locks), updates are first placed in the spillover table, which then periodically merges its contents back to the main table. This intermediary mechanism decouples the write path from the main data structure, enabling high-concurrency writes without compromising data integrity.
2Reliability
If exclusive locks are obtained on data store tables for each update operation, then data consistency is ensured, but transaction velocity and response time deteriorate
Solution Approach 1:
The system performs preliminary actions by placing update operations in the spillover table before they are applied to the main data table. This preliminary staging of updates allows multiple transactions to be queued without blocking each other, and the actual application to the main table occurs in controlled batches, thereby maintaining consistency while increasing transaction velocity.
Solution Approach 2:
The patent implements periodic merging of the spillover table contents back to the main data table. Instead of locking the main table for every update, the system periodically applies accumulated updates from the spillover table to the main table in batch operations. This periodic action pattern maintains data consistency while allowing high-velocity intermediate updates to proceed without locks.
3Measurement precision
If traditional data store tables are used for high volume updates, then data accuracy can be maintained with locking, but system availability and concurrency deteriorate
Solution Approach 1:
By segmenting the data storage into main table and spillover table, the system achieves both data accuracy and high concurrency capacity. The main table maintains accuracy through controlled updates, while the spillover table provides a concurrent write buffer that can handle high volumes of updates without affecting the main table's integrity or availability.
Data Source
AI summary
Disclosed are various embodiments for employing spillover tables that may improve the performance of data store updates. In some embodiments, transaction data that specifies a value by which to adjust a field in a data table is received. The field in the data table is associated with a predefined constraint. An attempt to obtain an exclusive lock for at least a portion of the data table is initiated. Whether the value has a potential to cause the predefined constraint to remain satisfied is identified. The transaction data is stored in a spillover table in response to a failure to obtain the exclusive lock and in further response to the value having the potential to cause the predefined constraint to remain satisfied.