Mailing-List: contact user-help@geronimo.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@geronimo.apache.org
Received-SPF: pass (asf.osuosl.org: local policy)
Message-ID: <447C6902.1060009@coderesearch.com>
Date: Tue, 30 May 2006 17:47:14 +0200
From: =?ISO-8859-1?Q?Mario_R=FCbsam?= <mr@coderesearch.com>
User-Agent: Thunderbird 1.5.0.2 (Windows/20060308)
MIME-Version: 1.0
To: user@geronimo.apache.org
Subject: Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB
References: <447C3184.1090406@coderesearch.com>
 <74e15baa0605300714g68bc928vbddf7510ac030163@mail.gmail.com>
 <447C65A1.8040604@coderesearch.com>
In-Reply-To: <447C65A1.8040604@coderesearch.com>
Content-Type: multipart/mixed;
 boundary="------------000500020609020304040306"

This is a multi-part message in MIME format.
--------------000500020609020304040306
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Aaron,

here is a stack trace attached, it hangs sometimes when calling the
prepareStatement and sometimes when executing the statement and it's
not always the same statement.

Thanks,
Mario


Mario R�bsam wrote:
> Aaron,
> 
> connections are correctly closed. If they are returned to the pool,
> don't know?
> 
> The code that opens/closes connections is a bit widespread and wrapped
> into a db abstraction layer. Here are some code fragments collected
> together without the try catches.
> 
> 
> open connection:
> 
> InitialContext tCtx = new InitialContext();
> 
> mJDBCDataSource = (DataSource)tCtx.lookup(
>   pDataSource.getString(PDbDataSource.URL));
> 
> String tUser = pDataSource.getString(PDbDataSource.USER, null);
> String tPass = pDataSource.getString(PDbDataSource.PASS, null);
> 
> if (tUser != null) {
>     mConnection = mJDBCDataSource.getConnection(tUser, tPass);
> } else {
>     mConnection = mJDBCDataSource.getConnection();
> }
> 
> mConnection.setAutoCommit(false);
> 
> 
> 
> call statements:
> 
> String tStmtSQL = "SELECT ....";
> PreparedStatement tStmt = mConnection.prepareStatement(tStmtSQL);
> 
> tStmt.setString(1, tName);
> ResultSet tRS = tStmt.executeQuery();
> 
> while (tRS.next()) {
> ...
> }
> 
> tRS.close();
> tStmt.close();
> 
> 
> 
> close the connection:
> 
> mConnection.close();
> 
> 
> 
> The DataSource lookup happens only once. After that I only
> call getConnection() on the DS and close() on the connection.
> 
> Thanks,
> Mario
> 
> 
> 
> Aaron Mulder wrote:
>> It sounds like connections are not being returned to the pool, though
>> it's hard to know without a stack trace.  Also, can you post the code
>> you're using to access the connection pool, execute the prepared
>> statements, and close the connection?  And what kind of component is
>> running these prepared statements?
>>
>> Thanks,
>>    Aaron
>>
>> On 5/30/06, Mario R�bsam <mr@coderesearch.com> wrote:
>>> Hi,
>>>
>>> I have some serious problems when executing prepared statements
>>> on MaxDB with pooled connections managed by Tranql in Geronimo 1.0.
>>>
>>> The problem is, that after calling a lot of these prepared statements
>>> the connection will hang until I get a timout. It's always
>>> a different statement that worked just fine some milliseconds before.
>>> There is no CPU load on the Geronimo machine and also no load
>>> on the DB machine. Just a hang up until timeout.
>>> I can run the same app on MySQL 4.x or PostgrSQL 8.x without any
>>> Problems. Also no problems with a client app and MaxDB that do
>>> a batch import and use exactly the same statements but without db
>>> pooling.
>>>
>>> So I think it must be a db pool problem with the MaxDB or a
>>> strange behaviour with that driver and connection pools.
>>>
>>> Any suggestions where I can start analysing the problem?
>>>
>>> here my MaxDB plan:
>>>
>>> <?xml version="1.0" encoding="UTF-8"?>
>>> <connector configId="user/database-pool-jdbc/default/1/car"
>>> xmlns="http://geronimo.apache.org/xml/ns/j2ee/connector-1.0">
>>>      <dep:dependency 
>>> xmlns:dep="http://geronimo.apache.org/xml/ns/deployment-1.0">
>>>          <dep:uri>mysql/sapdbc/7.6/jar</dep:uri>
>>>      </dep:dependency>
>>>      <resourceadapter>
>>>          <outbound-resourceadapter>
>>>              <connection-definition>
>>>
>>> <connectionfactory-interface>javax.sql.DataSource</connectionfactory-interface> 
>>>
>>>                  <connectiondefinition-instance>
>>>                      <name>jdbc/default</name>
>>>                      <config-property-setting
>>> name="UserName">sse</config-property-setting>
>>>                      <config-property-setting
>>> name="Password">sse</config-property-setting>
>>>                      <config-property-setting
>>> name="CommitBeforeAutocommit">false</config-property-setting>
>>>                      <config-property-setting
>>> name="Driver">com.sap.dbtech.jdbc.DriverSapDB</config-property-setting>
>>>                      <config-property-setting
>>> name="ExceptionSorterClass">org.tranql.connector.AllExceptionsAreFatalSorter</config-property-setting> 
>>>
>>>                      <config-property-setting
>>> name="ConnectionURL">jdbc:sapdb://192.168.8.3/service</config-property-setting> 
>>>
>>>                      <connectionmanager>
>>>                          <local-transaction/>
>>>                          <single-pool>
>>>                              <max-size>100</max-size>
>>>                              <min-size>50</min-size>
>>>
>>> <blocking-timeout-milliseconds>60000</blocking-timeout-milliseconds>
>>>                              
>>> <idle-timeout-minutes>60</idle-timeout-minutes>
>>>                              <match-one/>
>>>                          </single-pool>
>>>                      </connectionmanager>
>>>                  </connectiondefinition-instance>
>>>              </connection-definition>
>>>          </outbound-resourceadapter>
>>>      </resourceadapter>
>>> </connector>
>>>
>>>
>>> Thanks,
>>> Mario
>>>
>>
> 

