# US 2018/0322157 A1 · Adaptive query routing in a replicated database environment > Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt - Number: US 2018/0322157 A1 - Document ID: US20180322157A1 - Status: Published application - Kind: A1 - Application number: 15/589,160 - Filed: 2017-05-08 - Published: 2018-11-08 - Earliest claimed: 2017-05-08 - Assignees: Sap SE - Applicants: Sap SE - Inventors: Juchang Lee; Kyu Hwan Kim; Joo Yeon Lee; Kyungyul Park; Deok Hoe Kim - Web page: https://patentgrep.com/patent/US20180322157A1 ## CPC - G06F 16/2329: {using versioning} - G06F 11/2035: {without idle spare hardware} - G06F 11/2094: {Redundant storage or storage space (G06F11/2056 takes precedence)} - G06F 11/2097: {maintaining the standby controller/processing unit updated (initialisation or re-synchronisation thereof G06F11/1658 and subgroups)} - G06F 16/2322: {using timestamps} - G06F 16/2379: {Updates performed during online database operations; commit processing} - G06F 16/2471: {Distributed queries} - G06F 16/248: Presentation of query results - G06F 16/27: Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor - H04L 67/1095: Replication or mirroring of data, e.g. scheduling or transport for data synchronisation between network nodes - G06F 11/1658: {Data re-synchronization of a redundant component, or initial sync of replacement, additional or spare unit} - G06F 11/2048: {where the redundant components share neither address space nor persistent storage} - G06F 2201/80: Database-specific techniques - G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N) - H04L: TRANSMISSION OF DIGITAL INFORMATION, e.g. TELEGRAPHIC COMMUNICATION (arrangements common to telegraphic and telephonic communication H04M) ## Abstract Technologies are described providing adaptive query routing in a replicated database environment. The technologies can be used with a variety of replication protocols. Prepared statements, such as for queries, from a database client can be routed to a source node, or a source node and available replica nodes, when a replica node becomes unavailable. When a replica node becomes available again, a prepared statement can be updated to indicate that the updated prepared statement can be executed at nodes including the replica node that is available again. Prepared statement routing can also be used when a portion of replicated data becomes unavailable at a replica node, but a portion of replicated data remains available. ## Claims ### Claim 1 A method, implemented at least in part by a database system node comprising a processing unit and memory, the database system node being one of a plurality of database system nodes and being in communication with at least a portion of the plurality of database system nodes and with a database client, at least one of the plurality of database system nodes being a source node and at least one of the plurality of database system nodes being a replica node, the method comprising: determining that an availability status of the replica node has changed; receiving a prepared statement from the database client, the prepared statement specifying one or more nodes useable to execute the prepared statement; updating the prepared statement, the updating comprising adding a node to the one or more nodes in the prepared statement, or removing at least one of the one or more nodes in the prepared statement, depending on the type of availability status change of the replica node; and sending the updated prepared statement to the database client. ### Claim 2 (depends on 1) The method of claim 1, wherein the change in status of the replica node comprises the replica node becoming unavailable and updating the prepared statement comprises not including the replica node in the updated prepared statement. ### Claim 3 (depends on 1) The method of claim 1, wherein the change in status of the replica node comprises the replica node becoming available and updating the prepared statement comprises including the replica node in the updated prepared statement. ### Claim 4 (depends on 1) The method of claim 1, the method further comprising: in response to determining that the availability status of the replica node has changed, incrementing an identifier maintained by the database system node; comparing the incremented identifier with a version timestamp associated with the prepared statement; and determining that the version timestamp identifier is associated with an earlier state of the database system than a state of the database system associated with the incremented identifier; wherein the updating is carried out in response to determining that the version timestamp identifier is associated with an earlier state of the database system. ### Claim 5 (depends on 1) The method of claim 1, wherein the change in status of the replica node comprises the replica node being unavailable and updating the prepared statement comprises specifying that the prepared statement should be executed at the source database system node. ### Claim 6 (depends on 1) The method of claim 1, wherein the change in status of the replica node comprises the replica node being unavailable, the replica node is a first replica node, and updating the prepared statement comprises specifying that the updated prepared statement may be executed at the source database system node and at least a second replica node. ### Claim 7 (depends on 1) The method of claim 1, wherein determining that the availability status of the replica node has changed comprises determining that a portion of data replicated at the replica node is not available, a portion of the replicated data remains available, and updating the prepared statement comprises: determining data accessed by the prepared statement; determining whether data accessed by the prepared statement includes the portion of data replicated at the replica node that is not available; and if data accessed by the prepared statement is not available at the replica node, not including the replica node in the updated prepared statement. ### Claim 8 (depends on 1) The method of claim 1, wherein determining that the availability status of the replica node has changed comprises determining that a portion of data replicated at the replica node is not available, a portion of the replicated data remains available, and updating the prepared statement comprises: determining data accessed by the prepared statement; determining whether data accessed by the prepared statement includes the portion of data replicated at the replica node that is not available; and if data accessed by the prepared statement is available at the replica node, including the replica node in the updated prepared statement. ### Claim 9 (depends on 1) The method of claim 1, the method further comprising: updating a node status store to indicate the change in status of the replica node. ### Claim 10 (depends on 9) The method of claim 9, wherein the node status store maintains status information for at least a portion of the database system nodes, wherein each node is indicated as active or inactive, and updating the prepared statement comprising updating the prepared statement based on nodes having an active status. ### Claim 11 (depends on 1) The method of claim 1, wherein the node is the source node, the replica node is a first replica node and the database system further comprising at least a second replica node, the method further comprising: receiving a request to commit a database transaction, the database transaction comprising one or more database operations to be replicated at least at the at least a second replica node; beginning a process to commit the database transaction; marking the status of the first replica node as inactive; and after marking the status of the first replica node as inactive, committing the transaction. ### Claim 12 (depends on 11) The method of claim 11, the method further comprising receiving a communication from the at least a second replica node that the at least a second replica node marked the first replica node as inactive, wherein the committing occurs after receiving the communication. ### Claim 13 (depends on 11) The method of claim 11, the method further comprising: incrementing a first identifier maintained by the source node; and receiving a notification that the at least a second replica node has incremented a second identifier; wherein the first and second identifiers are useable to invalidate prepared statements maintained by the database client and the committing occurs after the incrementing and receiving the notification. ### Claim 14 A computer system comprising a database client comprising a processing unit and memory that accesses a replicated database system, the replicated database system comprising at least one source node and at least one replica node, the database client configured to perform a prepared statement execution method, the method comprising: sending a request to a database system node to prepare a statement; receiving a prepared statement from the database system node, the prepared statement specifying at least one database system node on which the prepared statement is executable; sending the prepared statement to the at least one node specified in the prepared statement; and receiving an updated prepared statement, the updated prepared statement specifying one or more nodes on which the updated prepared statement is executable, the one or more nodes comprising a node that was not the at least one node specified in the prepared statement, or omitting the at least one node specified in the prepared statement. ### Claim 15 (depends on 14) The computer system of claim 14, the method further comprising: sending the updated prepared statement to a node of the one or more nodes specified in the updated prepared statement for execution; and receiving execution results. ### Claim 16 One or more tangible computer-readable storage media storing computer-executable instructions for causing a computer device operating a first database system node, the computer device comprising a processing unit and memory and storing at least one database table and in communication with a database client and at least second and third database system nodes, programmed thereby to perform a method for facilitating prepared statement execution in a replicated database environment, the method comprising: determining that an availability status of a replica node of the replicated database environment has changed; recording the availability status change in a data store comprising availability status information for nodes of the replicated database system; incrementing a metadata identifier; receiving a prepared statement from the database client, the prepared statement associated with a version timestamp; comparing the incremented metadata identifier with the version timestamp; determining that the prepared statement should be updated; updating the prepared statement using the availability status information of the data store; and sending the updated prepared statement to the database client. ### Claim 17 (depends on 16) The one or more tangible computer-readable storage media of claim 16, wherein determining that an availability status of a replica node of the replicated database environment has changed comprises determining at least one replicated table that is not accessible at the replica node and recording the availability status change in the data store comprises recording that the at least one replicated table is not available at the replica node. ### Claim 18 (depends on 17) The one or more tangible computer-readable storage media of claim 17, wherein, after the recording, the data store comprises at least one replicated table that is accessible at the replica node. ### Claim 19 (depends on 18) The one or more tangible computer-readable storage media of claim 18, wherein updating the prepared statement comprises comparing tables accessed by the prepared statement with accessible tables recorded in the data store. ### Claim 20 (depends on 16) The one or more tangible computer-readable storage media of claim 16, the method further comprising: associating the incremented metadata identifier with the updated prepared statement.