Modifying the Multi-statement Processing Mode of a Proxy Instance

When you enable multi-statement execution for a proxy instance, you can set multi-statement processing mode to Strict, Loose, or Parse.

Strict (default)
If a request containing multiple statements is routed to the primary node, the subsequent requests are all routed to the primary node. Read/write splitting can be restored only after you disconnect the current connection and reconnect it.

Your proxy instances will not parse these statements, so the performance is better. It is suitable for short connections.
Loose
If a request containing multiple statements is routed to the primary node, the subsequent requests of the current connection can still be routed to the primary node or read replicas.

Your proxy instances will not parse these statements, so the performance is better. It is good for when multiple statements contain only DML SQL statements and do not contain operations like setting session variables, creating temporary tables, creating stored procedures, or executing uncommitted transactions.
Parse
If a request containing multiple statements is routed to the primary node, your proxy instance parses these statements and determines whether to restore read/write splitting for subsequent requests of the current connection based on the operations in the SQL statements. For details about operations in SQL statements, see • Parse-based mode description:If multi-statements contain the operations listed here, all subsequent requests are routed to the primary node. To restore read/write splitting, you need to disconnect the connection and then re-establish it.Creating temporary tablesCreating stored proceduresExecuting uncommitted transactions (for example, begin is executed but commit or rollback is not executed)Executing complex or special syntax. These statements will not be parsed..

Parsing statements affects the proxy instance performance. The degree of the impact depends on the length and complexity of statements. It is recommended that the statements be less than 100 MB.

Constraints

To configure the multi-statement processing mode on the management console, contact customer service.
The changed multi-statement processing mode applies to your proxy instance immediately. You do not need to reboot the proxy instance. If a read/write splitting connection fails due to a multi-statement execution, changing the multi-statement processing mode will not restore the connection. You will need to reconnect the connection manually.
Parse-based mode description:
If multi-statements contain the operations listed here, all subsequent requests are routed to the primary node. To restore read/write splitting, you need to disconnect the connection and then re-establish it.
- Creating temporary tables
- Creating stored procedures
- Executing uncommitted transactions (for example, begin is executed but commit or rollback is not executed)
- Executing complex or special syntax. These statements will not be parsed.

Procedure

Log in to the management console.
Click in the upper left corner and select a region and project.
Click in the upper left corner of the page, choose Databases > GaussDB(for MySQL).
On the Instances page, click the instance name to go to the Basic Information page.
In the navigation pane, choose Database Proxy.
Click a proxy instance name to go to the Basic Information page.
In the navigation pane, choose Parameter Modifications.
Configure the parameter multiStatementType as required.

Figure 1 Configuring the parameter multiStatementType

You can set this parameter to Strict, Loose, or Parse.
Click Save to save your change. In the displayed dialog box, click Yes.