XA transaction does not commit changes in procedures (intermittent)

Asked

Viewed 396 times

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?
java glassfish pl-sql ejb transactions
  • 1

    Is there any command EXECUTE IMMEDIATE or any dynamic database change DDL within the database? This type of command has a COMMIT implicit by nature and stand-alone transactions are generally recommended to avoid problems in such cases.

  • 1

    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 error fetch out of sequence.

  • Hi utluiz, there is no EXECUTE IMEDIATE and / or dynamic SQL. The suggestion of TMNOMIGRATE is very good, I’ll try.

  • 1

    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.

1 answer

1

The ideal would be to isolate this case of intermittence. The environment is complex and to give a concrete answer we need to know if at least in the happy scenario the transaction occurs successfully (commit). After that, you need to do some testing to see if we’re able to do the rollback in case of exceptions. It is better that you isolate the resources: first you have to guarantee a very simple case that works, IE, a certain exception class released in java should cause the rollback. And then go forward. Remember that not all exceptions in java would cause a rollback. That is, systemException does rollback, application Exception do not rollback unless you invoke the setRollBackOnly method(). (https://docs.oracle.com/javaee/6/tutorial/doc/bncij.html#bnciv)

Browser other questions tagged java glassfish pl-sql ejb transactions

You are not signed in. Login or sign up in order to post.