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