ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "vi am" <vi...@hotmail.com>
Subject RE: Connection leak
Date Tue, 07 Jun 2005 22:50:06 GMT
By mistake, there are two threads for this topic... I will try to use only 
this thread now.

I turned on the DEBUG feature... and the seemingly inconsistent behavior  is 
causing confusion.  If I use "SIMPLE",  on one try the connections were 
getting created and closed.  I could verify this using a SQL query against 
the v$ tables in Oracle database.   On another try, "DBCP" worked 
perfectly.... promptly returning connections to the pool.   However, I 
couldn't reproduce the same on my next try!

If I had,  <dataSource type="SIMPLE">,  here is the DEBUG output (which is 
the source of confusion):

DEBUG [http8090-Processor4] - Created connection 6189088.
DEBUG [http8090-Processor4] - {conn-100000} Connection
DEBUG [http8090-Processor4] - {pstm-100001} PreparedStatement:      select
     first_name, last_name     from  vj_batis
DEBUG [http8090-Processor4] - {pstm-100001} Parameters: []
DEBUG [http8090-Processor4] - {pstm-100001} Types: []
DEBUG [http8090-Processor4] - {rset-100002} ResultSet
DEBUG [http8090-Processor4] - {rset-100002} Header: [FIRST_NAME, LAST_NAME]
DEBUG [http8090-Processor4] - {rset-100002} Result: [jessica, simpson]
DEBUG [http8090-Processor4] - {rset-100002} Result: [paris, hilton]
DEBUG [http8090-Processor4] - Returned connection 6189088 to pool.

**** I was NOT expecting  "Returned Connection to pool".


*************************************************

If I had,  <dataSource type="DBCP">,  here is the DEBUG output  (again, 
source of confusion):

WebappClassLoader:   Resource 
'/WEB-INF/classes/batis_maps/sql-map-config.xml'
was modified; Date is now: Tue Jun 07 15:28:08 PDT 2005 Was: Tue Jun 07 
15:26:05 PDT 2005
DEBUG [Finalizer] - SimpleDataSource forcefully closed/removed all 
connections.
DEBUG [Finalizer] - SimpleDataSource forcefully closed/removed all 
connections.
DEBUG [Finalizer] - SimpleDataSource forcefully closed/removed all 
connections.
DEBUG [Finalizer] - SimpleDataSource forcefully closed/removed all 
connections.
DEBUG [http8090-Processor3] - {conn-100000} Connection
DEBUG [http8090-Processor3] - {pstm-100001} PreparedStatement:      select
     first_name, last_name     from  vj_batis
DEBUG [http8090-Processor3] - {pstm-100001} Parameters: []
DEBUG [http8090-Processor3] - {pstm-100001} Types: []
DEBUG [http8090-Processor3] - {rset-100002} ResultSet
DEBUG [http8090-Processor3] - {rset-100002} Header: [FIRST_NAME, LAST_NAME]
DEBUG [http8090-Processor3] - {rset-100002} Result: [jessica, simpson]
DEBUG [http8090-Processor3] - {rset-100002} Result: [paris, hilton]

What happened to returning connection to the pool?   There were no extra 
connections created in the database.   This means... even if it was DBCP,  a 
connection was created and closed.  But, the DEBUG did not show this !?


Here are the contents of "sql-map-config.xml" file:

***********************************************
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" 
"http://www.ibatis.com/dtd/sql-map-config-2.dtd">

<sqlMapConfig>

  <properties resource="properties/database.properties"/>

  <settings
    cacheModelsEnabled="true"
    enhancementEnabled="true"
    maxSessions="50"
    maxTransactions="30"
    maxRequests="2"/>


   <transactionManager type="JDBC">
       <dataSource type="SIMPLE">
           <property name="JDBC.Driver"  value="${driver}"/>
           <property name="JDBC.ConnectionURL" value="${url}"/>
           <property name="JDBC.Username" value="${username}"/>
           <property name="JDBC.Password" value="${password}"/>
           <property name="Pool.MaximumActiveConnections" value="5"/>
           <property name="Pool.MaximumIdleConnections" value="2"/>
           <property name="Pool.MaximumCheckoutTime" value="60000"/>
           <property name="Pool.TimeToWait" value="7000"/>
           <property name="Pool.PingQuery" value="select 1 as ISPINGWORKING 
