1

I read that JPA caches SQL instructions to improve performance:

JPA providers like Hibernate can cache the SQL instructions they are supposed to send to the database, often until you actually commit the transaction. For example, you call em.persist(), Hibernate remembers it has to make a database INSERT, but does not actually execute the instruction until you commit the transaction.

I have a Java EE 6 application deployed to a Glassfish cluster with two instances. In the application there is a race condition where two Singletons do some expensive queries and then cache the results in a database table. They're doing the same work and trying to write the same record, so I sometimes get an exception:

java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SOMESCHEMA.SOMETABLE_PK) violated

I decided the easiest way to deal with this would be to catch and ignore the exception:

// In a EJB with container-managed transactions.
public Entity getExpensiveEntity(int entityId) {
    Entity entity = entityManager.find(Entity.class, entityId);
    if (entity == null) {
        try {
            result = expensiveQueries();
            entityManager.persist(result);
            entityManager.flush();
        } catch (SQLIntegrityConstraintViolationException ex) {
            // The other instance already created the result, so get it.
            result = jpa.find(result.getId());
        }
    }
    return result;
}

I think the call to flush is necessary because otherwise the SQLIntegrityConstraintViolationException won't occur until the transaction ends somewhere up the EJB call stack, past catching and ignoring. Am I correct, is this a valid use case for flush? Is there a better way to handle this?

Reference

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
DavidS
  • 5,022
  • 2
  • 28
  • 55
  • in this case the second cluster item does the same expensive work as well, doesn't it? what is the goal? is it to avoid the duplicated work? – The Bitman Apr 12 '17 at 09:29
  • @TheBitman Thanks for asking. The end goal is to have the expensive work result "cached" in the database table because the result is too large to cache in memory. Subsequent calls will use this cache. There is no reason for both instances to try to create the cache, but I don't know of any mechanism to instruct only one instance to create the cache, so I thought the easy thing to do would be to let them both try to create it and ignore the exception caused by the race condition. – DavidS Apr 12 '17 at 18:18
  • 1
    there are some scenarios: 1., the entity found (ready to use). It is clear, just pass it back. 2/a, then entity not found (we are in race). we should register ourselves to notification. if we win (we reach the very end of the process without any exception), we persist the entity and send a JMS message to notify the competitors and pass back the result.2/b If an exception raises we should wait for the result contained by the notification message. – The Bitman Apr 12 '17 at 18:41
  • 1
    The registered message handlers called by the MDB and it sets our entity from null to the sent value (we check in the catch block if the entity is null. null = wait, not null exit the cycle). if you create the entity in the very beginning of the process and lock it, the exception raise soon for the competitors. – The Bitman Apr 12 '17 at 18:41

0 Answers0