Column-Based Database Locks for Concurrent Access
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems face inefficiencies in managing concurrent access to relational databases, as existing locking mechanisms either restrict multiple users from editing or reading records simultaneously or lead to data inconsistencies due to unrestricted access.
Innovation Solution
The method involves receiving a relational database transaction with a lock component for a target table, determining lock compatibility by comparing the target key and column to existing locks using a key hash chain and column bit array, and adding the lock component if compatible, allowing for column-based locking that can escalate to row-level locking when a threshold is reached.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a record is locked to prevent simultaneous access, then data integrity is maintained, but only one record can be processed at a time, reducing productivity
Solution Approach 1:
The patent segments the record locking mechanism into column-level locks instead of row-level locks. Each column can be locked independently, allowing multiple users to access different columns of the same row simultaneously. This segmentation enables concurrent processing while maintaining data integrity through selective locking of specific columns rather than the entire record.
2Productivity
If column-based locking is implemented, then concurrency is improved, but device complexity increases due to additional lock management structures
Solution Approach 1:
The patent implements a universal lock management structure that handles both column-level locks and row-level locks through a single integrated mechanism. The lock manager uses a hash table that can accommodate different lock types (column locks with column IDs, row locks with row identifiers), eliminating the need for separate locking mechanisms and reducing overall system complexity despite the enhanced functionality.
3Reliability
If row-level locking is used, then data consistency is ensured, but multiple users cannot edit or read records simultaneously, reducing ease of operation
Solution Approach 1:
The patent applies local quality by implementing column-specific locking instead of row-level locking. Each column can be locked independently based on local access requirements, allowing different users to access different columns simultaneously. This local locking approach maintains data consistency for locked columns while enabling concurrent access for other columns, improving ease of operation.
Data Source
AI summary
Disclosed are techniques for relational database locks based on columns. Database transactions may be targeted to specific columns of one or more records, instead of the entire row for those records, using primary keys. Column locks on specific keys are stored separately than column locks on ranges of keys, which are both checked when requesting a new column lock for either a single key or a range of keys. When a threshold number of columns for a given record, or range of records/keys, have been locked, the column locks for that record, or range of records, can be combined into a single row level lock to reduce resource costs for maintaining multiple concurrent locks.