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)
--
