Bug #84323 XA-COMMIT may not be blocked by FTWRL
Submitted: 23 Dec 2016 10:30 Modified: 18 Jan 2017 18:50
Reporter: Wei Zhao (OCA) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: XA transactions Severity:S2 (Serious)
Version:5.7.16 OS:Any
Assigned to: CPU Architecture:Any

[23 Dec 2016 10:30] Wei Zhao
Normally XA-COMMIT (as well as the 'commit' stmt) acquires the global COMMIT lock (in INTENTION_EXCLUSIVE mode) which conflicts with the COMMIT lock( in SHARED mode) acquired by FTWRL stmt, so that while a FTWRL is executed, no transaction can commit.

However, if XA-COMMIT is issued in another session than the one where the XA transaction branch executed, then it doesn't acquire the COMMIT lock anymore and FTWRL won't be able to block it. See below test case for details.

How to repeat:
---------------- Session 1 -----------------

mysql> xa start '125';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(44,44);
Query OK, 1 row affected (0.01 sec)

mysql> xa end '125';
Query OK, 0 rows affected (0.00 sec)

mysql> xa prepare '125';
Query OK, 0 rows affected (0.00 sec)

------<<<<<<<<<<<<<<<------ in session 2, do FTWRL after the xa prepare stmt, as shown in the "Session 2" section below.  ------

mysql> quit
david@david-VirtualBox:~/mysql_installs/mysql-5.7.16$ ./bin/mysql -S /home/david/mysql-instances/e/mysql.sock -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.16-debug-log Source distribution

Copyright (c) 2000, 2016, 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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> xa recover;
| formatID | gtrid_length | bqual_length | data |
|        1 |            3 |            0 | 125  |
1 row in set (0.00 sec)

mysql> xa commit '125';   ---- xa commit should have been blocked by the FTWRL stmt in session 2.
Query OK, 0 rows affected (0.00 sec)

------------------- Session 2 -----------------

mysql> xa recover;
| formatID | gtrid_length | bqual_length | data |
|        1 |            3 |            0 | 125  |
1 row in set (0.00 sec)

mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)

[11 Jan 2017 15:34] Bogdan Kecman

Thanks for your report but FTWRL is not compatible with XA transactions. FTWRL with InnoDB does not "lock", it just "prevents commits"

best regards
[13 Jan 2017 3:45] Wei Zhao
But the very problem is exactly that FTWRL is unable to block 'xa commit' from committing a transaction branch, if 'xa commit' is executed not in the same user session as the rest of the transaction branch, but later in a new one. 

And this means that backup tools like percona xtrabackup (and probably also MySQL enterprise backup) won't be able to work correctly after an unexpected mysqld restart(crash, power outage) when there are xa transaction branches prepared via 'xa prepare', but not yet done 'xa commit'.

So I do think it is a bug.
[16 Jan 2017 11:28] Bogdan Kecman
I'll verify this as documentation bug as from documentation is not clear how flush table with read lock "don't work" with innodb and how it's incompatible with XA transactions.

kind regards
[18 Jan 2017 18:50] Daniel Price
Posted by developer:
The following documentation was updated:


The changes should appear online soon.

Thank you for the bug report.
[18 Jan 2017 19:08] Daniel Price
Posted by developer:
Documentation update:

"FLUSH TABLES WITH READ LOCK is not compatible with XA transactions."