Re: connection pool and transaction for the few connections from pool

• Previous message: PYCTAM: "connection pool and transaction for the few connections from pool"
• In reply to: PYCTAM: "connection pool and transaction for the few connections from pool"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: Tue, 14 Dec 2004 15:24:24 -0800
To: PYCTAM <rbogubaev@bookinturkey.com>

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

PYCTAM wrote:

> 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://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-datasource-examples-howto.html#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
>

• Previous message: PYCTAM: "connection pool and transaction for the few connections from pool"
• In reply to: PYCTAM: "connection pool and transaction for the few connections from pool"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]