Shared Numeric Locks for Concurrent Database Transactions
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database systems face inefficiencies in processing transactions, particularly addition/subtraction operations, as they require locks to be released before new transactions can access the same row and column, leading to significant processing time due to frequent changes in specific columns like account balances in banking or inventory systems.
Innovation Solution
The introduction of a 'Shared Numeric Lock' allows multiple transactions with common characteristics to access and modify the same column concurrently without releasing the lock until all transactions have completed, enhancing processing efficiency by enabling simultaneous access and modification of columns like account balances or inventory levels.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If conventional row locking is used for transaction processing, then data integrity is maintained, but processing time increases significantly due to sequential access requirements
Solution Approach 1:
The patent segments the locking mechanism by introducing multiple lock types (shared numeric locks, exclusive locks, and intent locks) that can be applied at different granularities. This allows concurrent access for read-only operations on numeric columns while maintaining exclusive access when modifications are needed, thus reducing processing time without compromising data integrity.
Solution Approach 2:
The patent implements dynamic lock escalation and conversion mechanisms where lock types and granularities can change based on transaction requirements. Shared numeric locks can be upgraded to exclusive locks when write operations are needed, and intent locks can be placed before acquiring row locks. This dynamic approach optimizes concurrency while maintaining data integrity.
2Productivity
If locks are released before new transactions access the same row and column, then concurrent access is enabled, but data consistency may be compromised
Solution Approach 1:
The patent uses intent locks as preliminary actions before acquiring actual row locks. Intent locks are placed on pages or tables to signal upcoming lock requirements, allowing the database to prepare for concurrent access patterns while maintaining consistency. This preliminary signaling enables better resource allocation and reduces conflicts.
Solution Approach 2:
The patent introduces shared numeric locks as intermediary mechanisms that allow multiple transactions to access numeric columns concurrently for read operations. These shared locks act as mediators between conflicting read and write operations, enabling efficient concurrent access while maintaining data consistency through proper lock conversion protocols when write operations are needed.
3Productivity
If shared numeric locks are used to allow concurrent transactions, then processing efficiency improves, but lock management complexity increases
Solution Approach 1:
The patent designs the shared numeric lock mechanism to serve multiple functions: it enables concurrent read access, provides a foundation for lock escalation to exclusive locks, and works alongside intent locks for hierarchical locking. This multi-functionality reduces the need for separate mechanisms and simplifies overall lock management despite the enhanced capabilities.
Solution Approach 2:
The patent adds a new dimension to lock management by introducing shared numeric locks that operate independently from traditional row locks. This creates a two-layer locking system where shared numeric locks handle concurrent read access at the column level, while traditional row locks handle write operations. This dimensional addition enables efficient concurrency without significantly complicating the existing lock management infrastructure.
Data Source
AI summary
A method and system for performing at least one transaction on at least one column of a table in a database system is disclosed. The method includes determining whether a first transaction of the at least one transaction fulfills all of at least one criteria. The method further includes ensuring that the at least one column is in a shared numeric lock if the first transaction fulfills all of the at least one criteria, wherein the shared numeric lock is a lock that allows at least a second transaction fulfilling all of the at least one criteria to access the same column concurrently with the first transaction without releasing the shared numeric lock between transactions, and wherein the shared numeric lock is released only after all of the transactions fulfilling all of the criteria have completed processing. The method further includes processing the first transaction.