Women in Technology

Hear us Roar



Article:
  Adding Transactions to Servlets with JOTM
Subject:   Two phase commit and MySQL ?
Date:   2003-08-08 03:04:46
From:   anonymous2
Last time I looked, MySQL didn't support two-phase commit, so I can't see how it will work together with other resources when some of the transactions fail?


Are any of the other Free Software/Open Source databases able to really support distributed transactions from Java ?


/Sebastian

Full Threads Newest First

Showing messages 1 through 2 of 2.

  • Two phase commit and MySQL ?
    2003-08-12 02:30:56  jeff.mesnil [View]

    In the article, I used XAPool[1] which is a XA-aware JDBC Connection pool. It uses simple JDBC Driver and wraps it in XA objects.
    The advantage is that you can then use any (well, most) databases below (e.g. PostgreSQL, HSQLDB) as long as they support transactional tables.
    MySQL JDBC Driver was supporting XA in version 2.0.14 but it has been dropped from their latest version 3.0.18.
    SAPDB also provides implementation of XA objects.

    jeff

    [1] http://xapool.experlog.com
    • Two phase commit and MySQL ?
      2003-08-14 04:03:01  anonymous2 [View]

      I can understand that the interface is provided, but I can't see how the functionality is provided when the database server lacks support for two-phase commit.

      If I connect to two databases, update a field on the first database, update a field in the second database, then commit the transactions; the transaction manager has to commit the transactions for both databases of course. "Normal" two-phase commit protocol will ask both databases if the transaction will succeed, then commit them unless one of the databases complains. So lets assume the first database is an oracle database and the second is a mysql database. The transaction manager asks both databases if it is okay to commit, but since mysql doesn't support two-phase commit some code along the way has to lie and say that it is okay without asking the database, right? Then it will commit the oracle transaction correctly. When the mysql transaction is to be commited, it says the transaction couldn't be commited for some reason, but by then it is too late, the oracle transaction has already been commited and some recovery/inegrity testing would have to be done to the databases.

      Or have I missunderstood something completly ?