Database Row Locking After Qualification to Reduce Deadlocks
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional locking techniques in database systems lead to excessive memory usage, deadlocks, and performance issues, especially in large-scale big data and database as a service environments, due to improper coordination among multiple processes accessing the same data.
Innovation Solution
Implement a database management system that locks database objects only after they meet a condition, using a qualified timestamp generated at the start of a database operation to identify and update eligible rows, thereby reducing unnecessary locking and blocking.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If conventional locking techniques are used to maintain database consistency, then data consistency is improved, but memory usage and system performance deteriorate
Solution Approach 1:
The patent extracts the locking mechanism from the traditional pre-qualification phase and applies it only after row qualification is complete. This selective application of locking removes unnecessary locks on rows that don't meet update conditions, reducing memory consumption while maintaining data consistency for actually updated rows.
Solution Approach 2:
The patent performs row qualification actions (evaluating update conditions) before acquiring locks. By determining which rows meet the update criteria first, the system prepares a precise set of targets for locking, avoiding the need to lock all potentially affected rows and thereby reducing memory usage.
2Reliability
If conventional locking techniques are used to coordinate multiple processes, then data consistency is improved, but system performance and productivity deteriorate
Solution Approach 1:
The patent removes unnecessary locking operations by extracting the lock acquisition step and applying it selectively only after qualification. This eliminates lock overhead for rows that won't be updated, reducing blocking and improving system throughput and performance.
Solution Approach 2:
By performing qualification before locking, the system identifies exactly which rows require updates and locks only those. This preliminary identification reduces the scope of locking, minimizing blocking of concurrent operations and improving overall system productivity.
3Reliability
If locking is applied before row qualification, then data consistency is ensured, but unnecessary locking and deadlocks increase
Solution Approach 1:
The patent inverts the traditional sequence by performing row qualification before acquiring locks, rather than locking first. This reversal allows the system to identify qualifying rows without holding locks, then lock only those specific rows, thereby reducing lock scope and the probability of deadlocks.
Solution Approach 2:
The system performs the qualification action preliminarily before locking, determining which rows meet update conditions. This preliminary step creates a precise target set for locking, avoiding broad locking that increases deadlock risk while maintaining consistency for the actual update targets.
Data Source
AI summary
Example aspects include techniques for anomaly detection via sparse judgmental samples. These techniques may include generating a qualified timestamp corresponding to execution of a first operation of a first transaction over a database and determining, based on the qualified timestamp, that a row of a table is not locked by a second operation of a second transaction over the database. In addition, the techniques may include determining that the row meets a condition of the first operation. Further, the techniques may include updating, based on the qualified timestamp, the row in response to the row meeting the condition, the updating including acquiring a lock on the row of the table.