Database Index Repair Using Segmented Structural and Data Locks
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional database index repair methods prevent concurrent access to the index and data table, leading to performance degradation in systems with large data sets and high-volume transactions, as other processes are forced to wait until the repair is complete.
Innovation Solution
The system uses a combination of structural and data locks to allow concurrent access during index repair, acquiring a less restrictive lock on a set of database records for structural modifications and a more restrictive lock on specific records to correct inconsistencies, enabling other processes to access the database while the repair is ongoing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If traditional database index repair methods are used, then index consistency is restored, but concurrent access to the index and data table is blocked, leading to performance degradation
Solution Approach 1:
The patent segments the lock acquisition process into two distinct phases: structural lock acquisition for the index structure, and data lock acquisition for specific index entries. This segmentation allows different levels of access control, enabling concurrent reads during repair operations while maintaining data consistency. The structural lock protects the overall index structure while data locks protect only the specific entries being modified.
Solution Approach 2:
The patent implements dynamic lock management where locks are acquired and released based on the specific repair needs. Instead of holding a global lock for the entire repair duration, the system dynamically acquires locks only when modifying specific index entries and releases them immediately after. This dynamic approach minimizes lock holding time and maximizes concurrent access opportunities.
2Reliability
If a restrictive lock is placed on the database table during repair, then data consistency is maintained, but all other processes must wait until repair is complete
Solution Approach 1:
The patent applies local quality by placing locks only on the specific index entries being repaired rather than on the entire index structure or data table. This localized locking approach ensures data consistency for the modified entries while allowing other processes to continue accessing unaffected portions of the database, thereby reducing overall wait time.
Solution Approach 2:
The patent uses partial action by acquiring locks only for the minimum necessary duration and scope required to complete the repair of specific index entries. Instead of locking the entire table for the full repair duration, the system locks only the affected entries for the minimum time needed to perform the repair operation, then immediately releases the locks.
3Manufacturing precision
If the index is repaired without concurrent access, then repair correctness is ensured, but system performance degrades due to blocked operations
Solution Approach 1:
The patent segments the repair operation into discrete, lock-protected steps that ensure correctness while allowing concurrency. Each index entry repair is performed as a separate atomic operation with appropriate lock acquisition and release, ensuring that each modification is correct while allowing other operations to proceed in parallel on different entries.
Solution Approach 2:
The patent introduces locks as intermediary mechanisms that mediate between the repair process and concurrent access requests. The lock system acts as an intermediary that coordinates access to index entries, ensuring that repair operations complete correctly while allowing non-conflicting concurrent operations to proceed, thus maintaining both correctness and performance.
Data Source
AI summary
A computing system may repair a database index for a database table by placing a first structural lock on the database table to prevent modifications to the structure of the table. The computing system may scan the database table to determine a plurality of database records. The system may probe the database index for the database records to identify records with missing entries in the index. Based on the probing, the system may place a first data lock on a record of the database table corresponding to a missing index entry to prevent modification of data in the record. In response to placing the first record lock, the system adds an index entry for the locked database record to the database index. During repair of the index, one or more processes may use the database index to locate one of the plurality of database records included in the database table.