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 ]
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
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.
subject: transaction problem involving multiple databases
0
