4 Replies Latest reply on Feb 17, 2004 10:04 AM by spyer

Managed Connection Pool Problem

jbossuser Jan 8, 2004 10:52 AM

"jbossuser" wrote:
"jbossuser" wrote:
We are having serious connection pooling issues under load. At first, we believed it was our problem, but we have done sufficient load testing to suspect the connection pool itself. Here are the relevant points:

JBoss 3.2.3 over Oracle 8i using any of the thin drivers (tried all of them) on Solaris.
Multiple connection pools (printout below).
Apache to Tomcat JK connection pooling.
We use CMP but no CMR. We also have DAO that share the connection pools with CMP. We have confirmed proper closing of resources in DAO, we also have no nested querying or other such problems that might confuse resources.

Symptoms: Protocol Violations (yes, I know, this is a catch all), Bigger type length than Maximum and Refcursor value is invalid exceptions (examples below).

Observations:

1. Occurs when a cluster node is heavily loaded, generally when it exceeds 50% of the app. server's cpu capability. We have little or no throttling, except for the infamous jk connectionTimeout that holds on to sockets. If we lower that value or increase max processors, the problem manifests itself sooner than later in the load test. This occurs over a variety of database configurations and environments.

2. Initially connection pools are moderately used, with a large number of connections being used concurrently. Eventually (a few minutes), only a few connections are used concurrently. At the same time, we start getting the JDBC problems.

3. Our hunch is that one thread is closing JDBC resources on another thread by sharing the same connection, or that there are stale connections.

4. As a test, in one area it was happening we created our own connection each time from the driver manager and that works fine. Not a solution but shows that at least the basics work.

Please help?/Suggestions?

Sample exceptions:

15:50:45,194 ERROR [BusinessPartnerBean] Failed to execute SQL query SELECT DISTINCT BP.BUS_PRTNR_ID, BP.NAME, BP.STATUS_LOOKUP_ID FROM BUSPRTNR.BUS_PRTNR BP, BUSPRTNR.BUS_ROLE BR, BUSPRTNR.BUS_PRTNR_ROLE BPR WHERE BP.BUS_PRTNR_ID = BPR.BUS_PRTNR_ID AND BPR.BUS_ROLE_ID = BR.BUS_ROLE_ID AND UPPER(BR.NAME) = ? ORDER BY UPPER(BP.NAME)
java.sql.SQLException: Protocol violation
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:211)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:933)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:550)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:643)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1819)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2015)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:395)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:339)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:304)
.....

Caused by: java.sql.SQLException: Bigger type length than Maximum
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:211)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:933)
at oracle.jdbc.ttc7.MAREngine.buffer2Value(MAREngine.java:1995)
at oracle.jdbc.ttc7.MAREngine.unmarshalUB4(MAREngine.java:857)
at oracle.jdbc.ttc7.TTIoer.unmarshal(TTIoer.java:113)
at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:115)
at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:466)
at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:441)
at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:460)
at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:185)
at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:168)
at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java:614)
at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:492)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:353)
at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:211)
......

Caused by: java.sql.SQLException: Refcursor value is invalid
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:211)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:933)
at oracle.jdbc.ttc7.TTCStatement.<init>(TTCStatement.java:41)
at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:469)
at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:441)
at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:460)
at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:185)
at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:168)
at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java:614)
at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:492)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:353)
at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:211)

Our oracle data source is:

<?xml version="1.0" encoding="UTF-8"?>













<datasources>

<local-tx-datasource>
<jndi-name>ArgoUtlDS</jndi-name>
<connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>argoutl</user-name>
<password>argoutl</password>
<min-pool-size>0</min-pool-size>
<max-pool-size>40</max-pool-size>
<blocking-timeout-millis>20000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>

<local-tx-datasource>
<jndi-name>BusPrtnrDS</jndi-name>
<connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>busprtnr</user-name>
<password>bus</password>
<min-pool-size>0</min-pool-size>
<max-pool-size>30</max-pool-size>
<blocking-timeout-millis>20000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>

<local-tx-datasource>
<jndi-name>CampaignDS</jndi-name>
<connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>campaign</user-name>
<password>campaign</password>
<min-pool-size>0</min-pool-size>
<max-pool-size>15</max-pool-size>
<blocking-timeout-millis>20000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>

<local-tx-datasource>
<jndi-name>DeployDS</jndi-name>
<connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>system</user-name>
<password>argo1</password>
<min-pool-size>0</min-pool-size>
<max-pool-size>1</max-pool-size>
<blocking-timeout-millis>20000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>

