| Bug #93158 | read-only xa transaction rollback be blocked by "flush tables with read lock" | ||
|---|---|---|---|
| Submitted: | 11 Nov 2018 14:23 | Modified: | 12 Nov 2018 14:32 |
| Reporter: | dennis gao | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: XA transactions | Severity: | S3 (Non-critical) |
| Version: | 5.7.24 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[11 Nov 2018 14:23]
dennis gao
[12 Nov 2018 13:33]
MySQL Verification Team
Hi, Thank you for your bug report. However, this is not a bug. It is not a bug, because our Reference Manual clearly explains that FTWRL is preventing all transactions on InnoDB tables to ROLLBACK or COMMIT. This is the same for XA transactions or "normal" transactions. As you are using XA transactions, on how many servers are you running one transaction and what XA manager are you using ??? However, there are more important omissions and mistakes in your report. You have not run your transaction in read-only mode, so there is no way that a server would know what you were trying to do. Even if you did run in read-only mode, it would not be a bug, but it might make a viable feature request.
[12 Nov 2018 13:34]
MySQL Verification Team
A text above is quite clear and deals with issues that are already fully covered in our Reference Manual.
[12 Nov 2018 13:52]
dennis gao
Hi Sinisa Milivojevic ： Thanks for the response. I am confusing about "FTWRL is preventing all transactions on InnoDB tables to ROLLBACK or COMMIT.", plz check the follow test steps: ====================================================================================== Server version: 5.7.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test Database changed mysql> flush tables with read lock; Query OK, 0 rows affected (0.11 sec) mysql> START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */; Query OK, 0 rows affected (0.01 sec) mysql> rollback; Query OK, 0 rows affected (0.01 sec) mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) mysql> XA START '120-0-6-11'; Query OK, 0 rows affected (0.00 sec) mysql> xa end '120-0-6-11'; Query OK, 0 rows affected (0.00 sec) mysql> xa rollback '120-0-6-11'; Query OK, 0 rows affected (0.00 sec) ================================================================ You can see that: If there is only one session do "flush tables with read lock", mysql can do xa rollback in this session, mysql still consider this empty xa transaction as a read-only transaction. If there is two session execute "flush tables with read lock", the xa rollback will be blocked as I mentioned in the last post. That is confusing.
[12 Nov 2018 14:00]
MySQL Verification Team
HI, First of all, as our Reference Manual defines: FTWRL = Flush Tables With Read Block Second, what you describe is expected behaviour, because XA transactions are completely different from other types of transactions. Also, you have not replied to all of my questions .....
[12 Nov 2018 14:16]
dennis gao
Hi Sinisa Milivojevic ： I just test the xa transaction behavior manually. The XA manager is myself, and only has one mysql node. Do you mean the following behavior is expected and described in mysql manual: 1. if there is only one transaction execute "flush tables with read lock", this session allow to execute "xa rollback" without blocked for an empty xa transaction. (see my second post) 2. if there are two transactions execute "flush tables with read lock" at the same time, all "xa rollback" for an empty xa transaction will be blocked. (see my first post) IMO, If we design to block all commit/rollback operation for FTWRL, in the behavior 1, the "xa rollback" should also be blocked. If we design to consider the empty xa transaction as a read-only transaction, in the behavior 2, the "xa rollback" should not be blocked.
[12 Nov 2018 14:19]
MySQL Verification Team
Hi, No, your conclusions are quite wrong. Seems like you are not reading what I am writing. Simply, you have one "ordinary" transaction and one XA transaction. They do not behave the same, as our Reference Manual clearly describes. Needless to mention, running XA transaction on a single server makes no sense. That is all.
[12 Nov 2018 14:32]
dennis gao
Hi Sinisa Milivojevic , I test the xa transaction because I want to write a xa manager to manage the distribution transactions cross several mysqls for our application. I am sorry for the posts before to include not necessary information. I re-write the test description below to only test xa transaction: ================================================================================ situation 1: only has one session do "flush tables with read lock" -------------------------------------------------------------------------------------------------- mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) mysql> XA START '120-0-6-11'; Query OK, 0 rows affected (0.00 sec) mysql> xa end '120-0-6-11'; Query OK, 0 rows affected (0.00 sec) mysql> xa rollback '120-0-6-11'; Query OK, 0 rows affected (0.00 sec) ---------------------------------------------------------------------------------------- Test result shows: the "xa rollback" is allowed under "flush tables with read lock". ======================================================================== ==================================================================== situation 2: two sessions do "flush tables with read lock" at the same time ------------------------------------------------------------------------------------------------------------------------------------ session1 | session2 ==================================================================================================== flush tables with read lock; | flush tables with read lock; XA START '120-0-6-11'; | xa end '120-0-6-11'; | xa rollback '120-0-6-11'; | The "xa rollback" of session1 is blocked due to "Waiting for commit lock". -------------------------------------------------------------------------------------------------------------------------------------------- Test result shows: the "xa rollback" is blocked under "flush tables with read lock" ============================================================================================ situation 1 and situation 2 will get different and conflict conclusions.
[12 Nov 2018 14:36]
MySQL Verification Team
Yes, I understand what you wrote. What you describe is expected behaviour for XA transactions ... XA transactions do not behave like single transactions.
