On May 31, 2006, at 4:22 PM, Mario Rübsam wrote:
Going up the call stack I noticed that the commit on the
connection is only done when commitBeforeAutoCommit is true.
My commitBeforeAutoCommit is false as suggested. Why is
the Autocommit set always to true?
There are 2 things going on here:
-- Whether or not it's a good idea, we've implemented all the
connection.commit() etc operations in terms of the J2CA connector
framework spi LocalTransaction class. The required semantics of this
are that outside a transaction, the connection is in autocommit
mode. Therefore when you call connection.commit(), we turn that into
LocalTransaction.commit();
//now the connection is in autocommit mode
LocalTransaction.begin(); // this resets autocommit off.
IIRC not doing this causes lots and lots of problems trying to
synchronize the tx and autocommit state between a connection.commit
call and a LocalTransaction.commit() call.
-- The jdbc spec requires that setting autocommit(true) should commit
all pending work. Therefore, to commit the work in the local tx and
turn on autocommit, the state we want to be in after the first line
above, we _should_ just need to call setAutoCommit(true). However
some drivers don't do this properly: the only one I know of is
axion. The commitBeforeAutoCommit flag is to let the tranql
connector work with these buggy drivers.
Can you test if setAutoCommit(true) is actually committing the work
in maxDb? If not, the commitBeforeAutocommit flag set to true should
definitely force a commit.
thanks
david jencks
protected void localTransactionCommit(boolean isSPI) throws
ResourceException {
Connection c = (Connection) physicalConnection;
try {
if (commitBeforeAutoCommit) {
c.commit();
}
c.setAutoCommit(true);
} catch (SQLException e) {
try {
c.rollback();
} catch (SQLException e1) {
if (log != null) {
e.printStackTrace(log);
}
}
throw new LocalTransactionException("Unable to commit",
e);
}
super.localTransactionCommit(isSPI);
}
In the case of MaxDB you can configure the db to not use
Autocommit. Every setting of Autocommit via the JDBC driver
is ignored then. Set Autocommit without a following update
or close on the connection does nothing in case of the MaxDB
driver and already disabled Autocommit.
here is the drivers code:
/**
* setAutoCommit method comment.
*/
public void setAutoCommit(boolean autoCommit)
throws java.sql.SQLException
{
this.assertOpen ();
if (autoCommit){
this.commit();
}
this.autocommit = autoCommit;
}
I really don't understand that commitBeforeAutoCommit thingy
it must be a workaround for some driver, but for all connections
without c.getAutoCommit() == true only the commit should happen.
try {
if (c.getAutoCommit() && commitBeforeAutoCommit) {
c.commit();
c.setAutoCommit(true);
} else {
c.commit();
}
} catch (SQLException e) {
Looks a bit tautologous but does the same for the
commitBeforeAutoCommit
users as before and the simple commit for all without Autocommit.
Thanks,
Mario
Mario Rübsam wrote:
Ok, I couldn't resist and started debugging
it leads me to some code which enables Autocommit on the connection
just before calling some listeners for localTransactionCommit
this setting of Autocommit is also logged by the MaxDB JDBC logger
and the last code in Tranql
protected void localTransactionCommit(boolean isSPI) throws
ResourceException {
if (!isSPI) {
ConnectionEvent event = new ConnectionEvent(this,
ConnectionEvent.LOCAL_TRANSACTION_COMMITTED);
for (int i = listeners.size() - 1; i >= 0; i--) {
ConnectionEventListener listener =
(ConnectionEventListener) listeners.get(i);
listener.localTransactionCommitted(event);
}
}
}
there is only one listerner to call the
GeronimoConnectionEventListener
and the method localTransactionCommitted and right here is my problem
/**
* The <code>localTransactionCommitted</code> method
*
* @param event a <code>ConnectionEvent</code> value
* todo implement this method
*/
public void localTransactionCommitted(ConnectionEvent event) {
}
Geronimo source was from the trunk, Is this the 1.0 code?
What should I do now?
Thanks,
Mario
David Jencks wrote:
On May 31, 2006, at 2:39 PM, Mario Rübsam wrote:
David,
where can I get the source for Traql 1.2.1? I will try to debug
that commit handling.
svn co https://svn.codehaus.org/tranql/trunk/connector
will get the current source code: if it's not exactly the same
was what has been released it should be pretty close.
I have AutoCommit always off because I need the transaction
control so CommitBeforeAutocommit=true was no help for my problem.
I was pretty sure that was the case :-)
thanks
david jencks
Thanks,
Mario
David Jencks wrote:
Mario,
I don't see an obvious way for this stuff to break, but if you
are set up for debugging you could get the tranql connector
source code and trace through starting at
org.tranql.connector.jdbc.ConnectionHandle.commit(). I don't
see how the commit call wouldn't get eventually to the db, but
knowing which path execution takes should make it simpler to
figure out what is going on.
BTW someone was mentioning setting CommitBeforeAutocommit to
true. You should only set this on very buggy jdbc drivers that
violate the jdbc spec by not committing pending work when you
set autocommit to true (axion). Any real database should not
need this set.
thanks
david jencks
On May 31, 2006, at 11:05 AM, Mario Rübsam wrote:
Santosh,
I did the development mostly on PostgreSQL. It's working there
but don't know
if the commits come through or if the DB handle it in some way.
I can't test Derby atm because I have to setup a separate
Derby server to import
all the data needed. That needs to much time.
Thanks,
Mario
Santosh Koti wrote:
Mario,
Can u try the same thing on the Derby ,if it is not
cumbersome :)
I think tranQL has better support for Derby.
PS: Ofcourse it may not solve ur MaxDB problem ,but atleast
it says ur code is safe enough :)
Thanks,
Santosh.
"Don't talk about yourself; it will be done when you leave. "
-----Original Message-----
From: Santosh Koti [mailto:[EMAIL PROTECTED] Sent:
Wednesday, May 31, 2006 11:10 PM
To: [email protected]
Subject: RE: Problem with Geronimo 1.0, PreparedStatements,
pools and MaxDB
Mario,
I agree that is not a solution , but atleast it points out
that the problem is cornered around TranQL ?? . (That's what
I suspect !! If so, then look smething on transQL config, or
may need to chk for next release :) Time for the gurus to
dive in here !! :)
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 10:55 PM
To: [email protected]
Subject: Re: Problem with Geronimo 1.0, PreparedStatements,
pools and MaxDB
Santosh,
Santosh Koti wrote:
Mario,
Just give another try (of course a wild advise :-))
I did ;)
After running the transactions , chk ur DB for any updates,
then bring the server down -> & then chk ur DB, whether
commit is happening the moment u trigger the server with a
shutdown event ??
data is committed with shutdown, but that is not a solution
If so , some configuration needs to be looked at ? !
here is what I did now:
instead of calling:
mConnection.commit()
I did the following:
Statement tStatement = mConnection.createStatement();
tStatement.executeUpdate("COMMIT");
this is dirty but working, all commits are registered on the
MaxDB server
this is bypassing all the commit stuff in the Tranql
Connection object
and the driver
So I find me now debugging the commit stuff in the Connection
instance.
Thanks,
Mario
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 9:54 PM
To: [email protected]
Subject: Re: Problem with Geronimo 1.0, PreparedStatements,
pools and MaxDB
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.AllExceptio
nsAreFatalSorter</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)
--
**************** 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***
