Batching Database Transactions to Reduce Contention Overhead
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Business intelligence queries face severe contention issues due to high throughputs and concurrent modifications in traditional transaction systems, especially on multicore and NUMA hardware, leading to significant physical contention overheads.
Innovation Solution
The method involves clustering database transaction requests based on operation types, performing combined operations within a single thread, and preparing log records for recovery and rollback, allowing recoverable update operations to be executed within a single logical thread, thereby reducing contention and improving processing efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If every transaction runs in its own thread with latches and locks to protect data, then data consistency and reliability are ensured, but contention overhead increases severely at high throughputs
Solution Approach 1:
The patent merges multiple individual transaction threads into a single batch thread that processes multiple transactions together. The batch processor combines multiple transactions into a single thread of execution, allowing them to share latches and locks rather than each transaction acquiring them separately. This merging reduces the total contention overhead while maintaining data consistency through the batch processing mechanism.
2Ease of operation
If BI queries and updates run concurrently in separate threads, then query responsiveness and system availability are maintained, but physical contention overheads increase on multicore and NUMA hardware
Solution Approach 1:
The patent introduces a batch processor as an intermediary layer between individual transactions and the actual data operations. This batch processor acts as a mediator that collects multiple transactions, combines them into a batch, and executes them together in a single thread. This intermediary mechanism reduces direct contention between BI queries and updates while maintaining system availability and query responsiveness.
3Speed
If multiple transactions are processed in parallel threads, then processing speed and throughput are improved, but latching and logging costs multiply
Solution Approach 1:
The patent combines multiple transactions into a single batch that is processed in one thread, merging the latching and logging operations that would otherwise occur separately for each transaction. By executing multiple transactions together in a batch, the system reduces the total number of latch acquisitions and log writes, thereby reducing the multiplicative overhead while maintaining processing speed through efficient batch execution.
Data Source
AI summary
A method includes scanning multiple incoming database transaction requests. Each transaction includes one or more operations. Operations are clustered into a set of combined operations based on type of operation constraints. Log records are prepared and written for re-performing operations upon system failures, and for undoing operations upon an operation or a transaction failing to be processed fully. Each set of combined operations are performed within a thread. Each update operation is marked for a transaction within which the update operation belongs. Recoverable update operations belonging to a plurality of transactions are performed within a single logical thread of execution.