Wednesday, November 13, 2013

Overriding Entity doDML or prepareForDml Causes Locking Issues

It is not uncommon for people to override the doDML or prepareForDml method in an ADF BC Entity to do some additional work just before posting changes to the database. The official documentation even describes this extension point as being an equivalent to the Oracle Forms Post Processing triggers.
Today I discovered this can lead to locking issues in the database when this additional work changes other view objects or entities. The investigation is based on JDeveloper version 11.1.1.7 and this behavior might differ for other versions.

Update Nov 14: The issue doesn't reproduce in ADF 12.1.2.0. Inspecting of the source code seems to indicate (unpublished) bug 11896369 is to blame. Currently working with Oracle support to further investigate and ask for fix backport to 11.1.1.7

Update Jan 2: Oracle development acknowledged this behavior is caused by bug 11896369 and a backport request has been filed to bring the fix to 11.1.1.7.

Update Jan 10: Patch 11896369 is now available for download.

Let's start with an simplified example of an entity mutation another one. Below is the doDML method of the Department entity. Whenever a change happens to a department it sets the commission of its first employee to 99%. Not a very likely business scenario, but enough to demonstrate the issue.

@Override
protected void doDML(int i, TransactionEvent transactionEvent) {
    RowIterator employees = getEmployees();
    EmployeeImpl firstEmployee = (EmployeeImpl)employees.first();
    try {
        firstEmployee.setCommissionPct(new Number(0.99));
    } catch (SQLException e) {
        throw new JboException(e);
    }
    super.doDML(i, transactionEvent);
}

What we also need is a entity level validation on Employee that will be violated by this change. For demonstration purposes I've setup a range validation for CommissionPct to be between 0.00 and 0.50:
Entity-level Validation Rule

Now let's build a very simple ADF page to change a department and try to save the changes to the database. Here is what happens:
Validation Error in ADF Application

Everything appears to be okay. The user is confronted with the validation error as expected. But now let's try to update the same record from a different database session:
Database record locked after validation exception

This shows the database record is still locked. This is strange as this ADF BC application is set to its default of pessimistic locking. This means it should only lock the record while doing the DML and not keep the lock across requests. Further investigation points to the commitInternal method in the oracle.jbo.server.DBTransactionImpl class. I've simplified its code in pseudo-code below:

try {
  while (hasObjectsToPost()) {
    while (hasObjectsToValidate()) {
      JboException jboEx = null;
      try {
        validateAllObjects();
      } catch (JboException e) {
        jboEx = e;  // remember exception
        break; // abort hasObjectsToValidate loop
      }
    }
    if (jboEx != null) throw jboEx;
    setSavepoint();
    passivateStateForUndo();
    try {
      postChanges();
    } catch (RuntimeException r) {
      rollbackToSavepoint();
      throw r; // abort hasObjectsToPost
    }
  }
  try {
    doCommit();
  } catch (RuntimeException r) {
    rollbackToSavepoint();
    activateStateForUndo();
  }
} finally {
  clearSavePoint();
}

Let's ignore the loops for now. In line 6 all objects with pending changes are validated. In our example this is only the Department as that is the only entity the user changes from the user interface. This Department has no validation issues, so no exception is caught in lines 7-9 and no exception is re-thrown in line 12.

So the process continues at line 13 to set a database savepoint and then continues posting the real changes (performing DML) in line 16. This is where the prepareForDml and doDML on all changes entities is invoked. This is where DepartmentImpl is performing its SQL UPDATE statement. This is also where our DepartmentImpl makes changes to an EmployeeImpl entity. This does not throw any exception since EmployeeImpl has an entity-level validation which is not validated directly when changing an attribute but is postponed until the entity is saved to the database. So we never get to the exception handling in line 18 that might have performed a rollback.

We have now finished the first try of validation and posting changes but we have changed entities left. This means the loop at line 2 is going for another iteration. In line 6 we are again validating all entities with pending changes. In this second iteration, that is only the EmployeeImpl. This time the entity-level validations occur and an exception is thrown. We end up in the exception handling in line 7-9 where we keep the validation exception and abort the validation loop. The validation exception is then re-thrown in line 12. This immediately jumps to line 29 where the database savepoint is cleared, but we never rollback to that savepoint as the exception handling around postChanges and doCommit would have done. This means the already changed row in the Departments table is still changed (and thus locked).

I think this is a bug in ADF and a service request has been filed with Oracle Support. As long as this is not fixed, you can implement a workaround. Whenever you manipulate other objects from the prepareForDml or doDML be sure to force validation yourself. This would not only abort the DML on the entity itself but it also means the postChanges in line 16 of our DBTransactionImpl::commitInternal pseudo-code would throw an exception and a rollback of any changes posted by other entities occurs. To implement this you only need a simple change to the code in DepartmentImpl (see line 10):

@Override
protected void doDML(int i, TransactionEvent transactionEvent) {
    RowIterator employees = getEmployees();
    EmployeeImpl firstEmployee = (EmployeeImpl)employees.first();
    try {
        firstEmployee.setCommissionPct(new Number(0.99));
    } catch (SQLException e) {
        throw new JboException(e);
    }
    firstEmployee.validate();
    super.doDML(i, transactionEvent);
}

As always you can download the full sample application or browse the subversion repository to look at the source code.