Use Seata to implement distributed transaction management for OceanBase Database

Apache Seata is an open-source distributed transaction solution that provides high-performance and easy-to-use distributed transaction services in a microservices architecture. This topic describes how to integrate Seata with the Dubbo microservices framework to implement distributed transaction management for OceanBase Database. It covers the use of the AT mode, the creation of the undo_log table, and the testing of transaction commit and rollback.

Version compatibility

Seata is integrated with OceanBase Database. We recommend that you use the following versions:

• OceanBase Database: ≥ V4.2.1
• Seata-Server: ≥ V2.1.0

Limitations

When you use Seata with OceanBase Database, note the following limitations:

Limitations on SQL statements

• Seata transactions support only some features of the INSERT, UPDATE, and DELETE statements.
• DDL statements such as CREATE, ALTER, and DROP are not supported.
• Stored procedures, functions, and triggers cannot be called.
• The supported SQL statements are being expanded. We recommend that you use only the SQL statements supported by Seata.

Prerequisites

Before you use Seata, make sure that:

• You have installed Seata-Server. For more information, see Download Seata-Server.
• You have deployed OceanBase Database and created a MySQL mode user tenant. For more information, see Create a user tenant.
• You have cloned the seata-samples project to your local computer and imported it to your development tool. Then, find the /at-sample/dubbo-samples-seata subproject.

Procedure

Step 1: Obtain the connection string of OceanBase Database

Contact the OceanBase Database deployment personnel to obtain the connection string, for example:

obclient -h$host -P$port -u$user_name -p$password -D$database_name

Parameter description:

• $host: the IP address for connection. For ODP connection, use the ODP address. For direct connection, use the OBServer IP address.

• $port: the connection port. For ODP connection, the default value is 2883. For direct connection, the default value is 2881.

• $database_name: the database name.

  Notice

  The user for connecting to the tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL mode.

• $user_name: the connection account. For ODP connection, the format is user@tenant#cluster or cluster:tenant:user. For direct connection, the format is user@tenant.

• $password: the account password.

For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

Example:

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest

Step 2: Prepare the Seata sample project

1. Clone the sample project.

   Clone the Seata sample project from GitHub to your local machine:

   git clone https://github.com/apache/incubator-seata-samples
   

2. Import the project.

   Import the project into your development tool:

   cd incubator-seata-samples/
   ls -l 
   

3. Locate the subproject.

   Find the /at-sample/dubbo-samples-seata subproject.

   ls -l  at-sample/springboot-dubbo-seata/
   

   The following subprojects are displayed:

   drwxr-xr-x 3 root root 4096 Apr 22 14:50 springboot-dubbo-seata-account
   drwxr-xr-x 3 root root 4096 Apr 22 14:50 springboot-dubbo-seata-business
   drwxr-xr-x 3 root root 4096 Apr 22 14:50 springboot-dubbo-seata-common
   drwxr-xr-x 3 root root 4096 Apr 22 14:50 springboot-dubbo-seata-order
   drwxr-xr-x 3 root root 4096 Apr 22 14:50 springboot-dubbo-seata-storage
   

Step 3: Create a database and initialize data

Create a test database in OceanBase Database and execute the following SQL script:

1. Create the undo_log table (required for the Seata AT mode):

   -- for AT mode you must to init this sql for you business database. the seata server not need it.
   CREATE TABLE IF NOT EXISTS `undo_log`
   (
       `branch_id`     BIGINT       NOT NULL COMMENT 'branch transaction id',
       `xid`           VARCHAR(128) NOT NULL COMMENT 'global transaction id',
       `context`       VARCHAR(128) NOT NULL COMMENT 'undo_log context,such as serialization',
       `rollback_info` LONGBLOB     NOT NULL COMMENT 'rollback info',
       `log_status`    INT(11)      NOT NULL COMMENT '0:normal status,1:defense status',
       `log_created`   DATETIME(6)  NOT NULL COMMENT 'create datetime',
       `log_modified`  DATETIME(6)  NOT NULL COMMENT 'modify datetime',
       UNIQUE KEY `ux_undo_log` (`xid`, `branch_id`)
       ) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT ='AT transaction mode undo table';
   ALTER TABLE `undo_log` ADD INDEX `ix_log_created` (`log_created`);
   

