2

I am working at a Spring Java EE application that has to deal with two different datasources, A and B.

As the application needs to update A and B in a consistent way. If one update fails, the whole process fails and a rollback must take place.

I have two different idea on how to implement the application:

  1. I have to enclose both the updates in a distributed transaction XA. This approach is going to be expensive in terms of performances. Moreover, the B source will be switched off soon, and keeping the whole XA infrastracture could be a bottle neck;
  2. I could set up an Oracle DB link from db A to db B, and let my application believe that is working just with a single datasource and a local transaction, while Oracle deals with the update synchronization. When B will be switched off, I will simply remove the B update and switch off the DB Link.

What do you think about those two scenario?

Arjan Tijms
  • 37,782
  • 12
  • 108
  • 140
Ameba Spugnosa
  • 1,204
  • 2
  • 11
  • 25
  • I don't quite understand point 2, you're still going to have to update two tables on two database. However, it does imply that you might be willing to consider option 3, a [materialized view](http://docs.oracle.com/cd/E11882_01/server.112/e25789/schemaob.htm#CNCPT411). If it doesn't matter if B is slightly behind you could update only A and have a materialized view on B pulling the data from A over a DB link... – Ben Dec 27 '12 at 10:14
  • @Ben my understanding of dblink is that they provide you a way to access to remote tables as they were local, isn't it? So I guessed I could update my remote table without regarding about distributed TX issues, but I am probably wrong... – Ameba Spugnosa Dec 27 '12 at 10:39
  • Sort of, yes but it's not like you're working with a single data-source. If you have to update two tables on two databases they still need to be updated separately... – Ben Dec 27 '12 at 10:41

1 Answers1

0

A query that uses a DB link will start a distributed transaction in Oracle implicitly. So you get the same overhead as dealing with XA and two datasources from your application.

We are using XA all the time, and it has never been a bottleneck. Don't worry.

u1957752
  • 26
  • 2