Database Session Safe Release During Planned Maintenance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database management systems (DBMS) face challenges in safely disconnecting database sessions during planned maintenance operations due to the caching of connections, which can lead to ambiguous errors, user frustration, and application disruptions, as the DBMS lacks visibility into the connection management by the application or connection pool manager.
Innovation Solution
Implementing a method to detect safe points in database connections for safe disconnection, where the DBMS or client driver monitors for conditions such as connection tests, request boundaries, and session states to determine when it is safe to disconnect sessions without affecting the application, allowing for graceful shutdown and potential failover to another instance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of energy
If connections are cached by the application or connection pool manager to optimize resources, then resource efficiency is improved, but the DBMS loses visibility into session management making safe disconnection difficult
Solution Approach 1:
A notification mechanism is introduced as an intermediary between the connection pool manager and the DBMS. The CPM sends notifications to the DBMS when connections are returned to the pool, providing the DBMS with visibility into connection availability without requiring the DBMS to manage connections directly. This resolves the contradiction by maintaining resource efficiency through caching while enabling the DBMS to make informed disconnection decisions.
2Productivity
If the DBMS arbitrarily disconnects in-use sessions for planned maintenance, then maintenance operations can be performed, but application disruptions and errors occur
Solution Approach 1:
The system performs preliminary actions by sending a notification to the connection pool manager before executing the planned maintenance operation. The CPM receives this notification and begins draining connections by returning them to the pool and preventing new connections. This preliminary draining phase ensures that by the time maintenance begins, all connections are safely returned to the pool, allowing maintenance to proceed without disrupting active application sessions.
Solution Approach 2:
The notification mechanism establishes a feedback loop where the CPM communicates connection status to the DBMS. The DBMS can query the CPM to determine if connections are available for disconnection, and the CPM provides feedback about the current state of connections in the pool. This feedback enables the DBMS to make informed decisions about when it is safe to disconnect sessions for maintenance without causing application disruptions.
3Loss of time
If connections are always kept open to avoid connection establishment overhead, then connection performance is improved, but safe disconnection during maintenance becomes complex
Solution Approach 1:
The connection pool manager is designed to autonomously manage the connection lifecycle without requiring complex DBMS intervention. The CPM independently tracks which connections are in use and which are available in the pool, making disconnection management self-service. When maintenance is needed, the DBMS simply notifies the CPM, and the CPM handles the complex task of draining and returning connections to the pool, reducing the complexity of disconnection management for the overall system.
Data Source
AI summary
Techniques are described for closing one or more connections with a database management system (DBMS) as part of a planned operation such as maintenance and load balancing. In an embodiment, a request is received for a planned operation of a database server of a DBMS that requires releasing and migrating database sessions. Over a database connection with the database server, the DBMS or a Client-Driver on the client system that interfaces with the DBMS, may receive request(s) from the client system. Based on the request(s), a safe point, for the database connection with the database server, at which the database connection of the client system with the database server can be closed without affecting the success of execution of next one or more command that the client system intended to request the DBMS to execute on the database server, is determined. In an embodiment, in response to the request(s), the DBMS and/or Client-Driver, while the connection is valid, provide to the client system an invalid status for a connection test request. Such a response may cause the client system to use a different database connection to connect with the DBMS. After returning the response to the client system, the DBMS and/or Client-Driver causes the database session with the DBMS to close. In an embodiment, for database sessions to be released, the DBMS identifies a safe point in a database session by a detected request boundary or where the session states are determined to be recoverable or the failover is determined to be expected to succeed. At such a safe point, all database session states are known, and failing the session over to another database instance offering the requested service can be performed safely and transparent to applications.