You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2023/05/15 00:09:06 UTC
[shardingsphere] branch master updated: Add deadlock in transaction E2E (#25617)
This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new d593d25e492 Add deadlock in transaction E2E (#25617)
d593d25e492 is described below
commit d593d25e4929309be55a9215e0c25891a41322a8
Author: ZhangCheng <ch...@apache.org>
AuthorDate: Mon May 15 08:08:59 2023 +0800
Add deadlock in transaction E2E (#25617)
* Add deadlock in transaction E2E
* Add deadlock in transaction E2E
* Add deadlock in transaction E2E
* Add deadlock in transaction E2E
---
.../cases/base/BaseTransactionTestCase.java | 26 ++--
.../MultiOperationsCommitAndRollbackTestCase.java | 55 +++-----
.../deadlock/TransactionDeadlockTestCase.java | 139 +++++++++++++++++++++
.../src/test/resources/env/it-env.properties | 2 +-
.../src/test/resources/env/mysql/my.cnf | 1 +
.../data/actual/init-sql/mysql/01-actual-init.sql | 8 +-
.../actual/init-sql/opengauss/01-actual-init.sql | 8 +-
.../actual/init-sql/postgresql/01-actual-init.sql | 8 +-
.../data/actual/init-sql/mysql/01-actual-init.sql | 6 +-
.../actual/init-sql/opengauss/01-actual-init.sql | 6 +-
.../actual/init-sql/postgresql/01-actual-init.sql | 6 +-
11 files changed, 195 insertions(+), 70 deletions(-)
diff --git a/test/e2e/operation/transaction/src/test/java/org/apache/shardingsphere/test/e2e/transaction/cases/base/BaseTransactionTestCase.java b/test/e2e/operation/transaction/src/test/java/org/apache/shardingsphere/test/e2e/transaction/cases/base/BaseTransactionTestCase.java
index d1e5d8afd12..1cb2ed2b0f6 100644
--- a/test/e2e/operation/transaction/src/test/java/org/apache/shardingsphere/test/e2e/transaction/cases/base/BaseTransactionTestCase.java
+++ b/test/e2e/operation/transaction/src/test/java/org/apache/shardingsphere/test/e2e/transaction/cases/base/BaseTransactionTestCase.java
@@ -93,14 +93,12 @@ public abstract class BaseTransactionTestCase {
}
protected static void assertTableRowCount(final Connection connection, final String tableName, final int rowNum) throws SQLException {
- Statement statement = connection.createStatement();
- ResultSet resultSet = statement.executeQuery(String.format("select * from %s", tableName));
- int resultSetCount = 0;
- while (resultSet.next()) {
- resultSetCount++;
+ try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(String.format("SELECT COUNT(*) FROM %s", tableName))) {
+ if (resultSet.next()) {
+ int rowCount = resultSet.getInt(1);
+ assertThat(String.format("Recode num assert error, expect: %s, actual: %s.", rowNum, rowCount), rowCount, is(rowNum));
+ }
}
- statement.close();
- assertThat(String.format("Recode num assert error, expect: %s, actual: %s.", rowNum, resultSetCount), resultSetCount, is(rowNum));
}
protected void executeSqlListWithLog(final Connection connection, final String... sqlList) throws SQLException {
@@ -119,4 +117,18 @@ public abstract class BaseTransactionTestCase {
}
return result;
}
+
+ protected void assertAccountBalances(final Connection connection, final int... expectedBalances) throws SQLException {
+ try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT * FROM account")) {
+ while (resultSet.next()) {
+ int id = resultSet.getInt("id");
+ int actualBalance = resultSet.getInt("balance");
+ assertBalance(actualBalance, expectedBalances[id - 1]);
+ }
+ }
+ }
+
+ private void assertBalance(final int actual, final int expected) {
+ assertThat(String.format("Balance is %s, should be %s.", actual, expected), actual, is(expected));
+ }
}
diff --git a/test/e2e/operation/transaction/src/test/java/org/apache/shardingsphere/test/e2e/transaction/cases/commitrollback/MultiOperationsCommitAndRollbackTestCase.java b/test/e2e/operation/transaction/src/test/java/org/apache/shardingsphere/test/e2e/transaction/cases/commitrollback/MultiOperationsCommitAndRollbackTestCase.java
index 13a35bff5d1..10ba4092a4c 100644
--- a/test/e2e/operation/transaction/src/test/java/org/apache/shardingsphere/test/e2e/transaction/cases/commitrollback/MultiOperationsCommitAndRollbackTestCase.java
+++ b/test/e2e/operation/transaction/src/test/java/org/apache/shardingsphere/test/e2e/transaction/cases/commitrollback/MultiOperationsCommitAndRollbackTestCase.java
@@ -17,21 +17,14 @@
package org.apache.shardingsphere.test.e2e.transaction.cases.commitrollback;
-import com.google.common.base.Preconditions;
import org.apache.shardingsphere.test.e2e.transaction.cases.base.BaseTransactionTestCase;
import org.apache.shardingsphere.test.e2e.transaction.engine.base.TransactionBaseE2EIT;
import org.apache.shardingsphere.test.e2e.transaction.engine.base.TransactionContainerComposer;
import org.apache.shardingsphere.test.e2e.transaction.engine.base.TransactionTestCase;
-import org.apache.shardingsphere.test.e2e.transaction.engine.constants.TransactionTestConstants;
import javax.sql.DataSource;
import java.sql.Connection;
-import java.sql.ResultSet;
import java.sql.SQLException;
-import java.sql.Statement;
-
-import static org.hamcrest.CoreMatchers.is;
-import static org.hamcrest.MatcherAssert.assertThat;
/**
* Integration test of multiple operations in one transaction.
@@ -52,52 +45,32 @@ public final class MultiOperationsCommitAndRollbackTestCase extends BaseTransact
private void assertRollback() throws SQLException {
try (Connection connection = getDataSource().getConnection()) {
connection.setAutoCommit(false);
- assertTableRowCount(connection, TransactionTestConstants.ACCOUNT, 0);
- executeWithLog(connection, "insert into account(id, balance, transaction_id) values(1, 1, 1);");
- executeWithLog(connection, "insert into account(id, balance, transaction_id) values(2, 2, 2);");
- executeUpdateWithLog(connection, "update account set balance = 3, transaction_id = 3 where id = 2;");
- assertQueryAccount(connection, 1, 3);
+ assertAccountRowCount(connection, 0);
+ executeWithLog(connection, "INSERT INTO account(id, balance, transaction_id) VALUES(1, 1, 1)");
+ executeWithLog(connection, "INSERT INTO account(id, balance, transaction_id) VALUES(2, 2, 2)");
+ executeUpdateWithLog(connection, "UPDATE account SET balance = 3, transaction_id = 3 WHERE id = 2");
+ assertAccountBalances(connection, 1, 3);
connection.rollback();
}
try (Connection connection = getDataSource().getConnection()) {
- assertTableRowCount(connection, TransactionTestConstants.ACCOUNT, 0);
- assertQueryAccount(connection, 1, 2);
+ assertAccountRowCount(connection, 0);
+ assertAccountBalances(connection, 1, 2);
}
}
private void assertCommit() throws SQLException {
try (Connection connection = getDataSource().getConnection()) {
connection.setAutoCommit(false);
- assertTableRowCount(connection, TransactionTestConstants.ACCOUNT, 0);
- executeWithLog(connection, "insert into account(id, balance, transaction_id) values(1, 1, 1);");
- executeWithLog(connection, "insert into account(id, balance, transaction_id) values(2, 2, 2);");
- executeUpdateWithLog(connection, "update account set balance = 3, transaction_id = 3 where id = 2;");
- assertQueryAccount(connection, 1, 3);
+ assertAccountRowCount(connection, 0);
+ executeWithLog(connection, "INSERT INTO account(id, balance, transaction_id) VALUES(1, 1, 1)");
+ executeWithLog(connection, "INSERT INTO account(id, balance, transaction_id) VALUES(2, 2, 2)");
+ executeUpdateWithLog(connection, "UPDATE account SET balance = 3, transaction_id = 3 WHERE id = 2");
+ assertAccountBalances(connection, 1, 3);
connection.commit();
}
try (Connection connection = getDataSource().getConnection()) {
- assertTableRowCount(connection, TransactionTestConstants.ACCOUNT, 2);
- assertQueryAccount(connection, 1, 3);
- }
- }
-
- private void assertQueryAccount(final Connection connection, final int... expectedBalances) throws SQLException {
- Preconditions.checkArgument(2 == expectedBalances.length);
- Statement queryStatement = connection.createStatement();
- ResultSet resultSet = queryStatement.executeQuery("select * from account;");
- while (resultSet.next()) {
- int id = resultSet.getInt("id");
- int actualBalance = resultSet.getInt("balance");
- if (1 == id) {
- assertBalance(actualBalance, expectedBalances[0]);
- }
- if (2 == id) {
- assertBalance(actualBalance, expectedBalances[1]);
- }
+ assertAccountRowCount(connection, 2);
+ assertAccountBalances(connection, 1, 3);
}
}
-
- private void assertBalance(final int actual, final int expected) {
- assertThat(String.format("Balance is %s, should be %s.", actual, expected), actual, is(expected));
- }
}
diff --git a/test/e2e/operation/transaction/src/test/java/org/apache/shardingsphere/test/e2e/transaction/cases/deadlock/TransactionDeadlockTestCase.java b/test/e2e/operation/transaction/src/test/java/org/apache/shardingsphere/test/e2e/transaction/cases/deadlock/TransactionDeadlockTestCase.java
new file mode 100644
index 00000000000..00995b4d71b
--- /dev/null
+++ b/test/e2e/operation/transaction/src/test/java/org/apache/shardingsphere/test/e2e/transaction/cases/deadlock/TransactionDeadlockTestCase.java
@@ -0,0 +1,139 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.test.e2e.transaction.cases.deadlock;
+
+import lombok.extern.slf4j.Slf4j;
+import org.apache.shardingsphere.test.e2e.transaction.cases.base.BaseTransactionTestCase;
+import org.apache.shardingsphere.test.e2e.transaction.engine.base.TransactionBaseE2EIT;
+import org.apache.shardingsphere.test.e2e.transaction.engine.base.TransactionContainerComposer;
+import org.apache.shardingsphere.test.e2e.transaction.engine.base.TransactionTestCase;
+import org.apache.shardingsphere.test.e2e.transaction.engine.constants.TransactionTestConstants;
+
+import javax.sql.DataSource;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.Collection;
+import java.util.LinkedList;
+import java.util.concurrent.BrokenBarrierException;
+import java.util.concurrent.CyclicBarrier;
+import java.util.concurrent.ExecutorService;
+import java.util.concurrent.Executors;
+import java.util.concurrent.Future;
+
+import static org.hamcrest.CoreMatchers.is;
+import static org.hamcrest.MatcherAssert.assertThat;
+
+/**
+ * Transaction deadlock test case.
+ */
+@Slf4j
+@TransactionTestCase(dbTypes = TransactionTestConstants.MYSQL)
+public final class TransactionDeadlockTestCase extends BaseTransactionTestCase {
+
+ private final CyclicBarrier barrier = new CyclicBarrier(2);
+
+ private final ExecutorService executor = Executors.newFixedThreadPool(2);
+
+ public TransactionDeadlockTestCase(final TransactionBaseE2EIT baseTransactionITCase, final DataSource dataSource) {
+ super(baseTransactionITCase, dataSource);
+ }
+
+ @Override
+ protected void beforeTest() throws SQLException {
+ super.beforeTest();
+ prepare();
+ }
+
+ private void prepare() throws SQLException {
+ try (Connection connection = getDataSource().getConnection()) {
+ executeWithLog(connection, "DELETE FROM account");
+ executeWithLog(connection, "INSERT INTO account(id, balance, transaction_id) VALUES(1, 1, 1),(2, 2, 2),(3, 3, 3),(4, 4, 4)");
+ }
+ try (Connection connection = getDataSource().getConnection()) {
+ assertAccountRowCount(connection, 4);
+ }
+ }
+
+ @Override
+ protected void executeTest(final TransactionContainerComposer containerComposer) throws SQLException {
+ final long startTime = System.currentTimeMillis();
+ Collection<Future<Void>> futures = new LinkedList<>();
+ futures.add(executor.submit(this::executeTransfer1));
+ futures.add(executor.submit(this::executeTransfer2));
+ try (Connection connection = getDataSource().getConnection()) {
+ assertAccountRowCount(connection, 4);
+ }
+ for (Future<Void> each : futures) {
+ try {
+ each.get();
+ // CHECKSTYLE:OFF
+ } catch (final Exception ex) {
+ // CHECKSTYLE:ON
+ assertThat(ex.getMessage(), is("com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction"));
+ }
+ }
+ log.info("The deadlock test case execution time is: {}", System.currentTimeMillis() - startTime);
+ executor.shutdown();
+ try (Connection connection = getDataSource().getConnection()) {
+ assertAccountBalances(connection, 1, 2, 3, 4);
+ }
+ }
+
+ private Void executeTransfer1() throws SQLException {
+ Connection connection = getDataSource().getConnection();
+ try {
+ connection.setAutoCommit(false);
+ executeWithLog(connection, "UPDATE account SET balance = balance - 1 WHERE id = 1");
+ await();
+ executeWithLog(connection, "UPDATE account SET balance = balance + 1 WHERE id = 2");
+ await();
+ connection.commit();
+ } catch (final SQLException ex) {
+ connection.rollback();
+ throw ex;
+ } finally {
+ connection.close();
+ }
+ return null;
+ }
+
+ private Void executeTransfer2() throws SQLException {
+ Connection connection = getDataSource().getConnection();
+ try {
+ connection.setAutoCommit(false);
+ executeWithLog(connection, "UPDATE account SET balance = balance - 1 WHERE id = 2");
+ await();
+ executeWithLog(connection, "UPDATE account SET balance = balance + 1 WHERE id = 1");
+ await();
+ connection.commit();
+ } catch (final SQLException ex) {
+ connection.rollback();
+ throw ex;
+ } finally {
+ connection.close();
+ }
+ return null;
+ }
+
+ private void await() {
+ try {
+ barrier.await();
+ } catch (final InterruptedException | BrokenBarrierException ignored) {
+ }
+ }
+}
diff --git a/test/e2e/operation/transaction/src/test/resources/env/it-env.properties b/test/e2e/operation/transaction/src/test/resources/env/it-env.properties
index 0990d1cb5b2..f6cca5f2be0 100644
--- a/test/e2e/operation/transaction/src/test/resources/env/it-env.properties
+++ b/test/e2e/operation/transaction/src/test/resources/env/it-env.properties
@@ -17,7 +17,7 @@
# transaction.it.type=NONE,DOCKER,NATIVE
transaction.it.env.type=NONE
# transaction.it.env.cases=ClassicTransferTestCase, PostgreSQLSavePointTestCase, TransactionTypeHolderTestCase
-transaction.it.env.cases=MultiJDBCConnectionsTestCase, MultiTransactionInConnectionTestCase, MultiOperationsCommitAndRollbackTestCase, MySQLAutoCommitTestCase, PostgreSQLAutoCommitTestCase, BroadcastTableTransactionTestCase, ExceptionInTransactionTestCase, MultiTableCommitAndRollbackTestCase, SingleTableCommitAndRollbackTestCase, MySQLSetReadOnlyTestCase, MySQLSavePointTestCase, MySQLLocalTruncateTestCase, MySQLXATruncateTestCase, OpenGaussCursorTestCase, NestedTransactionTestCase, SetTr [...]
+transaction.it.env.cases=TransactionDeadlockTestCase, MultiJDBCConnectionsTestCase, MultiTransactionInConnectionTestCase, MultiOperationsCommitAndRollbackTestCase, MySQLAutoCommitTestCase, PostgreSQLAutoCommitTestCase, BroadcastTableTransactionTestCase, ExceptionInTransactionTestCase, MultiTableCommitAndRollbackTestCase, SingleTableCommitAndRollbackTestCase, MySQLSetReadOnlyTestCase, MySQLSavePointTestCase, MySQLLocalTruncateTestCase, MySQLXATruncateTestCase, OpenGaussCursorTestCase, Nes [...]
# transaction.it.env.transtypes=LOCAL, XA, BASE
transaction.it.env.transtypes=LOCAL, XA
# transaction.it.env.xa.providers=Atomikos, Bitronix, Narayana
diff --git a/test/e2e/operation/transaction/src/test/resources/env/mysql/my.cnf b/test/e2e/operation/transaction/src/test/resources/env/mysql/my.cnf
index 1a0e57d805e..c29a71c214f 100644
--- a/test/e2e/operation/transaction/src/test/resources/env/mysql/my.cnf
+++ b/test/e2e/operation/transaction/src/test/resources/env/mysql/my.cnf
@@ -25,3 +25,4 @@ binlog-row-image=full
max_connections=600
# for mysql 8.0
secure_file_priv=/var/lib/mysql
+innodb_lock_wait_timeout=5
diff --git a/test/e2e/operation/transaction/src/test/resources/env/scenario/default/data/actual/init-sql/mysql/01-actual-init.sql b/test/e2e/operation/transaction/src/test/resources/env/scenario/default/data/actual/init-sql/mysql/01-actual-init.sql
index 14979a7062f..27efd5aa5a0 100644
--- a/test/e2e/operation/transaction/src/test/resources/env/scenario/default/data/actual/init-sql/mysql/01-actual-init.sql
+++ b/test/e2e/operation/transaction/src/test/resources/env/scenario/default/data/actual/init-sql/mysql/01-actual-init.sql
@@ -30,14 +30,14 @@ CREATE TABLE transaction_it_0.`t_order_0` (`order_id` INT PRIMARY KEY, `user_id`
CREATE TABLE transaction_it_0.`t_order_1` (`order_id` INT PRIMARY KEY, `user_id` INT NOT NULL, `status` VARCHAR(45) NULL);
CREATE TABLE transaction_it_0.`t_order_item_0` (`item_id` INT PRIMARY KEY, `order_id` int NOT NULL, `user_id` int NOT NULL, `status` varchar(50) DEFAULT NULL);
CREATE TABLE transaction_it_0.`t_order_item_1` (`item_id` INT PRIMARY KEY, `order_id` int NOT NULL, `user_id` int NOT NULL, `status` varchar(50) DEFAULT NULL);
-CREATE TABLE transaction_it_0.`account_0`(`id` BIGINT, `balance` FLOAT, `transaction_id` INT);
-CREATE TABLE transaction_it_0.`account_1`(`id` BIGINT, `balance` FLOAT, `transaction_id` INT);
+CREATE TABLE transaction_it_0.`account_0`(`id` INT PRIMARY KEY, `balance` FLOAT, `transaction_id` INT);
+CREATE TABLE transaction_it_0.`account_1`(`id` INT PRIMARY KEY, `balance` FLOAT, `transaction_id` INT);
CREATE TABLE transaction_it_0.`t_address` (`id` INT PRIMARY KEY, `code` VARCHAR(36) DEFAULT NULL, `address` VARCHAR(36) DEFAULT NULL);
CREATE TABLE transaction_it_1.`t_order_0` (`order_id` INT PRIMARY KEY, `user_id` INT NOT NULL, `status` VARCHAR(45) NULL);
CREATE TABLE transaction_it_1.`t_order_1` (`order_id` INT PRIMARY KEY, `user_id` INT NOT NULL, `status` VARCHAR(45) NULL);
CREATE TABLE transaction_it_1.`t_order_item_0` (`item_id` INT PRIMARY KEY, `order_id` int NOT NULL, `user_id` int NOT NULL, `status` varchar(50) DEFAULT NULL);
CREATE TABLE transaction_it_1.`t_order_item_1` (`item_id` INT PRIMARY KEY, `order_id` int NOT NULL, `user_id` int NOT NULL, `status` varchar(50) DEFAULT NULL);
-CREATE TABLE transaction_it_1.`account_0`(`id` BIGINT, `balance` FLOAT, `transaction_id` INT);
-CREATE TABLE transaction_it_1.`account_1`(`id` BIGINT, `balance` FLOAT, `transaction_id` INT);
+CREATE TABLE transaction_it_1.`account_0`(`id` INT PRIMARY KEY, `balance` FLOAT, `transaction_id` INT);
+CREATE TABLE transaction_it_1.`account_1`(`id` INT PRIMARY KEY, `balance` FLOAT, `transaction_id` INT);
CREATE TABLE transaction_it_1.`t_address` (`id` INT PRIMARY KEY, `code` VARCHAR(36) DEFAULT NULL, `address` VARCHAR(36) DEFAULT NULL);
diff --git a/test/e2e/operation/transaction/src/test/resources/env/scenario/default/data/actual/init-sql/opengauss/01-actual-init.sql b/test/e2e/operation/transaction/src/test/resources/env/scenario/default/data/actual/init-sql/opengauss/01-actual-init.sql
index 8b92ab114b6..ec3929070ce 100644
--- a/test/e2e/operation/transaction/src/test/resources/env/scenario/default/data/actual/init-sql/opengauss/01-actual-init.sql
+++ b/test/e2e/operation/transaction/src/test/resources/env/scenario/default/data/actual/init-sql/opengauss/01-actual-init.sql
@@ -33,8 +33,8 @@ CREATE TABLE "t_order_0" ("order_id" INT PRIMARY KEY, "user_id" INT NOT NULL, "s
CREATE TABLE "t_order_1" ("order_id" INT PRIMARY KEY, "user_id" INT NOT NULL, "status" VARCHAR(45) NULL);
CREATE TABLE "t_order_item_0" ("item_id" INT PRIMARY KEY, "order_id" int NOT NULL, "user_id" int NOT NULL, "status" varchar(50) DEFAULT NULL);
CREATE TABLE "t_order_item_1" ("item_id" INT PRIMARY KEY, "order_id" int NOT NULL, "user_id" int NOT NULL, "status" varchar(50) DEFAULT NULL);
-CREATE TABLE "account_0"("id" INT, "balance" FLOAT, "transaction_id" INT);
-CREATE TABLE "account_1"("id" INT, "balance" FLOAT, "transaction_id" INT);
+CREATE TABLE "account_0"("id" INT PRIMARY KEY, "balance" FLOAT, "transaction_id" INT);
+CREATE TABLE "account_1"("id" INT PRIMARY KEY, "balance" FLOAT, "transaction_id" INT);
CREATE TABLE "t_address" ("id" INT PRIMARY KEY, "code" VARCHAR(36) DEFAULT NULL, "address" VARCHAR(36) DEFAULT NULL);
\c transaction_it_1;
@@ -43,6 +43,6 @@ CREATE TABLE "t_order_0" ("order_id" INT PRIMARY KEY, "user_id" INT NOT NULL, "s
CREATE TABLE "t_order_1" ("order_id" INT PRIMARY KEY, "user_id" INT NOT NULL, "status" VARCHAR(45) NULL);
CREATE TABLE "t_order_item_0" ("item_id" INT PRIMARY KEY, "order_id" int NOT NULL, "user_id" int NOT NULL, "status" varchar(50) DEFAULT NULL);
CREATE TABLE "t_order_item_1" ("item_id" INT PRIMARY KEY, "order_id" int NOT NULL, "user_id" int NOT NULL, "status" varchar(50) DEFAULT NULL);
-CREATE TABLE "account_0"("id" INT, "balance" FLOAT, "transaction_id" INT);
-CREATE TABLE "account_1"("id" INT, "balance" FLOAT, "transaction_id" INT);
+CREATE TABLE "account_0"("id" INT PRIMARY KEY, "balance" FLOAT, "transaction_id" INT);
+CREATE TABLE "account_1"("id" INT PRIMARY KEY, "balance" FLOAT, "transaction_id" INT);
CREATE TABLE "t_address" ("id" INT PRIMARY KEY, "code" VARCHAR(36) DEFAULT NULL, "address" VARCHAR(36) DEFAULT NULL);
diff --git a/test/e2e/operation/transaction/src/test/resources/env/scenario/default/data/actual/init-sql/postgresql/01-actual-init.sql b/test/e2e/operation/transaction/src/test/resources/env/scenario/default/data/actual/init-sql/postgresql/01-actual-init.sql
index 8b92ab114b6..ec3929070ce 100644
--- a/test/e2e/operation/transaction/src/test/resources/env/scenario/default/data/actual/init-sql/postgresql/01-actual-init.sql
+++ b/test/e2e/operation/transaction/src/test/resources/env/scenario/default/data/actual/init-sql/postgresql/01-actual-init.sql
@@ -33,8 +33,8 @@ CREATE TABLE "t_order_0" ("order_id" INT PRIMARY KEY, "user_id" INT NOT NULL, "s
CREATE TABLE "t_order_1" ("order_id" INT PRIMARY KEY, "user_id" INT NOT NULL, "status" VARCHAR(45) NULL);
CREATE TABLE "t_order_item_0" ("item_id" INT PRIMARY KEY, "order_id" int NOT NULL, "user_id" int NOT NULL, "status" varchar(50) DEFAULT NULL);
CREATE TABLE "t_order_item_1" ("item_id" INT PRIMARY KEY, "order_id" int NOT NULL, "user_id" int NOT NULL, "status" varchar(50) DEFAULT NULL);
-CREATE TABLE "account_0"("id" INT, "balance" FLOAT, "transaction_id" INT);
-CREATE TABLE "account_1"("id" INT, "balance" FLOAT, "transaction_id" INT);
+CREATE TABLE "account_0"("id" INT PRIMARY KEY, "balance" FLOAT, "transaction_id" INT);
+CREATE TABLE "account_1"("id" INT PRIMARY KEY, "balance" FLOAT, "transaction_id" INT);
CREATE TABLE "t_address" ("id" INT PRIMARY KEY, "code" VARCHAR(36) DEFAULT NULL, "address" VARCHAR(36) DEFAULT NULL);
\c transaction_it_1;
@@ -43,6 +43,6 @@ CREATE TABLE "t_order_0" ("order_id" INT PRIMARY KEY, "user_id" INT NOT NULL, "s
CREATE TABLE "t_order_1" ("order_id" INT PRIMARY KEY, "user_id" INT NOT NULL, "status" VARCHAR(45) NULL);
CREATE TABLE "t_order_item_0" ("item_id" INT PRIMARY KEY, "order_id" int NOT NULL, "user_id" int NOT NULL, "status" varchar(50) DEFAULT NULL);
CREATE TABLE "t_order_item_1" ("item_id" INT PRIMARY KEY, "order_id" int NOT NULL, "user_id" int NOT NULL, "status" varchar(50) DEFAULT NULL);
-CREATE TABLE "account_0"("id" INT, "balance" FLOAT, "transaction_id" INT);
-CREATE TABLE "account_1"("id" INT, "balance" FLOAT, "transaction_id" INT);
+CREATE TABLE "account_0"("id" INT PRIMARY KEY, "balance" FLOAT, "transaction_id" INT);
+CREATE TABLE "account_1"("id" INT PRIMARY KEY, "balance" FLOAT, "transaction_id" INT);
CREATE TABLE "t_address" ("id" INT PRIMARY KEY, "code" VARCHAR(36) DEFAULT NULL, "address" VARCHAR(36) DEFAULT NULL);
diff --git a/test/e2e/operation/transaction/src/test/resources/env/scenario/readwrite-splitting/data/actual/init-sql/mysql/01-actual-init.sql b/test/e2e/operation/transaction/src/test/resources/env/scenario/readwrite-splitting/data/actual/init-sql/mysql/01-actual-init.sql
index 2bba89ddf06..e70927b83fc 100644
--- a/test/e2e/operation/transaction/src/test/resources/env/scenario/readwrite-splitting/data/actual/init-sql/mysql/01-actual-init.sql
+++ b/test/e2e/operation/transaction/src/test/resources/env/scenario/readwrite-splitting/data/actual/init-sql/mysql/01-actual-init.sql
@@ -28,15 +28,15 @@ CREATE DATABASE read_ds_1;
CREATE TABLE write_ds.`t_order` (`order_id` INT PRIMARY KEY, `user_id` INT NOT NULL, `status` VARCHAR(45) NULL);
CREATE TABLE write_ds.`t_order_item` (`item_id` INT PRIMARY KEY, `order_id` int NOT NULL, `user_id` int NOT NULL, `status` varchar(50) DEFAULT NULL);
-CREATE TABLE write_ds.`account`(`id` BIGINT, `balance` FLOAT, `transaction_id` INT);
+CREATE TABLE write_ds.`account`(`id` INT PRIMARY KEY, `balance` FLOAT, `transaction_id` INT);
CREATE TABLE write_ds.`t_address` (`id` INT PRIMARY KEY, `code` VARCHAR(36) DEFAULT NULL, `address` VARCHAR(36) DEFAULT NULL);
CREATE TABLE read_ds_0.`t_order` (`order_id` INT PRIMARY KEY, `user_id` INT NOT NULL, `status` VARCHAR(45) NULL);
CREATE TABLE read_ds_0.`t_order_item` (`item_id` INT PRIMARY KEY, `order_id` int NOT NULL, `user_id` int NOT NULL, `status` varchar(50) DEFAULT NULL);
-CREATE TABLE read_ds_0.`account`(`id` BIGINT, `balance` FLOAT, `transaction_id` INT);
+CREATE TABLE read_ds_0.`account`(`id` INT PRIMARY KEY, `balance` FLOAT, `transaction_id` INT);
CREATE TABLE read_ds_0.`t_address` (`id` INT PRIMARY KEY, `code` VARCHAR(36) DEFAULT NULL, `address` VARCHAR(36) DEFAULT NULL);
CREATE TABLE read_ds_1.`t_order` (`order_id` INT PRIMARY KEY, `user_id` INT NOT NULL, `status` VARCHAR(45) NULL);
CREATE TABLE read_ds_1.`t_order_item` (`item_id` INT PRIMARY KEY, `order_id` int NOT NULL, `user_id` int NOT NULL, `status` varchar(50) DEFAULT NULL);
-CREATE TABLE read_ds_1.`account`(`id` BIGINT, `balance` FLOAT, `transaction_id` INT);
+CREATE TABLE read_ds_1.`account`(`id` INT PRIMARY KEY, `balance` FLOAT, `transaction_id` INT);
CREATE TABLE read_ds_1.`t_address` (`id` INT PRIMARY KEY, `code` VARCHAR(36) DEFAULT NULL, `address` VARCHAR(36) DEFAULT NULL);
diff --git a/test/e2e/operation/transaction/src/test/resources/env/scenario/readwrite-splitting/data/actual/init-sql/opengauss/01-actual-init.sql b/test/e2e/operation/transaction/src/test/resources/env/scenario/readwrite-splitting/data/actual/init-sql/opengauss/01-actual-init.sql
index 7934967650a..4d3b9cf4487 100644
--- a/test/e2e/operation/transaction/src/test/resources/env/scenario/readwrite-splitting/data/actual/init-sql/opengauss/01-actual-init.sql
+++ b/test/e2e/operation/transaction/src/test/resources/env/scenario/readwrite-splitting/data/actual/init-sql/opengauss/01-actual-init.sql
@@ -31,19 +31,19 @@ GRANT ALL PRIVILEGES ON DATABASE read_ds_1 TO test_user;
CREATE TABLE "t_order" ("order_id" INT PRIMARY KEY, "user_id" INT NOT NULL, "status" VARCHAR(45) NULL);
CREATE TABLE "t_order_item" ("item_id" INT PRIMARY KEY, "order_id" int NOT NULL, "user_id" int NOT NULL, "status" varchar(50) DEFAULT NULL);
-CREATE TABLE "account"("id" INT, "balance" FLOAT, "transaction_id" INT);
+CREATE TABLE "account"("id" INT PRIMARY KEY, "balance" FLOAT, "transaction_id" INT);
CREATE TABLE "t_address" ("id" INT PRIMARY KEY, "code" VARCHAR(36) DEFAULT NULL, "address" VARCHAR(36) DEFAULT NULL);
\c read_ds_0;
CREATE TABLE "t_order" ("order_id" INT PRIMARY KEY, "user_id" INT NOT NULL, "status" VARCHAR(45) NULL);
CREATE TABLE "t_order_item" ("item_id" INT PRIMARY KEY, "order_id" int NOT NULL, "user_id" int NOT NULL, "status" varchar(50) DEFAULT NULL);
-CREATE TABLE "account"("id" INT, "balance" FLOAT, "transaction_id" INT);
+CREATE TABLE "account"("id" INT PRIMARY KEY, "balance" FLOAT, "transaction_id" INT);
CREATE TABLE "t_address" ("id" INT PRIMARY KEY, "code" VARCHAR(36) DEFAULT NULL, "address" VARCHAR(36) DEFAULT NULL);
\c read_ds_1;
CREATE TABLE "t_order" ("order_id" INT PRIMARY KEY, "user_id" INT NOT NULL, "status" VARCHAR(45) NULL);
CREATE TABLE "t_order_item" ("item_id" INT PRIMARY KEY, "order_id" int NOT NULL, "user_id" int NOT NULL, "status" varchar(50) DEFAULT NULL);
-CREATE TABLE "account"("id" INT, "balance" FLOAT, "transaction_id" INT);
+CREATE TABLE "account"("id" INT PRIMARY KEY, "balance" FLOAT, "transaction_id" INT);
CREATE TABLE "t_address" ("id" INT PRIMARY KEY, "code" VARCHAR(36) DEFAULT NULL, "address" VARCHAR(36) DEFAULT NULL);
diff --git a/test/e2e/operation/transaction/src/test/resources/env/scenario/readwrite-splitting/data/actual/init-sql/postgresql/01-actual-init.sql b/test/e2e/operation/transaction/src/test/resources/env/scenario/readwrite-splitting/data/actual/init-sql/postgresql/01-actual-init.sql
index 7934967650a..4d3b9cf4487 100644
--- a/test/e2e/operation/transaction/src/test/resources/env/scenario/readwrite-splitting/data/actual/init-sql/postgresql/01-actual-init.sql
+++ b/test/e2e/operation/transaction/src/test/resources/env/scenario/readwrite-splitting/data/actual/init-sql/postgresql/01-actual-init.sql
@@ -31,19 +31,19 @@ GRANT ALL PRIVILEGES ON DATABASE read_ds_1 TO test_user;
CREATE TABLE "t_order" ("order_id" INT PRIMARY KEY, "user_id" INT NOT NULL, "status" VARCHAR(45) NULL);
CREATE TABLE "t_order_item" ("item_id" INT PRIMARY KEY, "order_id" int NOT NULL, "user_id" int NOT NULL, "status" varchar(50) DEFAULT NULL);
-CREATE TABLE "account"("id" INT, "balance" FLOAT, "transaction_id" INT);
+CREATE TABLE "account"("id" INT PRIMARY KEY, "balance" FLOAT, "transaction_id" INT);
CREATE TABLE "t_address" ("id" INT PRIMARY KEY, "code" VARCHAR(36) DEFAULT NULL, "address" VARCHAR(36) DEFAULT NULL);
\c read_ds_0;
CREATE TABLE "t_order" ("order_id" INT PRIMARY KEY, "user_id" INT NOT NULL, "status" VARCHAR(45) NULL);
CREATE TABLE "t_order_item" ("item_id" INT PRIMARY KEY, "order_id" int NOT NULL, "user_id" int NOT NULL, "status" varchar(50) DEFAULT NULL);
-CREATE TABLE "account"("id" INT, "balance" FLOAT, "transaction_id" INT);
+CREATE TABLE "account"("id" INT PRIMARY KEY, "balance" FLOAT, "transaction_id" INT);
CREATE TABLE "t_address" ("id" INT PRIMARY KEY, "code" VARCHAR(36) DEFAULT NULL, "address" VARCHAR(36) DEFAULT NULL);
\c read_ds_1;
CREATE TABLE "t_order" ("order_id" INT PRIMARY KEY, "user_id" INT NOT NULL, "status" VARCHAR(45) NULL);
CREATE TABLE "t_order_item" ("item_id" INT PRIMARY KEY, "order_id" int NOT NULL, "user_id" int NOT NULL, "status" varchar(50) DEFAULT NULL);
-CREATE TABLE "account"("id" INT, "balance" FLOAT, "transaction_id" INT);
+CREATE TABLE "account"("id" INT PRIMARY KEY, "balance" FLOAT, "transaction_id" INT);
CREATE TABLE "t_address" ("id" INT PRIMARY KEY, "code" VARCHAR(36) DEFAULT NULL, "address" VARCHAR(36) DEFAULT NULL);