Connection pooling

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Connection pooling

Gérald Quintana
Hello,

For some weird reason I won't explain, the Oracle database I am using
is restarted
every night. Since I introduced Bitronix connection pooling (the
problem didn't occur with DBCP), every morning the application is
dead: Each access to the database results in the following exception:
Caused by: java.sql.SQLException: Connexion interrompue
       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
       at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:1170)
       at oracle.jdbc.OracleConnectionWrapper.rollback(OracleConnectionWrapper.java:122)
       at bitronix.tm.resource.jdbc.JdbcConnectionHandle.rollback(JdbcConnectionHandle.java:96)
       ... 9 more

Restarting the application server (Tomcat) does the trick, but it is
not satisfactory solution.
Why do these stale connections remain in the pool?
What can I do to evict them?

Thanks for your help,
Gérald

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Connection pooling

Ludovic Orban
Administrator
You should set a test query on your connection pool, see: http://btm.codehaus.org/api/1.3.3/bitronix/tm/resource/jdbc/PoolingDataSource.html#setTestQuery%28java.lang.String%29

2009/10/27 Gérald Quintana <[hidden email]>
Hello,

For some weird reason I won't explain, the Oracle database I am using
is restarted
every night. Since I introduced Bitronix connection pooling (the
problem didn't occur with DBCP), every morning the application is
dead: Each access to the database results in the following exception:
Caused by: java.sql.SQLException: Connexion interrompue
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
      at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:1170)
      at oracle.jdbc.OracleConnectionWrapper.rollback(OracleConnectionWrapper.java:122)
      at bitronix.tm.resource.jdbc.JdbcConnectionHandle.rollback(JdbcConnectionHandle.java:96)
      ... 9 more

Restarting the application server (Tomcat) does the trick, but it is
not satisfactory solution.
Why do these stale connections remain in the pool?
What can I do to evict them?

Thanks for your help,
Gérald

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email



Reply | Threaded
Open this post in threaded view
|

Re: Connection pooling

Gérald Quintana
Thanks for your answer Ludovic,

At first, I imagined this solution. But I think it would be bad for
performances: I loose connections only once a day, and the test query
would be executed all day long (too many times). What's your
experience/opinion?

Then during the night, the application is not used anymore so I don't
understand why connections are kept opened.

Finally is there something (JMX MBean or other) to control (I mean
monitor opened connection, force closing) connection pools described
in resources.properties?


Gérald

2009/10/27 Ludovic Orban <[hidden email]>:

> You should set a test query on your connection pool, see:
> http://btm.codehaus.org/api/1.3.3/bitronix/tm/resource/jdbc/PoolingDataSource.html#setTestQuery%28java.lang.String%29
>
> 2009/10/27 Gérald Quintana <[hidden email]>
>>
>> Hello,
>>
>> For some weird reason I won't explain, the Oracle database I am using
>> is restarted
>> every night. Since I introduced Bitronix connection pooling (the
>> problem didn't occur with DBCP), every morning the application is
>> dead: Each access to the database results in the following exception:
>> Caused by: java.sql.SQLException: Connexion interrompue
>>       at
>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
>>       at
>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
>>       at
>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
>>       at
>> oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:1170)
>>       at
>> oracle.jdbc.OracleConnectionWrapper.rollback(OracleConnectionWrapper.java:122)
>>       at
>> bitronix.tm.resource.jdbc.JdbcConnectionHandle.rollback(JdbcConnectionHandle.java:96)
>>       ... 9 more
>>
>> Restarting the application server (Tomcat) does the trick, but it is
>> not satisfactory solution.
>> Why do these stale connections remain in the pool?
>> What can I do to evict them?
>>
>> Thanks for your help,
>> Gérald
>>
>> ---------------------------------------------------------------------
>> To unsubscribe from this list, please visit:
>>
>>    http://xircles.codehaus.org/manage_email
>>
>>
>
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Connection pooling

Ludovic Orban
Administrator
The performance hit of the test query usually isn't that high.

As an alternative if you know for sure the DB is restarted when there are no users you could set the minPoolSize to zero, make sure the maxIdleTime isn't too high and also make sure the pool is left untouched long enough for all connections to become inactive. The pool will then close them all and re-open them when activity starts over again.

2009/10/27 Gérald Quintana <[hidden email]>
Thanks for your answer Ludovic,

At first, I imagined this solution. But I think it would be bad for
performances: I loose connections only once a day, and the test query
would be executed all day long (too many times). What's your
experience/opinion?

Then during the night, the application is not used anymore so I don't
understand why connections are kept opened.

Finally is there something (JMX MBean or other) to control (I mean
monitor opened connection, force closing) connection pools described
in resources.properties?


Gérald

2009/10/27 Ludovic Orban <[hidden email]>:
> You should set a test query on your connection pool, see:
> http://btm.codehaus.org/api/1.3.3/bitronix/tm/resource/jdbc/PoolingDataSource.html#setTestQuery%28java.lang.String%29
>
> 2009/10/27 Gérald Quintana <[hidden email]>
>>
>> Hello,
>>
>> For some weird reason I won't explain, the Oracle database I am using
>> is restarted
>> every night. Since I introduced Bitronix connection pooling (the
>> problem didn't occur with DBCP), every morning the application is
>> dead: Each access to the database results in the following exception:
>> Caused by: java.sql.SQLException: Connexion interrompue
>>       at
>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
>>       at
>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
>>       at
>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
>>       at
>> oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:1170)
>>       at
>> oracle.jdbc.OracleConnectionWrapper.rollback(OracleConnectionWrapper.java:122)
>>       at
>> bitronix.tm.resource.jdbc.JdbcConnectionHandle.rollback(JdbcConnectionHandle.java:96)
>>       ... 9 more
>>
>> Restarting the application server (Tomcat) does the trick, but it is
>> not satisfactory solution.
>> Why do these stale connections remain in the pool?
>> What can I do to evict them?
>>
>> Thanks for your help,
>> Gérald
>>
>> ---------------------------------------------------------------------
>> To unsubscribe from this list, please visit:
>>
>>    http://xircles.codehaus.org/manage_email
>>
>>
>
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email