Hi,

I testet a bit further and tried the following situations:

I used a direct driver connection without the Geronimo pool --> working

I disabled all write access to the tables were the hang occurs,
only read access enabled --> working

I checked (with the MaxDB Database Manager) the connections/db sessions all
open and close correct so thats not the problem.

So my conclusion is that there is something different with the
transactions and Tranql. I debugged the commits and if I use the
direct driver connection I can see the commits in the Database Manager
stats. If I commit on the Tranql connection the commit is not
registered on the MaxDB server.

Is this now a driver or a Tranql issue?

Thanks,
Mario



Mario Rübsam wrote:
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 <[EMAIL PROTECTED]> 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





------------------------------------------------------------------------

DEBUG    DATE=2006-05-30 TIME=13:42:22:046 CATEGORY=cpa.db [EMAIL 
PROTECTED]: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 [EMAIL 
PROTECTED]:CDbContext.getPreparedStatement() SID=cpa.1-1-1.cpa MESSAGE=prepared statement 
"[EMAIL PROTECTED]"
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)
        

--
Dipl. Inf. Mario Rübsam
Geschäftsführer
CODERESEARCH GmbH & Co. KG
mail: [EMAIL PROTECTED]
web : www.coderesearch.com
tel : 03677/466420
fax : 03677/466419

Reply via email to