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/09/10 05:37:09 UTC
[GitHub] [shardingsphere] Vensence opened a new issue #7374: Transaction behaviour changed after using ShardingSphere-JDBC ShardingDataSource
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:
users@infra.apache.org
[GitHub] [shardingsphere] cherrylzhao commented on issue #7374: Transaction behaviour changed after using ShardingSphere-JDBC ShardingDataSource
Posted by GitBox <gi...@apache.org>.
cherrylzhao commented on issue #7374:
URL: https://github.com/apache/shardingsphere/issues/7374#issuecomment-692068621
actually it will be confusing if there exist multiple `auto commit` setting in actual datasource pool, `ShardingDataSource` could not decide which setting will be used, since we only have one ShardingConnection.
----------------------------------------------------------------
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] tuohai666 commented on issue #7374: Transaction behaviour changed after using ShardingSphere-JDBC ShardingDataSource
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #7374:
URL: https://github.com/apache/shardingsphere/issues/7374#issuecomment-690027054
@cherrylzhao This issue maybe relevant with #7357, can you check it?
----------------------------------------------------------------
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 #7374: Transaction behaviour changed after using ShardingSphere-JDBC ShardingDataSource
Posted by GitBox <gi...@apache.org>.
cherrylzhao commented on issue #7374:
URL: https://github.com/apache/shardingsphere/issues/7374#issuecomment-690846163
----------------------------------------------------------------
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 #7374: Transaction behaviour changed after using ShardingSphere-JDBC ShardingDataSource
Posted by GitBox <gi...@apache.org>.
kimmking closed issue #7374:
URL: https://github.com/apache/shardingsphere/issues/7374
----------------------------------------------------------------
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 #7374: Transaction behaviour changed after using ShardingSphere-JDBC ShardingDataSource
Posted by GitBox <gi...@apache.org>.
cherrylzhao commented on issue #7374:
URL: https://github.com/apache/shardingsphere/issues/7374#issuecomment-690846163
similar with #6913 #6919, ShardingDataSource is a logic datasource, it should not care about the underlying datasource status.
----------------------------------------------------------------
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] Vensence commented on issue #7374: Transaction behaviour changed after using ShardingSphere-JDBC ShardingDataSource
Posted by GitBox <gi...@apache.org>.
Vensence commented on issue #7374:
URL: https://github.com/apache/shardingsphere/issues/7374#issuecomment-691679020
Or may add some configuration options at least let users to choose to make it more look like "real" datasource or 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] kimmking commented on issue #7374: Transaction behaviour changed after using ShardingSphere-JDBC ShardingDataSource
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #7374:
URL: https://github.com/apache/shardingsphere/issues/7374#issuecomment-696058518
We only suggestion use connection.setAutoCommit to instead of url option.
----------------------------------------------------------------
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 #7374: Transaction behaviour changed after using ShardingSphere-JDBC ShardingDataSource
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #7374:
URL: https://github.com/apache/shardingsphere/issues/7374#issuecomment-696058518
We only suggestion use connection.setAutoCommit to instead of url option.
----------------------------------------------------------------
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 #7374: Transaction behaviour changed after using ShardingSphere-JDBC ShardingDataSource
Posted by GitBox <gi...@apache.org>.
kimmking closed issue #7374:
URL: https://github.com/apache/shardingsphere/issues/7374
----------------------------------------------------------------
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