peilinqian opened a new issue #14663: URL: https://github.com/apache/shardingsphere/issues/14663
## Bug Report **For English only**, other languages will not accept. Before report a bug, make sure you have: - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview). Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will **close it**. Please answer these questions before submitting your issue. Thanks! ### Which version of ShardingSphere did you use? https://github.com/apache/shardingsphere/tree/8cdac04b7d34196df8cd6abdda0ff395e6b8a3ec ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-Proxy ### Expected behavior During the execution of the following code，excute “select sum(balance) as a from account where transaction_id in (1,2)”， The expected result is 100 “ connection.setAutoCommit(false); statement1 = connection.createStatement(); System.out.println("开始转账"); statement1.execute("update account set balance=balance-1 where transaction_id=2;"); statement2 = connection.createStatement(); Thread.sleep(1000); statement2.execute("update account set balance=balance+1 where transaction_id=1;"); connection.commit(); ” ### Actual behavior During the execution of the following code，excute “select sum(balance) as a from account where transaction_id in (1,2)”， The actual result is not 100 ### Reason analyze (If you can) ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. server.yaml rules: - !AUTHORITY users: - root@%:root - sharding@:sharding provider: type: ALL_PRIVILEGES_PERMITTED - !TRANSACTION defaultType: XA providerType: Narayana props: max-connections-size-per-query: 2 sql-show: true proxy-backend-query-fetch-size: 1000 proxy-frontend-executor-size: 128 ### Example codes for reproduce this issue (such as a github link). import java.sql.*; public class ShardingTransaction { public static void main(String[] args) throws Exception { CreateTable(); for (int i=0; i<20;i++) { Thread t = new UpdateTread(); t.start(); Thread.sleep(100); System.out.println(String.format("balance sum: %d", getBalanceSum())); // t.join(); } Thread.sleep(3000); System.out.println(String.format("balance sum: %d", getBalanceSum())); } public static void CreateTable() { Statement stmt = null; try { Connection conn = DriverManager.getConnection("jdbc:opengauss://100.99.97.43:13000/transaction_db", "root", "root"); // Connection conn = DriverManager.getConnection("jdbc:opengauss://10.29.180.204:15000/tpcc", "tpcc", "Test@123"); stmt = conn.createStatement(); stmt.executeUpdate("drop table if exists account;create table account(id text, balance float ,transaction_id int);"); stmt.executeUpdate("insert into account(transaction_id,balance) values (1,0),(2,100);"); stmt.close(); System.out.println("creatable succeed!"); } catch (SQLException e) { if (stmt != null) { try { stmt.close(); } catch (SQLException e1) { e1.printStackTrace(); } } e.printStackTrace(); } } static int getBalanceSum() throws Exception{ int result = 0; Connection connection = DriverManager.getConnection("jdbc:opengauss://100.99.97.43:13000/transaction_db", "root", "root"); // Connection connection = DriverManager.getConnection("jdbc:opengauss://10.29.180.204:15000/tpcc", "tpcc", "Test@123"); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("select sum(balance) as a from account where transaction_id in (1,2)"); if (resultSet.next()) { result = resultSet.getInt(1); } statement.close(); connection.close(); return result; } } class UpdateTread extends Thread { public void run() { Connection connection = null; Statement statement1 = null; Statement statement2 = null; try { connection = DriverManager.getConnection("jdbc:opengauss://100.99.97.43:13000/transaction_db", "root", "root"); // connection = DriverManager.getConnection("jdbc:opengauss://10.29.180.204:15000/tpcc", "tpcc", "Test@123"); connection.setAutoCommit(false); statement1 = connection.createStatement(); System.out.println("开始转账"); statement1.execute("update account set balance=balance-1 where transaction_id=2;"); statement2 = connection.createStatement(); Thread.sleep(1000); statement2.execute("update account set balance=balance+1 where transaction_id=1;"); connection.commit(); System.out.println("转账完成"); } catch (SQLException | InterruptedException e) { e.printStackTrace(); } finally { try { statement1.close(); statement2.close(); connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
