JDBC Transactions across multiple databases

[Paul Bye]

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?

try {
  Connection con1 = ... Get connection for DB1
  Connection con2 = ... Get connection for DB2
 
  con1.setAutoCommit(false);
  con2.setAutoCommit(false);
 
  Statement st1 = con1.createStatement();
  ... etc, etc, etc, Do all the DB releated stuff here...
 
  con1.commit();
  con2.commit();
  con1.close();
  con2.close();
}
catch (Exception e) {
  con1.rollback();
  con2.rollback();
}

any help appreciated thanks.

[Shailesh Chandra]

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?

try {
  Connection con1 = ... Get connection for DB1
  Connection con2 = ... Get connection for DB2
 
  con1.setAutoCommit(false);
  con2.setAutoCommit(false);
 
  Statement st1 = con1.createStatement();
  ... etc, etc, etc, Do all the DB related stuff here...
 
  con1.commit();
  con2.commit();
  con1.close();
  con2.close();
}
catch (Exception e) {
  con1.rollback();
  con2.rollback();
}

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]

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]

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

Shailesh

[Peter Rooke]

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

[Paul Sturrock]

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]

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.