Configuring Multi-Statement Processing Modes

Scenarios

You can configure the way how database proxies process multiple statements as needed.

Multi-Statement Processing Modes

• Strict (default): If a request containing multiple statements is routed to the primary instance, the subsequent read and write requests sent over the same connection are all routed to the primary instance. Read/write splitting can be restored only after you disconnect your connection to the DB instance and re-establish it. Your database proxy will not parse these statements, so the Strict mode is suitable when short connections are used or there is no connection reuse.
• Loose: If a request containing multiple statements is routed to the primary instance, the subsequent requests sent over the current connection can still be routed to the primary instance or read replicas. Your database proxy will not parse these statements, so Loose is recommended 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: A read-only request containing multiple statements is routed based on weights. A read/write request containing multiple statements is routed to the primary instance, and the database proxy parses these statements and determines whether to split subsequent read and write requests received over the current connection based on the operations in the SQL statements (Parse Mode). Parsing a multi-statement request consumes more resources. The impact on proxy performance depends on the length and complexity of the statements, so it is recommended that the statements be less than 100 MB.

Parse Mode

If a multi-statement request contains any of the following operations, all subsequent requests are routed to the primary instance. 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 (In this case, parsing these statements will fail.)

Changing the multi-statement processing mode applies to your proxy immediately. You do not need to reboot the proxy. If read/write splitting is invalid on the connection over which the proxy has processed a multi-statement request, changing the multi-statement processing mode will not restore read/write splitting on this connection. You need to re-establish it.