connection pool and transaction for the few connections from pool

connection pool and transaction for the few connections from pool

Post by PYCTA » Thu, 16 Dec 2004 08:10:28


hi,

I would like to know whether possible or not to make a transaction
using db connection pool.

I have db connection pool to MS SQL 2000 using Tomcat 5.0's JDBC
DataSource (see on :
http://www.yqcomputer.com/ #Database%20Connection%20Pool%20(DBCP)%20Configurations)

Below is simple example code which I have. So I need to rollback all
data including inserted country and region records to db in case of
exception. Is it possible or not and how it is possible?

public void methodWithTransaction(){
Connection c = null;
Country ctr = new Country();
Region rgn = new Region();

try{
c = Session.getPooledConnection(); // Get connection from pool
c.setAutoCommit(false); // Open transaction

rgn.setCode("CH");
rgn.setName("CHUY");
rgn.doInsert(); // In this method was used
// another connection
// from pool

ctr.setCode("KG");
ctr.setName("Kyrgyzstan");
ctr.setRegion(rgn);
ctr.doInsert(); // In this method was used
// another connection
// from pool too.


//
// IMPORTANT QUESTION HERE !!!
//
// --> So we have used 3 connections
// --> from the pool but transaction
// --> was set only to connection within
// --> this method. What will happen
// --> in case of exception?
// --> Will be inserted country and
// --> region records rolled back?
//
}catch(Exception e){
if (!c.getAutoCommit()){
c.rollback();
c.setAutoCommit(true);
}
}finally{
if (!c.getAutoCommit()){
c.commit();
c.setAutoCommit(true);
}

try{
if (c != null){
c.close();
}
}catch(Exception e){
// do something
}finally{
rgn = null;
ctr = null;
c = null;
}
}
}

sincerely,
rustam bogubaev
 
 
 

connection pool and transaction for the few connections from pool

Post by Joe Weinst » Thu, 16 Dec 2004 08:24:24

Hi. If you use more than one connection in a transaction, you have to
use a JTA UserTransaction, and use XA connections. I am guessing that
these connections are all to the same DBMS, and you don't need XA.
In that case, you need to ensure that all JDBC done within a given
transaction is done one a single connection so you can guarantee that
the tx will commit or rollback together. Otherwise, if you tried to
commit 3 connections, what if the first two committed but the last one
failed to commit?
Joe Weinstein at BEA