I have an EJB Stateles that monitors files that fall into a folder. When a file arrives the same is handled, a receive event is registered in a central database and the file is inserted into a destination database:
@Override
@Asynchronous
@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
public void fileReceived(@Observes FileReceivedEvent fileReceivedEvent)
This process involves a central database, as well as a target datasource / 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 event of receipt of files makes several inserts and updates, as well as calls to stored procedures that also manipulate the bank:
final String procedureCall = "{call MEU_PACOTE.MEU_PROCEDURE(?, ?, ?, ?, ?, ?, ?)}";
Enabled the logs and all SQLs are generated correctly. No exception is thrown in the EJB layer. All SQL commands, when executed in the hand, work and generate entries in the database.
The problem happens in procedure calls. Around and half the changes made by the procedures are not persisted in the database when invoked through Java. We also get exceptions like fetch out of sequence
on the PL / SQL side.
I found posts like this in SOen, but we have no commits or cursors for update in the code (and again, the exceptions are not being propagated to Java).
If you use a Non-XA DataSource, procedures normally commit, 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 standalone 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 they have solved it.
- I need to do something besides configuring DataSources XA and annotating my EJBs so that the container takes control of distributed transactions?
- Should you be doing manual DataSource lookup (with
InitialContext
) might be influencing? - There are some exception flows within PL / SQL (
Exception When Others Then
). None of these exceptions are fired back into Java. Are these exceptions being handled within PL / SQL may be preventing commit when we use distributed transactions? If so, how do I solve the problem?