In condition of shardingjdbc, the 'autocommit' in the datasource configuration does not take effect

[fantacy2001]

i use sharding jdbc+mybatis+spring

sharding two db, one master two slave 。

spring config:

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

false java code in manager layer: i wrote ‘@Transactional’ out // @Transactional public Integer batchUpdateByDetailList(List 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}, WHERE is_delete = 0 and md5_value = #{md5Value,jdbcType=VARCHAR} and tenant_id = #{tenantId,jdbcType=VARCHAR} test code： @Test public void batchUpdateByDetailListTest() { List 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. the SQL is routed to a single database for execution sharding sphere vesion : 4.1.1 DataSource ： dbcp2 so ，What should I do with this non spring transaction managed of update or insert?

[tristaZero]

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

[cherrylzhao]

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

[kimmking]

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.

[terrymanu]

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

[fantacy2001]

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

OK，has Modified

[fantacy2001]

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