Ad-Hoc Stored Procedures for Low-Contention OLTP Transactions
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Efficient transaction processing in OLTP systems is hindered by lock contention, leading to performance degradation due to multiple round trips and unpredictable database access commands.
Innovation Solution
Implementing ad-hoc stored procedures that are created on-demand and executed within a transaction scope, eliminating parameter encoding, and unrolling loop structures to minimize locks and reduce round trips.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If traditional database access commands are used with multiple round trips, then flexibility in database operations is maintained, but transaction processing speed deteriorates
Solution Approach 1:
The patent combines multiple separate database access commands into a single stored procedure execution. By merging SELECT, UPDATE, and other SQL commands into one atomic stored procedure call, the system eliminates multiple round trips between the application server and database server, thereby improving transaction processing speed while reducing network overhead.
Solution Approach 2:
The patent performs preliminary actions by pre-compiling and storing the sequence of database operations as a stored procedure in the database server. This allows the database server to prepare and optimize the execution plan in advance, so when the stored procedure is invoked, it can execute the entire sequence of operations in a single call without requiring multiple separate command round trips.
2Reliability
If locks are held during transaction execution to ensure atomicity, then data consistency is maintained, but lock contention increases and performance deteriorates
Solution Approach 1:
The patent segments the transaction execution by moving the atomic operation sequence into a stored procedure that executes entirely on the database server side. This segmentation allows the application server to release locks earlier while maintaining data consistency through the database server's internal transaction management, thereby reducing lock contention and improving throughput.
Solution Approach 2:
The stored procedure acts as an intermediary between the application server and the database operations. It encapsulates the atomic transaction logic on the database server side, allowing the application server to simply call the stored procedure without holding locks during the entire transaction execution. This intermediary mechanism maintains data consistency while reducing lock contention and improving transaction processing throughput.
3Productivity
If ad-hoc stored procedures are created and executed on-demand, then transaction processing efficiency is improved, but database server resource overhead increases
Solution Approach 1:
The patent optimizes stored procedure creation by using parameterized queries and caching mechanisms. Instead of creating entirely new stored procedures for each transaction, the system reuses existing stored procedures with different parameter values, thereby reducing the overhead of compilation and optimization while maintaining the efficiency benefits of on-demand execution.
Data Source
AI summary
An example method of using ad-hoc stored procedures for OLTP operations includes: identifying, by a processing device, one or more values for updating a dataset stored in a database; creating an ad-hoc stored procedure comprising a sequence of commands specifying atomically-executable database update operations with respect to the dataset, wherein the atomically-executable database update operations utilize the one or more values that are encoded into the sequence of commands by respective inline constant values; executing the ad-hoc stored procedure, wherein the executing comprises acquiring a temporary lock of at least part of the dataset; and dropping the ad-hoc stored procedure.