different db connections for the same transaction

[aris]

We have a single transaction that is making multiple EJB calls. It seems that the database calls go over
multiple database connections thus resulting in database lockups since Oracle thinks it is different users trying to access the same rows.
Oracle Application server (and others) guarantee
that calls that belong to the same transaction will
use the same database connection.
Is there a way to resolve this with jBoss?

[darranl]

Are you talking about Entity bean access to the database or are you executing JDBC calls from your code?

[jamesstrachan]

I assume that you are making method calls on entity beans. There are two possibilities :-

a) Two or more different entity beans map to the same database table and row. This is bad object to database mapping which will cause trouble sooner or later. So presumably that is not the problem.

b) Your transaction makes several calls to the same EJB, each of which changes data mapped to the database. The J2EE specification mandates that each method call will be followed by a call to ejbStore(). So the ejbStore() following the first method call may update the database and lock rows pending a commit.

A second method call, followed by ejbStore(), will presumably get another database connection from the pool, with the results that you describe.

Assuming that the problem is (b), there are two solutions that may help :-

b1) Make sure that read only method calls on the EJB do not produce a database update. Simplest method is to use a dirty flag that is set true only when an update is needed. If the dirty flag is not set, ejbStore() does nothing. This will prevent read only methods writing to and locking the DB. You should do this anyway to prevent unnecessary writes.

b2) If you have multiple methods that modify the entity bean, wrap them in a single method. Instead of :-

doA()
doB()
doC()

use doABC()

which calls doA() etc. within the bean.

ejbStore() will then be called once only after doABC() has completed.

Hope this helps.

James Strachan

[aris]

We are using a Stateless Session bean that contains JDBC code.
The calling application is calling the same bean twice
in the same transaction.
The first time it inserts a row and the second time it
updates the row (I'm simplifying it here but this is the
general idea)
Each call of the bean however is using a different
Oracle connection. Oracle does not realize these
calls are part of the same transaction so when the
update comes it gets blocked.

[adrian.brock]

Use a jboss data source.
examples in docs/examples/jca

1) Straight jdbc doesn't know about the JTA transaction!
2) It will reuse the same connection in the same transaction.

Regards,
Adrian

[aris]

We are using jBoss DataSources and Container Managed transactions.

[adrian.brock]

Then you have some bad configuration or bad code.

Need more infomration to tell which.

Let's go through the standard list shall we (sigh!):
But keep it relevent, (e.g. I don't want your entire
server.log or an ejb-jar.xml with 100 ejbs)

Versions OS/JVM/JBoss
Code snippets
ejb-jar.xml/jboss.xml
oracle-ds.xml
Some proof from you that there are two connections
rather than a guess that this is the cause.

Less standard:
TRACE logging for
org.jboss.ejb.plugins, org.jboss.resource and org.jboss.tm

Regards,
Adrian

[jamesstrachan]

If you are using stateless session beans, JBoss will allocate an instance of the bean from a pool of stateless beans on each occasion that you call a method. The pool is used to avoid the performance hit of creating a new instance on each method call.

So the chances of getting the same instance (and DB connection) on two successive calls is very small. Hence the interference between the two operations on the database.

The quickest way out may be to change the bean to a stateful session bean so that the database connection is retained in the bean between the two calls. The ejbCreate() method must contain code to get the connection, and the ejbRemove() method must release the connection.

From the client, instead of :-

remote = home.create();
remote.doA();

// Other operations
remote = home.create();
remote.doB();

You write :-

remote = home.create();
remote.doA();

// Other operations
remote.doB();

remote = home.remove();

doA() and doB() should then use the same bean and DB connection. The remove() is needed to release the resources (bean and connection) for use by the next client.

James

[aris]

From the Oracle iAS manual:
Retrieving multiple connections from a data source
using the same username and password within a single global transaction
causes the logical connections to share a single physical connection.

From the BEA Weblogic manual:
Once a transaction begins, all database operations in an execute thread that get their connection from the same connection pool share the same connection
from that pool. These operations can be made through services such as Enterprise JavaBeans (EJB)
or Java Messaging Service (JMS), or by directly sending SQL statements using standard JDBC calls.
All of these operations will, by default, share the same connection and participate in the same
transaction.When the transaction is committed or rolled back, the connection is returned to the pool.

Can someone tell me what the expected jBoss behavior should be?

[adrian.brock]

If you use an xa datasource you should use
<track-connection-by-tx> in your -ds.xml
This should already be there in the example oracle-xa-ds.xml

If you use a local datasource the same.
Local does this track by connection automatically.

Regards,
Adrian

[aris]

We are using Local transactions.
The jBoss version is 3.0.6.
It doesn't seem to guarantee that there is
only one connection per transaction.
Do we need to set track-connection in this case?

[adrian.brock]

The 3.0 version does not support track-by-connection.

This was added for 3.2

I plan to backport the 3.2 jca implementation for 3.0.9 if some
backwards compatibility issues can be resolved in time.

Regards,
Adrian

[aris]

So, the 3.0.6 does not guarantee that the same connection will be used for Local transactions?
Should we upgrade to 3.2?
Exactly what version of 3.2 has this functionality?

[andreaseck]

Hi,
we are facing a similar problem.
We are using JBoss 3.0.4 with Oracle9i Release 9.2.0.4.0 (using LocalTxConnectionManager).
A stateful CMT Session Bean calls a BMP Entity Bean, all methods with Transaction REQUIRED. The primary key of the Entity Bean is then used to insert a detail using JDBC from the Session Bean.

That works most of the time, but sometimes the inserts get different connections.
I made some debug output into the bean logging the underlying connection. When I do the inserts, the underlying connection is the same most of the time. But very seldom and quite unpredictable I get different underlying connections causing a lock in the database.

The connection in the Session Bean is created in ejbCreate() and closed in ejbPassivate(). The Entity Bean calls getConnetion in every method where a connection is needed.

Regards,
Andreas

[andreaseck]

The problem of our configuration seems to be, that we store the SessionBeans in an own ConnectionPool for our Web-Application. We did this, because we used deploying EJBs directly to the Oracle 8 Database and Lookups were very slow ...
After switching to JBoss and Oracle 9 we left this for compatibility of the older version.
It seems, that if the Session Bean is stored for a while, the Managed Connection may be already removed from the pool (but is still in the bean), and the method call of the Session Bean is not associated with the connection, that is used for the transaction. The Entity Bean gets the correct connection. Therefore there are two different connections used for the insert causing an exception or even worse a deadlock in the database.

My questions concerning this are:

Is is generally bad to store Stateful Session Beans in a kind of connection pool on client side?

Is it bad to hold the connection in the Session Bean or preferable to fetch the connection from the DataSource everytime it is needed?

Will switching to a newer version of JBoss avoid these problems?

Regards,
Andreas