Database Proxy Prepared Statement Multiplexing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database proxies are unable to multiplex database connections when clients use prepared statements, leading to conflicts and errors due to the association of prepared statements with specific database connections, which prevents efficient resource utilization and increases costs.
Innovation Solution
A database proxy that stores state data associated with clients, allowing it to reuse database connections by sending prepared statement setup data to the database when a different connection is used, ensuring that prepared statements are set up correctly with the current connection, even if the client reuses them across different database connections.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If database connections are multiplexed for different clients, then resource utilization is improved and costs are reduced, but conflicts and errors occur due to prepared statements being associated with specific connections
Solution Approach 1:
The database proxy acts as an intermediary between clients and the database. It maintains a mapping between client connections and database connections, and when multiplexing occurs, the proxy sends prepared statement setup data to the database to ensure prepared statements are correctly associated with the current connection, thus resolving conflicts while enabling resource sharing
Solution Approach 2:
The database proxy performs preliminary action by storing prepared statement setup data and proactively sending it to the database before actual query execution. This ensures that when a database connection is reused for a different client, the prepared statements are already correctly set up for that connection, preventing conflicts and errors
2Reliability
If direct database connections are established with each client, then connection reliability is maintained, but resource usage increases and costs increase
Solution Approach 1:
The database proxy establishes a pool of database connections that can be universally reused across multiple clients. Instead of dedicating one connection per client, the proxy multiplexes connections among different clients, allowing a single connection to serve multiple clients sequentially, thereby reducing total resource usage while maintaining reliable service
3Manufacturing precision
If prepared statements are set up with each database connection, then statement correctness is ensured, but connection multiplexing becomes impossible
Solution Approach 1:
The database proxy serves as an intermediary that decouples the prepared statement setup process from the actual query execution. It stores prepared statement setup data and transmits it to the database when needed, allowing prepared statements to be correctly associated with different connections without requiring permanent 1:1 binding between clients and connections
Solution Approach 2:
The proxy performs preliminary setup by capturing prepared statement metadata and proactively sending it to the database before connection reuse occurs. This ensures that when a connection is switched to a different client, the prepared statements are already correctly configured for that connection, maintaining statement correctness while enabling multiplexing
Data Source
AI summary
Systems and techniques are disclosed for multiplexing database connections with a database proxy, in association with prepared statements. When a client sends initial messages, via a client connection, that set up a prepared statement with a database, the database proxy stores corresponding prepared statement setup data in state data associated with the client connection. When the client sends subsequent messages via the client connection that invoke the prepared statement, the database proxy selects a database connection to use to forward the subsequent messages to the database. The database proxy uses the stored prepared statement setup data to select a database connection that the database already associates with the prepared statement, or to inject prepared statement setup messages that cause the database to set up the prepared statement with the selected database connection.