ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Clinton Begin" <clinton.be...@gmail.com>
Subject Re: C3P0 configuration example for iBatis anyone?
Date Fri, 30 May 2008 14:35:37 GMT
No offense taken, just trying to be very up front here....

First:  I think it's reasonable for us to add a call to "setQueryTimeout" to
limit the time it takes the query to run... However, I'm not sure this will
solve your problem, as it might be that the call to createStatement is
timing out first (driver dependent).

More important:

There is something seriously wrong on the ground floor of your
architecture.  The following is the code between those log statements
(simplified here with your actual SQL).

Connection realConn = conn.getRealConnection();
Statement statement = realConn.createStatement();
ResultSet rs = statement.executeQuery("select 1 from DUAL");
rs.close();
statement.close();
if (!realConn.getAutoCommit()) {
  realConn.rollback();
}

There is nothing wrong with SimpleDataSource or any other connection pool.
If THAT takes 15 MINUTES regardless of what pool you're using, that is a
serious problem.  There's nothing any pool will be able to safely or cleanly
do.  You might find a way to start slamming connections closed, releasing
resources unsafely etc... it might even work.  But that would just be
skirting around the issue.  I would seek the solution further down in your
architecture.  Why is there a firewall there? Can timeouts on the firewall's
connections be increased or eliminated?  Maybe get your DBA and network
administrator together to discuss this.  It's an unacceptable, unfair and
possibly unnecessary  infrastructure limitation... at least if you want to
build software.  :-)

That said, if you want to continue with this, you can easily implement
whichever datasource you like by creating an implementation of
DataSourceFactory and setting it in the dataSource element of the
SqlMapConfig....

<dataSource type="com.whatever.YourDataSouceFactory">

public interface DataSourceFactory {
  public void initialize(Map map);
  public DataSource getDataSource();
}

If C3P0 doesn't work, you can just make a copy of SimpleDataSource to a
renamed/moved class and introduce a reaper thread to do what you need.

Cheers,
Clinton

On Fri, May 30, 2008 at 5:50 AM, Anton Rothenbacher <tcr49@hotmail.com>
wrote:

