Database Transaction Locking Reduction via Changeset Segmentation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Data locking in databases during transactions can significantly impact performance and hinder access for other devices and applications, especially during long-running transactions or when multiple users or applications modify data, leading to issues like conflicts and deadlocks.
Innovation Solution
A server application generates a changeset of modifications as a set of tables within the database, allowing queries to include statements for both the base data and the changeset without locking the base data, enabling simultaneous access and reducing locking duration by applying changes only when the transaction ends.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If data is locked during transactions to ensure consistency and prevent conflicts, then data integrity is maintained, but database performance deteriorates and access time increases
Solution Approach 1:
The patent segments the data access process into reading base data and reading change data separately. By querying the base data table and the change data table (stored as a set of tables) independently, the system eliminates the need to lock the entire base data during transactions, thus maintaining data integrity while improving performance.
Solution Approach 2:
The patent implements preliminary action by capturing changes in a separate change data table before they are applied to the base data. This allows queries to read the change data without locking the base data, as the changes are pre-stored in the change data table for immediate retrieval.
2Reliability
If data is locked during long-running transactions, then transaction consistency is ensured, but the time data is locked increases and other applications cannot access the data
Solution Approach 1:
The patent segments transaction processing into capturing changes (stored in change data table) and applying changes (updated to base data table). This segmentation allows the system to read change data without locking base data, reducing locking duration while maintaining transaction consistency.
Solution Approach 2:
The change data table acts as an intermediary between the base data and the queries. Instead of locking the base data during transactions, the system uses the change data table as a mediator to store and serve change information, allowing simultaneous access to base data by other applications.
3Productivity
If a cache is used to hold changes at the device, then data access performance improves, but device complexity increases and post-processing operations become complex
Solution Approach 1:
The patent uses the change data table as a server-side intermediary that stores changes centrally. This eliminates the need for device-side caching and complex post-processing operations, as the server application directly queries and returns change data from the change data table to requesting devices.
Solution Approach 2:
Instead of caching changes locally at devices, the patent creates a copy of the change data in a centralized change data table. This allows multiple devices to access change information without each device maintaining its own cache and performing post-processing, thereby reducing device complexity.
4Productivity
If multiple queries and modifications are made in succession, then data is modified repeatedly, but the data remains locked and access is prevented for other applications
Solution Approach 1:
The patent segments the data storage into base data table and change data table. Modifications are captured in the change data table without locking the base data, allowing multiple queries and modifications to be processed in succession while maintaining data access availability for other applications.
Solution Approach 2:
The patent implements preliminary action by storing changes in the change data table before applying them to the base data. This allows multiple modifications to be queued and processed without locking the base data, thereby maintaining access availability while enabling high modification throughput.
Data Source
AI summary
Concepts and technologies are described herein for reducing locking during database transactions without using a cache. In accordance with the concepts and technologies disclosed herein, a server application generates a changeset in response to receiving an operation for modifying base data stored in a database. The changeset is stored as a set of tables that are stored at the database with the base data, and include a first table reflecting records in the base data that have been created and/or modified by the operations. The changeset further can include a second table reflecting records in the base data that have been deleted by the operations.