-- 
Dipl. Inf. Mario R�bsam
Gesch�ftsf�hrer
CODERESEARCH GmbH & Co. KG
mail: mr@coderesearch.com
web : www.coderesearch.com
tel : 03677/466420
fax : 03677/466419

--------------000500020609020304040306
Content-Type: text/plain;
 name="stack.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="stack.txt"

DEBUG	 DATE=2006-05-30 TIME=13:42:22:046 CATEGORY=cpa.db SOURCE=mr@aeon:CDbContext.getPreparedStatement() SID=cpa.1-1-1.cpa MESSAGE=prepare statement "SELECT TbRegistryValues.TcValueText,TbRegistryValues.TcValueLineNo FROM TbRegistry,TbRegistryValues WHERE ((TbRegistry.TcEntityId=TbRegistryValues.TcEntityId) AND (TbRegistry.TcDomain=?)) ORDER BY TbRegistryValues.TcValueLineNo"
DEBUG	 DATE=2006-05-30 TIME=17:38:50:421 CATEGORY=cpa.db SOURCE=mr@aeon:CDbContext.getPreparedStatement() SID=cpa.1-1-1.cpa MESSAGE=prepared statement "org.tranql.connector.jdbc.PreparedStatementHandle@1728a26"
13:42:22,046 WARN  [GeronimoConnectionEventListener] connectionErrorOccurred called with null
com.sap.dbtech.jdbc.exceptions.ConnectionException: [-708] Timeout
	at com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:554)
	at com.sap.dbtech.jdbc.CallableStatementSapDB.sendCommand(CallableStatementSapDB.java:1764)
	at com.sap.dbtech.jdbc.StatementSapDB.sendSQL(StatementSapDB.java:808)
	at com.sap.dbtech.jdbc.CallableStatementSapDB.doParse(CallableStatementSapDB.java:233)
	at com.sap.dbtech.jdbc.CallableStatementSapDB.constructor(CallableStatementSapDB.java:186)
	at com.sap.dbtech.jdbc.CallableStatementSapDB.<init>(CallableStatementSapDB.java:88)
	at com.sap.dbtech.jdbc.ConnectionSapDB.prepareStatement(ConnectionSapDB.java:803)
	at org.tranql.connector.jdbc.ConnectionHandle.prepareStatement(ConnectionHandle.java:231)
	at com.coderesearch.cpa.db.jdbc.CDbContext.getPreparedStatement(CDbContext.java:1131)
	at com.coderesearch.cpa.reg.db.jdbc.CDbBrokerRegistry.lookup(CDbBrokerRegistry.java:129)
	at com.coderesearch.cpa.reg.db.jdbc.CDbBrokerRegistry.query(CDbBrokerRegistry.java:430)
	at com.coderesearch.cpa.naming.CPANamingContextDb.lookup(CPANamingContextDb.java:116)
	at com.coderesearch.cpa.reg.CPARegistryContext.lookup(CPARegistryContext.java:256)
	at com.coderesearch.cpa.reg.Registry.lookupInt(Registry.java:199)
	at com.coderesearch.abp.index.srv.CRPIndex.countLogin(CRPIndex.java:140)
	at com.coderesearch.abp.index.srv.CRPIndex.process(CRPIndex.java:266)
	at com.coderesearch.abp.found.srv.MainServlet.process(MainServlet.java:482)
	at com.coderesearch.abp.found.srv.MainServlet.doPost(MainServlet.java:610)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:615)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:688)
	at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428)
	at org.apache.geronimo.jetty.JettyServletHolder.handle(JettyServletHolder.java:99)
	at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplicationHandler.java:830)
	at org.mortbay.jetty.servlet.JSR154Filter.doFilter(JSR154Filter.java:170)
	at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplicationHandler.java:821)
	at org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandler.java:471)
	at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568)
	at org.mortbay.http.HttpContext.handle(HttpContext.java:1530)
	at org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationContext.java:633)
	at org.mortbay.http.HttpContext.handle(HttpContext.java:1482)
	at org.mortbay.http.HttpServer.service(HttpServer.java:909)
	at org.mortbay.http.HttpConnection.service(HttpConnection.java:816)
	at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:982)
	at org.mortbay.http.HttpConnection.handle(HttpConnection.java:833)
	at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:244)
	at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:357)
	at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:534)
	

--------------000500020609020304040306--