Mario,
Can u give a try for :
commitbeforeautocommit="true" in ur db deployment plan.
Not sure, but may work, I had the same problem with delayed EJB's commit.
Thanks,
Santosh.
"Don't talk about yourself; it will be done when you leave. "
-----Original Message-----
From: Mario Rübsam [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 31, 2006 7:11 PM
To: [email protected]
Subject: Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB
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 <[EMAIL PROTECTED]> 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 <[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
>>
>
--
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
**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are
not to copy, disclose, or distribute this e-mail or its contents to any other
person and any such actions are unlawful. This e-mail may contain viruses.
Infosys has taken every reasonable precaution to minimize this risk, but is not
liable for any damage you may sustain as a result of any virus in this e-mail.
You should carry out your own virus checks before opening the e-mail or
attachment. Infosys reserves the right to monitor and review the content of all
messages sent to or from this e-mail address. Messages sent to or from this
e-mail address may be stored on the Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***
