You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@seata.apache.org by "beanww (via GitHub)" <gi...@apache.org> on 2025/10/10 02:46:02 UTC
[I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
beanww opened a new issue, #7686:
URL: https://github.com/apache/incubator-seata/issues/7686
### Check Ahead
- [x] I have searched the [issues](https://github.com/seata/seata/issues) of this repository and believe that this is not a duplicate.
- [ ] I am willing to try to implement this feature myself.
### Why you need it?
背景：
postgres14+数据库
seata 2.1 xa模式
1个service内配置了2个数据源
所有数据源在启动时做了proxy
问题：
如果service的方法上注解了2个事务注解
@GlobalTransactional
@Transactional
public void doSth(){
..
}
那么
doSth里面如果通过@DS注解切换数据源，是无效的
如果不加@Transactional注解,即:
@GlobalTransactional
public void doSth(){
..
}
那么doSth里面@DS注解是有效的，可以切换数据源，
但是，不加@Transactional注解时，会导致service里每一个sql语句都会生成一个seata分支事务
，从pg的pending事务表：pg_prepared_xacts，也可以看出来，因此这种方式切换数据源对数据库要求过高，似乎没法使用
建议：
是否有一种机制，可以在seata事务内加上@Transactional事务注解的情况也支持多数据源切换？
或者，不加@Transactional注解时，seata可以实现不要让一个service内所有的sql都各自生成一个分支事务，而是判断如果使用的是相同的数据源时，使用同一个分支事务？
### How it could be?
config:
`seata:
data-source-proxy-mode: XA
enable-auto-data-source-proxy: false
enabled: true`
自己写的proxy数据源处理类:
`@Configuration
@ConditionalOnExpression("${seata.enabled:false} && '${seata.data-source-proxy-mode:}'.equalsIgnoreCase('XA')")
public class XaDynamicDataSourceConfig {
@Autowired
private DynamicDataSourceProperties dynamicDataSourceProperties;
@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider() {
// 从 YAML 里获取所有数据源的配置
Map<String, DataSourceProperty> dataSourcePropertiesMap = dynamicDataSourceProperties.getDatasource();
// 全局 Druid 配置
DruidConfig globalDruidConfig = dynamicDataSourceProperties.getDruid();
return () -> {
Map<String, DataSource> dataSourceMap = new HashMap<>();
// 遍历所有数据源，创建 XA 代理数据源
for (Map.Entry<String, DataSourceProperty> entry : dataSourcePropertiesMap.entrySet()) {
String dsKey = entry.getKey();
DataSourceProperty dsProp = entry.getValue(); // 包含 URL、用户名、密码
// 1. 创建 DruidXADataSource
DruidXADataSource druidXADataSource = new DruidXADataSource();
// 2. 手动注入连接信息（从 YAML 加载）
druidXADataSource.setUrl(dsProp.getUrl());
druidXADataSource.setUsername(dsProp.getUsername());
druidXADataSource.setPassword(dsProp.getPassword());
druidXADataSource.setDriverClassName(dsProp.getDriverClassName());
// 3. 注入全局 Druid 配置（initial-size、min-idle 等）
configureDruid(druidXADataSource, globalDruidConfig);
// 4. Seata XA 代理
DataSourceProxyXA proxyXA = new DataSourceProxyXA(druidXADataSource, dsKey);
// 5. 加入数据源 Map
dataSourceMap.put(dsKey, proxyXA);
}
return dataSourceMap;
};
}`
### Other related information
_No response_
--
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@seata.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "xiaoxiangyeyu0 (via GitHub)" <gi...@apache.org>.
xiaoxiangyeyu0 commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3388141717
可以试试升级版本，之前我们也遇到这个问题。
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "funky-eyes (via GitHub)" <gi...@apache.org>.
funky-eyes commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3388926884
Adding readOnly to the @Transactional annotation on a read-only SQL method will prevent the registration of an XA branch.
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "xiaoxiangyeyu0 (via GitHub)" <gi...@apache.org>.
xiaoxiangyeyu0 commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3388613306
> 你们好！我已经试了2.4版本，似乎还是有些问题：
>
> 验证时没有使用[@transactional](https://github.com/transactional)注解：
>
> 首先，日志中显示似乎有join事务的处理了： 2025-10-10 14:24:37.098 | �[34m INFO 8308�[0;39m | �[1;33mhttp-nio-48081-exec-10 [TID: N/A]�[0;39m �[1;32mo.a.seata.tm.api.TransactionalTemplate �[0;39m | join into a existing global transaction,xid=192.168.100.81:8091:3261279418629289739
>
> 但是我发现pg的pending事务表pg_prepared_xacts，仍然有很多记录，然后，我就发现了一个问题： seata现在对于每一个select语句（即使没有for update），也会创建一个分支事务
>
> 也就是说，现在的问题是： xa模式下，没有transactional注解时，怎么才能让select语句不产生分支事务呢？
我用sample试了是正常的，你的@DS是怎么切换，一个线程doSth1一个数据源，然后调用doSth2一个数据源吗？如果是这样，你可以先不切换数据源试试
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "xiaoxiangyeyu0 (via GitHub)" <gi...@apache.org>.
xiaoxiangyeyu0 commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3389139627
> > Adding readOnly to the `@Transactional` annotation on a read-only SQL method will prevent the registration of an XA branch.
>
> 您好，这个方式我恐怕不能使用，因为service里面的select语句，dml语句都有，没法单独给哪些做readonly
>
> 现在应该是两个方向：
>
> 1. [@transactional](https://github.com/transactional) 注解时 ， 能够切换数据源， 这一块似乎跟spring现在用的JdbcTransactionManager以及[@ds](https://github.com/ds)的实现也有关系，跟seata好像也有点关系，seata也有一个自己的tm。。。我再研究下吧，我觉得这个方式可能更好些，因为seata的@GlobalTransactional，一般是涉及到分布式事务时才加，而[@transactional](https://github.com/transactional)只要涉及数据变更就会加了，很难为了支持切数据源再把这个注解去掉
> 2. seata事务不加[@transactional](https://github.com/transactional)注解时，除了能切换数据源，还能够优化一下实现，使得尽量减少分支事务的创建,比如
>
> 这个问题本质我觉得是希望能够 seata 事务（at/xa）在开启[@transactional](https://github.com/transactional)注解的同时，能够切换数据源，可能不完全是seata的事，但是我觉得应用场景是比较普遍的吧，比如 读写分离场景
>
> 另外，刚才不小心把这个bug给close了，麻烦各位可以再打开继续讨论
单独设置@Transactional(readOnly = true)后，你DS就切换不了数据源了，加上DSTransactional可以切换数据源，但是目前它没有支持readOnly配置，要不你把@Transactional(readOnly = true)、@DSTransactional和@GlobalTransactional都加上看看是什么结果【/捂脸】，这样还不行的话，就只能拆成远程服务调用的方式了
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "beanww (via GitHub)" <gi...@apache.org>.
beanww commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3389172965
嗯，我可以试试，但是我觉得这个事最好的办法似乎是：
seata提供一个专用的事务管理器，代替spring现在的单数据源事务管理器，这样才能根本上让seata支持xa模式下单服务的多数据源xa事务
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "beanww (via GitHub)" <gi...@apache.org>.
beanww commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3392839041
> 这个[#7357](https://github.com/apache/incubator-seata/discussions/7357) 可能能解决你的问题
这个应该还是两个远程服务使用不同数据源的场景吧，即A->B->A数据源，第2个A的dml操作能够join到第一个A的xa事务分支上吧？
应该跟我这个的诉求不太一样，我是希望在一个服务里，能直接完成多个数据源xa事务
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "funky-eyes (via GitHub)" <gi...@apache.org>.
funky-eyes commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3388103162
This PR https://github.com/apache/incubator-seata/pull/7102 may resolve your issue. You can try upgrading to version 2.4.0, manually managing transaction commit and rollback, and reusing the same connection. With this PR, the prepare phase is moved to the point when the connection’s close method is called, meaning you can execute multiple SQL statements within the same connection before it is closed and the transaction is prepared.
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "beanww (via GitHub)" <gi...@apache.org>.
beanww commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3509271955
这个问题，我自己解决了，提供思路如下：
不使用mybatis默认提供的SpringManagedTransaction管理spring-mybatis下的@Transaction事务，因为这个实现只支持一个数据源，自己实现一个MultiDsSpringManagedTransaction，在每次getConnection时判断是否有@DS注解导致的数据源变更，如果变更则使用新数据源，并保存所有涉及到的数据源，在close方法时对所有涉及到的数据源发起close调用（仍然使用spring的DataSourceUtils.releaseConnection，只不过是对多个connection的操作）
这样改造后，就可以在seata的AT/XA模式下，在@GlobalTransactional @Transactional注解都开启的情况下，仍然可以通过@DS注解在任何类（主要是指Mapper上）切换到其他数据源，即实现了seata-XA/AT支持在一个service内切换多个数据源，无需拆分成多个服务的效果。
I have resolved this issue independently and would like to share the approach:
Instead of using the default SpringManagedTransaction provided by MyBatis for transaction management with @Transactional in a Spring-MyBatis environment (which only supports a single data source), I implemented a custom MultiDsSpringManagedTransaction.
The core logic is as follows:
Dynamic Data Source Detection:
Override the getConnection() method to check if a data source switch is required based on the presence of the @DS annotation (or other custom logic).
If a switch is detected, obtain a connection from the new target data source instead.
Track Multiple Connections:
Maintain a collection (e.g., List<Connection>) to track all connections acquired from different data sources during the transaction.
Multi-Connection Closure:
In the close() method, iterate over all tracked connections and release each one using Spring's DataSourceUtils.releaseConnection().
This modification enables Seata's AT/XA mode to work seamlessly with @GlobalTransactional and @Transactional annotations, while still allowing dynamic data source switching via @DS (primarily on Mapper interfaces).
Key Outcome:Achieves multi-data-source switching within a single service method under Seata's global transaction management, eliminating the need to split logic across multiple services.
eg code:
`public class MultiDsSpringManagedTransactionFactory extends SpringManagedTransactionFactory implements TransactionFactory {
@Override
public Transaction newTransaction(Connection conn) {
throw new UnsupportedOperationException("New Spring transactions require a DataSource");
}
@Override
public Transaction newTransaction(DataSource dataSource, TransactionIsolationLevel level, boolean autoCommit) {
return new MultiDsSpringManagedTransaction(dataSource);
}
}`
`public class MultiDsSpringManagedTransaction implements Transaction {
// private static final Logger LOGGER = LoggerFactory.getLogger(SpringManagedTransaction.class);
private final DataSource dataSource;
private Connection curConnection;
private Map<DataSource, Connection> connectionsInTrans = new HashMap<>();
private boolean isConnectionTransactional;
private boolean autoCommit;
public MultiDsSpringManagedTransaction(DataSource dataSource) {
notNull(dataSource, "No DataSource specified");
this.dataSource = dataSource;
}
/**
* {@inheritDoc}
*/
@Override
public Connection getConnection() throws SQLException {
// 定制逻辑:
// mybatis每一次sql执行前都会触发此方法，在此方法再执行一次动态数据源判断，就可以把数据源切换精细到mapper方法级，小于之前的远程服务级范围
DataSource determineDataSource = dataSource;
if (dataSource instanceof DynamicRoutingDataSource) {
determineDataSource = ((DynamicRoutingDataSource) dataSource).determineDataSource();
}
// 每个事务内使用的数据源，都记录到dataSouresInTrans里，如果切换到新数据源了，则再次调用openConnection获取
Connection usedConn = connectionsInTrans.get(determineDataSource);
if (null == usedConn) {
log.debug("准备从数据源获取新数据库连接: {}", determineDataSource);
openConnection(determineDataSource);
} else {
this.curConnection = connectionsInTrans.get(determineDataSource);
log.debug("复用已有数据库连接: Connection={}", this.curConnection);
}
return this.curConnection;
}
/**
* Gets a connection from Spring transaction manager and discovers if this
* {@code Transaction} should manage connection or let it to Spring.
* <p>
* It also reads autocommit setting because when using Spring Transaction
* MyBatis thinks that autocommit is always false and will always call
* commit/rollback so we need to no-op that calls.
*/
private void openConnection(DataSource curDataSource) throws SQLException {
// 定制逻辑:
// 获取指定数据源的连接，这时会注册conn的synchronization，里面有各个时机的回调逻辑
this.curConnection = DataSourceUtils.getConnection(curDataSource);
if(log.isDebugEnabled()) {
log.debug("获取新数据库连接: Connection={}", this.curConnection == null ? "NA" : this.curConnection.hashCode());
}
// 把连接跟ds关联关系记录下来，在一个事务中，一个ds只需要打开一个conn
Connection preConn = connectionsInTrans.put(curDataSource, curConnection);
if(preConn != null && preConn != curConnection) {
throw new IllegalArgumentException("Already has Connection with datasource:" + curDataSource);
}
this.autoCommit = this.curConnection.getAutoCommit();
this.isConnectionTransactional = DataSourceUtils.isConnectionTransactional(this.curConnection, curDataSource);
log.debug("JDBC Connection [" + this.curConnection + "] will" + (this.isConnectionTransactional ? " " : " not ")
+ "be managed by Spring");
}
/**
* {@inheritDoc}
*/
@Override
public void commit() throws SQLException {
if (this.curConnection != null && !this.isConnectionTransactional && !this.autoCommit) {
log.debug("Committing JDBC Connection [" + this.curConnection + "]");
this.curConnection.commit();
}
}
/**
* {@inheritDoc}
*/
@Override
public void rollback() throws SQLException {
if (this.curConnection != null && !this.isConnectionTransactional && !this.autoCommit) {
log.debug("Rolling back JDBC Connection [" + this.curConnection + "]");
this.curConnection.rollback();
}
}
/**
* {@inheritDoc}
*/
@Override
public void close() throws SQLException {
log.debug("关闭事务中的 {} 个数据库连接", connectionsInTrans.size());
Set<Entry<DataSource, Connection>> entrySet = connectionsInTrans.entrySet();
for(Entry<DataSource, Connection> entry: entrySet) {
if(log.isDebugEnabled()) {
log.debug("准备释放数据库连接: Connection={}", entry.getValue() == null ? "NA" : entry.getValue().hashCode());
}
releaseConnection(entry.getValue(), entry.getKey());
}
}
/**
* Close the given Connection, obtained from the given DataSource, if it is not
* managed externally (that is, not bound to the thread).
*
* @param con the Connection to close if necessary (if this is
* {@code null}, the call will be ignored)
* @param dataSource the DataSource that the Connection was obtained from (may
* be {@code null})
* @see #getConnection
*/
public static void releaseConnection(@Nullable Connection con, @Nullable DataSource dataSource) {
try {
doReleaseConnection(con, dataSource);
} catch (SQLException ex) {
log.debug("Could not close JDBC Connection", ex);
} catch (Throwable ex) {
log.debug("Unexpected exception on closing JDBC Connection", ex);
}
}
/**
* Actually close the given Connection, obtained from the given DataSource. Same
* as {@link #releaseConnection}, but throwing the original SQLException.
* <p>
* Directly accessed by {@link TransactionAwareDataSourceProxy}.
*
* @param con the Connection to close if necessary (if this is
* {@code null}, the call will be ignored)
* @param dataSource the DataSource that the Connection was obtained from (may
* be {@code null})
* @throws SQLException if thrown by JDBC methods
* @see #doGetConnection
*/
public static void doReleaseConnection(@Nullable Connection con, @Nullable DataSource dataSource)
throws SQLException {
if (con == null) {
return;
}
if (dataSource != null) {
ConnectionHolder conHolder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
if (conHolder != null && connectionEquals(conHolder, con)) {
// It's the transactional Connection: Don't close it.
conHolder.released();
if(log.isDebugEnabled()) {
log.debug("数据库连接使用数--: Connection={}", con == null ? "NA" : con.hashCode());
}
return;
}
}
doCloseConnection(con, dataSource);
}
/**
* Determine whether the given two Connections are equal, asking the target
* Connection in case of a proxy. Used to detect equality even if the
* user passed in a raw target Connection while the held one is a proxy.
* @param conHolder the ConnectionHolder for the held Connection (potentially a proxy)
* @param passedInCon the Connection passed-in by the user
* (potentially a target Connection without proxy)
* @return whether the given Connections are equal
* @see #getTargetConnection
*/
private static boolean connectionEquals(ConnectionHolder conHolder, Connection passedInCon) {
if (conHolder.getConnectionHandle() == null) {
return false;
}
Connection heldCon = conHolder.getConnection();
// Explicitly check for identity too: for Connection handles that do not implement
// "equals" properly, such as the ones Commons DBCP exposes).
return (heldCon == passedInCon || heldCon.equals(passedInCon) ||
getTargetConnection(heldCon).equals(passedInCon));
}
/**
* Return the innermost target Connection of the given Connection. If the given
* Connection is a proxy, it will be unwrapped until a non-proxy Connection is
* found. Otherwise, the passed-in Connection will be returned as-is.
* @param con the Connection proxy to unwrap
* @return the innermost target Connection, or the passed-in one if no proxy
* @see ConnectionProxy#getTargetConnection()
*/
public static Connection getTargetConnection(Connection con) {
Connection conToUse = con;
while (conToUse instanceof ConnectionProxy connectionProxy) {
conToUse = connectionProxy.getTargetConnection();
}
return conToUse;
}
/**
* Close the Connection, unless a {@link SmartDataSource} doesn't want us to.
*
* @param con the Connection to close if necessary
* @param dataSource the DataSource that the Connection was obtained from
* @throws SQLException if thrown by JDBC methods
* @see Connection#close()
* @see SmartDataSource#shouldClose(Connection)
*/
public static void doCloseConnection(Connection con, @Nullable DataSource dataSource) throws SQLException {
if (!(dataSource instanceof SmartDataSource smartDataSource) || smartDataSource.shouldClose(con)) {
if(log.isDebugEnabled()) {
log.debug("【实际】释放数据库连接: Connection={}", con.hashCode());
}
con.close();
} else {
if(log.isDebugEnabled()) {
log.debug("【忽略】释放数据库连接: Connection={}", con == null ? "NA" : con.hashCode());
}
}
}
/**
* {@inheritDoc}
*/
@Override
public Integer getTimeout() throws SQLException {
ConnectionHolder holder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
if (holder != null && holder.hasTimeout()) {
return holder.getTimeToLiveInSeconds();
}
return null;
}
}`
`@AutoConfiguration
@ConditionalOnExpression("${seata.enabled:false} && ${cptop.mybatis.multi-ds-transaction.enabled:true}")
public class MultiDsSpringManagedTransactionConfig {
/**
* 注册 MultiDsSpringManagedTransactionFactory Bean
* <p>
* 用于支持 MyBatis 在 Seata 分布式事务场景下的多数据源事务管理
* <p>
* 使用 @Primary 注解确保此 TransactionFactory 优先于默认的 SpringManagedTransactionFactory
*/
@Bean
@Primary
public TransactionFactory multiDsSpringManagedTransactionFactory() {
return new MultiDsSpringManagedTransactionFactory();
}
}`
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "beanww (via GitHub)" <gi...@apache.org>.
beanww commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3389045201
> Adding readOnly to the `@Transactional` annotation on a read-only SQL method will prevent the registration of an XA branch.
您好，这个方式我恐怕不能使用，因为service里面的select语句，dml语句都有，没法单独给哪些做readonly
现在应该是两个方向：
1. @Transactional 注解时 ， 能够切换数据源， 这一块似乎跟spring现在用的JdbcTransactionManager以及@DS的实现也有关系，跟seata好像也有点关系，seata也有一个自己的tm。。。我再研究下吧，我觉得这个方式可能更好些，因为seata的@GlobalTransactional，一般是涉及到分布式事务时才加，而@Transactional只要涉及数据变更就会加了，很难为了支持切数据源再把这个注解去掉
2. seata事务不加@Transactional注解时，除了能切换数据源，还能够优化一下实现，使得尽量减少分支事务的创建,比如
这个问题本质我觉得是希望能够
seata 事务（at/xa）在开启@Transactional注解的同时，能够切换数据源，可能不完全是seata的事，但是我觉得应用场景是比较普遍的吧，比如 读写分离场景
另外，刚才不小心把这个bug给close了，麻烦各位可以再打开继续讨论
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "beanww (via GitHub)" <gi...@apache.org>.
beanww commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3388649375
> > 你们好！我已经试了2.4版本，似乎还是有些问题：
> > 验证时没有使用[@transactional](https://github.com/transactional)注解：
> > 首先，日志中显示似乎有join事务的处理了： 2025-10-10 14:24:37.098 | �[34m INFO 8308�[0;39m | �[1;33mhttp-nio-48081-exec-10 [TID: N/A]�[0;39m �[1;32mo.a.seata.tm.api.TransactionalTemplate �[0;39m | join into a existing global transaction,xid=192.168.100.81:8091:3261279418629289739
> > 但是我发现pg的pending事务表pg_prepared_xacts，仍然有很多记录，然后，我就发现了一个问题： seata现在对于每一个select语句（即使没有for update），也会创建一个分支事务
> > 也就是说，现在的问题是： xa模式下，没有transactional注解时，怎么才能让select语句不产生分支事务呢？
>
> 我用sample试了是正常的，你的[@ds](https://github.com/ds)是怎么切换，一个线程doSth1一个数据源，然后调用doSth2一个数据源吗？如果是这样，你可以先不切换数据源试试
哦，我没说清楚，我再从新描述下问题：
现在问题不是@DS没有正常切换数据源， 因为如果不加@Transactional注解，数据源是可以切换的，
现在的问题是：如果不加@Transactional注解后，seata对于service中的所有select语句，都会生成一个新的分支事务，这样会使得一个全局事务会产生大量的分支事务，我加一下我这里的日志，可以看到每个select都会产生branch register success的日志，pg里也能看到创建了分支事务，这样对数据库的压力应该会太大，能否做到select语句不产生分支事务？或者加入已有的分支事务？
`[preHandle][开始请求 URL(/rpc-api/system/teacher/list-by-ids) 参数([274])]
a.seata.tm.api.TransactionalTemplate [0;39m | join into a existing global transaction,xid=192.168.100.81:8091:3261279418629289739
c.m.t.d.m.t.T.selectBatchIds [0;39m | ==> Preparing: SELECT id, remark, login_id, passwd, invalid_flag, lang,
c.m.t.d.m.t.T.selectBatchIds [0;39m | ==> Parameters: 274(Long)
a.seata.rm.AbstractResourceManager [0;39m | branch register success, xid:192.168.100.81:8091:3261279418629289739, branchId:3261279418629289743, lockKeys:null
c.m.t.d.m.t.T.selectBatchIds [0;39m | <== Total: 1
c.m.s.d.m.u.A.selectById [0;39m | ==> Preparing: SELECT id, username, password, nickname, remark, dept_id, post_ids, email, mobile
c.m.s.d.m.u.A.selectById [0;39m | ==> Parameters: 274(Long)
a.seata.rm.AbstractResourceManager [0;39m | branch register success, xid:192.168.100.81:8091:3261279418629289739, branchId:3261279418629289744, lockKeys:null
c.m.s.d.m.u.A.selectById [0;39m | <== Total: 1
c.m.s.d.m.u.A.updateById [0;39m | ==> Preparing: UPDATE system_users SET remark = ?, update_time = ?, updater = ? WHERE id = ? AND deleted = false
c.m.s.d.m.u.A.updateById [0;39m | ==> Parameters: test(String), 2025-10-10T15:24:37.150128600(LocalDateTime), null, 274(Long)
a.seata.rm.AbstractResourceManager [0;39m | branch register success, xid:192.168.100.81:8091:3261279418629289739, branchId:3261279418629289745, lockKeys:null
c.m.s.d.m.u.A.updateById [0;39m | <== Updates: 1
c.m.s.d.m.u.A.selectById [0;39m | ==> Preparing: SELECT id, username, password, nickname, remark, dept_id, post_ids, email, mobile, sex, avatar, status,
c.m.s.d.m.u.A.selectById [0;39m | ==> Parameters: 274(Long)
a.seata.rm.AbstractResourceManager [0;39m | branch register success, xid:192.168.100.81:8091:3261279418629289739, branchId:3261279418629289746, lockKeys:null
c.m.s.d.m.u.A.selectById [0;39m | <== Total: 1
c.m.s.d.m.p.UserRoleMapper.selectList [0;39m | ==> Preparing: SELECT id, user_id, role_id, create_time, update_time, creator, updater, deleted FROM system_user_role WHERE deleted = false AND (user_id = ?)
c.m.s.d.m.p.UserRoleMapper.selectList [0;39m | ==> Parameters: 274(Long)
a.seata.rm.AbstractResourceManager [0;39m | branch register success, xid:192.168.100.81:8091:3261279418629289739, branchId:3261279418629289747, lockKeys:null
c.m.s.d.m.p.UserRoleMapper.selectList [0;39m | <== Total: 0
c.m.s.d.m.u.A.selectById [0;39m | ==> Preparing: SELECT id, username, password, nickname, remark, dept_id, post_ids, email, mobile, sex, avatar, status,
c.m.s.d.m.u.A.selectById [0;39m | ==> Parameters: 274(Long)
a.seata.rm.AbstractResourceManager [0;39m | branch register success, xid:192.168.100.81:8091:3261279418629289739, branchId:3261279418629289748, lockKeys:null`
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "beanww (via GitHub)" <gi...@apache.org>.
beanww commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3388471805
你们好！我已经试了2.4版本，似乎还是有些问题：
验证时没有使用@Transactional注解：
首先，日志中显示似乎有join事务的处理了：
2025-10-10 14:24:37.098 | [34m INFO 8308[0;39m | [1;33mhttp-nio-48081-exec-10 [TID: N/A][0;39m [1;32mo.a.seata.tm.api.TransactionalTemplate [0;39m | join into a existing global transaction,xid=192.168.100.81:8091:3261279418629289739
但是我发现pg的pending事务表pg_prepared_xacts，仍然有很多记录，然后，我就发现了一个问题：
seata现在对于每一个select语句（即使没有for update），也会创建一个分支事务
也就是说，现在的问题是：
xa模式下，没有transactional注解时，怎么才能让select语句不产生分支事务呢？
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "beanww (via GitHub)" <gi...@apache.org>.
beanww commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3388957965
> doSth1调用doSth2使用远程调用。
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "beanww (via GitHub)" <gi...@apache.org>.
beanww commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3392820792
> 单独设置[@transactional](https://github.com/transactional)(readOnly = true)后，你DS就切换不了数据源了，加上DSTransactional可以切换数据源，但是目前它没有支持readOnly配置，要不你把[@transactional](https://github.com/transactional)(readOnly = true)、@DSTransactional和@GlobalTransactional都加上看看是什么结果【/捂脸】，这样还不行的话，就只能拆成远程服务调用的方式了
-----------------
经过验证：
1. 如下，是无法切换数据源的：
@GlobalTransactional
@Transactional(rollbackFor = Exception.class)
@DSTransactional
2. 如下，是可以切换数据源的，而且还解决了我上面说的select也开事务分支的问题，即：既可以切换数据源，又不会产生大量的分支事务，但是seata-xa下，这两个事务注解混合使用是否会有问题呢？而且还是之前说的，我们的@Transactional注解可能无法去掉，因为要确保不开启seata时本地事务可用，所以这个方案似乎还是不敢用
@GlobalTransactional
@DSTransactional
3. 如下，会报错
@GlobalTransactional
@Transactional(rollbackFor = Exception.class, readOnly = true)
@DSTransactional
这样报错：
### SQL: SELECT NEXTVAL('share_seq')
### Cause: org.postgresql.util.PSQLException: 错误: 不能在一个只读模式的事务中执行nextval()
; uncategorized SQLException; SQL state [25006]; error code [0]; 错误: 不能在一个只读模式的事务中执行nextval()
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:93)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:439)
at jdk.proxy2/jdk.proxy2.$Proxy188.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:160)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:87)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:152)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
at jdk.proxy2/jdk.proxy2.$Proxy200.selectQQENextUserIdSeq(Unknown Source)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:355)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at com.baomidou.dynamic.datasource.aop.DynamicDataSourceAnnotationInterceptor.invoke(DynamicDataSourceAnnotationInterceptor.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223)
at jdk.proxy2/jdk.proxy2.$Proxy201.selectQQENextUserIdSeq(Unknown Source)
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "beanww (via GitHub)" <gi...@apache.org>.
beanww closed issue #7686: XA模式下支持在一个service内切换多个数据源
URL: https://github.com/apache/incubator-seata/issues/7686
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "xiaoxiangyeyu0 (via GitHub)" <gi...@apache.org>.
xiaoxiangyeyu0 commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3389193414
> @DSTransactional跟seata、spring的事务混合起来似乎更难以预测了
主要还是@DSTransactional不支持只读，与seata我觉得没什么关系，不过ds那边没计划支持只读
<img width="638" height="212" alt="Image" src="https://github.com/user-attachments/assets/bc7e0796-d610-436e-ad5a-f3b53c2c9c14" />
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "beanww (via GitHub)" <gi...@apache.org>.
beanww commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3388966230
> 我建议doSth1和doSth2方法把[@transactional](https://github.com/transactional)和@GlobalTransactional都加上，doSth1调用doSth2使用远程调用。
是的，我现在看，如果要在一个service使用两个数据源的xa事务的话，把两个数据源的代码拆成两个远程服务，并且都启用@Transactional是最佳实践
我这个问题，主要想讨论一下在一个service里，使用多个数据源的情况
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "beanww (via GitHub)" <gi...@apache.org>.
beanww commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3389175279
@DSTransactional跟seata、spring的事务混合起来似乎更难以预测了
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "xiaoxiangyeyu0 (via GitHub)" <gi...@apache.org>.
xiaoxiangyeyu0 commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3388818942
> > > 你们好！我已经试了2.4版本，似乎还是有些问题：
> > > 验证时没有使用[@transactional](https://github.com/transactional)注解：
> > > 首先，日志中显示似乎有join事务的处理了： 2025-10-10 14:24:37.098 | �[34m INFO 8308�[0;39m | �[1;33mhttp-nio-48081-exec-10 [TID: N/A]�[0;39m �[1;32mo.a.seata.tm.api.TransactionalTemplate �[0;39m | join into a existing global transaction,xid=192.168.100.81:8091:3261279418629289739
> > > 但是我发现pg的pending事务表pg_prepared_xacts，仍然有很多记录，然后，我就发现了一个问题： seata现在对于每一个select语句（即使没有for update），也会创建一个分支事务
> > > 也就是说，现在的问题是： xa模式下，没有transactional注解时，怎么才能让select语句不产生分支事务呢？
> >
> >
> > 我用sample试了是正常的，你的[@ds](https://github.com/ds)是怎么切换，一个线程doSth1一个数据源，然后调用doSth2一个数据源吗？如果是这样，你可以先不切换数据源试试
>
> 哦，我没说清楚，我再从新描述下问题： 现在问题不是[@ds](https://github.com/ds)没有正常切换数据源， 因为如果不加[@transactional](https://github.com/transactional)注解，数据源是可以切换的， 现在的问题是：如果不加[@transactional](https://github.com/transactional)注解后，seata对于service中的所有select语句，都会生成一个新的分支事务，这样会使得一个全局事务会产生大量的分支事务，我加一下我这里的日志，可以看到每个select都会产生branch register success的日志，pg里也能看到创建了分支事务，这样对数据库的压力应该会太大，能否做到select语句不产生分支事务？或者加入已有的分支事务？
>
> `[preHandle][开始请求 URL(/rpc-api/system/teacher/list-by-ids) 参数([274])] a.seata.tm.api.TransactionalTemplate �[0;39m | join into a existing global transaction,xid=192.168.100.81:8091:3261279418629289739 c.m.t.d.m.t.T.selectBatchIds �[0;39m | ==> Preparing: SELECT id, remark, login_id, passwd, invalid_flag, lang, c.m.t.d.m.t.T.selectBatchIds �[0;39m | ==> Parameters: 274(Long) a.seata.rm.AbstractResourceManager �[0;39m | branch register success, xid:192.168.100.81:8091:3261279418629289739, branchId:3261279418629289743, lockKeys:null c.m.t.d.m.t.T.selectBatchIds �[0;39m | <== Total: 1 c.m.s.d.m.u.A.selectById �[0;39m | ==> Preparing: SELECT id, username, password, nickname, remark, dept_id, post_ids, email, mobile c.m.s.d.m.u.A.selectById �[0;39m | ==> Parameters: 274(Long) a.seata.rm.AbstractResourceManager �[0;39m | branch register success, xid:192.168.100.81:8091:3261279418629289739, branchId:3261279418629289744, lockKeys:null c.m.s.d.m.u.A.selectById
�[0;39m | <== Total: 1 c.m.s.d.m.u.A.updateById �[0;39m | ==> Preparing: UPDATE system_users SET remark = ?, update_time = ?, updater = ? WHERE id = ? AND deleted = false c.m.s.d.m.u.A.updateById �[0;39m | ==> Parameters: test(String), 2025-10-10T15:24:37.150128600(LocalDateTime), null, 274(Long) a.seata.rm.AbstractResourceManager �[0;39m | branch register success, xid:192.168.100.81:8091:3261279418629289739, branchId:3261279418629289745, lockKeys:null c.m.s.d.m.u.A.updateById �[0;39m | <== Updates: 1 c.m.s.d.m.u.A.selectById �[0;39m | ==> Preparing: SELECT id, username, password, nickname, remark, dept_id, post_ids, email, mobile, sex, avatar, status, c.m.s.d.m.u.A.selectById �[0;39m | ==> Parameters: 274(Long) a.seata.rm.AbstractResourceManager �[0;39m | branch register success, xid:192.168.100.81:8091:3261279418629289739, branchId:3261279418629289746, lockKeys:null c.m.s.d.m.u.A.selectById �[0;39m | <== Total: 1 c.m.s.d.m.p.UserRoleMapper.selectList �[0;39m |
==> Preparing: SELECT id, user_id, role_id, create_time, update_time, creator, updater, deleted FROM system_user_role WHERE deleted = false AND (user_id = ?) c.m.s.d.m.p.UserRoleMapper.selectList �[0;39m | ==> Parameters: 274(Long) a.seata.rm.AbstractResourceManager �[0;39m | branch register success, xid:192.168.100.81:8091:3261279418629289739, branchId:3261279418629289747, lockKeys:null c.m.s.d.m.p.UserRoleMapper.selectList �[0;39m | <== Total: 0 c.m.s.d.m.u.A.selectById �[0;39m | ==> Preparing: SELECT id, username, password, nickname, remark, dept_id, post_ids, email, mobile, sex, avatar, status, c.m.s.d.m.u.A.selectById �[0;39m | ==> Parameters: 274(Long) a.seata.rm.AbstractResourceManager �[0;39m | branch register success, xid:192.168.100.81:8091:3261279418629289739, branchId:3261279418629289748, lockKeys:null`
你这里是因为没有@transactional后，每次执行完sql后会关闭连接，执行下一个sql的时候，会重新拿一个新的connection连接，每个连接都会注册一个分支事务。
这种情况是本地切换数据源的事务，我记得ds是有本地分布式事务功能的，也是[funky-eyes](https://github.com/funky-eyes)大佬实现的，如果要用seata，我建议doSth1和doSth2方法把@transactional和@GlobalTransactional都加上，doSth1调用doSth2使用远程调用。
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org
Re: [I] XA模式下支持在一个service内切换多个数据源 [incubator-seata]
Posted by "xiaoxiangyeyu0 (via GitHub)" <gi...@apache.org>.
xiaoxiangyeyu0 commented on issue #7686:
URL: https://github.com/apache/incubator-seata/issues/7686#issuecomment-3392801910
这个https://github.com/apache/incubator-seata/discussions/7357可能能解决你的问题
--
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@seata.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@seata.apache.org
For additional commands, e-mail: notifications-help@seata.apache.org