{"id":298,"date":"2013-08-02T15:56:50","date_gmt":"2013-08-02T07:56:50","guid":{"rendered":"http:\/\/www.gpfeng.com\/?p=298"},"modified":"2013-08-23T23:11:15","modified_gmt":"2013-08-23T15:11:15","slug":"mysql-xa%e9%99%90%e5%88%b6%e4%b8%8e%e4%bf%ae%e5%a4%8d","status":"publish","type":"post","link":"http:\/\/www.gpfeng.com\/?p=298","title":{"rendered":"MySQL XA\u9650\u5236\u4e0e\u4fee\u590d"},"content":{"rendered":"

\u80cc\u666f\u77e5\u8bc6<\/h3>\n

MySQL\u7684XA\u5206\u4e3a\u5185\u90e8XA\u548c\u5916\u90e8XA\uff0c\u91c7\u7528\u7684\u534f\u8bae\u662f\u7ecf\u5178\u76842PC(2-Phase-Commit)<\/p>\n

MySQL\u5185\u90e8XA\u4e3b\u8981\u7528\u4e8e\u591a\u4e2a\u5b58\u50a8\u5f15\u64ce\u4e4b\u95f4\u7684\u4e8b\u52a1\u5904\u7406\uff0c\u7531binlog\u4f5c\u4e3aTransaction Coordinator\uff0c\u4e8b\u52a1\u63d0\u4ea4\u8fc7\u7a0b\u4e2d\u534f\u8c03\u5404\u4e2a\u53c2\u4e0e\u8005\uff08\u4e8b\u52a1\u5b58\u50a8\u5f15\u64ce\uff09prepare\uff0ccommit\/rollback\uff0c\u5404\u4e2a\u4e8b\u52a1\u5b58\u50a8\u5f15\u64ce\u5728prepare\u9636\u6bb5\u57fa\u672c\u505a\u5b8c\u4e86\u6240\u6709\u7684\u4e8b\u60c5\uff08\u9664\u4e86\u5199commit\u6807\u5fd7\uff0c\u91ca\u653e\u8d44\u6e90\uff09\uff0c\u5bf9\u4e8eInnoDB\u5b58\u50a8\u5f15\u64ce\uff0c\u63d0\u4ea4\u4e00\u4e2a\u4e8b\u52a1\u7684\u6d41\u7a0b\u5982\u4e0b\uff1a\uff08\u56fe\u7247\u6765\u81ea\u767b\u535a\uff09<\/p>\n

\"commit\"<\/a>
\n
\nbinlog\u672c\u8eab\u4e5f\u4f1aprepare\/commit\uff0c\u4f46\u662fbinlog\u7684prepare\u9636\u6bb5\u4e0d\u505a\u4efb\u4f55\u4e8b\u60c5\uff08\u610f\u5473\u7740\u4e0d\u53ef\u80fd\u5931\u8d25\uff09\uff0c\u4ee3\u7801\u4e2d\u53ef\u4ee5\u770b\u89c1binlog_prepare\u51fd\u6570\u662f\u7a7a\u7684\uff0c\u5199\u5165binlog\u7684\u8fc7\u7a0b\u662f\u5728commit\u9636\u6bb5\u505a\u7684\uff08\u5728\u5176\u4ed6\u4e8b\u52a1\u5b58\u50a8\u5f15\u64cecommit\u4e4b\u524d\uff09\uff0cbinlog\u5177\u6709\u201c\u539f\u5b50\u201d\u6027\uff1a\u6bcf\u4e2a\u4e8b\u52a1\u4ea7\u751f\u7684binlog\u662f\u8fde\u7eed\u3001\u5b8c\u6574\u7684\u5199\u5165\u5230binlog\u6587\u4ef6\u4e2d\uff0c\u4e8b\u52a1\u4e4b\u95f4\u4e0d\u4f1a\u4ea4\u53c9\u5199<\/strong>\uff08\u4e0eInnoDB\u7684redo log\u4e0d\u540c\uff09\uff0c\u56e0\u6b64\u5728crash recovery\u9636\u6bb5\uff0c\u5982\u679credo log\u4e2d\u672a\u63d0\u4ea4\u7684\u4e8b\u52a1\u80fd\u591f\u5728binlog\u4e2d\u53d1\u73b0\uff0c\u8bf4\u660e\u5176\u5df2\u7ecfprepare\u6210\u529f\uff0c\u53ef\u4ee5\u76f4\u63a5\u63d0\u4ea4\uff0c\u800c\u5982\u679credo\u4e2d\u672a\u63d0\u4ea4\u4e8b\u52a1\u4e0d\u5728binlog\u4e2d\uff0c\u76f4\u63a5\u56de\u6eda\u4e8b\u52a1\uff0c\u8fd9\u6837\u80fd\u591f\u4fdd\u8bc1binlog\u548c\u5b58\u50a8\u5f15\u64ce\u6570\u636e\u7684\u4e00\u81f4\u3002<\/p>\n

MySQL XA\u9650\u5236<\/h3>\n

MySQL\u5916\u90e8XA\u9700\u8981\u5916\u90e8APP\u4f5c\u4e3aTransaction Coordinator\uff0cDBMS\u6210\u4e3a\u8d44\u6e90\u7ba1\u7406\u8005\uff0c\u6b64\u65f6binlog\u6210\u4e3a\u4e86\u4e00\u4e2a\u53c2\u4e0e\u8005\uff0c\u7531\u4e8ebinlog\u7684prepare\u9636\u6bb5\u6ca1\u6709\u5199\u4efb\u4f55\u65e5\u5fd7\uff0cMySQL\u5916\u90e8XA\u5b58\u5728\u5982\u4e0b\u7f3a\u9677\uff1a
\nserver crash\u540e\u91cd\u542f\uff0cXA RECOVER\u53ef\u4ee5\u6062\u590d\u51fa\u672a\u63d0\u4ea4\u7684XA PREPARED\u4e8b\u52a1\uff0c\u4f46\u662f\u63d0\u4ea4\u65f6\u65e0\u6cd5\u8bb0\u5f55binlog\uff08crash\u65f6binlog cache\u4e22\u5931\uff09\uff0c\u8fd9\u5c31\u4f7f\u5f97binlog\u548c\u5b58\u50a8\u5f15\u64ce\u6570\u636e\u4e0d\u4e00\u81f4\uff0c\u4f1a\u5f15\u8d77\u590d\u5236\u4e2d\u65ad<\/strong><\/p>\n

\u8fd9\u4e24\u4e2a\u95ee\u9898\u5728mysql xa limitations<\/a>\u6709\u63d0\u5230\uff0c\u6709\u5173MySQL XA\u5b9e\u73b0\u53ef\u4ee5\u53c2\u8003\u767b\u535a\u7684\u4e00\u7bc7\u535a\u5ba2<\/a><\/p>\n

\u60f3\u8981\u5f7b\u5e95\u4fee\u590d\u8fd9\u4e2a\u95ee\u9898\u9700\u8981\u4fee\u6539binlog\u7684\u903b\u8f91\uff0c\u8fd9\u662f\u4e00\u4e2a\u5927\u5de5\u7a0b\uff0c\u800c\u4e4b\u524d\u8bbe\u8ba1\u7684binlog\u4e0d\u5199prepare\u65e5\u5fd7\u5b9e\u9645\u4e0a\u662f\u4e00\u4e2a\u4f18\u5316\u63aa\u65bd\uff0c\u6bd5\u7adfcrash\u8fd9\u79cd\u4e8b\u60c5\u4e0d\u4f1a\u5f88\u591a\uff0c\u5fc5\u987b\u4f7f\u7528MySQL\u5916\u90e8XA\u7684\u573a\u666f\u4e0d\u591a\uff0c\u56e0\u6b64\u4f7f\u7528MySQL\u5916\u90e8XA\u4e14crash\u7684\u7528\u6237\u9700\u6c42\u88ab\u5ffd\u7565\u4e86\u3002\u3002\u3002<\/p>\n

\u672c\u6587\u4e5f\u4e0d\u6253\u7b97\u4fee\u590d\u8fd9\u4e2a\u95ee\u9898\uff0c\u662f\u4e3a\u4e86\u4fee\u590d\u53e6\u5916\u4e00\u4e2a\u95ee\u9898\uff1a
\nsession\u6267\u884cxa prepare\u4e4b\u540e\u8fde\u63a5\u65ad\u6389\uff0cXA_PREPARED\u4e8b\u52a1\u4f1a\u81ea\u52a8\u56de\u6eda<\/strong><\/p>\n

\u8fd9\u662fMySQL server\u5c42\u7684\u884c\u4e3a\uff0c\u8fde\u63a5\u53ea\u8981\u65ad\u6389\uff0c\u672a\u63d0\u4ea4\u7684\u4e8b\u52a1\u5c31\u4f1a\u81ea\u52a8\u56de\u6eda\uff0c\u4f46\u8fd9\u5bf9\u4e8eXA PREPARED\u4e8b\u52a1\u6709\u4e9b\u4e0d\u53ef\u63a5\u53d7\uff0c\u7adf\u7136XA PREPARED\uff0c\u5c31\u5e94\u8be5\u59cb\u7ec8\u80fd\u591f\u901a\u8fc7\u201cXA RECOVER;\u201d\u627e\u5230\uff0c\u91cd\u65b0\u8fde\u63a5\u540e\u5e94\u8be5\u53ef\u4ee5\u518d\u6b21commit\uff08\u5373\u4f7fcrash\u540e\u90fd\u53ef\u4ee5\u6062\u590d\u5e76\u91cd\u65b0\u6267\u884c\u201cXA COMMIT\u201d\uff09\uff0c\u8fd9\u4e5f\u662fRDS\u56e2\u961f\u63d0\u51fa\u7684\u4e00\u4e2a\u9700\u6c42<\/p>\n

\u89e3\u51b3\u65b9\u6848<\/h3>\n

\u5728MySQL 5.0\u7684\u65f6\u5019\u5c31\u6709\u7528\u6237\u63d0\u51fa\u8fd9\u4e2a\u95ee\u9898<\/a>\uff0c\u5b98\u65b9\u4e00\u76f4\u5728\u8bf4\u8fdb\u884c\u4e2d\uff0c\u4f46\u76f4\u52305.6\u90fd\u6ca1\u6709\u7ed9\u51fa\u89e3\u51b3\u65b9\u6848\uff0c\u5e94\u8be5\u4f18\u5148\u7ea7\u8bbe\u7f6e\u5f97\u6bd4\u8f83\u4f4e\uff0c\u770b\u6765\u53ea\u80fd\u81ea\u5df1\u52a8\u624bfix\u4e86<\/p>\n

a) feng shao\u5728MySQL bug12161<\/a>\u4e2d\u63d0\u51fa\u4e86\u4e00\u4e2aha_semi_rollback_trans\u7684\u65b9\u6848\uff0c\u4f46\u662f\u5b58\u5728\u4e24\u4e2a\u95ee\u9898\uff1a
\n1\u3001\u91cd\u65b0\u8fde\u63a5\u540e\uff0c\u867d\u7136\u80fd\u591f\u901a\u8fc7\u201cXA RECOVER;\u201d\u627e\u56de\u4e8b\u52a1\uff0c\u4f46\u662f\u6267\u884c\u201cXA COMMIT;\u201d\u4e4b\u540ebinlog\u4f1a\u4e22\u5931
\n2\u3001\u53ea\u9488\u5bf9InnoDB\u5f15\u64ce\uff0c\u901a\u7528\u6027\u4e0d\u591f<\/p>\n

\u4e3a\u6b64\uff0c\u63d0\u51fa\u53e6\u5916\u4e00\u4e2a\u65b9\u6848\uff1asession\u8fde\u63a5\u65ad\u5f00\u65f6\uff0c\u6302\u8d77\u5bf9\u5e94\u7684*thd*\uff0c\u5e76\u8fd0\u884c\u5176\u4ed6session\u53ef\u4ee5\u201c\u4ee3\u7406\u201d XA COMMIT\/ROLLBACK<\/p>\n

b) patch\u4e2d\u6240\u6709\u4fee\u6539\u90fd\u5728MySQL Server\u5c42\uff0c\u4e3b\u8981\u6709\u4e00\u4e0b\u51e0\u5904\uff1a
\n1\u3001session\u8fde\u63a5\u65ad\u5f00\u65f6\uff0c\u5224\u65ad\u8fde\u63a5\u4e2d\u662f\u5426\u5b58\u5728XA PREPARED\u72b6\u6001\u7684\u672a\u63d0\u4ea4\u4e8b\u52a1\uff0c\u82e5\u5b58\u5728\uff0c\u5c06\u5176\u5bf9\u5e94\u7684*thd*\u4fdd\u5b58\u5230\u52a8\u6001\u6570\u7ec4xa_prepared_threads\u4e2d
\n2\u3001trans_xa_commit\uff0c\u5224\u65adxid\u662f\u5426\u5c5e\u4e8exa_prepared_threads\u4e2d\u7684\u67d0\u4e2a*thd*\uff0c\u5982\u679c\u662f\uff0c\u4ecexa_prepared_threads\u79fb\u9664\u5bf9\u5e94\u7684*thd*, \u5e76\u63d0\u4ea4\u4e8b\u52a1\uff0cunlink thd
\n3\u3001trans_xa_rollback\uff0c\u5224\u65adxid\u662f\u5426\u5c5e\u4e8exa_prepared_threads\u4e2d\u7684\u67d0\u4e2a*thd*\uff0c\u5982\u679c\u662f\uff0c\u4ecexa_prepared_threads\u79fb\u9664\u5bf9\u5e94\u7684*thd*, \u5e76\u56de\u6eda\u4e8b\u52a1,unlink thd
\n4\u3001kill session\uff0c\u5224\u65adxid\u662f\u5426\u5c5e\u4e8exa_prepared_threads\u4e2d\u7684\u67d0\u4e2a*thd*\uff0c\u5982\u679c\u662f\uff0c\u4ecexa_prepared_threads\u79fb\u9664\u5bf9\u5e94\u7684*thd*, \u5e76unlink thd<\/p>\n

patch\u6548\u679c<\/h3>\n

a) \u6d4b\u8bd5\u8868\u7ed3\u6784<\/p>\n

\r\nmysql> reset master;\r\nQuery OK, 0 rows affected (0.34 sec)\r\n\r\nmysql> show master logs;\r\n+------------------+-----------+\r\n| Log_name         | File_size |\r\n+------------------+-----------+\r\n| mysql-bin.000001 |       107 |\r\n+------------------+-----------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> show create table test.t\\G\r\n*************************** 1. row ***************************\r\n       Table: t\r\nCreate Table: CREATE TABLE `t` (\r\n  `id` int(11) NOT NULL AUTO_INCREMENT,\r\n  `num` int(11) DEFAULT NULL,\r\n  PRIMARY KEY (`id`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=gbk\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select * from test.t;\r\n+----+------+\r\n| id | num  |\r\n+----+------+\r\n|  1 |    1 |\r\n+----+------+\r\n1 row in set (0.00 sec)\r\n<\/pre>\n
b) \u6267\u884cxa prepared\u540e\u65ad\u5f00\u8fde\u63a5<\/p>\n
\r\nmysql> xa start 'xa_2';\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> insert into test.t(num) values(2);\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nmysql> xa end 'xa_2';\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> xa prepare 'xa_2';\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> quit\r\nBye\r\n<\/pre>\n
c) \u91cd\u65b0\u8fde\u63a5\u540e\u901a\u8fc7show processlist\/xa recover\u53ef\u4ee5\u67e5\u770b\u5230pending xa\u4e8b\u52a1\uff08XA_PREPARED\u72b6\u6001\uff09<\/p>\n
\r\n$bin\/mysql -uroot -S run\/mysql.sock \r\nWelcome to the MySQL monitor.  Commands end with ; or \\g.\r\nYour MySQL connection id is 4096\r\nServer version: 5.5.18-tb3633-log Source distribution\r\n\r\nCopyright (c) 2000, 2011, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nOracle is a registered trademark of Oracle Corporation and\/or its\r\naffiliates. Other names may be trademarks of their respective\r\nowners.\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\nmysql> show processlist\\G\r\n*************************** 1. row ***************************\r\n           Id: 4095\r\n         User: root\r\n         Host: localhost\r\n           db: NULL\r\n      Command: Killed\r\n         Time: 17\r\n        State: xa prepared, waiting for xa commit\/rollback\r\n         Info: NULL\r\n    Rows_sent: 0\r\nRows_examined: 0\r\n    Rows_read: 2\r\n*************************** 2. row ***************************\r\n           Id: 4096\r\n         User: root\r\n         Host: localhost\r\n           db: NULL\r\n      Command: Query\r\n         Time: 0\r\n        State: NULL\r\n         Info: show processlist\r\n    Rows_sent: 0\r\nRows_examined: 0\r\n    Rows_read: 1\r\n2 rows in set (0.00 sec)\r\n\r\nmysql> xa recover;\r\n+----------+--------------+--------------+------+\r\n| formatID | gtrid_length | bqual_length | data |\r\n+----------+--------------+--------------+------+\r\n|        1 |            4 |            0 | xa_2 |\r\n+----------+--------------+--------------+------+\r\n1 row in set (0.00 sec)\r\n<\/pre>\n
d) \u2018\u4ee3\u7406\u2019\u63d0\u4ea4\u65ad\u5f00\u8fde\u63a5\u7684xa prepared\u4e8b\u52a1<\/p>\n
\r\nmysql> xa commit 'xa_2';\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> select * from test.t;\r\n+----+------+\r\n| id | num  |\r\n+----+------+\r\n|  1 |    1 |\r\n|  2 |    2 |\r\n+----+------+\r\n2 rows in set (0.00 sec)\r\n\r\nmysql> xa recover;\r\nEmpty set (0.00 sec)\r\n\r\nmysql> show processlist\\G\r\n*************************** 1. row ***************************\r\n           Id: 4096\r\n         User: root\r\n         Host: localhost\r\n           db: NULL\r\n      Command: Query\r\n         Time: 0\r\n        State: NULL\r\n         Info: show processlist\r\n    Rows_sent: 0\r\nRows_examined: 0\r\n    Rows_read: 3\r\n1 row in set (0.00 sec)\r\n<\/pre>\n
e) commit \u540ebinlog\u6210\u529f\u5199\u5165\uff0c\u5982\u679c\u5728xa commit\u524d crash\u5c06\u4e22\u5931binlog<\/p>\n
\r\nmysql> show binlog events in 'mysql-bin.000001';\r\n+------------------+-----+-------------+-----------+-------------+----------------------------------------------+\r\n| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                         |\r\n+------------------+-----+-------------+-----------+-------------+----------------------------------------------+\r\n| mysql-bin.000001 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.18-tb3633-log, Binlog ver: 4 |\r\n| mysql-bin.000001 | 107 | Query       |         1 |         171 | BEGIN                                        |\r\n| mysql-bin.000001 | 171 | Table_map   |         1 |         212 | table_id: 186 (test.t)                       |\r\n| mysql-bin.000001 | 212 | Write_rows  |         1 |         250 | table_id: 186 flags: STMT_END_F              |\r\n| mysql-bin.000001 | 250 | Query       |         1 |         315 | COMMIT                                       |\r\n+------------------+-----+-------------+-----------+-------------+----------------------------------------------+\r\n5 rows in set (0.00 sec)\r\n<\/pre>\n
patch\uff1a<\/h3>\n
xa_prepared_disconnect.patch<\/a>(\u57fa\u4e8ePercona 5.5.18)<\/p>\n
\u6b22\u8fce\u8ba8\u8bba\uff1a<\/h3>\n
percona bug 1204353<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
\u80cc\u666f\u77e5\u8bc6 MySQL\u7684XA\u5206\u4e3a\u5185\u90e8XA\u548c\u5916\u90e8XA\uff0c\u91c7\u7528\u7684\u534f\u8bae\u662f\u7ecf\u5178\u76842PC(2-Phase-Commit) M … \u7ee7\u7eed\u9605\u8bfbMySQL XA\u9650\u5236\u4e0e\u4fee\u590d<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[1],"tags":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p3tPZp-4O","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/298"}],"collection":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=298"}],"version-history":[{"count":26,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/298\/revisions"}],"predecessor-version":[{"id":401,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/298\/revisions\/401"}],"wp:attachment":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=298"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=298"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=298"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}