Transaction XA does not commit changes in procedures (intermittent)

4

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?
asked by anonymous 04.11.2014 / 17:54

1 answer

1

The ideal would be to insulate this case from flashing. The environment is complex and to give a concrete answer we need to know if at least in the happy scenario the transaction is successful (commit). After that, you need to do some testing to see if we can do the rollback in the case of exceptions. It is best that you isolate the resources: first you have to guarantee a very simple but that works, that is, a certain class of exception thrown in java should cause the rollback. And then move on. Remember that not all exceptions in java would cause a rollback. That is, systemException does rollback, application exception does not rollback unless you invoke the setRollBackOnly () method. ( link )

    
24.11.2014 / 14:37