MySQL Bugs: #82253: deadlock xa transaction restarting break the ACID of transaction

Bug #82253	deadlock xa transaction restarting break the ACID of transaction
Submitted:	16 Jul 2016 14:21	Modified:	27 Jul 2016 2:41
Reporter:	dennis gao	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: XA transactions	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	xa deadlock

Description:
In 5.7.13, if one xa transaction get a dead lock error, such as:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

MySQL will still keep this xa transaction, but the only operation for this xa tansaction client can do is to end and rollback it.

Before client end and rollback this restarted xa transaction, client cannot start a new transaction by using "begin" or "xa start".

But before client end this xa transaction, client still can do dml to the mysql
and this dml will be seen by other client immediately.

It seams that the ACID of transaction is broken.

IMO, this behaviour is wrong and confusing for the client.

How to repeat:
Client 1:

MySQL [test]> xa begin '100';
Query OK, 0 rows affected (0.01 sec)

MySQL [test]> delete from sbtest1 where id=100;
Query OK, 1 row affected (0.00 sec)

Client 2:

mysql> xa begin '8';
Query OK, 0 rows affected (0.02 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> delete from sbtest1 where id=8;
Query OK, 1 row affected (0.02 sec)

Then Client 1 try to delete id 8

MySQL [test]> delete from sbtest1 where id=8;
--be blocked

And Client 2 try to delete id 100
mysql> delete from sbtest1 where id=100;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql> xa start '3';
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
mysql> begin;
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
mysql> select * from t2;
Empty set (0.01 sec)

mysql> insert into t2 values (1,2,3);
Query OK, 1 row affected (0.02 sec)

mysql> xa end '8';
ERROR 1614 (XA102): XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected
mysql> xa rollback '8';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 |
+------+------+------+
1 row in set (0.02 sec)

Suggested fix:
Why not rollback the xa transaction directly when it is deadlock?

The client only can do xa end and xa rollback, which IMO can be done by server.

Hi!

We need all the data and commands that you used in order to attempt to repeat your report.

We also require your entire configuration. Best way to provide data is to upload all tables used in the transactions..........

Thanks in advance .....

Just for your information, if it is only about local transactions, MySQL aborts one of the transactions involved in the deadlock automatically.

It can not abort global transaction. That is something that has to be done by DBA, as he / she only knows the precedence in the global transactions.

Global transactions are not managed by the SE, but by the client side.

The key problem is that:

The global xa transaction is not stopped (rollback), the client can not start a
local transaction, but still can do local modification, which is not controlled by the transaction.

Hi all,

I rewrite the bug description to make it more readable.

The test related tables:
mysql> show create table t1;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table                                                                           |
+-------+----------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

mysql> show create table t_xa;
+-------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------+
| t_xa  | CREATE TABLE `t_xa` (
  `c1` int(11) NOT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

mysql> select * from t1;
Empty set (0.06 sec)

mysql> select * from t_xa;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.08 sec)

Then we open two clients:

----------------------------------------------------------------
client 1                                 |       client 2

mysql>use test;                          | 
                                         | mysql>use test;
mysql>xa start '1';                      |   
                                         | mysql>xa start '2';
mysql>delete from t_xa where c1=1;       |
                                         | mysql>delete from t_xa where c1=2;
mysql>delete from t_xa where c1=2;       |
(be blocked)                             |
                                         | mysql>delete from t_xa where c1=1;
                                         | (Deadlock found when trying to get lock; try restarting transaction) 
                                         | mysql>select * from t1;
                                         | (empty result)

                                         | mysql>insert into t1 values (1);

                                         | mysql> xa end '2';
                                         | (XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected)

                                         | mysql> xa rollback '2';
                                       
                                         | mysql> select * from t1;
                                         | (value 1 has been inserted.)

IMO, after "Deadlock found when trying to get lock; try restarting transaction", mysql should refuse to execute any data modification until the xa
transaction is rolled back.

It is really confusing now.

mysql conf file

Attachment: my9999.cnf (application/octet-stream, text), 1.08 KiB.

Hi ,

I have to write once more a comment that  I wrote ... Please, point out at what is the bug, knowing the following:

Just for your information, if it is only about local transactions, MySQL
aborts one of the transactions involved in the deadlock automatically.

It can not abort global transaction. That is something that has to be
done by DBA, as he / she only knows the precedence in the global
transactions.

Global transactions are not managed by the single MySQL server, but by the client program that manages the entire transaction through its interface. 

Some of our connectors, like Connector/J have all the necessary elements in the interface.

I know it is not a big functional problem.

What I want is to adjust the mysql behaviour to make it less confusing and more work as design.

Normally, mysql does not allow to start a local transaction if the xa transaction is not finished.

For deadlock aborted xa transaction, you still can not use "begin/start transaction", so I think mysql still consider it as a running transaction,
and refuse to start a local transaction when the xa transaction is not finished.

But for deadlock aborted xa transaction, it allow the local single stmt transaction to be executed before the xa transaction is finished.
I think it does not work as design, it break the rule(possible):

"does not allow to start a local transaction if the xa transaction is not finished"

IMO, the "bug" also means does not work as expected.

Anyway, if mysql really design to allow the "local single stmt transaction to be executed before the xa transaction is finished", it is not a bug.