Re: Transactions

Chris,

> I am wondering how I would go about updating 2 database's in one go,
> safely.
>
> The databases are in different locations and so the network is a
>  point of
> failure.
>
> I was hoping I could do it in a single transaction block? is this
> possible. If not can anyone suggest any alternatives? I also had
>  thought
> about triggers but don't really know enough about them at present
>  :-).

You cannot do this though SQL, which, due to the SQL92 spec, is limited
 to one database only.  You will have to involve another programming
 language.

If this multi-database interaction is crucial to your application, then
 you will need to implement a full middleware layer to stand between
 the interface and the databases.  Such a layer would best be developed
 in Java or Perl::DBI, but you can do COM or .NET if you're a glutton
 for punishment :-)    For Java and Perl, there are several application
 development frameworks, such as Enhydra, that will help you do this.

With a middleware layer, then, you can do the following in your
 middleware code:
1. Start the update on database #1 without committing;
2. Start the update on database #2 without committing;
3. Test for failure. If either fails, rollback.
4. If both succeed, commit both.

For widely dispersed database servers (e.g. one in California, one in
 New Zealand) you'd need to take into account the slow nature of the
 connection and use messaging protocols and a 2-phase commit strategy
 rather than holding the transaction open as above.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco