Grouping Database Queries to Reduce Deadlocks
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems face issues such as deadlocks among transactions and high overhead in processing queries/transactions, leading to reduced performance and efficiency.
Innovation Solution
A method is introduced to group database queries and transactions by identifying commutative and associative operations, combining them into fewer statements, and submitting these grouped statements to the database system, which reduces the number of transactions and locks required, thereby enhancing efficiency and reducing deadlocks.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If multiple transactions place locks on relations and views to ensure transactional consistency, then data integrity is maintained, but deadlock conditions occur reducing system performance
Solution Approach 1:
The patent combines multiple individual transaction statements into a single grouped transaction statement. By merging commutative and associative operations (such as multiple UPDATE statements modifying the same relation with compatible lock requirements), the system reduces the total number of locks held simultaneously, thereby reducing deadlock probability while maintaining transactional consistency through the grouped transaction's atomic execution.
Solution Approach 2:
The patent performs preliminary analysis of transaction statements to identify commutative and associative operations before transaction execution. By pre-grouping statements that can be safely combined (those operating on the same relation with compatible lock requirements), the system prepares optimized transaction bundles that reduce lock contention and deadlock risk during actual execution.
2Productivity
If client systems send large numbers of queries/transactions to the database system, then query processing capacity is utilized, but database system performance suffers due to high overhead
Solution Approach 1:
The patent merges multiple individual queries and transactions into grouped transactions by identifying commutative and associative operations. This reduces the total number of separate query/transaction processing cycles the database system must handle, thereby reducing processing overhead and improving performance while maintaining the ability to handle large volumes of operations through efficient batching.
Solution Approach 2:
The patent performs preliminary grouping of queries and transactions before they reach the database engine. By pre-identifying and combining compatible operations (those with commutative and associative characteristics), the system reduces the number of individual processing requests, thereby reducing overhead and improving throughput for large-scale query workloads.
Data Source
AI summary
A method and apparatus identifies SQL statements in a first transaction that specify modification operations that are commutative and associative. The SQL statements are combined into one SQL statement, and the one SQL statement is submitted to a database system. Optionally, or alternatively, multiple transactions can be grouped together into one transaction.