Database Concurrency Control via Hybrid Locking
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database concurrency control methods using pessimistic and optimistic locks are mutually exclusive, leading to suboptimal performance in scenarios with varying probabilities of concurrent conflicts, as they either consume memory with locking and unlocking processes or fail to handle conflicts efficiently.
Innovation Solution
A method that integrates a lock in each database fragment using a global lock table, allowing reading and writing operations to proceed with optimistic locks while checking for conflicts only upon submission, and switching to pessimistic locks when necessary to prevent data conflicts, thereby maximizing concurrency and performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If pessimistic lock mechanism is used to guarantee data exclusivity and correctness, then data reliability is improved, but memory consumption increases due to locking and unlocking processes
Solution Approach 1:
The system dynamically switches between pessimistic lock and optimistic lock mechanisms based on conflict detection. When conflicts are detected, it transitions to pessimistic locking; when no conflicts occur, it uses optimistic locking. This dynamic adaptation resolves the contradiction by applying the appropriate locking strategy based on actual runtime conditions rather than using a fixed approach.
2Productivity
If optimistic lock mechanism is used to improve performance by allowing simultaneous data access, then productivity is improved, but conflict handling efficiency deteriorates due to centralized conflict determination
Solution Approach 1:
The system segments the locking mechanism into two distinct modes: optimistic locking for normal operations and pessimistic locking for conflict scenarios. This segmentation allows the system to maintain high performance through optimistic locking while having a dedicated fallback mechanism for conflict handling, thereby reducing the overall complexity of conflict determination.
3Reliability
If pessimistic lock is used to prevent data conflicts, then data reliability is improved, but concurrency deteriorates due to sequential access requirements
Solution Approach 1:
The system applies different locking qualities to different situations: optimistic locking (no actual lock) is applied when conflicts are unlikely, while pessimistic locking is applied only when conflicts are detected. This local differentiation of locking quality allows high concurrency in normal scenarios while maintaining reliability when needed, resolving the contradiction between concurrency and conflict prevention.
4Productivity
If optimistic lock is used to maximize concurrency by omitting locking processes, then productivity is improved, but reliability deteriorates in high conflict scenarios
Solution Approach 1:
The system performs preliminary conflict detection through version number comparison before committing transactions. This preliminary action allows the system to maintain high concurrency through optimistic locking while detecting potential reliability issues early, enabling timely switching to pessimistic locking to prevent data correctness problems in high conflict scenarios.
Data Source
AI summary
A concurrency control method of database transaction combining an optimistic lock and a pessimistic lock includes: integrating a lock in each fragment in a storage range, using a lock table globally, and recording a lock status of the each fragment in the lock table; before reading a data object of a fragment in the storage range, first querying in the lock table whether the data object of the fragment is locked by other reading-writing transactions; if the data object of the fragment is locked by other reading-writing transactions, blocking the current reading operation, and repeating the current reading operation, and if the current reading operation is blocked for more than a given time limit, since the pessimistic lock is invalid, intervening by the optimistic lock, and continuing reading the single row data of the current fragment.