MySQL Bugs: #84323: XA-COMMIT may not be blocked by FTWRL

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:	None
Category:	MySQL Server: XA transactions	Severity:	S2 (Serious)
Version:	5.7.16	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	FTWRL, XA-COMMIT

Description:
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
Bye
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
owners.

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)

-------------------------------------------------------

Hi,

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

best regards
Bogdan

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.

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
Bogdan

Posted by developer:
 
The following documentation was updated:

http://dev.mysql.com/doc/refman/5.7/en/flush.html
http://dev.mysql.com/doc/refman/5.7/en/xa-restrictions.html

The changes should appear online soon.

Thank you for the bug report.

Posted by developer:
 
Documentation update:

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