<local-tx-datasource>
<jndi-name>DupIdentificationDS</jndi-name>
<connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>dupuser</user-name>
<password>dupuser</password>
<min-pool-size>0</min-pool-size>
<max-pool-size>15</max-pool-size>
<blocking-timeout-millis>20000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>

<local-tx-datasource>
<jndi-name>MailBatchDS</jndi-name>
<connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>mailbatch</user-name>
<password>mail</password>
<min-pool-size>0</min-pool-size>
<max-pool-size>25</max-pool-size>
<blocking-timeout-millis>20000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>

<local-tx-datasource>
<jndi-name>SecurityDS</jndi-name>
<connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>security</user-name>
<password>security</password>
<min-pool-size>0</min-pool-size>
<max-pool-size>40</max-pool-size>
<blocking-timeout-millis>20000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>

<local-tx-datasource>
<jndi-name>SubmissionDS</jndi-name>
<connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>submission</user-name>
<password>submission</password>
<min-pool-size>0</min-pool-size>
<max-pool-size>25</max-pool-size>
<blocking-timeout-millis>20000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>



<local-tx-datasource>
<jndi-name>ThirdPartyDS</jndi-name>
<connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>tparty</user-name>
<password>tparty</password>
<min-pool-size>0</min-pool-size>
<max-pool-size>3</max-pool-size>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>

<local-tx-datasource>
<jndi-name>ArgoTransDS</jndi-name>
<connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>argotrans</user-name>
<password>argotrans</password>
<min-pool-size>0</min-pool-size>
<max-pool-size>15</max-pool-size>
<blocking-timeout-millis>20000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>

<local-tx-datasource>
<jndi-name>PTSdatasource</jndi-name>
<connection-url>jdbc:oracle:thin:@loaddb01:1521:load</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>rebate</user-name>
<password>rebate</password>
<min-pool-size>0</min-pool-size>
<max-pool-size>25</max-pool-size>
<blocking-timeout-millis>20000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>

<local-tx-datasource>
<jndi-name>MassRerateDS</jndi-name>
<connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>massrerate</user-name>
<password>massrerate</password>
<min-pool-size>0</min-pool-size>
<max-pool-size>5</max-pool-size>
<blocking-timeout-millis>60000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>

<local-tx-datasource>
<jndi-name>BatchUserDS</jndi-name>
<connection-url>jdbc:oracle:thin:@loaddb01:1521:lace</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>batchuser</user-name>
<password>batchuser</password>
<min-pool-size>0</min-pool-size>
<max-pool-size>5</max-pool-size>
<blocking-timeout-millis>20000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>

</datasources>

1. Re: Managed Connection Pool Problem

jmntn2000 Jan 8, 2004 5:41 PM (in response to jbossuser)

"jmntn2000" wrote:
"jmntn2000" wrote:
I don't think that this is an oracle problem. We are using 3.2.3 (default) under Linux, JRocket 1.4.1,sybase and have experianced problems with the connection pooling. The connection pool seems to leave hung sessions out there that it no longer uses and eventually our sybase max connections is reached and application stops responding with exceptions that it can't create another one. This always seems to happen under a load. I haven't had a chance to dive into the code yet but I am suspicious that it has to do with exceptions being thrown in the code or JDBC driver (such as sybase detecting a deadlock etc) that cause the connection to not be properly released?? This is just a guess at this point. I would love to solve the problem though! It doesn't happen near as much under 3.2.3 as it did under 3.2.1.
Actions
2. Re: Managed Connection Pool Problem

jbossuser Jan 9, 2004 1:10 PM (in response to jbossuser)

"jbossuser" wrote:
"jbossuser" wrote:
We are now also thinking it is at least partially related to the pool.
Actions
3. Re: Managed Connection Pool Problem

j_agra Feb 5, 2004 6:08 AM (in response to jbossuser)

The only times I get ProtocolViolations erros in Oracle is when I use the wrong driver version. It seems Oracle is very sensible to that at least for versions 8.16, 8.1.7 and 9i.

Try to use only the driver that's in jdbc directory of the Oracle server you're using, and check if you haven't another version forgotten in your classpath.

Sorry if this was already tried by you, it's kind of basic, but seems to be a recurrent problem for me :(
Actions
4. Re: Managed Connection Pool Problem

spyer Feb 17, 2004 10:04 AM (in response to jbossuser)

Oracle's thin client is to be avoided, we use the oci8 driver, I had major problems with oracle connection pooling also, and changed to use DBCP jakarta.apache.org/commons/dbcp/

hope this helps
Actions

Go to original post