• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

transaction problem involving multiple databases

 
Ranch Hand
Posts: 94
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I was wondering what is the best strategy, if available, to put couple of database updates into one transaction. These update sqls involve several databases, so usual connection.setAutoCommit(false) does not work here. One basic and maybe stupid way is to establish all connections first, and setAutoCommit(false) to all these connections, and after everything is done and successful, setAutoCommit(true)to all these connections. This approach does not apply to my situation, since I need to use some existing java classes, which establish connect5ion and do insertion themself. thanks
[ April 04, 2002: Message edited by: bill williams ]
[ April 04, 2002: Message edited by: bill williams ]
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
sounds like what you want is impossible. you can not have a transaction if the life of each connection involved does not remain open until the end of the transaction. Consider the following transaction:
Step 1. Insert into first DB using someone elses class. You must commit the changes within the method otherwise they will not be inserted into the DB.
Step 2. Update DB 2. OOps, network goes down...or application crashes ( damn Windows! )... there is no way to uncommit the steps taken in step 1.
So you'll have to maintain control of all the connections (and have them remain open) for the life of the transaction. If something goes wrong, you can Connection.rollback() on all the DB connections involved in the transaction.
Jamie
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
just to expand/clear up points on my previous post a little:
Your connections do not all have to be created at the beginning of your program. You can create them as needed. But the connections must last the length of the transaction in order to ensure proper execution of the commit or rollback.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic