geronimo-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mario Rübsam ...@coderesearch.com>
Subject Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB
Date Wed, 31 May 2006 18:03:56 GMT
Matt,

thanks for the reply. If you need some further info just mail.

Thanks,
Mario


Matt Hogstrom wrote:
> Mario,
> 
> I'll take a look at TranQL.  I think what your describing is a bug but I 
> need to create a testcase.  I'll work on that later today.
> 
> Mario Rübsam wrote:
>> The "setAutoCommit(false)" is required by MaxDB because
>> it's default is "on".
>>
>> I debugged the connections open and close and I can see
>> the connections (db sessions on the MaxDB server) come
>> and go when I do this. The only thing that not happen
>> on the server is the Connection.commit() whe I call it.
>>
>> If I use the direct driver connection I can see the
>> commit in the stats immediately after I call the commit.
>> If I use the connection from the pool no commit happens
>> on the server.
>>
>> Is there any delayed commit mechanism implemented?
>>
>> Thanks,
>> Mario
>>
>>
>>
>> Aaron Mulder wrote:
>>> The one thing that looked suspicious in your code was the
>>> setAutoCommit -- that shouldn't be done in a J2EE environment.  Can
>>> you try commenting out that line and see if it makes a difference?
>>>
>>> If that doesn't help, can you put printlns near where you close the
>>> connections and just convince yourself that they're definitely being
>>> closed?
>>>
>>> Thanks,
>>>     Aaron
>>>
>>> On 5/31/06, Mario Rübsam <mr@coderesearch.com> wrote:
>>>> 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 <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
>>>> >>>>
>>>> >>>
>>>> >>
>>>> >
>>>> >
>>>> > 
>>>> ------------------------------------------------------------------------

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

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

Mime
View raw message