>> What happened to returning connection to the pool?
You can't compare the logging output between DataSource implementations. They will be completely different.
So to answer your question: The return of the connection to the pool wasn't logged by DBCP....which doesn't mean it wasn't returned to the pool.
>> I couldn't find in the documentation if/when the connections are closed. Can anyone
>> clarify regarding connections please.
You won't find this information in general, because iBATIS doesn't control that. Each datasource implementation will close connections at their discretion...differently.
You need to read the documentation for the DataSource that you're using.
In the case of SimpleDataSource (the only one included with iBATIS), connections are never closed unless: 1) you call .forceCloseAll(), 2) The VM shuts down and you're using a decent JDBC driver, 3) the connection times out or otherwise becomes invalidated and is discarded.
Cheers,
Clinton
On 6/7/05, vi am <[EMAIL PROTECTED]> wrote:
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: <[EMAIL PROTECTED]>
>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
