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.

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;

Example

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"}