Distributed transaction routing

In OceanBase Database V4.1.0 and later, a transaction can be executed on multiple nodes, not just the one that starts the transaction. When a transaction contains multiple read/write statements and the accessed data is in different partitions, OceanBase Database Proxy (ODP) can send the requests in the transaction to the OBServer nodes where the data resides, rather than the node that starts the transaction. This can reduce remote plans and improve the transaction performance of OceanBase Database.

Select nodes for transaction routing

Two roles are involved in transaction routing: coordinator node and participant node.

• Coordinator node: the node that starts the transaction. It executes non-DML statements that affect the transaction status, such as BEGIN, START TRANSACTION, COMMIT, and ROLLBACK.

• Participant node: a node that executes DML statements that do not affect the transaction status.

ODP comprises a simple SQL parser that parses whether an SQL statement is a DML statement. For DML statements, ODP routes the statements to proper nodes based on table routing or logical data center (LDC)-based routing. The routed-to nodes are participant nodes of the transaction. The node that executes the first statement in the transaction is the coordinator node of the transaction.

Enable distributed transaction routing

You can enable distributed transaction routing by configuring the following ODP parameters:

ALTER PROXYCONFIG SET enable_ob_protocol_v2 = true;
ALTER PROXYCONFIG SET enable_transaction_internal_routing = true;

Examples

1. Execute the following statements to enable the OceanBase 2.0 protocol and distributed transaction routing:

   ALTER PROXYCONFIG SET enable_ob_protocol_v2 = true;
   ALTER PROXYCONFIG SET enable_transaction_internal_routing = true;
   

2. Execute the following statements to create a table, start a transaction, and initiate a query:

   • Create a table

     obclient [test]> CREATE TABLE T0(C1 INT)  PARTITION BY HASH(C1) PARTITIONS 8;
     

   • Start a transaction

     obclient [test]> BEGIN;
     

   • Initiate a query

     obclient [test]> SELECT * FROM T0 WHERE C1=0;
     

     Partition routing is performed for the next statement after the query, even if this statement is the first statement of the transaction.

3. Execute the following statement to query the routing process in ODP:

   obclient [test]> EXPLAIN ROUTE INSERT INTO T0 VALUES(7)\G
   

   The output is as follows:

   *************************** 1. row ***************************
   Route Plan:
   Trans First Query:"SELECT * FROM T0 WHERE C1=0"
   Trans Current Query:"EXPLAIN ROUTE INSERT INTO T0 VALUES(7)"
   
   Route Prompts
   -----------------
   > ROUTE_INFO
     [INFO] Will route to partition server or routed by route policy
   
   Route Plan
   -----------------
   > SQL_PARSE:{cmd:"COM_QUERY", table:"T0"}
   > ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP", in_transaction:true}
   > LOCATION_CACHE_LOOKUP:{mode:"oceanbase"}
     > TABLE_ENTRY_LOOKUP_DONE:{table:"T0", table_id:500021, partition_num:8, table_type:"USER TABLE", entry_from_remote:false}
     > PARTITION_ID_CALC_START:{}
       > EXPR_PARSE:{col_val:"=7"}
       > RESOLVE_EXPR:{part_range:"[7 ; 7]"}
         > RESOLVE_TOKEN:{token_type:"TOKEN_INT_VAL", resolve:{"BIGINT":7}, token:"7"}
       > CALC_PARTITION_ID:{part_description:"partition by hash(INT(binary)) partitions 8"}
     > PARTITION_ID_CALC_DONE:{partition_id:200012, level:1, partitions:"(p7)"}
     > PARTITION_ENTRY_LOOKUP_DONE:{leader:"10.10.10.1:50109", entry_from_remote:false}
   > ROUTE_POLICY:{chosen_route_type:"ROUTE_TYPE_LEADER"}
   > CONGESTION_CONTROL:{svr_addr:"10.10.10.1:50109"}