Re: distributed transactions

From: Michael Gentry (michael_gentr..anniemae.com)
Date: Fri Nov 07 2003 - 09:28:54 EST

  • Next message: Mike Kienenberger: "Re: prefetch & many-to-many"

    I haven't really looked at the code or tried to use multiple DBs, but it
    seems to me a practical (although not failsafe) approach would be to do
    all of the INSERT/UPDATE/DELETE statements across all of the DBs and
    then do the COMMITs across all of the DBs. You can detect when there is
    an error updating the data and rollback everything as one combined
    transaction. If all of the INSERT/UPDATE/DELETE statements succeed,
    then do all of the COMMITs, which will most likely succeed (no real
    reason why it shouldn't at that point). Of course, there is a chance
    that something will happen before you can get all the COMMIT statements
    processed (you process the first and then try to process the second, but
    you have lost the second DB connection, etc), but that window is pretty
    small. It might be a fair trade-off if you know the risks involved.

    Comments?

    /dev/mrg

    Andrus Adamchik wrote:

    >
    > On Nov 6, 2003, at 11:15 AM, Mike Kienenberger wrote:
    >
    >> Andrus Adamchik <andru..bjectstyle.org> wrote:
    >>
    >>> Cayenne definitely supports access of multiple databases from the same
    >>> DataContext. I believe there is still an (easy to fix) issue in the
    >>> DataContext underlying commit mechanism that internally commits
    >>> individual DB transactions independently. It is on my list of things to
    >>> fix.
    >>
    >>
    >> Just out of curiosity, is there a generic technique for committing a
    >> "distributed" transaction atomically? Or can that only happen on a
    >> per-database basis?
    >
    >
    >
    > This works on assumption that the SQL statements fail early if things
    > go wrong, not waiting till commit is executed, and do not fail in
    > "commit" itself. So if things fail while running statements over one
    > of the multiple connections, we still have a chance to rollback all
    > others as well.
    >
    > I must admit, this is probably too naive. E.g. on Oracle you can have
    > deferred constraint checking... I would imagine it failing in commit
    > if constraints are not satisfied.
    >
    > A more robust implementation would require using JTA, which we are
    > planning to do.
    >
    > Andrus
    >
    >
    >



    This archive was generated by hypermail 2.0.0 : Fri Nov 07 2003 - 09:29:01 EST