[GitHub] [incubator-shardingsphere] Sy9876 opened a new issue #3015: Why SQLExecutePrepareTemplate use different connections to execute actual sqls even in serial mode?

[GitBox <gi...@apache.org>]

Sy9876 opened a new issue #3015: Why SQLExecutePrepareTemplate use different connections to execute actual sqls even in serial mode?
URL: https://github.com/apache/incubator-shardingsphere/issues/3015
 
 
   ## Question
   I am using sharding-jdbc-core-4.0.0-RC1.
   
   Table T sharding with 4 actual tables **T_0, T_1, T_2, T_3**.
   
   I configured maxConnectionsSizePerQuery to 4.
   
   ```
   spring.shardingsphere.props.max.connections.size.per.query=4
   ```
   
   When I execute sql "select * from T" in a transactional method, I found sharding-jdbc **used 4 connections** to execute these sql in **serially**.
   
   ```
   @Transactional(readOnly = true)
   public void test() {
     jdbcTemplate.queryForList("select * from T");
   }
   ```
   
   I understand that we need use different connections to execute sql in paralle.
   but why it use different connections even in serially?
   
   ----
   Actually, in my application, following exception happened.
   "Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction"
   
   My application is delete and insert records to a sharding table. sample code is:
   ```
   @Transactional
   public void x() {
     // 1  delete * from T;
     // 2  insert records
     for(Record record : records) {
       // insert into T values(...)
     }
   }
   ```
   
   I debugged and found there are 4 connections(transactions) after delete in mysql
   conn1(trx1) -- delete T_0
   conn2(trx2) -- delete T_1
   conn3(trx3) -- delete T_2
   conn4(trx4) -- delete T_3
   
   and when do insert one by one, it only use conn1.
   Then, when use conn1 to do "insert into T_1 ...", it waits trx2 to release the locks, until Lock wait timeout happened.
   
   
   

----------------------------------------------------------------
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.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services