>  Hi Clinton, sorry I didn't mean to offend you, it may well be by design
> but it definitely is an issue in our situation. We have a firewall between
> the db and webserver which is closing inactive connections when the app sits
> idle for a period of time. After the idle period, the first person who
> attempts to logon to the website experiences a 15 minute lag while it waits
> for the connection pool to deem the connection it selected out of the pool
> bad, recycle it and start using the fresh connection. We are open to using
> any connection pool mechanism with ibatis that has an option to test idle
> pooled connections and recycle them if they are deemed bad on a configurable
> basis. See below for a log snippit, notice the timestamp delay between when
> it started testing the connection and when it finally failed.
>
> 2008-05-24 21:12:55,720 DEBUG [com.ibatis.common.jdbc.SimpleDataSource]
> Checked out connection 15102369 from pool.
> 2008-05-24 21:12:55,720 DEBUG [com.ibatis.common.jdbc.SimpleDataSource]
> Testing connection 15102369 ...
> 2008-05-24 21:28:21,364 WARN  [com.ibatis.common.jdbc.SimpleDataSource]
> Execution of ping query 'select 1 from DUAL' failed: Io exception:
> Connection timed out
> 2008-05-24 21:28:21,365 DEBUG [com.ibatis.common.jdbc.SimpleDataSource]
> Connection 15102369 is BAD: Io exception: Connection timed out
> 2008-05-24 21:28:21,365 DEBUG [com.ibatis.common.jdbc.SimpleDataSource] A
> bad connection (15102369) was returned from the pool, getting another
> connection.
> 2008-05-24 21:28:21,440 DEBUG [com.ibatis.common.jdbc.SimpleDataSource]
> Created connection 13520697
> 2008-05-24 21:28:21,460 DEBUG [com.ibatis.common.jdbc.SimpleDataSource]
> Testing connection 13520697 ...
> 2008-05-24 21:28:21,463 DEBUG [com.ibatis.common.jdbc.SimpleDataSource]
> Connection 13520697 is GOOD!
> 2008-05-24 21:28:21,463 DEBUG [com.ibatis.common.jdbc.SimpleDataSource]
> Returned connection 13520697 to pool.
>
> I am certainly open to trying
> oracle.jdbc.pool.OracleConnectionPoolDataSource - any suggestions for a
> datasource that has a feature to test idle connections on a configurable
> basis and recycle them (hopefully before a user notices a problem) would be
> great. We only saw SimpleDataSource and DBCP as options in the ibatis
> documentation so that is what we have tried so far (well we did try C3P0
> without success and that is the reason for the post - hopefully to find
> someone who has actually successfully implemented c3p0 or some other
> datasource that allows idle connection testing/eviction). Richard, would you
> have an example SqlMapConfig.xml using this datasource type?
> Thanks again.
>
>
>  ------------------------------
> Date: Thu, 29 May 2008 22:12:22 -0600
> From: clinton.begin@gmail.com
> To: user-java@ibatis.apache.org
> Subject: Re: C3P0 configuration example for iBatis anyone?
>
>
> >> SimpleDatasource exhibits an issue with pool.pingquery taking roughly
> 15min before a bad connection is deemed bad and removed from the pool, there
> seems to be no reaper or evictor thread to test idle connections on a
> regular basis as opposed to testing at checkout time. <<
>
> This is not an issue, it is by design.  SimpleDataSource is a synchronous
> connection pool.  It does not spawn threads for eviction or any other
> reason.
>
> Why do you care when the connection is removed from the pool?
>
> Clinton
>
> On Thu, May 29, 2008 at 9:34 PM, Anton Rothenbacher <tcr49@hotmail.com>
> wrote:
>
> Greetings, the short story is that we have tried DBCP and SimpleDatasource
> with iBatis but had some issues with each, we are connecting to an Oracle
> 10g database.
>
> * SimpleDatasource exhibits an issue with pool.pingquery taking roughly
> 15min before a bad connection is deemed bad and removed from the pool, there
> seems to be no reaper or evictor thread to test idle connections on a
> regular basis as opposed to testing at checkout time.
> * DBCP has an evictor thread that can test and recycle bad connections on a
> regular basis but we were unable to get any decent information in the log
> about it's idle connection test activity (not a huge issue granted but we
> would like to see some detail when there is a bad connection so that we can
> investigate what is going on).
>
> A third option that we are investigating is to try C3P0 to be used as a
> connection pool mechanism to be used by iBatis but I'm unable to find
> documentation or examples on exactly how to configure
> SqlMapConfig.xml/SqlMapConfig.properties to make it work.
>
> I found the following link that suggests it is definitely possible but I'm
> not following exactly how to configure this.
>
>
> *http://www.mail-archive.com/user-java@ibatis.apache.org/msg07957.html*<http://www.mail-archive.com/user-java@ibatis.apache.org/msg07957.html>
> =================================
> On 2/28/07, Clinton Begin <[EMAIL PROTECTED]> wrote:
> Absolutely. You can use any DataSource implementation you like. All you
> need to do is build a DataSourceFactory and pass the fully qualified
> classname (or type alias) into the datasource type in the <dataSource>
> element of your SqlMapConfig.xml
> public interface DataSourceFactory {
> public void initialize(Map map);
> public DataSource getDataSource();
> }
> Clinton
> On 2/28/07, JoeSmith < [EMAIL PROTECTED]> wrote:
> >
> > Is it possible to use the c3p0 datasource/connection pool with
> > iBatis? Does anyone know how this package compares to dbcp? Are there any
> > known issues or problems?
> =================================
>
> Has anyone successfully used C3P0 datasource with ibatis? If so could you
> share an example configuration (or point me to some docs) as to the proper
> config to swap out dbcp or simple datasource with c3p0?
>
> It seems it shouldn't be difficult but google is not being my friend in
> finding information on it. Does it require additional code in the app or
> just config changes in the SqlMapConfig.xml?
> Thanks for any insight you can provide.
>
>
> ------------------------------
> Change the world with e-mail. Join the i'm Initiative from Microsoft.<http://im.live.com/Messenger/IM/Join/Default.aspx?source=EML_WL_ChangeWorld>
>
>
>
> ------------------------------
> Make every e-mail and IM count. Join the i'm Initiative from Microsoft.<http://im.live.com/Messenger/IM/Join/Default.aspx?source=EML_WL_+MakeCount>
>

Mime
View raw message