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节点执行
