Need some explanation about XA transactions
Posted by:
Alex A.
Date: July 03, 2008 05:07AM
Hi everyone,
I already read all the documents related to MySQL support for XA distributed transactions. Based on what I understood, I tried to create a global transaction comprising two XA transactions (on tables managed by InnoDB). The version of MySQL is use is 5.0.51 on Linux.
1. On one host, I created then committed a XA transaction:
mysql -h host1 ...
xa start 'xatest', 'b1';
insert into mytable1(...) values(...);
xa end 'xatest', 'b1';
xa prepare 'xatest', 'b1';
xa commit 'xatest', 'b1';
2. On another second host, I created a second XA transaction but didn't commit it:
mysql -h host2 ...
xa start 'xatest', 'b2';
insert into mytable2(...) values(...);
xa end 'xatest', 'b2';
xa prepare 'xatest', 'b2';
3. Then I disconnected from both hosts.
Then I logged again on the first host (host1), and discovered that my partial XA transaction was actually committed. As I understand (I am a newbie on this subject), it should have been rolled back automatically?
From my readings, I understood that my local SQL client acts a a transaction manager. Consequently, if we suppose both XA transactions are in the state 'prepared', the command 'xa recover' should display both XA transactions, but I only see the first (resp. second) XA transaction on host1 (resp. host2). What is wrong with my understanding?
Any help on this issue would be greatly appreciated.
Best regards,
-- Alex