geronimo-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Aaron Mulder" <ammul...@alumni.princeton.edu>
Subject Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB
Date Wed, 31 May 2006 13:30:01 GMT
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
>

Mime
View raw message