On Oct 14, Haihao Tang wrote:
> Hi, Sergei, thanks for your reply.
> Resources mentioned here is like records are locked by InnoDB.
> In status PREPARE, I think InnoDB should lock the records that
> transaction needed.
InnoDB takes locks as necessary in the ACTIVE state.
It keeps them in the IDLE and PREPARE state and release only when
> What about status IDLE? Same as status PREPARE?
> I am investigating whether MySQL XA could apply to financial system.
> Are there any successful cases that using MySQL XA to achieve DTP?
Yes, so I've heard, although I don't have any names :(
> Compare with ORACLE in DTP, what is the main inferior position of MySQL XA?
Binlog does not support 2PC. So, basically, if you use MySQL XA (I mean,
MySQL "external" XA) and MySQL crashes after prepare a transaction will
not get into binlog. Even if after restart the TM will ask MySQL to
commit. InnoDB will be fine - it will recover and it will let the TM to
commit or rollback the prepared transaction. But binlog will not :(
Also MySQL does not implement all XA commands, few of them are not
supported. But it's much less on an issue. You almost never use MySQL
XA directly, but rely on a TM to use it - and popular TMs (see below) don't
use the features that MySQL doesn't have (that's why we didn't implement
them in the first place).
> Are there any mature Transaction Manger(TM) to combine with MySQL XA?
Yes, with JTA (Java Transaction API).
But I'm not a java person, I know very little about it.
Search for MysqlXADataSource and you'll find plenty of references
mentioning Tomcat, JBoss, JOnAS, and so on.