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