You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/08/18 09:02:41 UTC

[GitHub] [shardingsphere] fantacy2001 opened a new issue #6913: the 'autoCommit' of sharding jdbc

fantacy2001 opened a new issue #6913:
URL: https://github.com/apache/shardingsphere/issues/6913


   i use sharding jdbc+mybatis+spring
   
   sharding two db, one master two slave 。
   
   spring config:
    <!-- 使用JDBC事物 -->
       <bean id="transactionManager"
             class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
           <property name="dataSource" ref="shardingDataSource"/>
       </bean>
       <!-- 使用annotation注解方式配置事务 -->
       <tx:annotation-driven transaction-manager="transactionManager"/>
   
       <!-- mybatis 配置 sqlSessionFactory -->
       <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
           <property name="dataSource" ref="shardingDataSource"/>
           <property name="configLocation" value="classpath:mybatis/config/mybatis-config.xml"></property>
           <property name="mapperLocations"
                     value="classpath:mybatis/mapper/mysql/**/*Mapper.xml">
           </property>
       </bean>
   
       <!-- mybatis 配置 SQLSession模板 -->
       <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate"
             scope="prototype">
           <constructor-arg index="0" ref="sqlSessionFactory"/>
       </bean>
   
       <!-- mybatis 配置  自动生成Dao接口实现 -->
       <bean id="mybatisMapper" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
           <property name="basePackage"
                     value="**.com.xx.xxxx.xxxx.xxxxxx.**.dao.**"/>
           <!--  <property name="basePackage"
            value="com.xx.xxxx.xxxx.xxxxxx.dao"/> -->
           <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
       </bean>
   
   
       <!--spring 的JDBC模板 -->
       <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
           <property name="dataSource" ref="shardingDataSource">
           </property>
       </bean>
   
   connect pool autoCommit is configed 'false'
   <mvn.dbcp.defaultAutoCommit>false</mvn.dbcp.defaultAutoCommit>
   
   java code in manager layer:
   i  wrote ‘@Transactional’ out
    //   @Transactional
       public Integer batchUpdateByDetailList(List<MaintainDetailDto> maintainDetailDtoList) {
           int i = 0;
           for(MaintainDetailDto maintainDetailDto : maintainDetailDtoList){
               i += maintainDetailDao.updateByMd5Value(maintainDetailDto);
           }
           return i;
       }
   
   sql mapper:
       < update id="updateByMd5Value" parameterType="com.xx.xxxx.xxxx.xxxxxx.dto.MaintainDetailDto" >
           update maintain_detail
           < trim prefix="set" suffixOverrides="," >
               qty = #{qty,jdbcType=DECIMAL},
           </ trim >
           WHERE
           is_delete = 0
           and md5_value = #{md5Value,jdbcType=VARCHAR}
           and tenant_id = #{tenantId,jdbcType=VARCHAR}
       </ update >
   
   test code:
   @Test
       public void batchUpdateByDetailListTest() {
           List<MaintainDetailDto> maintainDetailDtoList = new ArrayList<>();
           MaintainDetailDto maintainDetailDto1 = new MaintainDetailDto();
           maintainDetailDto1.setMd5Value("e8e08c960f64eb89a355a8d65378b18f");
           maintainDetailDto1.setQty(new BigDecimal(1));
           maintainDetailDto1.setTenantId("jd");
           MaintainDetailDto maintainDetailDto2 = new MaintainDetailDto();
           maintainDetailDto2.setMd5Value("15a7055c82e7ee8f891c1006524ca44a");
           maintainDetailDto2.setQty(new BigDecimal(1));
           maintainDetailDto2.setTenantId("jd");
           maintainDetailDtoList.add(maintainDetailDto1);
           maintainDetailDtoList.add(maintainDetailDto2);
           maintainDetailManager.batchUpdateByDetailList(maintainDetailDtoList);
       }
   
    result:
    response success:Updates: 1
   But table‘s record do not update
   
   i find sharding‘s class: AbstractConnectionAdapter,  autoCommit  is set to 'true'
   when sql do not managed by spring transactions,spring may create new connection of shardingjdbc,autoCommit  is set to 'true', when mybatis  commit, find autoCommit  is true, do not commit sql.
   
   so ,What should I do with this non spring  transaction managed of update or insert?


----------------------------------------------------------------
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



[GitHub] [shardingsphere] tristaZero commented on issue #6913: the 'autoCommit' of sharding jdbc

Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #6913:
URL: https://github.com/apache/shardingsphere/issues/6913#issuecomment-675882467


   @cherrylzhao This issue has a clear description. Could you give it a look?


----------------------------------------------------------------
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



[GitHub] [shardingsphere] kimmking commented on issue #6913: the 'autoCommit' of sharding jdbc

Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #6913:
URL: https://github.com/apache/shardingsphere/issues/6913#issuecomment-678883517


   ShardingSphereConnection should be managing its own logic tx-auto-commit status, avoid to use underlying physical connnections. Consider about two underlying connnections, one with auto-commit, one with not.


----------------------------------------------------------------
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



[GitHub] [shardingsphere] cherrylzhao commented on issue #6913: the 'autoCommit' of sharding jdbc

Posted by GitBox <gi...@apache.org>.
cherrylzhao commented on issue #6913:
URL: https://github.com/apache/shardingsphere/issues/6913#issuecomment-678626051


   ShardingSphere don't care about the status of underlying datasource pool, `<mvn.dbcp.defaultAutoCommit>false</mvn.dbcp.defaultAutoCommit>` will not effective.
   I think you can get the connection from jdbcTemplate, then do the transaction control manually like that
   ```
   connection.setAutoCommit(false)
   ....
   connection.commit()
   ```


----------------------------------------------------------------
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



[GitHub] [shardingsphere] fantacy2001 commented on issue #6913: In condition of shardingjdbc, the 'autocommit' in the datasource configuration does not take effect

Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #6913:
URL: https://github.com/apache/shardingsphere/issues/6913#issuecomment-679803847


   > @fantacy2001, Hi, can you summary the title for search friendly?
   
   OK,has Modified


----------------------------------------------------------------
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



[GitHub] [shardingsphere] terrymanu commented on issue #6913: the 'autoCommit' of sharding jdbc

Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #6913:
URL: https://github.com/apache/shardingsphere/issues/6913#issuecomment-678897526


   @fantacy2001, Hi, can you summary the title for search friendly?


----------------------------------------------------------------
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



[GitHub] [shardingsphere] kimmking closed issue #6913: In condition of shardingjdbc, the 'autocommit' in the datasource configuration does not take effect

Posted by GitBox <gi...@apache.org>.
kimmking closed issue #6913:
URL: https://github.com/apache/shardingsphere/issues/6913


   


----------------------------------------------------------------
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



[GitHub] [shardingsphere] kimmking closed issue #6913: In condition of shardingjdbc, the 'autocommit' in the datasource configuration does not take effect

Posted by GitBox <gi...@apache.org>.
kimmking closed issue #6913:
URL: https://github.com/apache/shardingsphere/issues/6913


   


----------------------------------------------------------------
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



[GitHub] [shardingsphere] fantacy2001 commented on issue #6913: In condition of shardingjdbc, the 'autocommit' in the datasource configuration does not take effect

Posted by GitBox <gi...@apache.org>.
fantacy2001 commented on issue #6913:
URL: https://github.com/apache/shardingsphere/issues/6913#issuecomment-679806038


   It is recommended not to configure ‘autocommit‘’ in datasource properties


----------------------------------------------------------------
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