Bug #79940 XA with InnoDB allows a table to be dropped while an XA transaction has DML
Submitted: 12 Jan 2016 21:24 Modified: 4 Jul 13:39
Reporter: George Lorch (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: XA transactions Severity:S3 (Non-critical)
Version:5.7.7, 5.7.11 OS:Any
Assigned to: CPU Architecture:Any

[12 Jan 2016 21:24] George Lorch
Description:
If a session creates an XA transaction, does some work, prepares and terminates, it seems to release all locks on the tables referenced from within the XA transaction. This allows another session to drop those tables when in fact the drops should wait for the table locks held by the XA transaction to be relinquished.

Later, when the XA transaction is committed, it receives no error or indication that the data was not committed, thus, potential data loss.

This seems to have been introduced in 5.7.7 for replication according to this blog : http://mysqlserverteam.com/the-mysql-5-7-7-release-candidate-is-available/
Referencing links:
http://dev.mysql.com/worklog/task/?id=6860
http://bugs.mysql.com/bug.php?id=12161

In 5.6, if the session terminated with an XA transaction in the prepared state, the transaction would be aborted and locks freed and thus there would be no way for any other session to drop or alter the table until all locks were released.

How to repeat:
Consider the following sequence on two sessions:

mysql-1> create table t1 (a int) engine=innodb;
Query OK, 0 rows affected (0.09 sec)
mysql-1> insert t1 values (10);
Query OK, 1 row affected (0.01 sec)
mysql-1> insert t1 values (20);
Query OK, 1 row affected (0.00 sec)
mysql-1> select * from t1;
+------+
| a |
+------+
| 10 |
| 20 |
+------+
2 rows in set (0.00 sec)

mysql-2> xa start 'test';
Query OK, 0 rows affected (0.00 sec)
mysql-2> insert t1 values (40);
Query OK, 1 row affected (0.00 sec)
mysql-2> xa end 'test';
Query OK, 0 rows affected (0.00 sec)
mysql-2> xa prepare 'test';
Query OK, 0 rows affected (0.01 sec)
mysql-2> quit;
Bye

mysql-1> select * from t1;
+------+
| a |
+------+
| 10 |
| 20 |
+------+
2 rows in set (0.00 sec)
mysql-1> drop table t1;
Query OK, 0 rows affected (6.73 sec)

mysql-1> show tables;
Empty set (0.00 sec)

mysql-1> xa recover;
+----------+--------------+--------------+-------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+-------+
| 1 | 4 | 0 | test |
+----------+--------------+--------------+-------+
1 row in set (0.00 sec)

mysql-1> xa commit 'test';
Query OK, 0 rows affected (0.01 sec)

mysql-1> xa recover;
Empty set (0.00 sec)
[11 Feb 2016 8:22] Umesh Shastry
Hello George,

Thank you for the report and test case.
Verified as described with 5.7.11 build.

Thanks,
Umesh
[15 Jun 2016 14:07] Marko Mäkelä
MySQL 5.7.7 is slightly better in this regard, because it fixed the bug that a client disconnect would initiate roll back a XA PREPARE transaction:

Bug#12161 Xa recovery and client disconnection
[4 Jul 13:39] Paul Dubois
Posted by developer:
 
Fixed in 8.0.13.

One session could drop tables used by an XA transaction in progress
in another session.