Bug #64202 XA transaction deadlock rollbacks sends the wrong error code
Submitted: 2 Feb 2012 2:41 Modified: 16 Oct 2012 5:22
Reporter: Clement Pang Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: XA transactions Severity:S2 (Serious)
Version:5.5.20, 5.6.5 OS:Any
Assigned to: CPU Architecture:Any

[2 Feb 2012 2:41] Clement Pang
Description:
When there is an XA transaction rollback due to a deadlock, the error code that's sent back to the client is the generic deadlock rollback error code instead of the XA specific one.

The generic error code is: Error: 1213 SQLSTATE: 40001 (ER_LOCK_DEADLOCK)
The XA error is: Error: 1614 SQLSTATE: XA102 (ER_XA_RBDEADLOCK)

In our Java client, we see the following log messages:

WARN - 2012-02-01 18:01:51,658 - [qtp2077440619-134] (JDBCExceptionReporter.java:233) - SQL Error: 1213, SQLState: 40001
ERROR - 2012-02-01 18:01:51,658 - [qtp2077440619-134] (JDBCExceptionReporter.java:234) - Deadlock found when trying to get lock; try restarting transaction
com.mysql.jdbc.jdbc2.optional.MysqlXAException: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected

Notice how the error message contains the error string: XA_RBDEADLOCK, indicating that it's in an XA transaction that's deadlocking (and rolled back), but the error code that's coming back in 1213 instead of 1614.

How to repeat:
Use XA transactions against a database and induce a deadlock, the error code returned should be 1614 but is instead 1213.

Suggested fix:
Return the proper error code (1614) on XA transaction deadlock.
[16 Feb 2012 15:15] Sveta Smirnova
Thank you for the report.

Please send example of deadlocking XA transaction: we have automatic test which test ER_XA_RBDEADLOCK and it currently passes. So looks like you get deadlock in different way than we imitate.
[2 Mar 2012 1:17] Clement Pang
This bug is quite easy to reproduce, open 2 mysql consoles:

CONSOLE 1:

mysql> XA START 'xatest';
mysql> SELECT * FROM Table1 FOR UDPATE;

CONSOLE 2:

mysql> XA START 'xatest2';
mysql> SELECT * FROM Table2 FOR UPDATE;

CONSOLE 1:
mysql> SELECT * FROM Table2 FOR UPDATE;

CONSOLE 2:
mysql> SELECT * FROM Table1 FOR UPDATE;

At this point, CONSOLE 1 reads:

mysql> select * from Markets for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Note that at this point if the client executes XA END, it would get the correct error code:

mysql> XA END 'xatest';
ERROR 1614 (XA102): XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected

EXPECTED RESULT:

The same error code 1614 is returned consistently.
[3 Mar 2012 15:31] Valeriy Kravchuk
Thank you for the detailed test case. Verified with 5.6.5 also.
[16 Oct 2012 5:22] Erlend Dahl
Analysis from the dev team: 

After discussion with the team we conclude that it is OK to return normal
deadlock during the transaction, but attempt to end such a deadlocked
transaction should end up with XA_RBDEADLOCK. At least, such semantic has
Java DB . Since MYSql has the same behaviour we close this bug.