xa事务异常问题处理

 MGR集群 xa事务异常问题处理

现象：

select * from
performance_schema.data_locks;

oms库中有三个表一直锁着

被锁的数据

select * from
oms.a where id=702;
select * from oms.b where
id=766;
select * from oms.c where
id=914;

select * from
Performance_schema.data_lock_waits;   #无数据等待

processlist无异常SQL

分布式事务异常

xa recover

mysql> xa recover;

+----------+--------------+--------------+-------------------------------------------------------------+

| formatID |
gtrid_length | bqual_length | data                                                       
|

+----------+--------------+--------------+-------------------------------------------------------------+

|     9752 |           39 |           20 |
172.16.85.175:18091:1387412165398013222-1387412165284095289 |

|     9752 |           39 |           20 |
172.16.85.175:18091:1387412165398013222-1387412165284095286 |

|     9752 |           39 |           20 |
172.16.85.175:18091:1387412165398013222-1387412165284095284 |

+----------+--------------+--------------+-------------------------------------------------------------+

3 rows in set (0.00
sec)

拼接回滚脚本：

xa rollback 'left(data,gtrid_length)'，'substr(data,gtrid_length+1,bqual_length)',
formatID;

left(data,gtrid_length):left('172.16.85.175:18091:1387412165398013222-1387412165284095289',
39)

substr(data,gtrid_length+1,bqual_length)：substr('172.16.85.175:18091:1387412165398013222-1387412165284095289',39+1,20)

mysql> select
left('172.16.85.175:18091:1387412165398013222-1387412165284095289', 39) ;

+-------------------------------------------------------------------------+

|
left('172.16.85.175:18091:1387412165398013222-1387412165284095289', 39) |

+-------------------------------------------------------------------------+

| 172.16.85.175:18091:1387412165398013222                                 |

+-------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select
substr('172.16.85.175:18091:1387412165398013222-1387412165284095289',39+1,20);

+-------------------------------------------------------------------------------+

|
substr('172.16.85.175:18091:1387412165398013222-1387412165284095289',39+1,20) |

+-------------------------------------------------------------------------------+

|
-1387412165284095289                                                        
 |

+-------------------------------------------------------------------------------+

1 row in set (0.00
sec)

mysql>

xa rollback
'172.16.85.175:18091:1387412165398013222','-1387412165284095289', 9752;# parmary节点执行