Thread: Transactions
Hi, 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 :-). Thanks for any help. Cheers Chris -- This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. Any offers or quotation of service are subject to formal specification. Errors and omissions excepted. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of edNET or lightershade ltd. Finally, the recipient should check this email and any attachments for the presence of viruses. edNET and lightershade ltd accepts no liability for any damage caused by any virus transmitted by this email. -- -- Virus scanned by edNET.
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
02-01-30 10.21 Chris Thompson thompson@lightershade.com > I was hoping I could do it in a single transaction block? is this > possible. Everything between BEGIN and END is rolled back unless all actions in the block are completed succesfully. AFAIK that is the definition of a transaction. Apart from that I'm not sure how to connect to a database inside a block, let alone how to switch connections. >If not can anyone suggest any alternatives? I also had thought > about triggers but don't really know enough about them at present :-). How immportant is it that both databases are sychronised? Probably you should look into replication instead. I think there is an Open Source project regarding replicating Postgres. Regards, Vänliga hälsningar Torbjörn Andersson --------------------------------------------------- Embryo Communication phone: +46 (0) 31-774 39 11(00) Kungsgatan 7a fax: +46 (0)31 774 07 80 S-411 19 Göteborg mobile: 0708-30 70 04 Sweden home: http://www.embryo.se/ mail: torbjorn.andersson@embryo.se --------------------------------------------------- "Att idag tänka annorlunda än igår skiljer den vise från den envise." John Steinbeck