Priority Lock Requests for Database Throughput
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In transactional computing environments, long-running queries and maintenance operations like DDL statements face delays due to the need for exclusive locks, which block other operations and lead to increased wait times and reduced system throughput, especially under heavy OLTP workloads.
Innovation Solution
Introduction of 'kill' and 'low priority' lock request types, where 'kill' requests have the highest priority to immediately acquire locks by aborting existing transactions, and 'low priority' requests allow new normal priority requests to proceed while waiting, with optional user-configurable settings to manage wait times and actions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a DDL statement requests an exclusive lock to modify table schema, then the lock ensures data consistency and prevents conflicting operations, but the DDL operation is blocked for a long time if long-running queries are present on the table
Solution Approach 1:
The patent introduces a priority parameter for lock requests, allowing DDL operations to be assigned higher priority than normal transactions. This parameter change enables the system to differentiate between critical maintenance operations and regular queries, allowing high-priority DDL requests to preempt or interrupt lower-priority long-running queries, thereby reducing DDL wait time while maintaining data consistency through controlled lock acquisition
2Ease of operation
If a DDL statement holds an exclusive lock to complete maintenance operations, then the operation can access and modify the table, but all new lock requests are blocked and the table becomes unavailable
Solution Approach 1:
The patent implements dynamic lock request handling where the system can adjust lock acquisition behavior based on real-time conditions. Instead of holding exclusive locks for the entire duration of maintenance operations, the system uses short-lived exclusive locks only when absolutely necessary for schema modifications, and otherwise allows concurrent access through shared locks or optimistic concurrency control. This dynamic approach maintains ease of operation for maintenance tasks while preserving system throughput during normal operations
Solution Approach 2:
The patent segments maintenance operations into phases that require different lock types. Critical schema modification moments use exclusive locks, while data migration and other less critical phases use shared locks or no locks at all, allowing concurrent read operations. This segmentation reduces the duration and scope of blocking, enabling maintenance operations to proceed while minimizing impact on overall system productivity
3Reliability
If the system waits for long-running queries to complete before allowing DDL operations, then data consistency is maintained, but the table remains inaccessible for extended periods during heavy OLTP workloads
Solution Approach 1:
The patent implements preliminary actions to prepare for DDL operations without requiring full exclusive locks in advance. The system can pre-validate DDL requests, check for potential conflicts, and notify users of expected wait times before actual lock acquisition. This preliminary preparation allows the system to make informed decisions about whether to proceed with blocking, abort conflicting transactions, or reschedule operations, thereby reducing unnecessary table unavailability while maintaining data consistency
Data Source
AI summary
A method includes requesting a lock on a resource. The request for the lock on the resource is specified as a low priority non-blocking request that does not block one or more other requests such that one or more other requests can request a lock on the resource and obtain the lock on the resource in priority to the low priority non-blocking request. Based on the low priority request, the method includes maintaining the low priority request in a non-blocking fashion until a predetermined condition occurs. As a result of the predetermined condition occurring, the method includes handling the low priority request such that it is no longer treated as a low priority non-blocking request. Embodiments may further include a kill request which kills any operations on the resource, aborts any transactions having a lock on the resource, and locks the resource.