terrymanu commented on issue #29505:
URL:
https://github.com/apache/shardingsphere/issues/29505#issuecomment-3619410866
• Issue Understanding
- In your follow-up you showed START TRANSACTION; update ...; ROLLBACK; to
indicate a transaction was opened, yet after rollback the update still appears.
Scenario: Proxy 5.4.1 + MySQL 8.0.33, sharded table user routed by age to
multiple
physical tables, expecting a unique-key conflict to rollback everything,
but seeing partial success.
Root Cause
- The script alone cannot confirm the transaction actually ran in the same
session/same backend connection. Many GUI/clients run each statement in a new
connection or keep autocommit=1, so START TRANSACTION and subsequent statements
may not
share the same physical connection, or earlier updates were
auto-committed and ROLLBACK cannot undo them.
- Proxy does not auto-wrap split physical SQLs into a transaction; you
must disable autocommit and explicitly start a transaction in the same session
so all routed SQLs run on the same backend connection and MySQL can guarantee
atomicity.
Official transaction docs:
https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-proxy/feature/transaction/
Analysis
- We still cannot rule out “transaction not executed in the same session”
as the main possibility: the screenshots don’t show @@autocommit, nor sql-show
frontend/backend connectionIds, so connection reuse is unclear.
- With autocommit=1 or multi-connection execution, the update routed to
t_order_1 would be auto-committed; the later unique-key conflict on t_order_2
raises an error but cannot roll back what has already committed—matching the
observed
behavior.
Conclusion
- With current info this is more likely a transaction/session usage issue
than a confirmed Proxy defect. Please help confirm by providing:
1. A single-session (preferably mysql CLI) transcript with outputs:
select @@autocommit; select @@tx_isolation; START TRANSACTION; update ...;
ROLLBACK;
2. Matching sql-show logs showing frontend/backend connectionIds to
verify they stay the same
3. Whether your client/GUI uses auto-commit or multiple connections
- If you can still reproduce with a single session, autocommit=0, and
consistent connectionIds, please share the above details and we’ll continue to
investigate.
--
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]
