Stored Procedure Execution in Distributed Database Systems
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In distributed database systems, executing stored procedures across multiple independent database servers leads to inconsistencies and performance issues due to differing outcomes, transactional control interference, and complex locking requirements, which can result in data deadlocks and incorrect data access.
Innovation Solution
A load balancer processor identifies and executes specific executable statements of a stored procedure, distributing database operations across multiple data store systems, allowing selective locking and reducing the need for extensive locking, thereby maintaining data integrity and improving performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If stored procedures are executed on multiple independent database servers, then system productivity and scalability are improved, but data consistency and reliability deteriorate due to differing execution outcomes and transactional control interference
Solution Approach 1:
The patent introduces a central coordinator server as an intermediary that manages stored procedure execution across distributed database servers. The coordinator receives execution requests, determines the primary database server using a deterministic algorithm (hashing the procedure name and database name), and routes the execution to that specific server. This intermediary ensures that all executions of the same stored procedure on the same database are directed to the same primary server, guaranteeing data consistency while allowing the system to scale by adding more database servers to the distributed architecture.
2Reliability
If extensive locking is implemented to ensure data consistency, then reliability is improved, but system performance and productivity worsen due to deadlocks and performance bottlenecks
Solution Approach 1:
The patent extracts the transaction coordination and locking management functions from the individual database servers and centralizes them in the coordinator server. The coordinator maintains information about which database server is primary for each stored procedure and manages the locking scope. By taking out these control functions, individual database servers can execute stored procedures with minimal locking overhead, as the coordinator ensures that only the primary server executes the procedure, eliminating the need for extensive inter-server locking protocols and reducing deadlock risks.
3Reliability
If a data dictionary is maintained to manage locking scopes, then data consistency is improved, but device complexity and ease of operation worsen due to manual entries and maintenance overhead
Solution Approach 1:
The patent implements a self-updating data dictionary mechanism where the coordinator server automatically determines and records the primary database server for each stored procedure using a deterministic algorithm that hashes the procedure name and database name. When a stored procedure is created or modified, the system automatically updates the data dictionary entry with the calculated primary server identifier. This eliminates the need for manual data dictionary maintenance, as the system self-generates and self-updates the necessary routing information based on the hashing algorithm results.
Data Source
AI summary
A method may include receiving a stored procedure associated with data stored in a plurality of data stores. The stored procedure may include a plurality of executable statements. The method may further include identifying a first executable statement of the plurality of executable statements to be executed by the processor and a second executable statement of the plurality of executable statements that is executable by at least one of a plurality of other processors. The other processors each may have access to only a respective one of the plurality of copies of the data. The method may further include executing the first executable statement. A system and computer-readable medium may also be implemented.