This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of The Journey To Enterprise Agility and have Daryl Kulak & Hong Li on-line!
See this thread for details.
Win a copy of The Journey To Enterprise Agility this week in the Agile and Other Processes forum! And see the welcome thread for 20% off.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Junilu Lacar
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Ganesh Patekar
  • Tim Moores
  • Pete Letkeman
  • Stephan van Hulst
Bartenders:
  • Carey Brown
  • Tim Holloway
  • Joe Ess

JDBC Transactions across multiple databases  RSS feed

 
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.
 
Ranch Hand
Posts: 1084
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: 1084
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
 
Rancher
Posts: 964
9
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.
 
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 ]
 
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.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!