connection pool and transaction for the few connections from pool

From: PYCTAM (rbogubaev_at_bookinturkey.com)
Date: 12/15/04 

Date: 14 Dec 2004 15:10:28 -0800

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

Relevant Pages

  • Re: Close and Dispose argument
    ... > fact that even Microsoft is not clear on the issue. ... > connections from the pool is still a debate. ... > method removes the conneciton from the connection pool". ... > Your issue was forwarded to us here at Microsoft Courseware Support. ...
    (microsoft.public.dotnet.framework.adonet)
  • Close and Dispose argument
    ... here is an email conversation between me and Microsoft ... connections from the pool is still a debate. ... Programming with Microsoft ADO.NET" module 2, p 18: "Calling the Dispose ... method removes the conneciton from the connection pool". ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: MinPoolSize behaviour
    ... Min Pool Size, The first time you open a connection we will open the 1 ... > Is the pool created forever? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Q:Multithreaded ADO.Net Connections Are Non Pooled?
    ... different you get a new pool. ... > the process/app domain differences, does running on a thread created by ... If you're using SSPI, each connection must be ... >> connect to the SQL Server machine, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Connection pooling and changed password issue
    ... There is no "clear the pool" or "drop the pool" until ADO.NET 2.0. ... > Hi - so a previously made connection with the old credentials will still ... > "William Vaughn" wrote: ... >>> username/password then the connection pool will be used and the same ...
    (microsoft.public.dotnet.framework.adonet)