Database Mass Delete via Transaction Log Pointer
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing mass deletion methods in database systems are inefficient, leading to significant transaction log data production, potential memory overflow, and database crashes, while non-transactional methods lack tracking and result in corrupted states upon failure.
Innovation Solution
A method that creates a data structure with identifiers of records to be deleted and inserts only a pointer into the transaction log, rearranging partitions based on the number of records to be deleted, and using a 'commit' command to deallocate areas, ensuring efficient processing and fail-safety.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If transactional mass delete is used to delete data records individually with transaction log entries, then data tracking and reliability are improved, but processing speed deteriorates and transaction log size increases causing memory overflow risk
Solution Approach 1:
The patent segments the mass delete operation into two distinct phases: a preparation phase that creates a deletion plan stored in the transaction log, and an execution phase that performs the actual deletions. This segmentation allows the transaction log to contain only metadata about the deletion operation rather than individual deletion records, reducing log size while maintaining reliability through the preparatory commit step.
Solution Approach 2:
The patent performs preliminary actions by creating a deletion plan and storing it in the transaction log before actual deletions occur. The plan includes identifiers of records to be deleted and is committed to the transaction log in advance. This preliminary action enables the system to track the intended deletions while avoiding the overhead of logging each deletion individually, thus improving processing speed while maintaining data integrity.
2Productivity
If non-transactional mass delete is used to improve processing speed by avoiding transaction log entries, then processing efficiency is improved, but data consistency deteriorates and corruption risk increases upon failure
Solution Approach 1:
The patent performs a preliminary action of creating and committing a deletion plan to the transaction log before executing the actual mass delete operation. This preliminary commit ensures that the deletion intent is recorded and can be recovered if the operation fails, maintaining data consistency while allowing efficient execution without individual transaction log entries for each deletion.
Solution Approach 2:
The patent introduces an intermediary deletion plan data structure that mediates between the transaction log and the actual data deletion process. This intermediary contains the identifiers of records to be deleted and serves as a recovery mechanism, allowing the system to achieve both high processing efficiency and data consistency by separating the logging function from the deletion execution.
3Reliability
If transactional mass delete is used to ensure each deletion is recorded, then data integrity is improved, but transaction log size increases causing memory overflow and system crash
Solution Approach 1:
The patent extracts the essential deletion information (record identifiers and deletion plan) from the detailed individual deletion records and stores only this condensed representation in the transaction log. This extraction reduces the transaction log data volume significantly while maintaining data integrity, as the condensed plan contains all necessary information to recover and execute the deletions if needed.
Solution Approach 2:
The patent performs a preliminary action of creating a condensed deletion plan that captures all necessary deletion information in a compact format. This plan is committed to the transaction log once rather than recording each deletion individually, thereby reducing transaction log volume while preserving data integrity through the ability to recover and verify the deletion operation.
4Ease of manufacture
If mass delete operation modifies database partitions internally, then deletion processing is simplified, but parallel read access capability deteriorates
Solution Approach 1:
The patent segments the mass delete operation into a preparation phase that creates a deletion plan without modifying data partitions, and an execution phase that performs deletions. During the preparation phase, read access is fully maintained. This segmentation allows the system to maintain parallel read access capability while still achieving simplified deletion processing through the structured two-phase approach.
Data Source
Figure 1
Figure 2a
Figure 2b
AI summary
The present invention concerns a method for mass-deleting a plurality of data records (20) from a database system (1), wherein the method comprises the following step performed during a processing of a mass delete request: a. creating (120) a data structure (40) comprising an identifier of each of the plurality of data records (20) to be deleted and inserting (130) a pointer to the data structure (40) into a transaction log (50) of the database system (1).