Session status synchronization

This topic describes the purpose and process of OceanBase Database Proxy (ODP) session status synchronization.

Purpose

When a client establishes a connection to OceanBase Database through ODP, there is only one connection between the client and ODP and there may be multiple connections between ODP and OBServer nodes, as shown in the following figure. In other words, one client connection maps multiple server connections. To ensure the accuracy of execution results, the session status of multiple server connections must be consistent.

As shown in the figure above, between the client and ODP, there is a session called Session0, which is perceived by the client. If the state of Session0 is not synchronized to Session1, Session2, or Session3, an error may occur when an SQL statement is executed.

To solve this problem, ODP introduces the versioning mechanism to ensure the status synchronization of the database, session variables, last_insert_id, and prepared statements. ODP will synchronize the state of Session0 (such as system variables, user variables, and transaction status) to Session1, Session2, and Session3 so that OBServer nodes can take correct actions based on the session status.

As an example to illustrate the problem caused by out-of-sync state, assuming that autocommit is set to 1 for the current cluster, execute the following statements:

set autocommit = 0;
insert into t1 values(1);
insert into t2 values(2);

The execution process is as follows:

1. ODP sends the set autocommit = 0; statement to OBServer1.

2. ODP sends the insert into t1 values(1); statement to OBServer1.

3. ODP switches to the connection with OBServer2 and sends the insert into t2 values(2); statement to OBServer2.

Since the connection status autocommit=0 is not synchronized to the session between ODP and OBServer2, the transaction is committed on OBServer2 after the third SQL statement insert into t2 values(2); is executed, but OBServer1 is still in the transaction. To avoid this issue, ODP must synchronize the value of the autocommit variable before sending the INSERT SQL statement to OBServer2.

Process

In this section, we demonstrate how ODP performs status synchronization by modifying session-level system variables.

1. The client initiates a connection (Session0) with ODP, and ODP establishes a connection (Session1) with one of the OBServer nodes in the current cluster.

2. Assuming that autocommit is set to 1 for the cluster, when the client sends the set autocommit = 0 statement to change the value of the system variable autocommit to 0, ODP records the change in Session0 and then forwards it to OBServer1 through Session1.

   

3. If the client sends an SQL statement to access the data on OBServer2, ODP parses the statement and establishes a connection (Session2) with OBServer2 upon receipt of the statement.

   

4. In Session0, the system variable autocommit is set to 0, while in Session2, the system variable remains at its default value of 1. In this case, ODP will override the value of AUTOCOMMIT=0 from Session0 to Session2 to ensure that the client's session status remains consistent with the server's session status.