2. Create the business table and initialize the test data:

   DROP TABLE IF EXISTS `stock_tbl`;
   CREATE TABLE `stock_tbl`
   (
       `id`             int(11) NOT NULL AUTO_INCREMENT,
       `commodity_code` varchar(255) DEFAULT NULL,
       `count`          int(11) DEFAULT 0,
       PRIMARY KEY (`id`),
       UNIQUE KEY (`commodity_code`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
   
   DROP TABLE IF EXISTS `order_tbl`;
   CREATE TABLE `order_tbl`
   (
       `id`             int(11) NOT NULL AUTO_INCREMENT,
       `user_id`        varchar(255) DEFAULT NULL,
       `commodity_code` varchar(255) DEFAULT NULL,
       `count`          int(11) DEFAULT 0,
       `money`          int(11) DEFAULT 0,
       PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
   
   DROP TABLE IF EXISTS `account_tbl`;
   CREATE TABLE `account_tbl`
   (
       `id`      int(11) NOT NULL AUTO_INCREMENT,
       `user_id` varchar(255) DEFAULT NULL,
       `money`   int(11) DEFAULT 0,
       PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
   ---INITIALIZE THE ACCOUNT TABLE
   INSERT INTO account_tbl(`user_id`,`money`) VALUES('ACC_001','1000');
   ---INITIALIZE THE STOCK TABLE
   INSERT INTO stock_tbl(`commodity_code`,`count`) VALUES('STOCK_001','100');
   

Step 4: Configure the application connection information

Update the database connection information in the following three submodules to match your OceanBase Database connection information:

• dubbo-samples-seata-account
• dubbo-samples-seata-order
• dubbo-samples-seata-stock

Example:

url: jdbc:mysql://$host:$port/$database_name?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useOldAliasMetadataBehavior=true
username: $user_name
password: $password

Description of the Seata configuration parameters

Example:

seata:
  tx-service-group: my_tx_group
  service:
    vgroup-mapping:
      my_tx_group: default
    default:
      grouplist: 127.0.0.1:8091
  enable-auto-data-source-proxy: true

Step 5: Start Seata-Server

1. Download the binary package of Seata-Server from the Seata-Server GitHub repository and decompress it:

   wget https://github.com/apache/incubator-seata/archive/refs/tags/v2.6.0.tar.gz
   

2. Go to the bin directory of the decompressed package:

   tar -zxvf v2.6.0.tar.gz
   cd incubator-seata-2.6.0/distribution/bin
   

3. Execute the corresponding startup command based on your operating system:

   • For Mac OS or Linux:

     ./seata-server.sh
     

   • For Windows:

     ./seata-server.bat
     

Step 6: Start the sample services

Start the following subprojects in sequence:

1. Account Service
2. Order Service
3. Stock Service
4. Business Service

Step 7: Test distributed transactions

Test distributed transactions by accessing the following links:

1. Test the successful commit process of a distributed transaction:

   Access: http://$test_host:$test_port/test/commit?userId=ACC_001&commodityCode=STOCK_001&orderCount=1

   When a distributed transaction is successfully committed, the data in the business tables will be updated normally. Please observe the data in the database tables.

2. Test the rollback process of a distributed transaction:

   Access: http://$test_host:$test_port/test/rollback?userId=ACC_001&commodityCode=STOCK_001&orderCount=1

   When a distributed transaction is rolled back, the data in the business tables will remain unchanged. Please observe the data in the database tables.

Verify the results

1. Verify that the transaction is successfully committed: After accessing the test link for a successful transaction commit, check if the data in the account_tbl, stock_tbl, and order_tbl tables in the database has been updated as expected.
2. Verify that the transaction is rolled back: After accessing the test link for a failed transaction rollback, check if the data in the database tables remains unchanged to confirm that the transaction rollback was effective.
3. Verify the status of the Seata-Server: Ensure that the Seata-Server is running normally. You can check the logs to confirm the transaction coordination status.