aspose file tools*
The moose likes JDBC and the fly likes JDBC Transactions across multiple databases Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Transactions across multiple databases" Watch "JDBC Transactions across multiple databases" New topic
Author

JDBC Transactions across multiple databases

Paul Bye
Greenhorn

Joined: Oct 25, 2005
Posts: 10
We need to query/write to two different databases (one mySQL, one Sybase) within the same transaction. I've been reading up on this on Sun's website and google but all the information I can find is related to global transactions running within a J2EE app server.

Our application does not run on a J2EE app server and handles all the connection management and transactions itself.

Can the issue of updating multiple databases be as simple as the code I have written below or am I forgetting something?



any help appreciated thanks.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Paul Bye:


Can the issue of updating multiple databases be as simple as the code I have written below or am I forgetting something?





It is not as simple as you have thought....Just consider a scenario when you issue con1.commit(); on connection for DB1 and this is successful, but somehow con2.commit(); fails, and throws an exception.

Your code will reach in exception block, where you can not rollback a committed transaction.

con1.rollback(); will not rollback a committed transaction.


Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Paul Bye
Greenhorn

Joined: Oct 25, 2005
Posts: 10
Thanks for the reply. After some more thought I've realised it's not as simple as I hoped for.

I'm currently reading up on XATransactions, XADataSources, etc. One of the most confusing Java things I've come across yet.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

you can start reading from here http://docs.sun.com/source/819-0079/dgjts.html

Shailesh
Peter Rooke
Ranch Hand

Joined: Oct 21, 2004
Posts: 803

Isn�t this an example of two phase commit. Just some background information.


Regards Pete
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Originally posted by Peter Rooke:
Isn�t this an example of two phase commit. Just some background information.


That's exactly what it is, hence the reference to XATransactions ;)

Just a side note, you don't necessarily need two-phase commit. You can emulate two-phase commit behaviour safely with non-XA DataSource if you persist the result of the database transaction in the database itself.
[ October 04, 2006: Message edited by: Paul Sturrock ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 861
You are trying to do more than most do with your two phase commit. You are trying a two phase commit across RDBMS vendors. This may require special third party software, if you require your db commands to be transactional across both RDBMS's.


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC Transactions across multiple databases