Vensence opened a new issue #7374:
URL: https://github.com/apache/shardingsphere/issues/7374
## Bug Report
### situation description:
ORM framework: **mybatis**
tansaction manager:
org.springframework.jdbc.datasource.**DataSourceTransactionManager**
DataSource: dbcp DataSource, and its **defaultAutoCommit** is **FALSE**
#### before using shardingsphere
the code without `@Tansactional` annotaion works fine, transaction can be
committed, data can be saved
#### after using shardingsphere
When using dbcp DataSource as the nested DataSource of ShardingDataSource,
data could't be saved, transaction wasn't committed
Please answer these questions before submitting your issue. Thanks!
### Which version of ShardingSphere did you use?
ShardingSphere 4.0.0
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
transaction can be committed
### Actual behavior
transcation wasn't committed
### Reason analyze (If you can)
The code without `@Transactional` annotation means the transaction isn't
managed by Spring, thus mybatis would take it over.
as the code snippet shows below (in
`org.mybatis.spring.transaction.SpringManagedTransaction` )
```java
public void commit() throws SQLException {
if (this.connection != null && !this.isConnectionTransactional &&
!this.autoCommit) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("Committing JDBC Connection [" + this.connection + "]");
}
this.connection.commit();
}
}
```
```java
private void openConnection() throws SQLException {
this.connection = DataSourceUtils.getConnection(this.dataSource);
this.autoCommit = this.connection.getAutoCommit();
this.isConnectionTransactional =
DataSourceUtils.isConnectionTransactional(this.connection, this.dataSource);
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(
"JDBC Connection ["
+ this.connection
+ "] will"
+ (this.isConnectionTransactional ? " " : " not ")
+ "be managed by Spring");
}
}
```
`isConnectionTransactional ` means transaction managed by spring or not
`autoCommit` comes from the connection which mybatis get from the dataSource
When a datasource connection was set as not autoCommit and it wasn't managed
by spring, mybatis would take over the transaction and commit it.
#### However
When using shardingSphere dataSource as the dataSource of mybatis, and the
actual dataSource: dbcp-dataSource as the nested dataSource of shardingSphere
dataSource, the situation got changed.
the connection which mybatis got is a `ShardingConnection`
in
`org.apache.shardingsphere.shardingjdbc.jdbc.core.datasource.ShardingDataSource`
```java
public final ShardingConnection getConnection() {
return new ShardingConnection(getDataSourceMap(), runtimeContext,
TransactionTypeHolder.get());
}
```
at this time the connection is like a "raw" connection, and its default
autoCommit is **TRUE**
```java
private boolean autoCommit = true;
```
but the real connection that in used is **FALSE** (comes from dbcp
dataSource setting )
so mybatis could't commit it (because `autoCommit` is TRUE ), and spring
won't commit it (whithout `@Transactional` annotation ), thus data won't be
saved.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
using any dataSource as the nested dataSource of ShardingDataSource, and set
its autoCommit behaviour to FALSE
### Example codes for reproduce this issue (such as a github link).
```XML
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xmlns:tx="http://www.springframework.org/schema/tx";
xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding";
xmlns:bean="http://www.springframework.org/schema/util";
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.1.xsd
http://shardingsphere.apache.org/schema/shardingsphere/sharding
http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util.xsd";
default-lazy-init="true">
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="${DS_DRIVER}" />
<property name="url" value="${DS_URL}" />
<property name="username" value="${DS_USER}" />
<property name="password" value="${DS_PASSWORD}" />
<property name="defaultAutoCommit" value="false" />
</bean>
<sharding:data-source id="shardingDataSource">
<sharding:sharding-rule data-source-names="dataSource">
<sharding:table-rules>
<sharding:table-rule logic-table="table"
actual-data-nodes="dataSource.table_$->{0..9}"
table-strategy-ref="tableStrategy" key-generator-ref="tableIDGenerator" />
</sharding:table-rules>
</sharding:sharding-rule>
</sharding:data-source>
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="shardingDataSource" />
</bean>
<tx:annotation-driven transaction-manager="transactionManager"
proxy-target-class="true" />
<bean id="sqlSessionFactory"
class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="mapperLocations"
value="classpath:/mapper/**/*Mapper.xml" />
<property name="dataSource" ref="shardingDataSource" />
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.example.dao.mapper" />
<property name="sqlSessionFactoryBeanName"
value="sqlSessionFactory" />
</bean>
</beans>
```
### Personal Suggestion
add some configuration options just as the javax.sql.DataSource
specification API support
----------------------------------------------------------------
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:
[email protected]
