4
I have an EJB Stateles that monitors files that fall into a folder. When a file arrives it is handled, a receiving event is recorded in a central database and the file is inserted into a target database:
@Override
@Asynchronous
@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
public void fileReceived(@Observes FileReceivedEvent fileReceivedEvent)
This process involves a central database as well as a datasource / destination schema that are obtained in Runtime (JNDI lookup):
<jdbc-connection-pool datasource-classname="oracle.jdbc.xa.client.OracleXADataSource"
res-type="javax.sql.XADataSource" name="DBCentral">
<property name="url" value="jdbc:oracle:thin:@//ip:1521/dbcentral">
<property name="password" value="****"></property>
<property name="user" value="DB_CENTRAL"></property>
</jdbc-connection-pool>
<jdbc-resource pool-name="DBCentral" jndi-name="jdbc/dbcentral"></jdbc-resource>
<jdbc-connection-pool datasource-classname="oracle.jdbc.xa.client.OracleXADataSource"
res-type="javax.sql.XADataSource" name="DBDestino1">
<property name="url" value="jdbc:oracle:thin:@//ip:1521/dbdestino">
<property name="password" value=""*****"></property>
<property name="user" value="DB_DESTINO1"></property>
</jdbc-connection-pool>
<jdbc-resource pool-name="DBDestino1" jndi-name="jdbc/dbdestino1"></jdbc-resource>
<!-- Destino 2, Destino 3, etc -->
The file receiving event makes multiple inserts and updates, as well as calls to stored procedures that also manipulate the bank:
final String procedureCall = "{call MEU_PACOTE.MEU_PROCEDURE(?, ?, ?, ?, ?, ?, ?)}";
I enabled the logs and all Sqls are generated correctly. No exception is fired in the EJB layer. All SQL commands, when executed at hand, work and generate entries in the database.
The problem happens in the calls of procedures. Back and forth changes made by procedures are not persisted in the database when invoked through Java. We also receive exceptions such as fetch out of sequence
on the PL/SQL side.
I found posts like that in Soen, but we don’t have commits or cursors for update in the code (and again, the exceptions are not being propagated to Java).
If you use a non-AXA Datasource procedures commit normally, but in this case we really need distributed transactions to ensure the integrity of the solution.
We were able to "eliminate" the problem by making the transactions stand-alone on the PL/SQL side:
PROCEDURE MEU_PROCEDURE(p_um IN OUT VARCHAR2
, p_dois IN NUMBER
, p_tres IN OUT VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
But this is not an ideal solution (Java should control the transaction and rollback the entire set of operations in case of an error).
I wonder if anyone has ever faced a similar problem and how it managed to solve.
- I need to do something besides setting up Datasources XA and annotate my Ejbs for the container to control distributed transactions?
- The fact that you are doing manual lookup of Datasources (with
InitialContext
) may be influencing? - There are some exception streams within PL/SQL (
Exception When Others Then
). None of these exceptions are fired back into Java. These exceptions that are being handled within the PL/SQL may be preventing the commit when we use distributed transactions? If yes, how do I solve the problem?
Is there any command
EXECUTE IMMEDIATE
or any dynamic database change DDL within the database? This type of command has aCOMMIT
implicit by nature and stand-alone transactions are generally recommended to avoid problems in such cases.– utluiz
An unlikely but possible cause is an implementation failure that coordinates the distributed transaction. It can be read on Oracle documentation on distributed transactions in JDBC that the absence of a parameter
TMNOMIGRATE
in certain scenarios may cause errorfetch out of sequence
.– utluiz
Hi utluiz, there is no
EXECUTE IMEDIATE
and / or dynamic SQL. The suggestion ofTMNOMIGRATE
is very good, I’ll try.– Anthony Accioly
Just to give a update. I did what I could to navigate the JDBC driver code with Intellij 14 (the new built-in decompiler is pretty cool) but I still can’t isolate streams with bug. That said, I found I don’t need to have both
DataSources
configured as XA (Glassfish implements Last Agent Optimization). For now we are leaving the fate side as non-ASA. This "solved" the problems.– Anthony Accioly