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
