Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • 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: 848
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic