[GitHub] [shardingsphere] bojiw opened a new issue, #20302: Ordinary sql opens the transaction

[GitBox]

bojiw opened a new issue, #20302:
URL: https://github.com/apache/shardingsphere/issues/20302

   Scenes
   within a method query within a transaction update outside of a transaction
   The updated sql is not committed
   
   `        transactionTemplate.execute(t->{
               addressesMapper.getById(1L);
               addressesMapper.getById(1L);
               return null;
           });
   
           ArrayList<Long> ids = new ArrayList<>();
           ids.add(15L);
           recentItemMapper.updateItemNameByIds(ids,"工作簿1.xlsx");
   `
   
   recentItemMapper.updateItemNameByIds 
   After the code is executed, it returns normally without blocking waiting
   but
   Re-request update in another place and find that it has been blocked and 
waited
   
   
   <img width="880" alt="image" 
src="https://user-images.githubusercontent.com/22230991/185582378-8fb44fe3-2e7b-4ad4-a324-0fa7b8a04923.png";>
   
   
   
   
   sharding-proxy log
   
   `[INFO ] 2022-08-19 16:31:49.421 [ShardingSphere-Command-2] 
ShardingSphere-SQL - Actual SQL: ds_0 ::: SELECT @@session.transaction_read_only
   [INFO ] 2022-08-19 16:31:49.661 [ShardingSphere-Command-1] 
ShardingSphere-SQL - Logic SQL: select
            
           id, 
           address, phone,enterprise_id,receiver_name,is_default
        
        
           from addresses
           where id=1
   [INFO ] 2022-08-19 16:31:49.662 [ShardingSphere-Command-1] 
ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(table=Optional.empty, 
limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
   [INFO ] 2022-08-19 16:31:49.662 [ShardingSphere-Command-1] 
ShardingSphere-SQL - Actual SQL: ds_0 ::: select
            
           id, 
           address, phone,enterprise_id,receiver_name,is_default
        
        
           from addresses
           where id=1
   [INFO ] 2022-08-19 16:31:53.061 [ShardingSphere-Command-1] 
ShardingSphere-SQL - Logic SQL: SELECT @@session.transaction_read_only
   [INFO ] 2022-08-19 16:31:53.063 [ShardingSphere-Command-1] 
ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(table=Optional.empty, 
limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
   [INFO ] 2022-08-19 16:31:53.063 [ShardingSphere-Command-1] 
ShardingSphere-SQL - Actual SQL: ds_0 ::: SELECT @@session.transaction_read_only
   [INFO ] 2022-08-19 16:31:53.248 [ShardingSphere-Command-1] 
ShardingSphere-SQL - Logic SQL: UPDATE  
           recent_items
        
           SET item_name = '工作簿1.xlsx'
           WHERE
           id IN
            (  
               15
            )
   [INFO ] 2022-08-19 16:31:53.249 [ShardingSphere-Command-1] 
ShardingSphere-SQL - SQLStatement: MySQLUpdateStatement(orderBy=Optional.empty, 
limit=Optional.empty)
   [INFO ] 2022-08-19 16:31:53.249 [ShardingSphere-Command-1] 
ShardingSphere-SQL - Actual SQL: ds_0 ::: UPDATE  
           recent_items
        
           SET item_name = '工作簿1.xlsx'
           WHERE
           id IN
            (  
               15
            )
   `
   
   
   Through arthas, it is found that setAutoCommit will be executed 3 times, and 
setAutoCommit(false) and setAutoCommit(true) will be executed within the 
transaction. After the transaction is completed, the update will be executed, 
and setAutoCommit(false) will be executed again, resulting in the following 
ordinary sql not submitted.
   
   <img width="736" alt="image" 
src="https://user-images.githubusercontent.com/22230991/185581621-f0bb22e6-0b33-47a9-a424-9c9ae7afbfa5.png";>
   
   
   
   environment
   
   spring-boot-2.6.7
   mybatis-spring-boot-starter-2.2.2
   HikariCP-3.4.2
   sharding-proxy-5.1.1
   mysql-8.0.29
   mysql-connector-java-8.0.11
   
   config
   
   server.yaml
   
   rules:
     - !AUTHORITY
       users:
         - app@:843KewMLpcmac2PtH5wkNnsBHijtft6r
       provider:
         type: ALL_PRIVILEGES_PERMITTED
   props:
     max-connections-size-per-query: 2
     kernel-executor-size: 2
     proxy-frontend-flush-threshold: 128
     proxy-opentracing-enabled: false
     proxy-hint-enabled: false
     sql-show: true
     check-table-metadata-enabled: false
     proxy-frontend-database-protocol-type: MySQL
   
   
   config-sharding.yaml
   
   schemaName: cloudoffice
   dataSources:
     ds_0:
       url: 
jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true
       username: root
       password: 123456
       connectionTimeoutMilliseconds: 500000
       idleTimeoutMilliseconds: 300000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 8000
       minPoolSize: 1
   


-- 
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: 
notifications-unsubscr...@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org