Re: [I] Transaction not rolled back [shardingsphere]

["terrymanu (via GitHub)" <gi...@apache.org>]

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: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org