terrymanu commented on issue #28401:
URL:
https://github.com/apache/shardingsphere/issues/28401#issuecomment-3619419412
• Understanding
- In a single-datasource sharding setup, you insert first, then throw an
exception to expect LOCAL transactions (defaultType=LOCAL) to roll back, but
the record is not rolled back; the same flow rolls back under XA/Atomikos.
- The sample repo sharging-jdbc contains no @Transactional, and
sharding.yaml sets defaultType: XA while the code uses
DataSourceTransactionManager.
Root Cause
- No Spring transaction boundary: without @Transactional (or manual
setAutoCommit(false)/rollback), MySQL auto-commits each statement, so it looks
like “LOCAL transaction is invalid.”
- Transaction type and manager mismatch: sharding.yaml is XA, but you use
a local transaction manager. To test LOCAL, set defaultType: LOCAL and ensure
@Transactional is active.
- ShardingSphere 5.3.2 already has e2e coverage for local transaction
rollback; no similar rollback bug is known.
Analysis
- In LOCAL mode, ShardingSphere delegates to the underlying datasource;
you must annotate service methods with @Transactional (or manage JDBC
transactions manually), otherwise each write auto-commits.
- If you want XA, specify @ShardingTransactionType(TransactionType.XA) or
TransactionTypeHolder.set(TransactionType.XA) before the call, and use a JTA
JtaTransactionManager (e.g., Atomikos). Otherwise it falls back to local
transactions.
- Official transaction doc (Spring + local/XA config):
https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/usage/transaction/
Conclusion
- This is a usage/configuration issue rather than a ShardingSphere bug.
Recommendations:
1. For local transactions, set defaultType: LOCAL in sharding.yaml and
ensure the tables use InnoDB.
2. Add @Transactional to service methods that include multiple writes,
e.g.:
```java
@Service
public class OrderService {
@Transactional
public void addOrderThenFail() {
orderMapper.insert(...);
int x = 1 / 0; // simulate exception
orderItemMapper.insert(...);
}
}
```
3. Ensure Spring’s transaction manager is based on the ShardingSphere
DataSource.
- If it still doesn’t roll back, please share: a minimal repro including
@Transactional, your current sharding.yaml, SHOW CREATE TABLE outputs, and SQL
logs around the exception so we can dig deeper.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
