Transaction Log Lock Acquisition Protocol for Database Scalability
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Acquiring physical locks on transaction log pages in database systems is a resource-intensive operation that leads to performance bottlenecks and scalability issues, especially in distributed multi-node databases, as it requires significant resources and memory, and current methods involve expensive operations that hinder database growth.
Innovation Solution
A new protocol that synchronizes operations on the last transaction log page using the Last Log Object Lock (LLOL) at the node level and the Append Log Semaphore at the task level, minimizing the need for physical locks on new transaction log page allocations, including the last transaction log page, by categorizing log scanners and synchronizing read/write operations efficiently.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If physical locks are taken on all data and transaction log pages to maintain buffer cache coherency, then cache coherency is guaranteed, but system performance deteriorates due to resource-intensive lock acquisition and management operations
Solution Approach 1:
The patent applies different locking strategies to different types of log pages. New log pages and the last log page use a simplified lock mechanism (Last Log Object Lock) that does not require physical locks, while existing log pages continue to use physical locks for coherency. This local differentiation resolves the contradiction by avoiding expensive lock operations where possible while maintaining coherency where needed.
Solution Approach 2:
The patent segments the transaction log into new pages, last pages, and existing pages, applying different locking protocols to each segment. This segmentation allows the system to optimize performance for frequently accessed new/last pages while maintaining reliability for existing pages, thereby resolving the contradiction between performance and coherency.
2Reliability
If physical locks are taken on transaction log pages in distributed multi-node databases, then access synchronization is achieved, but scalability deteriorates due to increased resource consumption and management complexity
Solution Approach 1:
The patent applies a lightweight lock mechanism specifically to new and last log pages in distributed environments, avoiding the heavy physical lock mechanism for these frequently accessed pages. This local optimization reduces the burden on individual nodes while maintaining overall system synchronization, thereby improving scalability.
Solution Approach 2:
The patent extracts the lock acquisition step from the general transaction log access process for new and last pages. By using a separate, simplified synchronization mechanism (Last Log Object Lock) that does not require physical locks, the system removes the scalability bottleneck while maintaining access synchronization.
3Reliability
If physical lock acquisition steps are executed for every page access, then data consistency is maintained, but execution time increases impacting overall system throughput
Solution Approach 1:
The patent applies different consistency guarantees to different log pages. New and last pages use a faster synchronization mechanism that provides sufficient consistency without requiring full physical lock acquisition, while existing pages maintain full consistency through physical locks. This local differentiation reduces execution time for frequently accessed pages while maintaining data consistency where critical.
Solution Approach 2:
The patent applies partial locking (or no locking) to new and last pages, using only the necessary synchronization mechanism rather than full physical lock acquisition. This partial action is sufficient for maintaining data consistency in these specific contexts, thereby reducing execution time without sacrificing necessary consistency.
4Reliability
If physical locks are retained on transaction log pages, then access control is maintained, but memory consumption increases due to lock record storage
Solution Approach 1:
The patent extracts the lock record storage requirement from the access control mechanism for new and last pages. By using a simplified Last Log Object Lock that does not require physical lock records, the system maintains access control for these pages while significantly reducing memory consumption compared to retaining physical locks on all pages.
Data Source
AI summary
A system, method, and computer program product for improving physical lock acquisition for database transaction logs are described herein. In an embodiment, the method operates by receiving a request for a transaction log page and determining whether a requested log page is newly-allocated or already exists. A determination is made regarding whether the last log page is being modified. A physical lock is taken on the requested log page when it has been determined that the requested log page is not newly-allocated and that the last log page is not being modified. Operations on the last log page are synchronized without a physical lock when it is determined that the requested log page is newly-allocated or that the last log page is being modified.