Database Row Lock Segmentation for Parent-Child Updates
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In relational databases with a parent-child hierarchy, simultaneous updates to child records often require a row lock on the parent record, leading to row-lock contention, which causes latency, delays, and database inconsistencies, especially when multiple child records need to be updated in close proximity, as other child records are unable to access the parent record until the initial update is committed or rolled back.
Innovation Solution
Allowing updates to child records only during a specified time period without requiring a row lock on the parent record, enabling multiple child records to be processed swiftly by acquiring a row lock on the respective child records, thus eliminating the need for dual row locks and reducing buffer storage requirements.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a row lock is required on the parent record when updating child records, then data consistency is maintained, but processing time increases and productivity decreases
Solution Approach 1:
The patent segments the locking mechanism from the parent record to the child record level. Instead of acquiring a row lock on the parent record before updating child records, the system allows child records to be updated independently without requiring parent record locks. This segmentation eliminates the blocking effect while maintaining data consistency through the foreign key relationship between parent and child records.
2Reliability
If a row lock is acquired on the parent record for child record updates, then data integrity is protected, but latency increases
Solution Approach 1:
The patent extracts the row lock requirement from the parent record context and applies it at the child record level instead. By removing the dependency that required parent record locking, the system eliminates the latency caused by waiting for parent lock acquisition and release, while child record updates maintain integrity through their own locking mechanisms.
3Productivity
If simultaneous updates to multiple child records are allowed without parent row locks, then productivity improves, but database inconsistencies may occur
Solution Approach 1:
The patent introduces the foreign key relationship as an intermediary that ensures consistency between parent and child records. This intermediary mechanism allows child records to be updated simultaneously without parent row locks while maintaining referential integrity. The database constraint system acts as the mediator that prevents inconsistencies without requiring explicit locking.
4Reliability
If row locks are held on parent records during child updates, then data safety is ensured, but device complexity increases
Solution Approach 1:
The patent applies local quality by implementing row locks at the child record level rather than requiring them at the parent record level. This localized approach reduces the scope and complexity of the locking mechanism, as each child record can be locked independently without coordinating with parent record locks, simplifying the overall concurrency control system.
Data Source
AI summary
Described herein is a system and method for performing an updating of child records associated with a parent record within the specified time period, wherein the parent record has a value including a sum of values present in the associated child records, wherein a row lock on the child records is obtained, and the update is performed, and wherein such an update does not require a row lock on the associated parent record, wherein the parent record is periodically updated within the specified time period.