from dual"/>
           <property name="Pool.PingEnabled" value="true"/>
           <property name="Pool.PingConnectionsOlderThan" value="1000"/>
           <property name="Pool.PingConnectionsNotUsedFor" value="1000"/>
    </dataSource>
  </transactionManager>


  <sqlMap resource="batis_maps/Account.xml"/>
  <sqlMap resource="batis_maps/Product.xml"/>

</sqlMapConfig>

***********************************************

Any ideas on what I might be doing wrong?  Thanks for your help.




>From: <erlend.bjorge@aftenposten.no>
>Reply-To: ibatis-user-java@incubator.apache.org
>To: <ibatis-user-java@incubator.apache.org>
>Subject: SV: Connection leak
>Date: Tue, 7 Jun 2005 09:20:37 +0200
>
>Hi there!
>
> >Is there a way to limit the maximum number of connections (at any given
> >time) Apache's DBCP pool?
>
>We haven't used that one, but have you tried the simple datasource ?
>Is it the same behaviour ?
>
>Our config look like this:
>
>         <dataSource type="SIMPLE">
>             <property name="JDBC.Driver" value="${driver}"/>
>             <property name="JDBC.ConnectionURL" value="${url}"/>
>             <property name="JDBC.Username" value="${username}"/>
>             <property name="JDBC.Password" value="${password}"/>
>             <property name="Pool.MaximumActiveConnections" value="30"/>
>             <property name="Pool.MaximumIdleConnections" value="0"/>
>             <property name="Pool.MaximumCheckoutTime" value="60000"/>
>             <property name="Pool.TimeToWait" value="7000"/>
>             <property name="Pool.PingQuery" value="select 1 as 
>ISPINGWORKING from dual"/>
>             <property name="Pool.PingEnabled" value="true"/>
>             <property name="Pool.PingConnectionsOlderThan" value="1000"/>
>             <property name="Pool.PingConnectionsNotUsedFor" value="1000"/>
>
>We use Oracle too, 9.2 ... With the classes12.jar driver.
>
>With the ping stuff .... You will also get something like this when you are 
>in debug mode for log4j:
>
>DEBUG 09:02:43,041 [main] com.ibatis.common.jdbc.SimpleDataSource - Testing 
>connection 25942001...
>DEBUG 09:02:43,051 [main] com.ibatis.common.jdbc.SimpleDataSource - 
>Connection 25942001 is GOOD!
>DEBUG 09:02:43,061 [main] com.ibatis.common.jdbc.SimpleDataSource - Closed 
>connection 25942001.
>
>When you have explisit transaction handling:
>
>             sqlMap.startTransaction();
>             sqlMap.commitTransaction();
>		sqlMap.endTransaction();
>
>So when you get closed connection, it's all cleaned up.
>
>---
>
>When you have implisit transaction handling... The connection get closed 
>automatically.
>Don't you see that in debug mode with log4j ??
>
>--
>
>I also recommend you to e.g use a dynamic proxy ... Then you can call 
>sqlMap.endTransaction() in a finally
>clause in the invoke() method and you make sure the connection is returned 
>independently if you use
>explisit and implisitt transaction handling ... And you don't have to call 
>endTransaction() yourself.
>
>AND you also can introduce automatically rollback when an exception happens 
>in your DAO class e.g. very
>nice feature... So you don't have to program a rollback yourself each time, 
>when you have a write transaction.
>
>Let me know if you want some code examples of dynamic proxy.
>
>
>Take care,
>Erlend Bjørge



Mime
View raw message