You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@geronimo.apache.org by Mario Rübsam <mr...@coderesearch.com> on 2006/05/30 13:50:28 UTC
Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB
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
Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB
Posted by Mario Rübsam <mr...@coderesearch.com>.
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
Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB
Posted by Matt Hogstrom <ma...@hogstrom.org>.
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
>>>
>>
>
Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB
Posted by Mario Rübsam <mr...@coderesearch.com>.
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
Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB
Posted by Aaron Mulder <am...@alumni.princeton.edu>.
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
>
Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB
Posted by Mario Rübsam <mr...@coderesearch.com>.
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
Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB
Posted by Mario Rübsam <mr...@coderesearch.com>.
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
>>>
>>
>
--
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
Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB
Posted by Mario Rübsam <mr...@coderesearch.com>.
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
>>
>
--
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
Re: Problem with Geronimo 1.0, PreparedStatements, pools and MaxDB
Posted by Aaron Mulder <am...@alumni.princeton.edu>.
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
>