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 2022/10/10 06:30:28 UTC
[GitHub] [shardingsphere] 13612687938 opened a new issue, #15629: Springboot with JPA, readwrite-splitting always on write-ds
13612687938 opened a new issue, #15629:
URL: https://github.com/apache/shardingsphere/issues/15629
## Bug Report
Current situation：
SpringBoot + Mybatis-Plus + MySQL8.0 + ShardingSphere-JDBC ===> SUCCESS
SpringBoot + SpringDataJPA + MySQL8.0 + ShardingSphere-JDBC ===> FAIL （read and write both on write-ds）
detail code: (an API method just contain userRepository.findAll();)
/**
* query all list
*
* @return
*/
@GetMapping("/list")
public List<User> list() {
return userService.list();
}
expect：
write on write-ds, read on read-ds.
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- ShardingSphere -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.1.0</version>
</dependency>
</dependencies>
application.properties just copy url "https://github.com/apache/shardingsphere/blob/master/examples/shardingsphere-jdbc-example/single-feature-example/readwrite-splitting-example/readwrite-splitting-spring-boot-jpa-example/src/main/resources/application-readwrite-splitting.properties"
`spring.shardingsphere.datasource.names=write-ds,read-ds-0,read-ds-1
spring.shardingsphere.datasource.write-ds.jdbc-url=jdbc:mysql://localhost:3306/shardingsphere-main?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.write-ds.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.write-ds.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.write-ds.username=root
spring.shardingsphere.datasource.write-ds.password=root
spring.shardingsphere.datasource.read-ds-0.jdbc-url=jdbc:mysql://localhost:3306/shardingsphere-slave1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.read-ds-0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.read-ds-0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.read-ds-0.username=root
spring.shardingsphere.datasource.read-ds-0.password=root
spring.shardingsphere.datasource.read-ds-1.jdbc-url=jdbc:mysql://localhost:3306/shardingsphere-slave2?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.read-ds-1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.read-ds-1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.read-ds-1.username=root
spring.shardingsphere.datasource.read-ds-1.password=root
spring.shardingsphere.rules.readwrite-splitting.data-sources.readwrite_ds.type=Static
spring.shardingsphere.rules.readwrite-splitting.data-sources.readwrite_ds.props.write-data-source-name=write-ds
spring.shardingsphere.rules.readwrite-splitting.data-sources.readwrite_ds.props.read-data-source-names=read-ds-0,read-ds-1
spring.shardingsphere.rules.readwrite-splitting.data-sources.readwrite_ds.load-balancer-name=round_robin
spring.shardingsphere.rules.readwrite-splitting.load-balancers.round_robin.type=ROUND_ROBIN
spring.shardingsphere.props.sql-show=true`
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] pxzxj commented on issue #15629: Springboot with JPA, readwrite-splitting always on write-ds
Posted by GitBox <gi...@apache.org>.
pxzxj commented on issue #15629:
URL: https://github.com/apache/shardingsphere/issues/15629#issuecomment-1105154441
看了下上面都是国内的开发那就用中文吧，看起来方便点儿。
这个问题的根源是Spring Data JPA默认所有方法都使用了事务，当然查询是只读事务，而ShardingSphere判断使用了事务就走主库，这两点可以参考Spring Data JPA的 [SimpleJpaRepository](https://github.com/spring-projects/spring-data-jpa/blob/a70fff4515d04be2e0f6f18edbcd7c1c0bff704e/spring-data-jpa/src/main/java/org/springframework/data/jpa/repository/support/SimpleJpaRepository.java#L89) 类跟ShardingSphere的 [ReadwriteSplittingDataSourceRouter](https://github.com/apache/shardingsphere/blob/4354c3df905e38ca749e6ff40217037ffe2533df/shardingsphere-features/shardingsphere-readwrite-splitting/shardingsphere-readwrite-splitting-core/src/main/java/org/apache/shardingsphere/readwritesplitting/route/impl/ReadwriteSplittingDataSourceRouter.java#L56)
类。
比较理想的方法当然是ShardingSphere这边判断事务是不是只读的，不过这个不太确定ShardingSphere的开发团队愿不愿意改，就算改也要等到下个版本了。所以我这里提供两种调整Spring Data JPA事务的方案。
### 第一种：
在Spring Boot的启动类加上 @EnableJpaRepositories(enableDefaultTransactions = false)，这样就关闭了Spring Data JPA的默认事务，但这也导致之前依赖默认事务的代码的出错，当然主要是涉及修改的操作，需要在Service或者Repository上手动添加@Transcational注解来解决报错。
所以这种方法虽然配置简单，但对老代码有侵入性，要谨慎使用。
### 第二种：
修改Spring Data JPA的事务判断，如果是只读事务(也就是readOnly=true)那就不开启事务，配置方法如下，其实就是创建 `IgnoreJPAReadOnlyTransactionBeanPostProcessor ` 类并声明为Bean
```java
public class IgnoreJPAReadOnlyTransactionBeanPostProcessor implements BeanPostProcessor {
@Override
public Object postProcessBeforeInitialization(Object bean, String beanName) throws BeansException {
if (bean instanceof JpaRepositoryFactoryBean) {
RepositoryFactoryCustomizer customizer = new RepositoryFactoryCustomizer() {
@Override
public void customize(RepositoryFactorySupport repositoryFactory) {
repositoryFactory.addRepositoryProxyPostProcessor(new IgnoreJPAReadOnlyTransactionRepositoryProxyPostProcessor());
}
};
((RepositoryFactoryBeanSupport<?, ?, ?>) bean).addRepositoryFactoryCustomizer(customizer);
}
return bean;
}
private static class IgnoreJPAReadOnlyTransactionRepositoryProxyPostProcessor implements RepositoryProxyPostProcessor {
@Override
public void postProcess(ProxyFactory factory, RepositoryInformation repositoryInformation) {
Advisor[] advisors = factory.getAdvisors();
TransactionInterceptor transactionInterceptor = null;
for(Advisor advisor : advisors) {
if(advisor != null && advisor.getAdvice() instanceof TransactionInterceptor) {
transactionInterceptor = (TransactionInterceptor) advisor.getAdvice();
break;
}
}
if(transactionInterceptor != null) {
TransactionAttributeSource transactionAttributeSource = transactionInterceptor.getTransactionAttributeSource();
TransactionAttributeSource ignoreReadOnlyDelegate = (method, targetClass) -> {
TransactionAttribute transactionAttribute = transactionAttributeSource.getTransactionAttribute(method, targetClass);
if(transactionAttribute != null && transactionAttribute.isReadOnly()) {
return null;
}
return transactionAttribute;
};
transactionInterceptor.setTransactionAttributeSource(ignoreReadOnlyDelegate);
}
}
}
}
@Configuration
public class WebConfig {
@Bean
public IgnoreJPAReadOnlyTransactionBeanPostProcessor ignoreJPAReadOnlyTransactionBeanPostProcessor() {
return new IgnoreJPAReadOnlyTransactionBeanPostProcessor();
}
}
```
这种方法经过测试原来代码也都能正常运行，只是禁用了只读事务，所以建议用这个方法。
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang closed issue #15629: Springboot with JPA, readwrite-splitting always on write-ds
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang closed issue #15629: Springboot with JPA, readwrite-splitting always on write-ds
URL: https://github.com/apache/shardingsphere/issues/15629
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lsoimpe commented on issue #15629: Springboot with JPA, readwrite-splitting always on write-ds
Posted by GitBox <gi...@apache.org>.
lsoimpe commented on issue #15629:
URL: https://github.com/apache/shardingsphere/issues/15629#issuecomment-1088611805
The same problem!!!
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] github-actions[bot] commented on issue #15629: Springboot with JPA, readwrite-splitting always on write-ds
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #15629:
URL: https://github.com/apache/shardingsphere/issues/15629#issuecomment-1272350315
Hello , this issue has not received a reply for several days.
This issue is supposed to be closed.
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] gzgzapp commented on issue #15629: Springboot with JPA, readwrite-splitting always on write-ds
Posted by GitBox <gi...@apache.org>.
gzgzapp commented on issue #15629:
URL: https://github.com/apache/shardingsphere/issues/15629#issuecomment-1191408441
load-balancers type use TRANSACTION_ROUND_ROBIN to replace ROUND_ROBIN can fix 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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] github-actions[bot] closed issue #15629: Springboot with JPA, readwrite-splitting always on write-ds
Posted by GitBox <gi...@apache.org>.
github-actions[bot] closed issue #15629: Springboot with JPA, readwrite-splitting always on write-ds
URL: https://github.com/apache/shardingsphere/issues/15629
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #15629: Springboot with JPA, readwrite-splitting always on write-ds
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #15629:
URL: https://github.com/apache/shardingsphere/issues/15629#issuecomment-1407584004
Looks like the issue has been resolved, I will close this it, thank you all!
--
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org