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(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco