• Post Reply Bookmark Topic Watch Topic
  • New Topic

JDBC Transactions across multiple databases

 
Paul Bye
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Paul Bye
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you can start reading from here http://docs.sun.com/source/819-0079/dgjts.html

Shailesh
 
Peter Rooke
Ranch Hand
Posts: 852
1
Java Linux Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Isn�t this an example of two phase commit. Just some background information.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
steve souza
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Fire me boy! Cool, soothing, shameless self promotion:
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!