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:[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***
--
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