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 16:24:29 GMT
Santosh,

it works, but only in the following situation:

open connection --> call update --> commit


if you I do a:

open connection --> call query --> call update --> commit

no commit happen on server, like without commitbeforeautocommit=true

Strange is that CommitBeforeAutocommit is a workaround for setAutoCommit(true)
which I don't use. And "setAutoCommit(true)" also doesn't commit.

Thanks,
Mario


Santosh Koti wrote:
> 
> 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:mr@coderesearch.com]
> 
> Sent: Wednesday, May 31, 2006 7:11 PM
> To: user@geronimo.apache.org
> 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 <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
> 
> **************** 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***
> 

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